Date: Thu, 21 May 2015 12:57:12 +0000 (UTC)
From: Glyn Astill <glynastill@yahoo.co.uk>
Reply-To: Glyn Astill <glynastill@yahoo.co.uk>
To: Thomas SIMON <tsimon@neteven.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Message-ID: <1300059800.5810274.1432213032449.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <555DCBA6.1050607@neteven.com>
References: <555DCBA6.1050607@neteven.com>
Subject: Re: Performances issues with SSD volume ?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 5720
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

> 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, 13:12
> Subject: Re: [ADMIN] Performances issues with SSD volume ?
>=20
> Le 20/05/2015 18:50, Glyn Astill a =C3=A9crit :
>>=20
>>>  From: Thomas SIMON <tsimon@neteven.com>
>>>  To: glynastill@yahoo.co.uk
>>>  Cc: "pgsql-admin@postgresql.org"=20
> <pgsql-admin@postgresql.org>
>>>  Sent: Wednesday, 20 May 2015, 16:41
>>>  Subject: Re: [ADMIN] Performances issues with SSD volume ?
>>>=20
>>>  Hi Glyn,
>>>=20
>>>  I'll try to answer this points.
>>>=20
>>>  I've made some benchs, and indeed 3.2 not helping. not helping at=20
> all.
>>>  I changed to 3.14 and gap is quite big !
>>>  With pgbench RW test, 3.2 --> 4200 TPS ; 3.14 --> 6900 TPS in=20
> same
>>>  conditions
>>>  With pgbench RO test, 3.2 --> 37000 TPS ; 3.14 --> 95000 TPS,=20
> same
>>>  conditions too.
>>=20
>>  That's a start then.
>>=20
>>>  It should so be better, but when server was in production, and ever=20
> with
>>>  bad kernel, performances was already quite good before they quickly
>>>  decreased.
>>>  So i think too I have another configuration problem.
>>>=20
>>>  You say you're IO bound, so some output from sar / iostat / dstat=20
> and
>>>  pg_stat_activity etc before and during the issue would be of use.
>>>=20
>>>  -> 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=20
> pgreplay.
>>>  I hoped that the server falls back by replaying this traffic, but it
>>>  never happens ... Another thing I can't understand ...
>>>=20
>>>  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=20
> decreased.
>>  It's a shame we can't get any insight into activity on the server=20
> during the issues.
>>>=20
>>>  Other things you asked
>>>=20
>>>        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 :
>>>=20
> ftp://ftp.ovh.net/made-in-ovh/bzImage/3.14.43/config-3.14.43-xxxx-std-ipv=
6-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
>>>=20
>>>  max_connections=3D1800
>>>  shared_buffers=3D8GB
>>>  temp_buffers=3D32MB
>>>  work_mem=3D100MB
>>>  maintenance_work_mem=3D12GB
>>>  bgwriter_lru_maxpages=3D200
>>>  effective_io_concurrency=3D4
>>>  wal_level=3Dhot_standby
>>>  wal_sync_method=3Dfdatasync
>>>  wal_writer_delay=3D2000ms
>>>  commit_delay=3D1000
>>>  checkpoint_segments=3D80
>>>  checkpoint_timeout=3D15min
>>>  checkpoint_completion_target=3D0.7
>>>  archive_command=3D'rsync ....'
>>>  max_wal_senders=3D10
>>>  wal_keep_segments=3D38600
>>>  vacuum_defer_cleanup_age=3D100
>>>  hot_standby =3D on
>>>  max_standby_archive_delay =3D 5min
>>>  max_standby_streaming_delay =3D 5min
>>>  hot_standby_feedback =3D on
>>>  random_page_cost =3D 1.0
>>>  effective_cache_size =3D 240GB
>>>  log_min_error_statement =3D warning
>>>  log_min_duration_statement =3D 0
>>>  log_checkpoints =3D on
>>>  log_connections =3D on
>>>  log_disconnections =3D on
>>>  log_line_prefix =3D '%m|%u|%d|%c|'
>>>  log_lock_waits =3D on
>>>  log_statement =3D 'all'
>>>  log_timezone =3D 'localtime'
>>>  track_activities =3D on
>>>  track_functions =3D pl
>>>  track_activity_query_size =3D 8192
>>>  autovacuum_max_workers =3D 5
>>>  autovacuum_naptime =3D 30s
>>>  autovacuum_vacuum_threshold =3D 40
>>>  autovacuum_analyze_threshold =3D 20
>>>  autovacuum_vacuum_scale_factor =3D 0.10
>>>  autovacuum_analyze_scale_factor =3D 0.10
>>>  autovacuum_vacuum_cost_delay =3D 5ms
>>>  default_transaction_isolation =3D 'read committed'
>>>  max_locks_per_transaction =3D 128
>>>=20
>>>=20
>>>=20
>>>        Connection pool sizing (pgpool2)
>>>  num_init_children =3D 1790
>>>  max_pool =3D 1
>>=20
>>  1800 is quite a lot of connections, and with max_pool=3D1 in pgpool=20
> you're effectively just using pgpool as a proxy (as I recall, my memory i=
s a=20
> little fuzzy on pgpool now).  Unless your app is stateful in some way or =
has=20
> unique users for each of those 1800 connections you should lower the quan=
tity of=20
> active connections.  A general starting point is usually cpu cores * 2, s=
o you=20
> could up max_pool and divide num_init_children by the same amount.
>>=20

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

>> 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=20
> various number of connexions (twisted + zope apps)
> result is ~ 900 idle connexions for ~ 60 active connexions, but=20
> sometimes (when stopping/starting prod), we need almost double of=20
> connexion because some twisted services don't stop their connexions=20
> immediatly.
> But this is the actual (working) configuration, and I don't think think=20
> 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 coul=
d be causing the disk i/o.  I assume you've tried to reproduce the disk iss=
ues with a simple disk benchmark like bonnie++?


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