Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Fair enough; I'm so used to bumping wal_buffers up to 16MB nowadays that
I forget sometimes that people actually run with the default where this
becomes an important consideration.

Do you have any testing in favor of 16mb vs. lower/higher?

From some tests I had done some time ago, using separate spindles (RAID1) for xlog, no battery, on 8.4, with stuff that generates lots of xlog (INSERT INTO SELECT) :

When using a small wal_buffers, there was a problem when switching from one xlog file to the next. Basically a fsync was issued, but most of the previous log segment was still not written. So, postgres was waiting for the fsync to finish. Of course, the default 64 kB of wal_buffers is quickly filled up, and all writes wait for the end of this fsync. This caused hiccups in the xlog traffic, and xlog throughput wassn't nearly as high as the disks would allow. Sticking a sthetoscope on the xlog harddrives revealed a lot more random accesses that I would have liked (this is a much simpler solution than tracing the IOs, lol)

I set wal writer delay to a very low setting (I dont remember which, perhaps 1 ms) so the walwriter was in effect constantly flushing the wal buffers to disk. I also used fdatasync instead of fsync. Then I set wal_buffers to a rather high value, like 32-64 MB. Throughput and performance were a lot better, and the xlog drives made a much more "linear-access" noise.

What happened is that, since wal_buffers was larger than what the drives can write in 1-2 rotations, it could absorb wal traffic during the time postgres waits for fdatasync / wal segment change, so the inserts would not have to wait. And lowering the walwriter delay made it write something on each disk rotation, so that when a COMMIT or segment switch came, most of the time, the WAL was already synced and there was no wait.

Just my 2 c ;)

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux