Skip site navigation (1) Skip section navigation (2)

Re: strange pauses

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Adrian Moisey <adrian(at)careerjunction(dot)co(dot)za>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange pauses
Date: 2008-01-18 12:41:21
Message-ID: 20080118124121.GA5326@alvh.no-ip.org (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-performance by date

Next:From: Sean DavisDate: 2008-01-18 13:03:42
Subject: Re: [OT] RAID controllers blocking one another?
Previous:From: Adrian MoiseyDate: 2008-01-18 12:03:03
Subject: Re: strange pauses

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group