Re: Performances issues with SSD volume ?

> From: Thomas SIMON <tsimon(at)neteven(dot)com>

> To: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)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.