Re: Weird disk write load caused by PostgreSQL?

From: Alexander Staubo <alex(at)purefiction(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Weird disk write load caused by PostgreSQL?
Date: 2006-10-02 18:19:38
Message-ID: D84BA268-ED6C-450C-93EF-9C54D0C6F58C@purefiction.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm surprised that I have to reply to myself, since in hindsight this
should be bloody obvious: It's the pgsql_tmp directory. I just
monitored the file creation in that directory, and found PostgreSQL
to be creating huge temporary, extremely short-lived files ranging
from 1MB to 20MB in size. I increased work_mem to 25MB -- should be
perfectly safe on a box with 4GB of RAM, I hope - and the iostat
traffic seems to have dropped to near zero.

I'm a bit baffled as to why PostgreSQL would ever be sorting 20MB of
data in the first place, even with ~12 connections running queries
concurrently, but I suppose I will have to look more closely at our
query patterns.

Alexander.

On Oct 2, 2006, at 19:25 , Alexander Staubo wrote:

> On Oct 2, 2006, at 17:50 , Tom Lane wrote:
>
>> Alexander Staubo <alex(at)purefiction(dot)net> writes:
>>> I have a production PostgreSQL instance (8.1 on Linux 2.6.15) that
>>> seems to be writing data to disk at rates that I think are
>>> disproportional to the update load imposed on the database. I am
>>> looking for ways to determine the cause of this I/O.
>>
>> Are you sure that iostat is to be trusted?
>
> No. :) But iostat reads directly from /dev/diskstats, which should
> be reliable. Of course, it still doesn't say anything about which
> process is doing the writing; for that I would need to install the
> atop kernel patches or similar.
>
> ...
>> The read numbers in
>> particular look suspiciously uniform ... it would be a strange
>> query load that would create a read demand changing less than 1%
>> from hour to hour, unless perhaps that represented the disk's
>> saturation point, which is not the case if you're not seeing
>> obvious performance problems.
>
> They are not uniform at all -- they correlate perfectly with the
> web traffic; it just so happens that the samples I quoted were from
> peak hours. Take a look at the Munin graph. (The spikes correspond
> to scheduled maintenance tasks like backups.)
>
> Alexander.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Oisin Glynn 2006-10-02 18:40:03 Performance and Generic Config after install
Previous Message Brandon Aiken 2006-10-02 18:03:30 Re: Advantages of postgresql