Re: General performance/load issue - Mailing list pgsql-general

Dne 26.11.2011 00:17, Cédric Villemain napsal(a):
> Le 25 novembre 2011 23:47, Gaëtan Allart <gaetan@nexylan.com> a écrit :
>> Hello Tomas and Cédric,
>>
>> Right now, the server is not all right. Load is above 30 and queries are
>> slow like hell.
>>
>>
>> Here's the complete iotop. Note the 71 MB/s writes (apparently on SELECT
>> queries).
>>
>> Total DISK READ: 633.35 K/s | Total DISK WRITE: 71.06 M/s
>>  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
>>
>>
>> 27352 be/4 postgres   15.64 K/s   86.01 K/s  0.00 % 99.99 % postgres:
>> database database 176.31.228.6(38816) SELECT
>> 20226 be/4 postgres    7.82 K/s    0.00 B/s  0.00 % 99.99 % postgres:
>> database database 176.31.228.6(34166) SELECT
>> 26950 be/4 postgres   23.46 K/s    0.00 B/s  0.00 % 82.14 % postgres:
>> database database 46.105.104.205(40820) SELECT
>> 23160 be/4 postgres    3.91 K/s    0.00 B/s  0.00 % 81.14 % postgres:
>> database database 46.105.104.205(58091) SELECT
>> 29184 be/4 postgres    7.82 K/s    0.00 B/s  0.00 % 79.17 % postgres:
>> database database 46.105.104.205(51047) SELECT

Gaetan, you keep deleting the interesting rows for some reason. None of
the rows you've posted writes more than a few KB/s - what about the rows
that actually write those 71MB/s?

>> Here is dirty_expire_centisecs :
>>
>> cat /proc/sys/vm/dirty_expire_centisecs
>> 3000

OK. That's a default value and it's usually too high (just like the
ratios), but don't change it until we find out what's wrong.

>> Bgwriter configuration is default :
>>
>> #bgwriter_delay = 200ms   # 10-10000ms between rounds
>> #bgwriter_lru_maxpages = 100  # 0-1000 max buffers written/round
>> #bgwriter_lru_multiplier = 2.0  # 0-10.0 multipler on buffers scanned/round

OK.

>> Is there anything I can provide to help you ?
>
> the checkpoints logs and the output of pg_stat_bgwriter (as asked by Tomas).
>
> It is probable that shared_buffers are too small for your workload
> (expected), do you still have issue with checkpoint sync duration ?

I don't think that's the case. Too small shared buffers usually cause a
lot of reads (especially when all the queries are SELECTs as here), but
he has problems with writes.

And according to the pg_stat_bgwriter Gaetan posted, the checkpoints
wrote about 54MB, bgwriter about 370MB and backends about 80MB (during
the 5 minutes between snapshots).

So I'm really wondering where those 70MB/s of writes come from.

Two things just crossed my mind. The first one are hint bits - this may
cause a SELECT to write a lot of data. But I guess this is included in
the pg_stat_bgwriter stats.

The second one is on-disk sorting - this happens when a query needs to
sort so much data it can't be done in work_mem, so the data are pushed
to the disk, and AFAIK it's not included into the pg_stat_bgwriter.

But he has work_mem set to 128MB so it's rather unlikely. Gaetan, can
you verify that those queries that write the most data to the disk are
not performing any huge sorts etc? (See iotop which postgres process is
writing a lot of data and use the PID and pg_stat_activity to find out
which query it's executing.)

Tomas