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@xxxxxxxxxxxxxxx> 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
