We recently migrated our postgresql server from a heroku 400mb ram add-on to a SSD VPS with 1GB of ram and we have seen a huge performance drop.
I am using ubuntu 12.06 and have increased the shmmax to 512MB of the 1G memory and increased the shared_buffers to 250MB so that I could increase the number of connections to 200 (we are connecting to this db from many sidekiq workers).
Is there anything else that would be causing this issue. We did not have a problem before we switched from heroku.
Thank you, Stefanie
2000 operations per test O_DIRECT supported on this platform for open_datasync and open_sync. Compare file sync methods using one 8kB write: (in wal_sync_method preference order, except fdatasync is Linux's default) open_datasync 4660.907 ops/sec fdatasync 4740.347 ops/sec fsync 4443.546 ops/sec fsync_writethrough n/a open_sync 4523.423 ops/sec Compare file sync methods using two 8kB writes: (in wal_sync_method preference order, except fdatasync is Linux's default) open_datasync 2335.532 ops/sec fdatasync 4382.159 ops/sec fsync 4091.025 ops/sec fsync_writethrough n/a open_sync 2144.664 ops/sec Compare open_sync with different write sizes: (This is designed to compare the cost of writing 16kB in different write open_sync sizes.) 16kB open_sync write 4053.276 ops/sec 8kB open_sync writes 2106.780 ops/sec 4kB open_sync writes 1176.523 ops/sec 2kB open_sync writes 575.395 ops/sec 1kB open_sync writes 288.484 ops/sec Test if fsync on non-write file descriptor is honored: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 4372.483 ops/sec write, close, fsync 4477.458 ops/sec Non-Sync'ed 8kB writes: write 486381.323 ops/sec
sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Doing CPU performance benchmark Threads started! Done. Maximum prime number checked in CPU test: 20000 Test execution summary: total time: 27.9008s total number of events: 10000 total time taken by event execution: 27.8976 per-request statistics: min: 2.73ms avg: 2.79ms max: 4.79ms approx. 95 percentile: 2.80ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 27.8976/0.00
sysbench --test=fileio --file-total-size=3G
sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Initializing random number generator from timer. Extra file open flags: 0 128 files, 24Mb each 3Gb total file size Block size 16Kb Number of random requests for random IO: 0 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Threads started! Time limit exceeded, exiting... Done. Operations performed: 455459 Read, 303639 Write, 971520 Other = 1730618 Total Read 6.9498Gb Written 4.6332Gb Total transferred 11.583Gb (39.536Mb/sec) 2530.29 Requests/sec executed Test execution summary: total time: 300.0049s total number of events: 759098 total time taken by event execution: 184.4369 per-request statistics: min: 0.00ms avg: 0.24ms max: 108.62ms approx. 95 percentile: 0.63ms Threads fairness: events (avg/stddev): 759098.0000/0.00 execution time (avg/stddev): 184.4369/0.00