Date: Wed, 20 May 2015 16:50:29 +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: <1247264598.4844463.1432140629700.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <555CAB39.1090403@neteven.com>
References: <555CAB39.1090403@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: 10531
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


> From: Thomas SIMON <tsimon@neteven.com>
> To: glynastill@yahoo.co.uk
> Cc: "pgsql-admin@postgresql.org" <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 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=20
> conditions
> With pgbench RO test, 3.2 --> 37000 TPS ; 3.14 --> 95000 TPS, same=20
> conditions too.

>=20


That's a start then.

> It should so be better, but when server was in production, and ever with=
=20
> bad kernel, performances was already quite good before they quickly=20
> decreased.
> So i think too I have another configuration problem.
>=20
> You say you're IO bound, so some output from sar / iostat / dstat and=20
> 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=20
> 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=20
> never happens ... Another thing I can't understand ...
>=20
> Below is my dstat output when I replay this traffic (and so when server=20
> runs normally)
> I have unfortunately no more outputs when server's performances decreased.

>=20

It's a shame we can't get any insight into activity on the server during th=
e issues.
>=20
>=20
> Other things you asked
>=20
>      System memory size : 256 Go
>      SSD Model numbers and how many : 4 SSd disks ; RAID 10 ; model=20
> 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 you're =
effectively just using pgpool as a proxy (as I recall, my memory is a littl=
e fuzzy on pgpool now).  Unless your app is stateful in some way or has uni=
que 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 t=
hough.  What's the nature of the app(s)?

> I also add megacli parameters :
>=20
> Virtual Drive: 2 (Target Id: 2)
> Name                :datassd
> RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
> Size                : 893.25 GB
> Sector Size         : 512
> Is VD emulated      : Yes
> Mirror Data         : 893.25 GB
> State               : Optimal
> Strip Size          : 256 KB
> Number Of Drives per span:2
> Span Depth          : 2
> Default Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write=20
> Cache if Bad BBU
> Current Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write=20
> Cache if Bad BBU
> Default Access Policy: Read/Write
> Current Access Policy: Read/Write
> Disk Cache Policy   : Enabled
> Encryption Type     : None
> Bad Blocks Exist: No
> PI type: No PI
>=20
> Is VD Cached: No

>=20


Not using your raid controllers write cache then?  Not sure just how import=
ant that is with SSDs these days, but if you've got a BBU set it to "WriteB=
ack". Also change "Cache if Bad BBU" to "No Write Cache if Bad BBU" if you =
do that.


>=20
> Other outputs :
>      fdisk -l
>=20
> Disk /dev/sdc: 959.1 GB, 959119884288 bytes
> 255 heads, 63 sectors/track, 116606 cylinders, total 1873281024 sectors
> Units =3D sectors of 1 * 512 =3D 512 bytes
> Sector size (logical/physical): 512 bytes / 4096 bytes
> I/O size (minimum/optimal): 4096 bytes / 4096 bytes
> Disk identifier: 0x00000000
>=20
> Disk /dev/mapper/vg_datassd-lv_datassd: 751.6 GB, 751619276800 bytes
> 255 heads, 63 sectors/track, 91379 cylinders, total 1468006400 sectors
> Units =3D sectors of 1 * 512 =3D 512 bytes
> Sector size (logical/physical): 512 bytes / 4096 bytes
> I/O size (minimum/optimal): 4096 bytes / 4096 bytes
> Disk identifier: 0x00000000
>=20
>=20
>      cat /etc/mtab
> /dev/mapper/vg_datassd-lv_datassd /datassd ext4=20
> rw,relatime,discard,nobarrier,data=3Dordered 0 0
> (I added nobarrier option)
>=20
>=20
>      cat /sys/block/sdc/queue/scheduler
> noop [deadline] cfq
>=20

>=20


You could swap relatime for noatime,nodiratime.

>      sysctl kernel | grep sched
> kernel.sched_child_runs_first =3D 0
> kernel.sched_rr_timeslice_ms =3D 25
> kernel.sched_rt_period_us =3D 1000000
> kernel.sched_rt_runtime_us =3D 950000
>=20
> I've read some advices about changing kernel.sched_autogroup_enabled=3D0=
=20
> and kernel.sched_migration_cost_ns=3D5000000, but this parameters are not=
=20
> recognized by my kernel. So I don't know what to do with that...

>=20

sched_migration_cost_ns would be called sched_migration_cost in your old 3.=
2 kernel, not sure why=20
sched_autogroup_enabled wouldn't be recognized though.


>      sysctl vm
> vm.admin_reserve_kbytes =3D 8192
> vm.block_dump =3D 0
> vm.dirty_background_bytes =3D 8388608
> vm.dirty_background_ratio =3D 0
> vm.dirty_bytes =3D 67108864
> vm.dirty_expire_centisecs =3D 3000
> vm.dirty_ratio =3D 0
> vm.dirty_writeback_centisecs =3D 500
> vm.drop_caches =3D 3
> vm.laptop_mode =3D 0
> vm.legacy_va_layout =3D 0
> vm.lowmem_reserve_ratio =3D 256    256    32
> vm.max_map_count =3D 65530
> vm.memory_failure_early_kill =3D 0
> vm.memory_failure_recovery =3D 1
> vm.min_free_kbytes =3D 65008
> vm.min_slab_ratio =3D 5
> vm.min_unmapped_ratio =3D 1
> vm.mmap_min_addr =3D 4096
> vm.nr_pdflush_threads =3D 0
> vm.numa_zonelist_order =3D default
> vm.oom_dump_tasks =3D 1
> vm.oom_kill_allocating_task =3D 0
> vm.overcommit_kbytes =3D 0
> vm.overcommit_memory =3D 2
> vm.overcommit_ratio =3D 50
> vm.page-cluster =3D 3
> vm.panic_on_oom =3D 0
> vm.percpu_pagelist_fraction =3D 0
> vm.scan_unevictable_pages =3D 0
> vm.stat_interval =3D 1
> vm.swappiness =3D 60
> vm.user_reserve_kbytes =3D 131072
> vm.vfs_cache_pressure =3D 100
> vm.zone_reclaim_mode =3D 0
>=20
>=20
>      select * from pg_stat_activity
> I've got hundred of entries for that when i'm in production, and I=20
> can't=20
> paste it here due to confidentiality.
> By day, it is around 50 millions queries usually. (35% selects ; 55%=20
> updates & 5% inserts)
>=20
>=20
>      lspci | grep -E 'RAID|SCSI|IDE|SATA'
> 00:1f.2 SATA controller: Intel Corporation C600/X79 series chipset=20
> 6-Port SATA AHCI Controller (rev 06)
> 02:00.0 RAID bus controller: LSI Logic / Symbios Logic MegaRAID SAS 2208=
=20
> [Thunderbolt] (rev 05)
> 07:00.0 Serial Attached SCSI controller: Intel Corporation C602 chipset=20
> 4-Port SATA Storage Control Unit (rev 06)
>=20
> Thanks
>=20
> Thomas
>=20
>=20
> Le 18/05/2015 16:29, Glyn Astill a =C3=A9crit :
>>>  From: Koray Eyido=C4=9Fan <korayey@gmail.com>
>>>  To: Thomas SIMON <tsimon@neteven.com>
>>>  Cc: pgsql-admin@postgresql.org
>>>  Sent: Monday, 18 May 2015, 14:51
>>>  Subject: Re: [ADMIN] Performances issues with SSD volume ?
>>>=20
>>>=20
>>>=20
>>>  Hi Thomas,
>>>=20
>>>=20
>>>  3.2 kernel may be #1 cause of your I/O load problem:
>>>=20
>>>=20
>>>=20
> http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32=
.html
>>>=20
>>>=20
> https://medium.com/postgresql-talk/benchmarking-postgresql-with-different=
-linux-kernel-versions-on-ubuntu-lts-e61d57b70dd4
>>>=20
>>>=20
>>>=20
>>>  Have a nice day.
>>>=20
>>>=20
>>>  Koray
>>=20
>>  Likely 3.2 kernel isn't helping, but I think we need much more=20
> information before jumping to conclusions.
>>=20
>>  You say you're IO bound, so some output from sar / iostat / dstat and=20
> pg_stat_activity etc before and during the issue would be of use.
>>=20
>>  Also:
>>       System memory size
>>=20
>>       SSD Model numbers and how many
>>       Raid controller
>>       Partition allignments and stripe sizes
>>       Kernel options
>>       Filesystem used and mount options
>>       IO Scheduler
>>       Postgresql version and configuration
>>       Connection pool sizing
>>=20
>>=20=20=20
>>  Perhaps you could thow us the output of some of these:
>>=20
>>       fdisk -l
>>       cat /etc/mtab
>>       cat /sys/block/<ssd device>/queue/scheduler
>>       sysctl kernel | grep sched
>>       sysctl vm
>>       select * from pg_stat_activity
>>       select name, setting from pg_settings
>>       lspci | grep -E 'RAID|SCSI|IDE|SATA'
>=20
>=20
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


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