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.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
