Re: 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)

>
>
> Also see here:
> https://wiki.postgresql.org/wiki/Performance_Analysis_Tools
> https://wiki.postgresql.org/wiki/Monitoring
>
>>> I'm asking myself another question, about master/slave configuration.
>> For doing my test, I will put my ssd server as slave of hdd server.
>
> Unless you've got a mainly read-only worlkoad, you can't really test the slave properly that way as all it's doing is replaying the wal.
Sorry, I mispoke.
I meant that for promoting my actual ssd test server to master, I had to
promote its as a slave in a first time.
>
>> After that, I will promote him as master.
>> In case I still have performance issues and I must do a rollback, am I
>> necessarily forced to reconstruct completely my new slave (hdd) with
>> pg_basebackup (and wait some hours file are transferer), or can I
>> promote directly this old master as a slave without pending time to
>> reconstruct (as files should be the same on both servers) ?
>>
>
> Yes you will need to rebuild it or look at pg_rewind:
>
>
> https://github.com/vmware/pg_rewind
Thanks for reference. Could do the trick.
I'll read that with interest.
>
>