From: Alvaro Herrera <alvherre@commandprompt.com>
To: Adrian Moisey <adrian@careerjunction.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange pauses
Date: Fri, 18 Jan 2008 09:41:21 -0300
Message-ID: <20080118124121.GA5326@alvh.no-ip.org> (raw)
In-Reply-To: <47909577.4070704@careerjunction.co.za>
References: <478DDF83.8070106@careerjunction.co.za>
<478DE16F.50308@archonet.com>
<4790863E.4000309@careerjunction.co.za>
<47909577.4070704@careerjunction.co.za>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, adrian@careerjunction.co.za
Subject: Re: strange pauses
In-Reply-To: <20080118124121.GA5326@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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