Re: [PERFORM] [GENERAL] Strange performance degradation
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
Re: [PERFORM] [GENERAL] Strange performance degradation
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. -- 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] [GENERAL] Strange performance degradation
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 -- 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] [GENERAL] Strange performance degradation
Tom Lane wrote: Lorenzo Allegrucci lorenzo.allegru...@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 ?Ss15: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? -- 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] [GENERAL] Strange performance degradation
Bill Moran wmo...@potentialtech.com writes: In response to Lorenzo Allegrucci lorenzo.allegru...@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 -- 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] [GENERAL] Strange performance degradation
Lorenzo Allegrucci lorenzo.allegru...@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 -- 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] [GENERAL] Strange performance degradation
Sam Jas wrote: Is there any idle connections exists ? I didn't see any, I'll look better next time. -- 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] [GENERAL] Strange performance degradation
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
