Friday, October 07, 2011

pgbench on UNLOGGED table(s), Round 2

As one pointed out in a comment in the last post, I have tried additional pgbench runs for "synchronous_commit = off" and "fsync = off".

The results are shown in the right pic.

It shows that "sync_commit = off" and "fsync = off" are almost the same, and these are faster as much as "UNLOGGED", except a tiny behind.

I think the tiny behind means the difference between "sync_commit = off (or fsync = off)" and "UNLOGGED" that the WAL records would be eventually written, or not.

"UNLOGGED" table  allows DBA to make some choices on the "Performance-Consistency" trade-off for each table.

"synchronous_commit = off" also allows it for each transaction, and "fsync = off" allows it for a whole database cluster.

These really look like, except the UNLOGGED table must be truncated on the crash recovery.

See also:
Robert Haas: Global Temporary and Unlogged Tables


Heiko W. Rupp said...

I think the difference may show up more when having a lot of parallel activity going on. Not writing wal with unlogged should create somewhat less disk i/o than just delaying the i/o with async commit or not forcing commits.

And then unlogged + async may be most interesting for tables with lots of writes of data that could be lost on crash?

Satoshi Nagayasu said...

Indeed. Thanks for pointing out about that.

The difference may be occurred by not only write i/o itself, but also lock stuffs in parallel processing. Lock contention is never happen with using unlogged tables.

Anyway, I think unlogged and async are never happen at the same time if async means wal i/o, because there're no wal i/o with "unlogged". I understand correctly?