Date: Fri, 22 May 2015 09:37:36 +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: <339846919.317679.1432287456484.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <555E0E29.4030100@neteven.com>
References: <555E0E29.4030100@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: 2325
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

> From: Thomas SIMON <tsimon@neteven.com>

> To: Glyn Astill <glynastill@yahoo.co.uk>
> Cc: "pgsql-admin@postgresql.org" <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 :
>>=20
>>>>    I think at this point you could do with going back and trying to=20
> reproduce
>>>>  the issue, then trace back up to pg_stat_activity to see what=20
> activity could be
>>>>  causing the disk i/o.  I assume you've tried to reproduce the=20
> disk issues
>>>>  with a simple disk benchmark like bonnie++?
>>>  Yes, I think the same thing. Probably I will doing this tomorrow early
>>>  in the morning.
>>>  I tried to reproduce disk issues with different stress tests like
>>>  bonnie, fio, tsung, and I use a more realistic scenario with pgreplay=
=20
> to
>>>  reproduce my production trafic from postgresql logfile.
>>>  However, I'm note sure how to diagnostic performance issues.
>>>  I mean, if I see ssd are 100% full, how can I figure out why their
>>>  behavior changes ?
>>>=20
>>  Well the disk benchmarks are purely to see what your disks are capable =
of,=20
> and help with your initial tuning.
>>=20
>>=20
>>  You need to trace back which processes are causing most of the IO=20
> you're seeing, as well as the postgresql logs something like iotop, or ds=
tat=20
> with the --top-bio option might help you there.
>>=20
>>=20
>>  You could also look at the pg_statio_user_tables view to narrow down wh=
ich=20
> 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 |=20
> idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit |=20
> tidx_blks_read | tidx_blks_hit
> -------+------------+---------+----------------+---------------+---------=
------+--------------+-----------------+----------------+----------------+-=
--------------
> (0 rows)
>=20


Looks like you need to set track_counts=3Don then.  Infact if you've got tr=
ack_counts off then you're also not running autovacuum, that's a warning fl=
ag unless it's intentional.


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