> From: Thomas SIMON <tsimon@xxxxxxxxxxx> > To: Glyn Astill <glynastill@xxxxxxxxxxx> > Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx> > Sent: Thursday, 21 May 2015, 13:12 > Subject: Re: Performances issues with SSD volume ? > > Le 20/05/2015 18:50, Glyn Astill a écrit : >> >>> From: Thomas SIMON <tsimon@xxxxxxxxxxx> >>> To: glynastill@xxxxxxxxxxx >>> Cc: "pgsql-admin@xxxxxxxxxxxxxx" > <pgsql-admin@xxxxxxxxxxxxxx> >>> Sent: Wednesday, 20 May 2015, 16:41 >>> Subject: Re: 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++? -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
