Re: Performances issues with SSD volume ?

> 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 ?
>
> Le 21/05/2015 16:30, Glyn Astill a écrit :
>>
>>>>    I think at this point you could do with going back and trying to
> reproduce
>>>>  the issue, then trace back up to pg_stat_activity to see what
> activity could be
>>>>  causing the disk i/o.  I assume you've tried to reproduce the
> 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
> 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 ?
>>>
>>  Well the disk benchmarks are purely to see what your disks are capable of,
> and help with your initial tuning.
>>
>>
>>  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, or dstat
> with the --top-bio option might help you there.
>>
>>
>>  You could also look at the pg_statio_user_tables view to narrow 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
>
> postgres=# 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
>
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
> (0 rows)
>


Looks like you need to set track_counts=on then.  Infact if you've got track_counts off then you're also not running
autovacuum,that's a warning flag unless it's intentional.