Postgres creates / restores a lot of time on amazon ec2

Question

I have an instance of amazon ec2 (SAY S1) (4core-7GB memory) using Ubuntu 12.04, which runs my web application with postgresql 9.1 . All postgres data is stored on another ssd (non-root) volume of 100 GB. (Write now, now it is only 26%).

Suddenly, a lot of time began with a day or two of postgres actions. Create a command (52 seconds) and restore db (9 minutes now, maximum 50 seconds).

By running iostat while postgres is running, I can confirm that its ec2 IOPS has reached its limit (3 IOPS / GB equals 300 IOPS for 100 GB). You can see it below by running the command iostat -d 5 -x -p xvdf .

 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.35 2.28 1.20 298.99 19.65 13082.19 87.29 23.42 78.03 64.19 78.09 3.29 98.75 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 1.80 0.00 297.40 0.00 13067.20 87.88 126.47 420.75 0.00 420.75 3.35 99.76 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 1.80 0.00 297.40 0.00 13067.20 87.88 126.32 417.95 0.00 417.95 3.35 99.76 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 1.80 0.00 297.80 0.00 13093.60 87.94 131.70 440.82 0.00 440.82 3.36 100.00 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 0.00 0.00 301.00 0.00 13225.60 87.88 129.36 422.97 0.00 422.97 3.32 99.84 

The aws IO characteristics say that every IOPS accepts a request of 256 KB or less, so do postgres use smaller data blocks to write resulting in more IOPS requests?

While I have another instance of ec2 (Say S2) with a capacity of 100 GB (currently 95%), the postgres data is on the root volume and works fine. Therefore, the size of the volume is what I am sure does not matter here.

The infected volume S1 stores postgres data, but I can see the statistics on iostat below. Not sure why the statistics are, and how I can reduce the time of postgres commands without increasing the size of the volume. (Although all operations of 3 GB of memory are always free)

 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.34 2.29 1.23 298.93 20.10 13079.03 87.28 26.19 87.26 66.96 87.34 3.29 98.78 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 2.40 0.60 299.00 4.80 13020.80 86.95 132.22 434.48 108.00 435.14 3.34 100.00 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 3.20 4.40 295.20 43.20 12866.40 86.18 122.18 417.09 142.00 421.20 3.34 100.00 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 2.80 2.40 297.20 23.20 12940.00 86.54 122.70 401.11 124.00 403.34 3.34 99.92 Device: rrqm/s wrqm/sr/sw/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util xvdf 0.00 3.40 4.80 294.80 46.40 12840.00 86.02 127.43 433.15 161.67 437.57 3.34 99.92 

Note: the affected postgres volume contains 100 different postgres db with an average size of 110 MB / dB (but to be honest, I don't think this is a problem anyway)

Answer 1

So finally, this problem is resolved. And I found that it was a postgres statistics collector that worked in the background and produced many small (less than 256 KB) io requests (since we have 100 + dbs), they eat all 300 IOPS for a 100 GB disk. The result of all postgres actions was scheduled in the queue and took a long time to process.

Postgres doc says

The statistics collector transfers the collected information to (including autovacuum) through temporary files. These files are stored in the pg_stat_tmp subdirectory. When the postmaster closes down, a permanent copy of the statistics is stored in a global subdirectory. To improve performance, the stats_temp_directory parameter can be pointed to a RAM-based file system, reducing physical I / O requirements.

I pointed to the pg_stats_tmp file to flag the drive instead by setting pg_stats_tmp to the tmpfs file system. This blog explains how to do this step by step.