Content-Type: multipart/alternative;
	boundary=Apple-Mail-E713BE55-5EAE-4F5B-8772-C632ED79C8B8
Mime-Version: 1.0 (1.0)
Subject: Re: driving postgres to achieve benchmark results similar to bonnie++
From: John Scalia <jayknowsunix@gmail.com>
In-Reply-To: 
 <CAB9893gs_pWP7zpD1HDw0+K41vHwPx72T2DsiTa5+zhqV3wYsg@mail.gmail.com>
Date: Tue, 10 May 2016 12:01:37 -0500
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: 7bit
Message-Id: <EE62BF4B-B4E2-4D22-9A72-224905F05DBA@gmail.com>
References: 
 <CAB9893gs_pWP7zpD1HDw0+K41vHwPx72T2DsiTa5+zhqV3wYsg@mail.gmail.com>
To: Mike Broers <mbroers@gmail.com>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--Apple-Mail-E713BE55-5EAE-4F5B-8772-C632ED79C8B8
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable


Sent from my iPad

> On May 10, 2016, at 10:48 AM, Mike Broers <mbroers@gmail.com> wrote:
>=20
> I'm having trouble getting postgres to drive enough disk activity to get e=
ven close to the disk benchmarking I'm getting with bonnie++.   We have SSD S=
AN and the xlog is on its own ssd volume as well, postgres 9.5 running on ce=
ntos 6.
>=20
> bonnie++ -n 0 -f -b is the command im running, pointing to either primary d=
ata or xlog location Im consistently seeing numbers like this:
>=20
> Version 1.03e       ------Sequential Output------ --Sequential Input- --Ra=
ndom-
>                     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Se=
eks--
>=20
>       Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
>=20
> 23808M           786274  92 157465  29           316097  17  5751  16
>=20
> So during bonnie++ tests I've confirmed in our monitoring write peaks at 7=
00/800 MB/sec and read peaks around 280/300 MB/sec.=20
>=20
> We have 12GB RAM on the server, when I run pgbench with a scale that sets t=
he pgbench database in the realm of 18GB - 25GB I barely break 110MB/sec wri=
tes and 80MB/sec.   I'm running with different options such unlogged tables a=
nd logged tables, prepared transactions or not, and transaction counts betwe=
en 1000 and 40000.
>=20
> I thought a parallel pg_dump / restore might also drive disk but that perf=
ormance doesnt drive disk throughput either, topping out around 75MB/sec rea=
d.  Nightly vacuums also seem to peak below 110MB/sec reads as well. =20
>=20
> Here are the nondefault pg settings:=20
>=20
> max_connections =3D 1024
> shared_buffers =3D 1024MB
> wal_buffers =3D 16MB
> checkpoint_completion_target =3D '.9'
> archive_mode =3D on
> random_page_cost =3D '1.5'
> maintenance_work_mem =3D 512MB
> work_mem =3D 64MB
> max_wal_senders =3D 5
> checkpoint_timeout =3D 10min
> effective_io_concurrency =3D 4
> effective_cache_size =3D 8GB
> wal_keep_segments =3D 512
> wal_level =3D hot_standby
> synchronous_commit =3D off
>=20
> 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++ leve=
ls from within postgres that I am missing?  I'm guessing I'm just not drivin=
g enough activity to push it to the limit but I'm not sure of a straightforw=
ard method to verify this.=20
>=20
> Thanks,
>=20
> Mike
>=20
Well, I'm no expert with Bonnie, but several of your PostgreSQL settings loo=
k incorrect according to the system config you provided. With 12Gb of RAM, s=
hared_buffers should probably be closer to at least 3092Mb. That is if you f=
ollow the general suggestion for having that at 1/4 of your available RAM. A=
lso, your max_connections settings looks awfully high. Do you really need 10=
24 connections? Suggest, if so, that you look into a connection pooling soft=
ware, like pgpool-II or something.

With an SSD drive, I also doubt your random page cost is that high. With tha=
t 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 w=
ithout knowing what kind of queries you're running.
--
Jay=

--Apple-Mail-E713BE55-5EAE-4F5B-8772-C632ED79C8B8
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto"><div><br><br>Sent from my iPad</div><div><b=
r>On May 10, 2016, at 10:48 AM, Mike Broers &lt;<a href=3D"mailto:mbroers@gm=
ail.com">mbroers@gmail.com</a>&gt; wrote:<br><br></div><blockquote type=3D"c=
ite"><div><div dir=3D"ltr">I'm having trouble getting postgres to drive enou=
gh disk activity to get even close to the disk benchmarking I'm getting with=
 bonnie++. &nbsp; We have SSD SAN and the xlog is on its own ssd volume as w=
ell, postgres 9.5 running on centos 6.<div><br></div>


<div>bonnie++ -n 0 -f -b is the command im running, pointing to either prima=
ry data or xlog location Im consistently seeing numbers like this:<div><br>V=
ersion 1.03e &nbsp; &nbsp; &nbsp; ------Sequential Output------ --Sequential=
 Input- --Random-<br></div><div>
<p class=3D""><span class=3D"">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nb=
sp; &nbsp; &nbsp; &nbsp; -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- -=
-Seeks--</span></p>
<p class=3D""><span class=3D"">&nbsp; &nbsp; &nbsp;&nbsp;Size K/sec %CP K/se=
c %CP K/sec %CP K/sec %CP K/sec %CP&nbsp; /sec %CP</span></p>
<p class=3D""><span class=3D"">23808M &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 786=
274&nbsp; 92 157465&nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 316097&nbsp;=
 17&nbsp; 5751&nbsp; 16</span></p><p class=3D"">So during bonnie++ tests I'v=
e confirmed in our monitoring write peaks at 700/800 MB/sec and read peaks a=
round 280/300 MB/sec.&nbsp;</p><p class=3D"">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. &nbsp; I'm run=
ning with different options such unlogged tables and logged tables, prepared=
 transactions or not, and transaction counts between 1000 and 40000.</p><p c=
lass=3D"">I thought a parallel pg_dump / restore might also drive disk but t=
hat performance doesnt drive disk throughput either, topping out around 75MB=
/sec read.&nbsp; Nightly vacuums also seem to peak below 110MB/sec reads as w=
ell. &nbsp;</p><p class=3D"">Here are the nondefault pg settings:&nbsp;</p><=
p class=3D""><span class=3D"">max_connections =3D 1024<br></span>shared_buff=
ers =3D 1024MB<br>wal_buffers =3D 16MB<br>checkpoint_completion_target =3D '=
.9'<br>archive_mode =3D on<br>random_page_cost =3D '1.5'<br>maintenance_work=
_mem =3D 512MB<br>work_mem =3D 64MB<br>max_wal_senders =3D 5<br>checkpoint_t=
imeout =3D 10min<br>effective_io_concurrency =3D 4<br>effective_cache_size =3D=
 8GB<br>wal_keep_segments =3D 512<br>wal_level =3D hot_standby<br>synchronou=
s_commit =3D off</p><p class=3D"">Any idea of if/why postgres might be bottl=
enecking disk throughput?&nbsp; Or if there is a method for testing to achie=
ve something closer the bonnie++ levels from within postgres that I am missi=
ng?&nbsp; I'm guessing I'm just not driving enough activity to push it to th=
e limit but I'm not sure of a straightforward method to verify this.&nbsp;</=
p><p class=3D"">Thanks,</p><p class=3D"">Mike</p></div></div></div>
</div></blockquote>Well, I'm no expert with Bonnie, but several of your Post=
greSQL settings look incorrect according to the system config you provided. W=
ith 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 you=
r available RAM. Also, your max_connections settings looks awfully high. Do y=
ou really need 1024 connections? Suggest, if so, that you look into a connec=
tion pooling software, like pgpool-II or something.<div><br></div><div>With a=
n SSD drive, I also doubt your random page cost is that high. With that sett=
ing, you may be forcing a lot of queries to use sequential access, not rando=
m.</div><div><br></div><div>Finally, increase the two work mem settings. I c=
an't recommend any settings without knowing what kind of queries you're runn=
ing.</div><div>--</div><div>Jay</div></body></html>=

--Apple-Mail-E713BE55-5EAE-4F5B-8772-C632ED79C8B8--