pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedstrange pauses
14+ messages / 8 participants
[nested] [flat]
* strange pauses
@ 2008-01-16 10:42 Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-16 15:30 ` Re: strange pauses Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Adrian Moisey @ 2008-01-16 10:42 UTC (permalink / raw)
To: pgsql-performance
Hi
I've noticed a strange pattern in our logs.
We only log anything that takes longer then 5 seconds.
If I tail the logs, I see a steady stream of functions that took longer
then 5 seconds. But every now and then I see nothing for 3 minutes and
after that a whole bunch (about 20 - 30) queries all finishing at the
same second.
During this 3 minutes of nothing being logged, the db is still active
and I can access our website
Looks to me like "something" is "locking" and causing requests to be
delayed. Once this "lock" unlocks, everything goes through.
Sorry for the bad description, can anybody help? Where do I start looking?
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-16 10:50 ` Richard Huxton <dev@archonet.com>
2008-01-16 15:23 ` Re: strange pauses Tom Lane <tgl@sss.pgh.pa.us>
2008-01-16 15:37 ` Re: strange pauses Greg Smith <gsmith@gregsmith.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
1 sibling, 3 replies; 14+ messages in thread
From: Richard Huxton @ 2008-01-16 10:50 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
Adrian Moisey wrote:
>
> If I tail the logs, I see a steady stream of functions that took longer
> then 5 seconds. But every now and then I see nothing for 3 minutes and
> after that a whole bunch (about 20 - 30) queries all finishing at the
> same second.
Search this list for references to "checkpoints". If you run
vmstat/iostat for a bit you should see bursts of disk activity at those
times.
Counter-intuitively you probably want *more* checkpoints (with less data
to write out each time), but see the list archives for discussion. Also
check the manuals for some background info.
HTH
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
@ 2008-01-16 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2008-01-16 15:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Adrian Moisey <adrian@careerjunction.co.za>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> Adrian Moisey wrote:
>> If I tail the logs, I see a steady stream of functions that took longer
>> then 5 seconds. But every now and then I see nothing for 3 minutes and
>> after that a whole bunch (about 20 - 30) queries all finishing at the
>> same second.
> Search this list for references to "checkpoints". If you run
> vmstat/iostat for a bit you should see bursts of disk activity at those
> times.
A different line of thought is that something is taking out an exclusive
lock on a table that all these queries need. If vmstat doesn't show any
burst of activity during these pauses, look into pg_locks.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
@ 2008-01-16 15:37 ` Greg Smith <gsmith@gregsmith.com>
2008-01-17 06:50 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2 siblings, 1 reply; 14+ messages in thread
From: Greg Smith @ 2008-01-16 15:37 UTC (permalink / raw)
To: ; +Cc: Adrian Moisey <adrian@careerjunction.co.za>; pgsql-performance
On Wed, 16 Jan 2008, Richard Huxton wrote:
> Search this list for references to "checkpoints". If you run vmstat/iostat
> for a bit you should see bursts of disk activity at those times.
The most straightforward way to prove or disprove that the slow queries
line up with checkpoints is to set to checkpoint_warning to a high value
(3600 should work), which should log every checkpoint, and then see if
they show up at the same time in the logs.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-16 15:37 ` Re: strange pauses Greg Smith <gsmith@gregsmith.com>
@ 2008-01-17 06:50 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-17 13:44 ` Re: strange pauses Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Adrian Moisey @ 2008-01-17 06:50 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
Hi
>> Search this list for references to "checkpoints". If you run
>> vmstat/iostat for a bit you should see bursts of disk activity at
>> those times.
>
> The most straightforward way to prove or disprove that the slow queries
> line up with checkpoints is to set to checkpoint_warning to a high value
> (3600 should work), which should log every checkpoint, and then see if
> they show up at the same time in the logs.
You guys were spot on. During these pauses the IO goes up high.
I've got the following set:
checkpoint_timeout = 5min
checkpoint_warning = 3600s
log_min_messages = info
But I see nothing in the logs about checkpoints
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-16 15:37 ` Re: strange pauses Greg Smith <gsmith@gregsmith.com>
2008-01-17 06:50 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-17 13:44 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2008-01-17 13:44 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
On Jan 17, 2008 12:50 AM, Adrian Moisey <adrian@careerjunction.co.za> wrote:
> Hi
>
> >> Search this list for references to "checkpoints". If you run
> >> vmstat/iostat for a bit you should see bursts of disk activity at
> >> those times.
> >
> > The most straightforward way to prove or disprove that the slow queries
> > line up with checkpoints is to set to checkpoint_warning to a high value
> > (3600 should work), which should log every checkpoint, and then see if
> > they show up at the same time in the logs.
>
> You guys were spot on. During these pauses the IO goes up high.
You need to tune your background writer process. See this link:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
@ 2008-01-18 10:58 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:03 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 15:36 ` Re: strange pauses Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 2 replies; 14+ messages in thread
From: Adrian Moisey @ 2008-01-18 10:58 UTC (permalink / raw)
To: pgsql-performance
Hi
>> If I tail the logs, I see a steady stream of functions that took
>> longer then 5 seconds. But every now and then I see nothing for 3
>> minutes and after that a whole bunch (about 20 - 30) queries all
>> finishing at the same second.
>
> Search this list for references to "checkpoints". If you run
> vmstat/iostat for a bit you should see bursts of disk activity at those
> times.
>
> Counter-intuitively you probably want *more* checkpoints (with less data
> to write out each time), but see the list archives for discussion. Also
> check the manuals for some background info.
We use a lot of checkpoints in our database, we don't care about these
much. We think this is causing the checkpoint to write a lot of data at
checkpoint time. Can we stop this behavior ?
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-18 12:03 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:41 ` Re: strange pauses Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 14+ messages in thread
From: Adrian Moisey @ 2008-01-18 12:03 UTC (permalink / raw)
To: pgsql-performance
Hi
>>> If I tail the logs, I see a steady stream of functions that took
>>> longer then 5 seconds. But every now and then I see nothing for 3
>>> minutes and after that a whole bunch (about 20 - 30) queries all
>>> finishing at the same second.
>>
>> Search this list for references to "checkpoints". If you run
>> vmstat/iostat for a bit you should see bursts of disk activity at
>> those times.
>>
>> Counter-intuitively you probably want *more* checkpoints (with less
>> data to write out each time), but see the list archives for
>> discussion. Also check the manuals for some background info.
>
> We use a lot of checkpoints in our database, we don't care about these
We use a lot of temporary tables in our database, sorry.
> much. We think this is causing the checkpoint to write a lot of data at
> checkpoint time. Can we stop this behavior ?
>
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:03 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-18 12:41 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-01-21 07:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
0 siblings, 1 reply; 14+ messages in thread
From: Alvaro Herrera @ 2008-01-18 12:41 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
Adrian Moisey wrote:
> Hi
>
>>>> If I tail the logs, I see a steady stream of functions that took longer
>>>> then 5 seconds. But every now and then I see nothing for 3 minutes and
>>>> after that a whole bunch (about 20 - 30) queries all finishing at the
>>>> same second.
>>>
>>> Search this list for references to "checkpoints". If you run
>>> vmstat/iostat for a bit you should see bursts of disk activity at those
>>> times.
>>>
>>> Counter-intuitively you probably want *more* checkpoints (with less data
>>> to write out each time), but see the list archives for discussion. Also
>>> check the manuals for some background info.
>>
>> We use a lot of checkpoints in our database, we don't care about these
>
> We use a lot of temporary tables in our database, sorry.
>
>> much. We think this is causing the checkpoint to write a lot of data at
>> checkpoint time. Can we stop this behavior ?
No, data written in temp tables do not cause extra I/O on checkpoint.
Catalog changes due to temp table creation could, but I doubt that's the
problem.
Perhaps, if you want to avoid I/O caused by temp tables (but it's not at
checkpoint time, so perhaps this has nothing to do with your problem),
you could try raising temp_buffers.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:03 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:41 ` Re: strange pauses Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-01-21 07:58 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-21 19:29 ` Re: strange pauses Decibel! <decibel@decibel.org>
0 siblings, 1 reply; 14+ messages in thread
From: Adrian Moisey @ 2008-01-21 07:58 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-performance
Hi
> Perhaps, if you want to avoid I/O caused by temp tables (but it's not at
> checkpoint time, so perhaps this has nothing to do with your problem),
> you could try raising temp_buffers.
How can I find out if temp_buffers is being exceeded ?
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:03 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:41 ` Re: strange pauses Alvaro Herrera <alvherre@commandprompt.com>
2008-01-21 07:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-21 19:29 ` Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 14+ messages in thread
From: Decibel! @ 2008-01-21 19:29 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
On Jan 21, 2008, at 1:58 AM, Adrian Moisey wrote:
>> Perhaps, if you want to avoid I/O caused by temp tables (but it's
>> not at
>> checkpoint time, so perhaps this has nothing to do with your
>> problem),
>> you could try raising temp_buffers.
>
> How can I find out if temp_buffers is being exceeded ?
You could monitor the pgsql_tmp directory under the appropriate
database directory ($PGDATA/base/oid_number_of_the_database). You
could also see how many pages temporary objects in that connection
are using; you'd have to find the temp schema that your session is
using (\dn pg_temp* from psql), and then
SELECT sum(relpages) FROM pg_class c JOIN pg_namespace n ON
(c.relnamespace=n.oid) AND n.nspname='pg_temp_blah';
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Re: strange pauses Richard Huxton <dev@archonet.com>
2008-01-18 10:58 ` Re: strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-18 15:36 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2008-01-18 15:36 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
On Jan 18, 2008 4:58 AM, Adrian Moisey <adrian@careerjunction.co.za> wrote:
>
> We use a lot of checkpoints in our database, we don't care about these
> much. We think this is causing the checkpoint to write a lot of data at
> checkpoint time. Can we stop this behavior ?
You can smooth out the checkpoint behaviour by using that link I
posted earlier to tune your bgwriter to write things out ahead of when
the checkpoint will occurr.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
@ 2008-01-16 15:30 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2008-01-16 15:30 UTC (permalink / raw)
To: Adrian Moisey <adrian@careerjunction.co.za>; +Cc: pgsql-performance
On Jan 16, 2008 4:42 AM, Adrian Moisey <adrian@careerjunction.co.za> wrote:
> Hi
>
> I've noticed a strange pattern in our logs.
>
> We only log anything that takes longer then 5 seconds.
>
> If I tail the logs, I see a steady stream of functions that took longer
> then 5 seconds. But every now and then I see nothing for 3 minutes and
> after that a whole bunch (about 20 - 30) queries all finishing at the
> same second.
>
> During this 3 minutes of nothing being logged, the db is still active
> and I can access our website
>
> Looks to me like "something" is "locking" and causing requests to be
> delayed. Once this "lock" unlocks, everything goes through.
Hmmm. Are these queries actually all finishing at the same time or
just showing up in the logs at the same time. Just thinking there
might be some kind of issue going on with logging. Are you using
syslog or postgresql stderr logging?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: strange pauses
@ 2008-01-17 09:16 Brian Herlihy <btherl@yahoo.com.au>
0 siblings, 0 replies; 14+ messages in thread
From: Brian Herlihy @ 2008-01-17 09:16 UTC (permalink / raw)
To: pgsql-performance
We had the same situation, and did two things
1. Reduce checkpoint timeout
2. Reduce quantity of data going into database (nice if it's possible!)
1 alone wasn't enough to eliminate the delays, but it did make each delay small enough that the user interface was only minimally affected. Previously, the delays were causing timeouts in the user interface.
Our symptoms were that the queries finishing "at the same time" were appearing in clusters every 5 minutes + some seconds, which happens to be the checkpoint timeout. Seems a new checkpoint timeout is started only after the checkpoint is complete, hence 5 minute plus, rather than exactly 5 minutes.
Brian
----- Original Message ----
From: Adrian Moisey <adrian@careerjunction.co.za>
Hi
>> Search this list for references to "checkpoints". If you run
>> vmstat/iostat for a bit you should see bursts of disk activity at
>> those times.
>
> The most straightforward way to prove or disprove that the slow
queries
> line up with checkpoints is to set to checkpoint_warning to a high
value
> (3600 should work), which should log every checkpoint, and then see
if
> they show up at the same time in the logs.
You guys were spot on. During these pauses the IO goes up high.
I've got the following set:
checkpoint_timeout = 5min
checkpoint_warning = 3600s
log_min_messages = info
But I see nothing in the logs about checkpoints
--
Adrian Moisey
System Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2008-01-21 19:29 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-16 10:42 strange pauses Adrian Moisey <adrian@careerjunction.co.za>
2008-01-16 10:50 ` Richard Huxton <dev@archonet.com>
2008-01-16 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-16 15:37 ` Greg Smith <gsmith@gregsmith.com>
2008-01-17 06:50 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-17 13:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-18 10:58 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:03 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-18 12:41 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-01-21 07:58 ` Adrian Moisey <adrian@careerjunction.co.za>
2008-01-21 19:29 ` Decibel! <decibel@decibel.org>
2008-01-18 15:36 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-16 15:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-17 09:16 Re: strange pauses Brian Herlihy <btherl@yahoo.com.au>
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