pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issues with postgresql-8.4.0
5+ messages / 4 participants
[nested] [flat]
* Performance issues with postgresql-8.4.0
@ 2010-06-29 07:01 Sachin Kumar <Sachin.Ku@globallogic.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sachin Kumar @ 2010-06-29 07:01 UTC (permalink / raw)
To: pgsql-performance
Hi,
We are using postgresql-8.4.0 on 64-bit Linux machine (open-SUSE 11.x).
It's a master/slave deployment & slony-2.0.4.rc2 is used for DB
replication on the slave box.
At times we have observed that postgres stops responding for several
minutes, even couldn't fetch the number of entries in a particular
table. One such instance happens when we execute the following steps:
- Add few lakh entries (~20) to table X on the master DB.
- After addition, slony starts replication on the slave DB. It
takes several minutes (~25 mins) for replication to finish.
- During this time (while replication is in progress), sometimes
postgres stops responding, i.e. we couldn't even fetch the number of
entries in any table (X, Y, etc).
Can you please let us know what could the reason for such a behavior and
how it can be fixed/improved.
Regards,
Sachin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Performance issues with postgresql-8.4.0: Query gets stuck sometimes
@ 2010-07-02 05:40 Sachin Kumar <Sachin.Ku@globallogic.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sachin Kumar @ 2010-07-02 05:40 UTC (permalink / raw)
To: pgsql-performance; +Cc: Vivek Gupta <vivek.gupta@globallogic.com>
Hi,
We are using postgresql-8.4.0 on 64-bit Linux machine (open-SUSE 11.x).
It's a master/slave deployment & slony-2.0.4.rc2 is used for DB
replication (from master to slave).
At times we have observed that postgres stops responding for several
minutes, even couldn't fetch the number of entries in a particular
table. One such instance happens when we execute the following steps:
- Add few lakh entries (~20) to table X on the master DB.
- After addition, slony starts replication on the slave DB. It
takes several minutes (~25 mins) for replication to finish.
- During this time (while replication is in progress), sometimes
postgres stops responding, i.e. we couldn't even fetch the number of
entries in any table (X, Y, etc).
Can you please let us know what could the reason for such a behavior and
how it can be fixed/improved.
Please let us know if any information is required wrt hardware
details/configurations etc.
Regards,
Sachin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issues with postgresql-8.4.0: Query gets stuck sometimes
@ 2010-07-02 05:50 Scott Marlowe <scott.marlowe@gmail.com>
parent: Sachin Kumar <Sachin.Ku@globallogic.com>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-07-02 05:50 UTC (permalink / raw)
To: Sachin Kumar <Sachin.Ku@globallogic.com>; +Cc: pgsql-performance@postgresql.org, Vivek Gupta <vivek.gupta@globallogic.com>
On Fri, Jul 2, 2010 at 1:40 AM, Sachin Kumar <Sachin.Ku@globallogic.com> wrote:
> Hi,
>
> We are using postgresql-8.4.0 on 64-bit Linux machine (open-SUSE 11.x). It’s
> a master/slave deployment & slony-2.0.4.rc2 is used for DB replication (from
> master to slave).
You should really be running 8.4.4, not 8.4.0, as there are quite a
few bug fixes since 8.4.0 was released.
slony 2.0.4 is latest, and I'm not sure I trust it completely just
yet, and am still running 1.2.latest myself. At least move forward
from 2.0.4.rc2 to 2.0.4 release.
> At times we have observed that postgres stops responding for several
> minutes, even couldn’t fetch the number of entries in a particular table.
Note that retrieving the number of entries in a table is not a cheap
operation in pgsql. Try something cheaper like "select * from
sometable limit 1;" and see if that responds. If that seems to hang,
open another session and see what select * from pg_statistic has to
say about waiting queries.
> One such instance happens when we execute the following steps:
>
> - Add few lakh entries (~20) to table X on the master DB.
Note that most westerner's don't know what a lakh is. (100k I believe?)
> - After addition, slony starts replication on the slave DB. It takes
> several minutes (~25 mins) for replication to finish.
>
> - During this time (while replication is in progress), sometimes
> postgres stops responding, i.e. we couldn’t even fetch the number of entries
> in any table (X, Y, etc).
I have seen some issues pop up during subscription of large sets like
this. Most of the time you're just outrunning your IO subsystem.
Occasionally a nasty interaction between slony, autovacuum, and user
queries causes a problem.
> Can you please let us know what could the reason for such a behavior and how
> it can be fixed/improved.
You'll need to see what's happening on your end. If pg_statistic says
your simple select * from X limit 1 is waiting, we'll go from there.
If it returns but bigger queries take a long time you've got a
different issue and probably need to monitor your IO subsystem with
things like iostat, vmstat, iotop, etc.
> Please let us know if any information is required wrt hardware
> details/configurations etc.
Always useful to have.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issues with postgresql-8.4.0
@ 2010-07-04 12:38 Craig Ringer <craig@postnewspapers.com.au>
parent: Sachin Kumar <Sachin.Ku@globallogic.com>
0 siblings, 0 replies; 5+ messages in thread
From: Craig Ringer @ 2010-07-04 12:38 UTC (permalink / raw)
To: Sachin Kumar <Sachin.Ku@globallogic.com>; +Cc: pgsql-performance
On 29/06/10 15:01, Sachin Kumar wrote:
> At times we have observed that postgres stops responding for several
> minutes, even couldn't fetch the number of entries in a particular
> table.
Quick guess: checkpoints. Enable checkpoint logging, follow the logs,
see if there's any correspondance.
In general, looking at the logs might help you identify the issue.
One such instance happens when we execute the following steps:
>
> - Add few lakh entries (~20) to table X on the master DB.
>
> - After addition, slony starts replication on the slave DB. It
> takes several minutes (~25 mins) for replication to finish.
>
> - During this time (while replication is in progress), sometimes
> postgres stops responding, i.e. we couldn't even fetch the number of
> entries in any table (X, Y, etc).
Fetching the number of entries in a table - using count(...) - is
actually a rather expensive operation, and a poor choice if you just
want to see if the server is responsive.
SELECT id FROM tablename LIMIT 1;
where "id" is the primary key of the table would be a better option.
--
Craig Ringer
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issues with postgresql-8.4.0: Query gets stuck sometimes
@ 2010-07-06 00:34 Robert Haas <robertmhaas@gmail.com>
parent: Sachin Kumar <Sachin.Ku@globallogic.com>
1 sibling, 0 replies; 5+ messages in thread
From: Robert Haas @ 2010-07-06 00:34 UTC (permalink / raw)
To: Sachin Kumar <Sachin.Ku@globallogic.com>; +Cc: pgsql-performance@postgresql.org, Vivek Gupta <vivek.gupta@globallogic.com>
On Fri, Jul 2, 2010 at 1:40 AM, Sachin Kumar <Sachin.Ku@globallogic.com> wrote:
> At times we have observed that postgres stops responding for several
> minutes, even couldn’t fetch the number of entries in a particular table.
> One such instance happens when we execute the following steps:
Sounds sort of like a checkpoint spike.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-07-06 00:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-29 07:01 Performance issues with postgresql-8.4.0 Sachin Kumar <Sachin.Ku@globallogic.com>
2010-07-04 12:38 ` Craig Ringer <craig@postnewspapers.com.au>
2010-07-02 05:40 Performance issues with postgresql-8.4.0: Query gets stuck sometimes Sachin Kumar <Sachin.Ku@globallogic.com>
2010-07-02 05:50 ` Re: Performance issues with postgresql-8.4.0: Query gets stuck sometimes Scott Marlowe <scott.marlowe@gmail.com>
2010-07-06 00:34 ` Re: Performance issues with postgresql-8.4.0: Query gets stuck sometimes Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox