Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Marcus Andree S. Magalhaes
Marcus, We are experiencing exactly the same problem here, and we use 7.4 on Linux/i386 SMP (2 processors). Our databases does even more access: about 30k selects per hour, 10k updates and inserts per hour Vacuum analyze is done daily. What is your max_fsm_pages setting? If you are

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Fabio Esposito
On Fri, 26 Mar 2004, Josh Berkus wrote: Fabio, Recently it started eating up the cpu, and cannot keepup with the system like it used to. The interesting thing here is that it still runs great on an older system with less ram, one slower cpu, and an older disk. This really points to

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Mark Lubratt
On Mar 29, 2004, at 9:36 AM, Tom Lane wrote: Marcus Andree S. Magalhaes [EMAIL PROTECTED] writes: Also do you run VACUUM ANALYZE as a superuser, or as a regular user? As a regular user (database owner). Is thery any difference when vacuuming as a super user? That's your problem. A regular user

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Josh Berkus
Fabio, We run VACUUM ANALYZE after we remove about 1000 rows every hour on the halh hour. Our max_fsm_pages is set to 1 Have you checked how long these vacuums take? If they are starting to overlap, that would explain your high CPU usage and poor performance.You might want to

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Fabio Esposito
The Vacuum's don't take too long, 10 minutes at most. I can tell from ps -ef | grep and top that its the selects/inserts/updates from the postgres related to our app that take all that time up. If we rerun initdb and reload the data, it works great for about two days, then goes bad again. We

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-29 Thread Josh Berkus
Fabio, The Vacuum's don't take too long, 10 minutes at most. I can tell from ps -ef | grep and top that its the selects/inserts/updates from the postgres related to our app that take all that time up. If we rerun initdb and reload the data, it works great for about two days, then goes bad

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-28 Thread Josh Berkus
Marcus, We are experiencing exactly the same problem here, and we use 7.4 on Linux/i386 SMP (2 processors). Our databases does even more access: about 30k selects per hour, 10k updates and inserts per hour Vacuum analyze is done daily. What is your max_fsm_pages setting? If you are

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-27 Thread Marcus Andree S. Magalhaes
snip We are experiencing exactly the same problem here, and we use 7.4 on Linux/i386 SMP (2 processors). Our databases does even more access: about 30k selects per hour, 10k updates and inserts per hour Vacuum analyze is done daily. We migrated our database to a new server. Initially,

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread Josh Berkus
Fabio, Postgres initially worked wonderfully, fast and solid. It preformed complex joins in 0.01secs, and was able to keep up with our message queue. It stayed this way for almost a year during our development. Recently it started eating up the cpu, and cannot keepup with the system

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread scott.marlowe
On Fri, 26 Mar 2004, Fabio Esposito wrote: On Fri, 26 Mar 2004, scott.marlowe wrote: It maintains 48hours of data, so its not a large database; roughly 600mbs. We do this by running a housekeeping program in a cron job. It deletes all data older then 48hours, then vaccuum analyzes.

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread Josh Berkus
Fabio, I'll have to get back to you on that, but I'm 90% sure its the default out of the box. Raise it, a lot. Perhaps to 30,000 or 50,000. VACUUM VERBOSE ANALYZE should show you how many data pages are being reclaimed between vacuums. Because of your very high rate of updates and

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread scott.marlowe
On Tue, 23 Mar 2004, Fabio Esposito wrote: Hello fellow PostgreSQL users. We've been working on this interesting issue for some time now, and we're hoping that someone can help. We've recently integrated postgres into an existing mature app. Its a time sensitive 24x7 system. It runs

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread Tom Lane
Fabio Esposito [EMAIL PROTECTED] writes: Did you start from a fresh initdb, or just drop and recreate user tables? I'm wondering about index bloat on the system tables ... I don't think I re initdb it, just dropped. We did try a reindex command in the interactive editor, with no success.

Re: [PERFORM] postgres eating CPU on HP9000

2004-03-26 Thread Tom Lane
Fabio Esposito [EMAIL PROTECTED] writes: We've recently integrated postgres into an existing mature app. Its a time sensitive 24x7 system. It runs on HP9000, a K370 Dual Processor system. Postgres is version 7.3.2. Its spawned as a child from a parent supervisory process, and they