Hi pgsql-performance,
I was doing mass insertions on my desktop machine and getting at most
1 MB/s disk writes (apart from occasional bursts of 16MB). Inserting 1
million rows with a single integer (data+index 56 MB total) took over
2 MINUTES! The only tuning I had done was shared_buffers=256MB. So I
got around to tuning the WAL writer and found that wal_buffers=16MB
works MUCH better. wal_sync_method=fdatasync also got similar results.
First of all, I'm running PostgreSQL 9.0.1 on Arch Linux
* Linux kernel 2.6.36 (also tested with 2.6.35.
* Quad-core Phenom II
* a single Seagate 7200RPM SATA drive (write caching on)
* ext4 FS over LVM, with noatime, data=writeback
I am creating a table like: create table foo(id integer primary key);
Then measuring performance with the query: insert into foo (id) select
generate_series(1, 1000000);
130438,011 ms wal_buffers=64kB, wal_sync_method=open_datasync (all defaults)
29306,847 ms wal_buffers=1MB, wal_sync_method=open_datasync
4641,113 ms wal_buffers=16MB, wal_sync_method=open_datasync
^ from 130s to 4.6 seconds by just changing wal_buffers.
5528,534 ms wal_buffers=64kB, wal_sync_method=fdatasync
4856,712 ms wal_buffers=16MB, wal_sync_method=fdatasync
^ fdatasync works well even with small wal_buffers
2911,265 ms wal_buffers=16MB, fsync=off
^ Not bad, getting 60% of ideal throughput
These defaults are not just hurting bulk-insert performance, but also
everyone who uses synchronus_commit=off
Unless fdatasync is unsafe, I'd very much want to see it as the
default for 9.1 on Linux (I don't know about other platforms). I
can't see any reasons why each write would need to be sync-ed if I
don't commit that often. Increasing wal_buffers probably has the same
effect wrt data safety.
Also, the tuning guide on wiki is understating the importance of these
tunables. Reading it I got the impression that some people change
wal_sync_method but it's dangerous and it even literally claims about
wal_buffers that "1MB is enough for some large systems"
But the truth is that if you want any write throughput AT ALL on a
regular Linux desktop, you absolutely have to change one of these. If
the defaults were better, it would be enough to set
synchronous_commit=off to get all that your hardware has to offer.
I was reading mailing list archives and didn't find anything against
it either. Can anyone clarify the safety of wal_sync_method=fdatasync?
Are there any reasons why it shouldn't be the default?
Regards,
Marti
