Re: [PERFORM] Strange performance degradation
On Tue, 24 Nov 2009, Denis Lussier wrote: Bouncing the app will roll back the transactions. Depends on the application. Some certainly use a shutdown hook to flush data out to a database cleanly. Obviously if you kill -9 it, then all bets are off. Matthew -- Software suppliers are trying to make their software packages more 'user-friendly' Their best approach, so far, has been to take all the old brochures, and stamp the words, 'user-friendly' on the cover. -- Bill Gates -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance degradation
On Tue, 24 Nov 2009, Denis Lussier wrote: IMHO the client application is already confused and it's in Prod. Shouldn't he perhaps terminate/abort the IDLE connections in Prod and work on correcting the problem so it doesn't occur in Dev/Test?? The problem is, the connection isn't just IDLE - it is idle IN TRANSACTION. This means that there is quite possibly some data that has been modified in that transaction. If you kill the backend, then that will automatically roll back the transaction, and all of those changes would be lost. I agree that correcting the problem in dev/test is the priority, but I would be very cautious about killing transactions in production. You don't know what data is uncommitted. The safest thing to do may be to bounce the application, rather than Postgres. Matthew -- All of this sounds mildly turgid and messy and confusing... but what the heck. That's what programming's all about, really -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Strange performance degradation
Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? I can post my postgresql.conf if needed. Thank you for your help, -- Lorenzo -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance degradation
In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance degradation
A. Kretschmer wrote: In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. Why running vacuum by hand is a bad idea? vacuum doesn't solve anyway, it seems only a plain restart stops the performance degradation. So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. below is the output of vacuum analyze verbose (NOTE: I've already run vacuum this morning, this is a second run) DETAIL: A total of 58224 page slots are in use (including overhead). 58224 page slots are required to track all free space. Current limits are: 200 page slots, 1000 relations, using 11784 kB. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance degradation
Lorenzo Allegrucci lorenzo.allegrucci 'at' forinicom.it writes: A. Kretschmer wrote: In response to Lorenzo Allegrucci : Hi all, I'm experiencing a strange behavior with my postgresql 8.3: performance is degrading after 3/4 days of running time but if I just restart it performance returns back to it's normal value.. In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. The load of my server is composed of many INSERTs on a table, and many UPDATEs and SELECT on another table, no DELETEs. I tried to run vacuum by the pg_maintenance script (Debian Lenny) but it doesn't help. (I have autovacuum off). Bad idea. Really. Why running vacuum by hand is a bad idea? It's rather turning autovacuum off which is a bad idea. vacuum doesn't solve anyway, it seems only a plain restart stops the performance degradation. Notice: normally, restarting doesn't help for vacuum-related problems. Your degradation might come from a big request being intensive on PG's and OS's caches, resulting in data useful to other requests getting farther (but it should get back to normal if the big request is not performed again). And btw, 25% is far from 100% so response time should be the same if there are no other factors; you should rather have a look at IOs (top, vmstat, iostat) during problematic time. How do you measure your degradation, btw? So, my main question is.. how can just a plain simple restart of postgres restore the original performance (3% cpu time)? You should enable autovacuum. And you should run vacuum verbose manually and see the output. below is the output of vacuum analyze verbose (NOTE: I've already run vacuum this morning, this is a second run) DETAIL: A total of 58224 page slots are in use (including overhead). 58224 page slots are required to track all free space. Current limits are: 200 page slots, 1000 relations, using 11784 kB. Which means your FSM settings look fine; but doesn't mean your database is not bloated (and with many UPDATEs and no correct vacuuming, it should be bloated). One way to know is to restore a recent backup, issue VACUUM VERBOSE on a table known to be large and regularly UPDATE's/DELETE'd on both databases (in production, and on the restore) and compare the reported number of pages needed. The difference is the potential benefit of running VACUUM FULL (or CLUSTER) in production (once your DB is bloated, a normal VACUUM doesn't remove the bloat). db_production=# VACUUM VERBOSE table; [...] INFO: table: found 408 removable, 64994 nonremovable row versions in 4395 pages db_restored=# VACUUM VERBOSE table; [...] INFO: table: found 0 removable, 64977 nonremovable row versions in 628 pages In that 628/4395 example, we have 85% bloat in production. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Strange performance degradation
On Fri, 20 Nov 2009, Lorenzo Allegrucci wrote: performance is degrading... In normal conditions the postgres process uses about 3% of cpu time but when is in degraded conditions it can use up to 25% of cpu time. You don't really give enough information to determine what is going on here. This could be one of two situations: 1. You have a constant incoming stream of short-lived requests at a constant rate, and Postgres is taking eight times as much CPU to service it as normal. You're looking at CPU usage in aggregate over long periods of time. In this case, we should look at long running transactions and other slowdown possibilities. 2. You are running a complex query, and you look at top and see that Postgres uses eight times as much CPU as when it has been freshly started. In this case, the performance degradation could actually be that the data is more in cache, and postgres is able to process it eight times *faster*. Restarting Postgres kills the cache and puts you back at square one. Which of these is it? Matthew -- Reality is that which, when you stop believing in it, doesn't go away. -- Philip K. Dick -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
