Message-ID: <555F4B40.8040808@neteven.com>
Date: Fri, 22 May 2015 17:29:04 +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: <555E0E29.4030100@neteven.com>
 <339846919.317679.1432287456484.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <339846919.317679.1432287456484.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


Thomas

Le 22/05/2015 11:37, Glyn Astill a écrit :
>> From: Thomas SIMON <tsimon@neteven.com>
>> To: Glyn Astill <glynastill@yahoo.co.uk>
>> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
>> Sent: Thursday, 21 May 2015, 17:56
>> Subject: Re: [ADMIN] 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)
>>
>
> Looks like you need to set track_counts=on then.  Infact if you've got track_counts off then you're also not running autovacuum, that's a warning flag unless it's intentional.
Arf, no it's just me who does dummy things ...
I was in postgres database, not in my app...


Here is result of my top 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
----------+------------+------------------------------------------------------+----------------+---------------+---------------+---------------+-----------------+----------------+----------------+---------------
   2056411 | public     | table 1                                      
|   326588572770 | 432317896616 |   12839634494 |   33230958888 |        
52298815 |       20020628 |        9406665 |     153557966
   2059324 | public     | table 2                               | 
223323685944 |   72194867978 |   10319078550 |  103672304590 |          
189135 |         262100 |          27815 |        207212
   2056368 | public     | table 3 
                                                |   198706578066 | 
1259514828344 |   21083719219 | 1456776226532 |           90446 
|         331680 |          30850 |        367441
   2056347 | public     | table 4 |   124086575669 |  554775474730 |    
5236764894 |  614913194098 |        14978587 |       67085822 |         
751951 |     134187396
   2057296 | public     | table 5                                        
|    68120702371 | 18134568265 |    1243107028 |   10537765277 |       
350646003 |       87888306 |      119113944 |     629790819
   2058460 | public     | table 6 |    35119995405 |  158177258403 |    
1687014495 | 1395923096049 |          738542 |         214574 |         
325765 |       1318187
   2058900 | public     | table 7                                       
|    34205345184 | 271135546976 |   21050227933 |  252099340046 
|            3924 |           6567 |            422 |         16333
   2059195 | public     | table 8 
                                              |    29169256266 | 
152937546457 |    2263998316 |  127782237565 |               0 
|              0 |              0 |             0

how you interpret these values?
You check ratio between  heap_read & heap_hit ?
i've made ratio, it's about 89%


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin