>> 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
somethinglike 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
mightgive you some clues.
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
replayingthe wal.
> 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
