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

are you running the pgbench on the same server as the postgres database.
Can you check if there's a saturation at the CPU?

On 11 May 2016 at 03:02, Mike Broers <mbroers(at)gmail(dot)com> wrote:

> We have a 10Gb storage network.
>
> bonnie++ with fsync option shows ~ 700MB/sec write, 300MB/sec read,
> writing a file twice as big as ram.
>
> I am unable to push postgres to get higher than 115MB/sec writes or reads
> on those same mounts I am verifying are performing well with bonnie++.
> This is probably either because my tests arent intense enough or I have
> misconfigured postgres. If someone has an idea for how to set up pgbench
> to really push the io to prove out ssd potential, or a different
> script/approach that would be awesome.
>
>
>
> On Tue, May 10, 2016 at 1:44 PM, Scott Whitney <scott(at)journyx(dot)com> wrote:
>
>> What is your connection to your SAN?
>>
>>
>>
>> Sent via the Samsung GALAXY S®4, an AT&T 4G LTE smartphone
>>
>>
>> -------- Original message --------
>> From: Mike Broers <mbroers(at)gmail(dot)com>
>> Date: 05/10/2016 1:29 PM (GMT-06:00)
>> To: John Scalia <jayknowsunix(at)gmail(dot)com>
>> Cc: pgsql-admin(at)postgresql(dot)org
>> Subject: Re: [ADMIN] driving postgres to achieve benchmark results
>> similar to bonnie++
>>
>> I've made those changes and seen negligible improvements (increase in
>> reads like 10MB/sec from 99MB/sec to 112MB/sec, so those didnt hurt but
>> they didnt exactly unleash huge gains.
>>
>> Any idea of a way I can really push the postgres server to see numbers
>> closer to the bonnie++ results or provide an explanation of the ceiling?
>>
>>
>>
>>
>>
>> On Tue, May 10, 2016 at 12:08 PM, Mike Broers <mbroers(at)gmail(dot)com> wrote:
>>
>>> Thanks for the feedback, I'll update those configs, run some more tests,
>>> and follow up.
>>>
>>>
>>>
>>> On Tue, May 10, 2016 at 12:01 PM, John Scalia <jayknowsunix(at)gmail(dot)com>
>>> wrote:
>>>
>>>>
>>>>
>>>> Sent from my iPad
>>>>
>>>> On May 10, 2016, at 10:48 AM, Mike Broers <mbroers(at)gmail(dot)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
>>>>
>>>
>>>
>>
>>
>> Journyx, Inc.
>> 7600 Burnet Road #300
>> Austin, TX 78757
>> www.journyx.com
>>
>> p 512.834.8888
>> f 512-834-8858
>>
>> Do you receive our promotional emails? You can subscribe or unsubscribe
>> to those emails at http://go.journyx.com/emailPreference/e/4932/714/
>>
>
>

--
Regards,
Ang Wei Shan