Penguin
Note: You are viewing an old revision of this page. View the current version.

This is a scratch pad for some PostgreSQL benchmarks. The contributed utility pgbench is used for the testing.

For most of the testing, important parts of the PostgreSQL configuration used are
shared_buffers    = 23987
max_fsm_relations = 5950
max_fsm_pages     = 3207435

wal_buffers         = 544
checkpoint_segments = 40
checkpoint_timeout  = 900
checkpoint_warning  = 300
commit_delay        = 20000
commit_siblings     = 3
wal_sync_method     = fdatasync

enable_seqscan        = off
default_with_oids     = off
stats_start_collector = false

Exceptions will be listed as the tests are performed.

The pgbench test database was created with the -s100 scale factor option. This results in a fresh database of about 1.4GB. Consecutive runs of pgbench grow the database, however. All test runs were executed with the -c100 option for 100 connections. The transactions per connection was adjusted as needed to give a stable test result, without obvious effects of caching. Typical settings were -t100 to -t1000.

The pgbench client was actually run over a 100Mbit, full-duplex network connection from a client machine for most of the testing. Running pgbench remotely has not measurably degraded the performance. The client machine is a dual 3.06GHz Xeon running Linux 2.4.27.

The base hardware:

  • HP DL380 G4
  • Dual 3.20GHz Xeon, 1MB L2 Cache, 800MHz FSB, HyperThreading disabled
  • 1GB DDR2-400 (PC2-3200) registered ECC memory
  • Broadcom PCI-X onboard NIC
  • SmartArray 6i onboard RAID controller
  • Battery-backed write cache enabled

The base software:

On with the testing!

Results: 4-disk configurations

  • Data array: RAID5, 4x 72GB 10k RPM
    WAL array: On data array

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 100
    number of transactions actually processed: 10000/10000
    tps = 132.257337 (including connections establishing)
    tps = 141.908320 (excluding connections establishing)
  • Data array: RAID5, 4x 72GB 10k RPM
    WAL array: On data array
    Other notes: commit_delay disabled

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 100
    number of transactions actually processed: 10000/10000
    tps = 135.567199 (including connections establishing)
    tps = 146.354640 (excluding connections establishing)
  • Data array: RAID5, 4x 72GB 10k RPM
    WAL array: On data array
    Other notes: battery-backed write cache disabled

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 50
    number of transactions actually processed: 5000/5000
    tps = 76.678506 (including connections establishing)
    tps = 83.263195 (excluding connections establishing)
  • Data array: RAID5, 4x 72GB 10k RPM
    WAL array: On data array
    Other notes: Battery-backed write cache and commit_delay disabled

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 50
    number of transactions actually processed: 5000/5000
    tps = 50.434271 (including connections establishing)
    tps = 53.195151 (excluding connections establishing)
  • Data array: RAID1, 2x 72GB 10k RPM
    WAL array: RAID1, 2x 72GB 10k RPM

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 217.737758 (including connections establishing)
    tps = 220.277597 (excluding connections establishing)
  • Data array: RAID1+0, 4x 72GB 15k RPM
    WAL array: On data array

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 325.140579 (including connections establishing)
    tps = 330.843403 (excluding connections establishing)
    
    scaling factor: 600
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 284.662951 (including connections establishing)
    tps = 285.127666 (excluding connections establishing)
  • Data array: RAID5, 4x 72GB 15k RPM
    WAL array: On data array

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 192.430583 (including connections establishing)
    tps = 194.404205 (excluding connections establishing)
  • Data array: RAID1, 2x 72GB 15k RPM
    WAL array: RAID1, 2x 72GB 15k RPM

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 263.185661 (including connections establishing)
    tps = 266.928392 (excluding connections establishing)

Results: 6-disk configurations

  • Data array: RAID1+0, 4x 72GB 15k RPM
    WAL array: RAID1, 2x 72GB 10k RPM

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 2000
    number of transactions actually processed: 200000/200000
    tps = 409.561669 (including connections establishing)
    tps = 414.078634 (excluding connections establishing)
    
    scaling factor: 600
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 340.756686 (including connections establishing)
    tps = 341.404543 (excluding connections establishing)
  • Data array: RAID5, 4x 72GB 15k RPM
    WAL array: RAID1, 2x 72GB 10k RPM

    scaling factor: 100
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 276.581309 (including connections establishing)
    tps = 280.727719 (excluding connections establishing)
    
    scaling factor: 600
    number of clients: 100
    number of transactions per client: 1000
    number of transactions actually processed: 100000/100000
    tps = 212.377629 (including connections establishing)
    tps = 212.615105 (excluding connections establishing)
  • Data array:
    WAL array:
    Other notes:

    
    

Other observations

  • The test database started at 1.4GB, and got to at least 14GB during testing. Has this growth affected results?
  • The WAL consumes large amounts of Kernel page cache. When moving the WAL between devices, when the old files are unlinked, 1/2 of the page cache is freed. Since the WAL is never read and written only once, this is as waste!
  • The battery-backed write cache makes write performance very erratic.
  • The HP SmartArray hardware (or perhaps driver) tends to block reads while there are cached writes occuring. Large read latencies (whole seconds) result. I have not yet found a way to tune this.

Part of CategoryDiskNotes