From: | Marti Raudsepp <marti(at)juffo(dot)org> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1? |
Date: | 2010-11-08 14:23:27 |
Message-ID: | AANLkTi=M+Pn-p5C00iqT_gengR-U4emH9P7B+ea8qLzU@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Nov 8, 2010 at 02:05, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Where's your benchmarks proving it then? If you're right about this, and
> I'm not saying you aren't, it should be obvious in simple bechmarks by
> stepping through various sizes for wal_buffers and seeing the
> throughput/latency situation improve.
Since benchmarking is the easy part, I did that. I plotted the time
taken by inserting 2 million rows to a table with a single integer
column and no indexes (total 70MB). Entire script is attached. If you
don't agree with something in this benchmark, please suggest
improvements.
Chart: http://ompldr.org/vNjNiNQ/wal_sync_method1.png
Spreadsheet: http://ompldr.org/vNjNiNg/wal_sync_method1.ods (the 2nd
worksheet has exact measurements)
This is a different machine from the original post, but similar
configuration. One 1TB 7200RPM Seagate Barracuda, no disk controller
cache, 4G RAM, Phenom X4, Linux 2.6.36, PostgreSQL 9.0.1, Arch Linux.
This time I created a separate 20GB ext4 partition specially for
PostgreSQL, with all default settings (shared_buffers=32MB). The
partition is near the end of the disk, so hdparm gives a sequential
read throughput of ~72 MB/s. I'm getting frequent checkpoint warnings,
should I try larger checkpoing_segments too?
The partition is re-created and 'initdb' is re-ran for each test, to
prevent file system allocation from affecting results. I did two runs
of all benchmarks. The points on the graph show a sum of INSERT time +
COMMIT time in seconds.
One surprising thing on the graph is a "plateau", where open_datasync
performs almost equally with wal_buffers=128kB and 256kB.
Another noteworthy difference (not visible on the graph) is that with
open_datasync -- but not fdatasync -- and wal_buffers=128M, INSERT
time keeps shrinking, but COMMIT takes longer. The total INSERT+COMMIT
time remains the same, however.
----
I have a few expendable hard drives here so I can test reliability by
pulling the SATA cable as well. Is this kind of testing useful? What
workloads do you suggest?
Regards,
Marti
Attachment | Content-Type | Size |
---|---|---|
pgtest.sh | application/x-sh | 961 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-11-08 15:23:32 | Re: Select * is very slow |
Previous Message | Francisco Reyes | 2010-11-08 09:13:35 | Re: Regression: 8.3 2 seconds -> 8.4 100+ seconds |