Tuesday, October 04, 2011

pgbench on UNLOGGED table(s)

To satisfy my curiosity, I just tried a quick run for pgbench with UNLOGGED tables.

Under the pgbench transaction model, a kind of tpc-b, changing the history table attribute to "UNLOGGED" did not contribute to the performance.

However, changing all tables to "UNLOGGED" improved the performance *substantially*. :)

The results shown in the right image are averages of running pgbench 10 times each.

Here is the steps to make this test:

  • Start PostgreSQL 9.1.
  • Create `testdb' to run a regular pgbench.
  • Dump schemas with `pg_dump --schema-only testdb' into a schema file.
  • Dump data with `pg_dump --data-only testdb' into a data file.
  • Edit the schema file, by hands, to create the UNLOGGED table(s).
  • Create another database `testdb_nologged'.
  • Import the schemas with using the schema file into `testdb_nologged'.
  • Import the data with using the data file into `testdb_nologged'.
  • Run pgbench against `testdb' and 'testdb_nologged'.

In addition, this test was executed on the following server:
  • NEC Express5800/GT110b
  • Dual Core Celeron @ 2.27GHz
  • Physical Memory 12GB
  • WDC WD1602ABYS-19B7A0 (SATA)
  • Red Hat Enterprise Linux 6.0
and with the following PostgreSQL configuration:
  • Shared Buffers : 2048MB
  • Checkpoint Segments : 32
  • WAL Buffers : 1024kB
  • Pgbench: Scale factor 10, 1,000 Transactions * 32 Clients
That's all. Thanks for reading! I need to back to work! :)


Anonymous said...

How does that compare with settting fsync=off or synchronous_commit=off?

Satoshi Nagayasu said...

That seems a good point. I'm interested in it, too. So I will test and post it in a few days. Thanks for the comment.

Satoshi Nagayasu said...

Just posted the updated one.