Dne 26.11.2011 00:17, Cédric Villemain napsal(a): > Le 25 novembre 2011 23:47, Gaëtan Allart <gaetan@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
