From: Thomas SIMON <tsimon@neteven.com>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Performances issues with SSD volume ?
Date: Thu, 21 May 2015 18:56:09 +0200
Message-ID: <555E0E29.4030100@neteven.com> (raw)
In-Reply-To: <1531490365.5920903.1432218656001.JavaMail.yahoo@mail.yahoo.com>
References: <555DE32B.4000706@neteven.com>
<1531490365.5920903.1432218656001.JavaMail.yahoo@mail.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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_rewindThanks for reference. Could do the trick.
I'll read that with interest.
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tsimon@neteven.com, glynastill@yahoo.co.uk
Subject: Re: Performances issues with SSD volume ?
In-Reply-To: <555E0E29.4030100@neteven.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox