Re: [GENERAL] Strange performance degradation
Brian Modra
>
> 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-...@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/
--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Bill Moran
> Tom Lane wrote:
> > Lorenzo Allegrucci <lorenzo.a...@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/
Tom Lane
> In response to Lorenzo Allegrucci <lorenzo.a...@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
Denis Lussier
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 <mat...@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-pe...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Denis Lussier
pending updates/inserts, wouldn't he be able to see them in one of the
system tables...
On 11/24/09, Matthew Wakeling <mat...@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