Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

pg_test_fsync

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 --test=cpu

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
share|improve this question
    
That's quite surprising; I'd usually expect to see the reverse. I'd look into your autovacuum settings first; the kind of intensive table updates you see in queuing systems tend to require very aggressive vacuum. Severe table bloat would explain the performance drop. –  Craig Ringer Jul 3 '13 at 6:25
    
I turned on the autovacuum and set the max_workers to 10 and this did not change the performance of the server. –  Stefanie Jul 8 '13 at 16:10
    
OK. Please add sysbench CPU and disk test results and pg_test_fsync output from the VPS while it's idle; see if the underlying machine actually has decent performance. –  Craig Ringer Jul 8 '13 at 23:14
    
I have added the results from the tests above. –  Stefanie Jul 9 '13 at 17:19
    
Based on that, your disk I/O performance is pretty dire for an SSD based machine. "40 Mb/s", even assuming that is really megaBYTEs, is terrible. The pg_test_fsync results suggest that the disk is capable of about 4500 iops, and sysbench tends to agree with about 2500 requests/second. Yet you're getting some pretty dire results for overall I/O performance. I'd raise this with the VPS host - but it's not so bad that it's an obvious support issue unless you're paying a premium for good disk performance. –  Craig Ringer Jul 10 '13 at 0:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.