Date: Fri, 22 May 2015 16:21:24 +0000 (UTC)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
To: Thomas SIMON <tsimon@neteven.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Message-ID: <1693885845.798995.1432311684859.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <555F4B40.8040808@neteven.com>
References: <555F4B40.8040808@neteven.com>
Subject: Re: Performances issues with SSD volume ?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 6917
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

----- Original Message -----

> From: Thomas SIMON <tsimon@neteven.com>
> To: Glyn Astill <glynastill@yahoo.co.uk>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Sent: Friday, 22 May 2015, 16:29
> Subject: Re: [ADMIN] Performances issues with SSD volume ?
>=20
>=20
> Thomas
>=20
> Le 22/05/2015 11:37, Glyn Astill a =C3=A9crit :
>>>  From: Thomas SIMON <tsimon@neteven.com>
>>>  To: Glyn Astill <glynastill@yahoo.co.uk>
>>>  Cc: "pgsql-admin@postgresql.org"=20
> <pgsql-admin@postgresql.org>
>>>  Sent: Thursday, 21 May 2015, 17:56
>>>  Subject: Re: [ADMIN] Performances issues with SSD volume ?
>>>=20
>>>  Le 21/05/2015 16:30, Glyn Astill a =C3=A9crit :
>>>>>>      I think at this point you could do with going back and=20
> trying to
>>>  reproduce
>>>>>>    the issue, then trace back up to pg_stat_activity to see=20
> what
>>>  activity could be
>>>>>>    causing the disk i/o.  I assume you've tried to=20
> reproduce the
>>>  disk issues
>>>>>>    with a simple disk benchmark like bonnie++?
>>>>>    Yes, I think the same thing. Probably I will doing this=20
> tomorrow early
>>>>>    in the morning.
>>>>>    I tried to reproduce disk issues with different stress tests=20
> like
>>>>>    bonnie, fio, tsung, and I use a more realistic scenario with=20
> pgreplay
>>>  to
>>>>>    reproduce my production trafic from postgresql logfile.
>>>>>    However, I'm note sure how to diagnostic performance=20
> issues.
>>>>>    I mean, if I see ssd are 100% full, how can I figure out why=20
> their
>>>>>    behavior changes ?
>>>>>=20
>>>>    Well the disk benchmarks are purely to see what your disks are=20
> capable of,
>>>  and help with your initial tuning.
>>>>=20
>>>>    You need to trace back which processes are causing most of the IO
>>>  you're seeing, as well as the postgresql logs something like iotop,=20
> or dstat
>>>  with the --top-bio option might help you there.
>>>>=20
>>>>    You could also look at the pg_statio_user_tables view to narrow=20
> down which
>>>  tables are being hit the hardest, which might give you some clues.
>>>  Is there something to activate for seeing something in this table ?
>>>  Because its empty on my production server
>>>=20
>>>  postgres=3D# select * from pg_statio_user_tables;
>>>     relid | schemaname | relname | heap_blks_read | heap_blks_hit |
>>>  idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |
>>>  tidx_blks_read | tidx_blks_hit
>>>=20
> -------+------------+---------+----------------+---------------+---------=
------+--------------+-----------------+----------------+----------------+-=
--------------
>>>  (0 rows)
>>>=20
>>=20
>>  Looks like you need to set track_counts=3Don then.  Infact if you've go=
t=20
> track_counts off then you're also not running autovacuum, that's a=20
> warning flag unless it's intentional.
> Arf, no it's just me who does dummy things ...
> I was in postgres database, not in my app...
>=20
>=20
> Here is result of my top tables.
>=20
> relid   | schemaname | relname                        | heap_blks_read |=
=20
> heap_blks_hit | idx_blks_read | idx_blks_hit  | toast_blks_read |=20
> toast_blks_hit | tidx_blks_read | tidx_blks_hit
> ----------+------------+-------------------------------------------------=
-----+----------------+---------------+---------------+---------------+----=
-------------+----------------+----------------+---------------
>    2056411 | public     | table 1=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
> |   326588572770 | 432317896616 |   12839634494 |   33230958888 |=20=20=
=20=20=20=20=20=20
> 52298815 |       20020628 |        9406665 |     153557966
>    2059324 | public     | table 2                               |=20
> 223323685944 |   72194867978 |   10319078550 |  103672304590 |=20=20=20=
=20=20=20=20=20=20=20
> 189135 |         262100 |          27815 |        207212
>    2056368 | public     | table 3=20
>                                                 |   198706578066 |=20
> 1259514828344 |   21083719219 | 1456776226532 |           90446=20
> |         331680 |          30850 |        367441
>    2056347 | public     | table 4 |   124086575669 |  554775474730 |=20=
=20=20=20
> 5236764894 |  614913194098 |        14978587 |       67085822 |=20=20=20=
=20=20=20=20=20
> 751951 |     134187396
>    2057296 | public     | table 5=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
> |    68120702371 | 18134568265 |    1243107028 |   10537765277 |=20=20=20=
=20=20=20
> 350646003 |       87888306 |      119113944 |     629790819
>    2058460 | public     | table 6 |    35119995405 |  158177258403 |=20=
=20=20=20
> 1687014495 | 1395923096049 |          738542 |         214574 |=20=20=20=
=20=20=20=20=20
> 325765 |       1318187
>    2058900 | public     | table 7=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
> |    34205345184 | 271135546976 |   21050227933 |  252099340046=20
> |            3924 |           6567 |            422 |         16333
>    2059195 | public     | table 8=20
>                                               |    29169256266 |=20
> 152937546457 |    2263998316 |  127782237565 |               0=20
> |              0 |              0 |             0
>=20
> how you interpret these values?
> You check ratio between  heap_read & heap_hit ?
> i've made ratio, it's about 89%

>=20


It shows blocks fetched from disk, or possibly from OS the buffer cache as =
"read", and blocks already in shared buffers as "hit".=20=20


See here: http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#P=
G-STATIO-ALL-TABLES-VIEW


Also this thread appears to now be split over diagnosing the issue from two=
 different standpoints; 1) Is the i/o performance (or some other aspect) of=
 your new SSD server worse than that of your old live spinning disk system.=
 2) Are there improvements to be had with your database and application set=
up.

For 1) Maybe if you could post the output of bonnie++ results from the data=
 volumes on both servers when nothing else is going off on them.  If you ca=
n't do that on your production server perhaps just the output from sar -d d=
uring a busy period might help.  Hopefully that would help to clarify the s=
pecific disk performance differences for us.  Other interesting tests would=
 be the output of Greg Smiths stream scaling script (https://github.com/gre=
gs1104/stream-scaling).  We're still missing the exact specs of your old sy=
stem and the CPUs in the new one.


For 2) I think we're still missing a lot of information about what's happen=
ing in your database to be able to say much, i.e. what's the size of the da=
tabase, tables 1 - 8, typical read / write paterns, and transaction rate?  =
Perhaps you could give a simple description of what your apps(s) do on a da=
ily basis, and then post a diff of the outputs of the pg_stat_database, pg_=
stat_user_tables and pg_staio_user_tables views over a specific period of t=
ime.

You really need to dig into each of these yourself to try and see where you=
r new system might be lacking.


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin