Re: driving postgres to achieve benchmark results similar to bonnie++

Sent from my iPad

On May 10, 2016, at 10:48 AM, Mike Broers <mbroers@gmail.com> wrote:

I'm having trouble getting postgres to drive enough disk activity to get even close to the disk benchmarking I'm getting with bonnie++.   We have SSD SAN and the xlog is on its own ssd volume as well, postgres 9.5 running on centos 6.

bonnie++ -n 0 -f -b is the command im running, pointing to either primary data or xlog location Im consistently seeing numbers like this:

Version 1.03e       ------Sequential Output------ --Sequential Input- --Random-

                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--

      Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP

23808M           786274  92 157465  29           316097  17  5751  16

So during bonnie++ tests I've confirmed in our monitoring write peaks at 700/800 MB/sec and read peaks around 280/300 MB/sec. 

We have 12GB RAM on the server, when I run pgbench with a scale that sets the pgbench database in the realm of 18GB - 25GB I barely break 110MB/sec writes and 80MB/sec.   I'm running with different options such unlogged tables and logged tables, prepared transactions or not, and transaction counts between 1000 and 40000.

I thought a parallel pg_dump / restore might also drive disk but that performance doesnt drive disk throughput either, topping out around 75MB/sec read.  Nightly vacuums also seem to peak below 110MB/sec reads as well.  

Here are the nondefault pg settings: 

max_connections = 1024
shared_buffers = 1024MB
wal_buffers = 16MB
checkpoint_completion_target = '.9'
archive_mode = on
random_page_cost = '1.5'
maintenance_work_mem = 512MB
work_mem = 64MB
max_wal_senders = 5
checkpoint_timeout = 10min
effective_io_concurrency = 4
effective_cache_size = 8GB
wal_keep_segments = 512
wal_level = hot_standby
synchronous_commit = off

Any idea of if/why postgres might be bottlenecking disk throughput?  Or if there is a method for testing to achieve something closer the bonnie++ levels from within postgres that I am missing?  I'm guessing I'm just not driving enough activity to push it to the limit but I'm not sure of a straightforward method to verify this. 

Thanks,

Mike

Well, I'm no expert with Bonnie, but several of your PostgreSQL settings look incorrect according to the system config you provided. With 12Gb of RAM, shared_buffers should probably be closer to at least 3092Mb. That is if you follow the general suggestion for having that at 1/4 of your available RAM. Also, your max_connections settings looks awfully high. Do you really need 1024 connections? Suggest, if so, that you look into a connection pooling software, like pgpool-II or something.

With an SSD drive, I also doubt your random page cost is that high. With that setting, you may be forcing a lot of queries to use sequential access, not random.

Finally, increase the two work mem settings. I can't recommend any settings without knowing what kind of queries you're running.

--

Jay