Re: Weird disk write load caused by PostgreSQL?

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