Re: Performances issues with SSD volume ?

> From: Thomas SIMON <tsimon(at)neteven(dot)com>

> To: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
> Sent: Thursday, 21 May 2015, 13:12
> Subject: Re: [ADMIN] Performances issues with SSD volume ?
>
> Le 20/05/2015 18:50, Glyn Astill a écrit :
>>
>>> From: Thomas SIMON <tsimon(at)neteven(dot)com>
>>> To: glynastill(at)yahoo(dot)co(dot)uk
>>> Cc: "pgsql-admin(at)postgresql(dot)org"
> <pgsql-admin(at)postgresql(dot)org>
>>> Sent: Wednesday, 20 May 2015, 16:41
>>> Subject: Re: [ADMIN] Performances issues with SSD volume ?
>>>
>>> Hi Glyn,
>>>
>>> I'll try to answer this points.
>>>
>>> I've made some benchs, and indeed 3.2 not helping. not helping at
> all.
>>> I changed to 3.14 and gap is quite big !
>>> With pgbench RW test, 3.2 --> 4200 TPS ; 3.14 --> 6900 TPS in
> same
>>> conditions
>>> With pgbench RO test, 3.2 --> 37000 TPS ; 3.14 --> 95000 TPS,
> same
>>> conditions too.
>>
>> That's a start then.
>>
>>> It should so be better, but when server was in production, and ever
> with
>>> bad kernel, performances was already quite good before they quickly
>>> decreased.
>>> So i think too I have another configuration problem.
>>>
>>> You say you're IO bound, so some output from sar / iostat / dstat
> and
>>> pg_stat_activity etc before and during the issue would be of use.
>>>
>>> -> My server is not in production right now, so it is difficult to
>>> replay production load and have some useful metrics.
>>> The best way I've found is to replay trafic from logs with
> pgreplay.
>>> I hoped that the server falls back by replaying this traffic, but it
>>> never happens ... Another thing I can't understand ...
>>>
>>> Below is my dstat output when I replay this traffic (and so when server
>>> runs normally)
>>> I have unfortunately no more outputs when server's performances
> decreased.
>> It's a shame we can't get any insight into activity on the server
> during the issues.
>>>
>>> Other things you asked
>>>
>>> System memory size : 256 Go
>>> SSD Model numbers and how many : 4 SSd disks ; RAID 10 ; model
>>> INTEL SSDSC2BB480G4
>>> Raid controller : MegaRAID SAS 2208
>>> Partition alignments and stripe sizes : see fdisk delow
>>> Kernel options : the config file is here :
>>>
> ftp://ftp.ovh.net/made-in-ovh/bzImage/3.14.43/config-3.14.43-xxxx-std-ipv6-64
>>> Filesystem used and mount options : ext4, see mtab below
>>> IO Scheduler : noop [deadline] cfq for my ssd raid volume
>>> Postgresql version and configuration : 9.3.5
>>>
>>> max_connections=1800
>>> shared_buffers=8GB
>>> temp_buffers=32MB
>>> work_mem=100MB
>>> maintenance_work_mem=12GB
>>> bgwriter_lru_maxpages=200
>>> effective_io_concurrency=4
>>> wal_level=hot_standby
>>> wal_sync_method=fdatasync
>>> wal_writer_delay=2000ms
>>> commit_delay=1000
>>> checkpoint_segments=80
>>> checkpoint_timeout=15min
>>> checkpoint_completion_target=0.7
>>> archive_command='rsync ....'
>>> max_wal_senders=10
>>> wal_keep_segments=38600
>>> vacuum_defer_cleanup_age=100
>>> hot_standby = on
>>> max_standby_archive_delay = 5min
>>> max_standby_streaming_delay = 5min
>>> hot_standby_feedback = on
>>> random_page_cost = 1.0
>>> effective_cache_size = 240GB
>>> log_min_error_statement = warning
>>> log_min_duration_statement = 0
>>> log_checkpoints = on
>>> log_connections = on
>>> log_disconnections = on
>>> log_line_prefix = '%m|%u|%d|%c|'
>>> log_lock_waits = on
>>> log_statement = 'all'
>>> log_timezone = 'localtime'
>>> track_activities = on
>>> track_functions = pl
>>> track_activity_query_size = 8192
>>> autovacuum_max_workers = 5
>>> autovacuum_naptime = 30s
>>> autovacuum_vacuum_threshold = 40
>>> autovacuum_analyze_threshold = 20
>>> autovacuum_vacuum_scale_factor = 0.10
>>> autovacuum_analyze_scale_factor = 0.10
>>> autovacuum_vacuum_cost_delay = 5ms
>>> default_transaction_isolation = 'read committed'
>>> max_locks_per_transaction = 128
>>>
>>>
>>>
>>> Connection pool sizing (pgpool2)
>>> num_init_children = 1790
>>> max_pool = 1
>>
>> 1800 is quite a lot of connections, and with max_pool=1 in pgpool
> you're effectively just using pgpool as a proxy (as I recall, my memory is a
> little fuzzy on pgpool now). Unless your app is stateful in some way or has
> unique users for each of those 1800 connections you should lower the quantity of
> active connections. A general starting point is usually cpu cores * 2, so you
> could up max_pool and divide num_init_children by the same amount.
>>

>> Hard to say what you need to do without knowing what exactly you're

>> doing though. What's the nature of the app(s)?
>
> Yes, we just use it as a proxy for now.
> We have approximately 100 different active users, doing for all of then
> various number of connexions (twisted + zope apps)
> result is ~ 900 idle connexions for ~ 60 active connexions, but
> sometimes (when stopping/starting prod), we need almost double of
> connexion because some twisted services don't stop their connexions
> immediatly.
> But this is the actual (working) configuration, and I don't think think
> my performance disk is related to this.

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++?