Обсуждение: 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
Is there any idle connections exists ? -- Thanks Sam Jas --- On Fri, 20/11/09, Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it> wrote:
|
The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
2009/11/20 Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it>: > > 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). I had a similar problem: I did a large delete, and then a selct which "covered" the previous rows. It took ages, because the index still had those deleted rows. Possibly the same happens with update. Try this: vacuum analyse reindex database .... (your database name instead of ...) or, rather do this table by table: vacuum analyse .... reindex table ... Autovacuum is a generally good thing. > So, my main question is.. how can just a plain simple restart of postgres > restore the original performance (3% cpu time)? there were probably some long transactions running. Stopping postgres effectively kills them off. > I can post my postgresql.conf if needed. > Thank you for your help, > > -- > Lorenzo > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it> writes: > So, my main question is.. how can just a plain simple restart of postgres > restore the original performance (3% cpu time)? Are you killing off any long-running transactions when you restart? regards, tom lane
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
Sam Jas wrote: > > Is there any idle connections exists ? I didn't see any, I'll look better next time.
Brian Modra wrote: > I had a similar problem: I did a large delete, and then a selct which > "covered" the previous rows. > It took ages, because the index still had those deleted rows. > Possibly the same happens with update. > > Try this: > vacuum analyse > reindex database .... > (your database name instead of ...) > > or, rather do this table by table: > vacuum analyse .... > reindex table ... > > > Autovacuum is a generally good thing. > >> So, my main question is.. how can just a plain simple restart of postgres >> restore the original performance (3% cpu time)? > > there were probably some long transactions running. Stopping postgres > effectively kills them off. I'll try that, thanks for your help Brian.
Tom Lane wrote: > Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it> writes: >> So, my main question is.. how can just a plain simple restart of postgres >> restore the original performance (3% cpu time)? > > Are you killing off any long-running transactions when you restart? After three days of patient waiting it looks like the common '<IDLE> in transaction' problem.. [sorry for >80 cols] 19329 ? S 15:54 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf 19331 ? Ss 3:40 \_ postgres: writer process 19332 ? Ss 0:42 \_ postgres: wal writer process 19333 ? Ss 15:01 \_ postgres: stats collector process 19586 ? Ss 114:00 \_ postgres: forinicom weadmin [local] idle 20058 ? Ss 0:00 \_ postgres: forinicom weadmin [local] idle 13136 ? Ss 0:00 \_ postgres: forinicom weadmin 192.168.4.253(43721) idle in transaction My app is a Django webapp, maybe there's some bug in the Django+psycopg2 stack? Anyway, how can I get rid those "idle in transaction" processes? Can I just kill -15 them or is there a less drastic way to do it?
In response to Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it>: > Tom Lane wrote: > > Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it> writes: > >> So, my main question is.. how can just a plain simple restart of postgres > >> restore the original performance (3% cpu time)? > > > > Are you killing off any long-running transactions when you restart? > > After three days of patient waiting it looks like the common > '<IDLE> in transaction' problem.. > > [sorry for >80 cols] > > 19329 ? S 15:54 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf > 19331 ? Ss 3:40 \_ postgres: writer process > 19332 ? Ss 0:42 \_ postgres: wal writer process > 19333 ? Ss 15:01 \_ postgres: stats collector process > 19586 ? Ss 114:00 \_ postgres: forinicom weadmin [local] idle > 20058 ? Ss 0:00 \_ postgres: forinicom weadmin [local] idle > 13136 ? Ss 0:00 \_ postgres: forinicom weadmin 192.168.4.253(43721) idle in transaction > > My app is a Django webapp, maybe there's some bug in the Django+psycopg2 stack? > > Anyway, how can I get rid those "idle in transaction" processes? > Can I just kill -15 them or is there a less drastic way to do it? Connections idle in transaction do not cause performance problems simply by being there, at least not when there are so few. If you -TERM them, any uncommitted data will be rolled back, which may not be what you want. Don't -KILL them, that will upset the postmaster. My answer to your overarching question is that you need to dig deeper to find the real cause of your problem, you're just starting to isolate it. Try turning full query logging on and track what those connections are actually doing. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Bill Moran <wmoran@potentialtech.com> writes: > In response to Lorenzo Allegrucci <lorenzo.allegrucci@forinicom.it>: >> Tom Lane wrote: >>> Are you killing off any long-running transactions when you restart? >> Anyway, how can I get rid those "idle in transaction" processes? >> Can I just kill -15 them or is there a less drastic way to do it? > Connections idle in transaction do not cause performance problems simply > by being there, at least not when there are so few. The idle transaction doesn't eat resources in itself. What it does do is prevent VACUUM from reclaiming dead rows that are recent enough that they could still be seen by the idle transaction. The described behavior sounds to me like other transactions are wasting lots of cycles scanning through dead-but-not-yet-reclaimed rows. There are some other things that also get slower as the window between oldest and newest active XID gets wider. (8.4 alleviates this problem in many cases, but the OP said he was running 8.3.) > If you -TERM them, any uncommitted data will be rolled back, which may > not be what you want. Don't -KILL them, that will upset the postmaster. -TERM isn't an amazingly safe thing either in 8.3. Don't you have a way to kill the client-side sessions? > My answer to your overarching question is that you need to dig deeper to > find the real cause of your problem, you're just starting to isolate it. Agreed, what you really want to do is find and fix the transaction leak on the client side. regards, tom lane
You may use connection pooling for "idle connections" like pgbouncer or pgpool. Following link will give you details about pgbouncer & pgpool. https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html Hope it may help you!!! -- Thanks Sam Jas --- On Mon, 23/11/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
|
The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote: > Anyway, how can I get rid those "idle in transaction" processes? > Can I just kill -15 them or is there a less drastic way to do it? Are you crazy? Sure, if you want to destroy all of the changes made to the database in that transaction and thoroughly confuse the client application, you can send a TERM signal to a backend, but the consequences to your data are on your own head. Fix the application, don't tell Postgres to stop being a decent database. Matthew -- I would like to think that in this day and age people would know better than to open executables in an e-mail. I'd also like to be able to flap my arms and fly to the moon. -- Tim Mullen
Matthew Wakeling wrote: > On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote: >> Anyway, how can I get rid those "idle in transaction" processes? >> Can I just kill -15 them or is there a less drastic way to do it? > > Are you crazy? Sure, if you want to destroy all of the changes made to > the database in that transaction and thoroughly confuse the client > application, you can send a TERM signal to a backend, but the > consequences to your data are on your own head. I'm not crazy, it was just a question.. Anyway, problem solved in the Django application.
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
Lorenzo Allegrucci escribió: > Matthew Wakeling wrote: >> On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote: >>> Anyway, how can I get rid those "idle in transaction" processes? >>> Can I just kill -15 them or is there a less drastic way to do it? >> >> Are you crazy? Sure, if you want to destroy all of the changes made >> to the database in that transaction and thoroughly confuse the client >> application, you can send a TERM signal to a backend, but the >> consequences to your data are on your own head. > > I'm not crazy, it was just a question.. > Anyway, problem solved in the Django application. > > Matthew replied to you of that way because this is not a good manner to do this, not fot thr fact that you are crazy. You can find better ways to do this. Regards
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?? On 11/24/09, Matthew Wakeling <matthew@flymine.org> wrote: > On Mon, 23 Nov 2009, Lorenzo Allegrucci wrote: >> Anyway, how can I get rid those "idle in transaction" processes? >> Can I just kill -15 them or is there a less drastic way to do it? > > Are you crazy? Sure, if you want to destroy all of the changes made to the > database in that transaction and thoroughly confuse the client > application, you can send a TERM signal to a backend, but the consequences > to your data are on your own head. > > Fix the application, don't tell Postgres to stop being a decent database. > > Matthew > > -- > I would like to think that in this day and age people would know better > than > to open executables in an e-mail. I'd also like to be able to flap my arms > and fly to the moon. -- Tim Mullen > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
Bouncing the app will roll back the transactions. If there were any pending updates/inserts, wouldn't he be able to see them in one of the system tables... On 11/24/09, Matthew Wakeling <matthew@flymine.org> wrote: > 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 >
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