Re: [PERFORM] postgres: 100% CPU utilization
Thanks for this reply, Ron. This is almost what I was looking for. While the upgrade to the latest version is out of the question (which unfortunately for me became the subject of this discussion) still, I was looking for the ways to improve the performance of the 7.0.2 version. Extensive use of vacuum was almost obvious, though I was hoping to get some more tips from postrges gurus (or dinosaurs, if you want). Anyways, the 8.2.4 was not performing so well without auto-vacuum. It ramped up to 50% of CPU usage in 2 hours under the load. With the auto-vacuum ON I've got what I really need and thus I know what to do next. Just for the record - the hardware that was used for the test has the following parameters: AMD Opteron 2GHZ 2GB RAM LSI Logic SCSI Thanks everyone for your assistance! Sergey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Sent: Monday, April 23, 2007 11:07 AM To: Mario Weilguni Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] postgres: 100% CPU utilization At 04:53 AM 4/23/2007, Mario Weilguni wrote: Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky: 2. What would be the recommended set of parameters to tune up in order to improve the performance over the time, instead of considering an option to vacuum every 30 minutes or so? 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes? No problem. 4. Suggestions? Do yourself a favor and upgrade at least to 8.1.x and use autovacuum. In fact, I'll go one step further and say that pg improves so much from release to release that everyone should make superhuman efforts to always be running the latest stable release. Even the differences between 8.1.x and 8.2.x are worth it. (and the fewer and more modern the releases out in the wild, the easier community support is) Cheers, Ron Peacetree ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] postgres: 100% CPU utilization
On Tue, 2007-04-24 at 10:30, Sergey Tsukinovsky wrote: Thanks for this reply, Ron. This is almost what I was looking for. While the upgrade to the latest version is out of the question (which unfortunately for me became the subject of this discussion) still, I was looking for the ways to improve the performance of the 7.0.2 version. Extensive use of vacuum was almost obvious, though I was hoping to get some more tips from postrges gurus (or dinosaurs, if you want). Anyways, the 8.2.4 was not performing so well without auto-vacuum. It ramped up to 50% of CPU usage in 2 hours under the load. With the auto-vacuum ON I've got what I really need and thus I know what to do next. Could you give us a better picture of how you were testing 8.2.4? My guess is that you were doing something that seemed right to you, but was working against yourself, like constant vacuum fulls and getting index bloat, or something else. Why were you trying to not use autovacuum, btw? I've found it to be quite capable, with only a few situations (high speed queueing) where I needed to manually schedule vacuums. And I've never seen a situation since about 7.4 where regular full vacuums were required. Just for the record - the hardware that was used for the test has the following parameters: AMD Opteron 2GHZ 2GB RAM LSI Logic SCSI Nice hardware. I'd really like to hear the logic behind your statement that upgrading to 8.1 or 8.2 is out of the question. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] postgres: 100% CPU utilization
Sergey Tsukinovsky wrote: Just for the record - the hardware that was used for the test has the following parameters: AMD Opteron 2GHZ 2GB RAM LSI Logic SCSI And you ran FreeBSD 4.4 on it right? This may be a source of high cpu utilization in itself if the box is SMP or dual core, as multi-cpu support was pretty primitive in that release (4.12 would be better if you are required to stick to the 4.x branch, if not the 6.2 is recommended)! Cheers Mark ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] postgres: 100% CPU utilization
On Thu, 19 Apr 2007, Sergey Tsukinovsky wrote: I know that 7.0.2 is an old version and therefore ran the same test on 7.3.18 - the performance behavior was similar. Why have you choosen just another very old version for performance comparison and not the latest stable release? Kind regards Andreas. -- http://fam-tille.de ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] postgres: 100% CPU utilization
Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky: 2. What would be the recommended set of parameters to tune up in order to improve the performance over the time, instead of considering an option to vacuum every 30 minutes or so? 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes? No problem. 4. Suggestions? Do yourself a favor and upgrade at least to 8.1.x and use autovacuum. Best regards Mario Weilguni ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] postgres: 100% CPU utilization
At 04:53 AM 4/23/2007, Mario Weilguni wrote: Am Donnerstag, 19. April 2007 schrieb Sergey Tsukinovsky: 2. What would be the recommended set of parameters to tune up in order to improve the performance over the time, instead of considering an option to vacuum every 30 minutes or so? 3. Is it safe to run 'vacuum' as frequently as every 15-30 minutes? No problem. 4. Suggestions? Do yourself a favor and upgrade at least to 8.1.x and use autovacuum. In fact, I'll go one step further and say that pg improves so much from release to release that everyone should make superhuman efforts to always be running the latest stable release. Even the differences between 8.1.x and 8.2.x are worth it. (and the fewer and more modern the releases out in the wild, the easier community support is) Cheers, Ron Peacetree ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] postgres: 100% CPU utilization
On Thu, 2007-04-19 at 14:29, Sergey Tsukinovsky wrote: Hi, I’m currently dealing with performance issues of postgres and looking for some advice. Platform Postgres: 7.0.2 OS: FreeBSD4.4 DB: size - about 50M, most frequently updated tables are of an average size of 1000-2000 rows and there are not many of them, about 15 in total SNIP I know that 7.0.2 is an old version and therefore ran the same test on 7.3.18 – the performance behavior was similar. So, are you running this on an Intel 486DX2-50 with a Seagate ST-4096 with an ISA based RLL encoding controller, or are you using the more advanced AMD 586 CPU running at 90MHz with an Adaptec ARC2090 PCI based SCSI card? And do you have 32 or 64 Megs of memory in that machine? Cause honestly, that's the kinda hardware I was running 7.0.2 on, so you might as well get retro in your hardware department while you're at it. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] postgres: 100% CPU utilization
On Apr 23, 2007, at 12:09 PM, Scott Marlowe wrote: And do you have 32 or 64 Megs of memory in that machine? Cause honestly, that's the kinda hardware I was running 7.0.2 on, so you might as well get retro in your hardware department while you're at it. I think you're being too conservative... I recall that those specs for me correspond to running Pg 6.5 as the latest release... talk about performance and corruption issues... :-) He's probably got at least a Pentium II. smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] postgres: 100% CPU utilization
On Mon, 2007-04-23 at 15:00, Vivek Khera wrote: On Apr 23, 2007, at 12:09 PM, Scott Marlowe wrote: And do you have 32 or 64 Megs of memory in that machine? Cause honestly, that's the kinda hardware I was running 7.0.2 on, so you might as well get retro in your hardware department while you're at it. I think you're being too conservative... I recall that those specs for me correspond to running Pg 6.5 as the latest release... talk about performance and corruption issues... :-) He's probably got at least a Pentium II. Yeah, now that you mention it, I think I was able to come up with a Pentium 100 with 64 Megs of RAM about the time 6.5 came out, on RedHat 5.1 then 5.2, and with a pair of 1.2 gig IDE drives under the hood. Those were the days, huh? I honestly kinda wondered if the original post came out of a time warp, like some mail relay somewhere held onto it for 4 years or something. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgres: 100% CPU utilization
Scott Marlowe wrote: (snippage) that's the kinda hardware I was running 7.0.2 on, so you might as well get retro in your hardware department while you're at it. Notice he's running FreeBSD 4.4(!), so it could well be a very old machine... Cheers Mark ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] postgres: 100% CPU utilization
On Mon, 23 Apr 2007, Scott Marlowe wrote: I honestly kinda wondered if the original post came out of a time warp, like some mail relay somewhere held onto it for 4 years or something. That wouldn't be out of the question if this system is also his mail server. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] postgres: 100% CPU utilization
Sergey Tsukinovsky [EMAIL PROTECTED] writes: I'm currently dealing with performance issues of postgres and looking for some advice. Postgres: 7.0.2 Stop right there. You have *no* business asking for help on an installation you have not updated in more than six years. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
