Message-ID: <555E0E29.4030100@neteven.com>
Date: Thu, 21 May 2015 18:56:09 +0200
From: Thomas SIMON <tsimon@neteven.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Glyn Astill <glynastill@yahoo.co.uk>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Performances issues with SSD volume ?
References: <555DE32B.4000706@neteven.com>
 <1531490365.5920903.1432218656001.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <1531490365.5920903.1432218656001.JavaMail.yahoo@mail.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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