From: Glyn Astill <glynastill@xxxxxxxxxxx>
To: Thomas SIMON <tsimon@xxxxxxxxxxx>
Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx>
Sent: Wednesday, 20 May 2015, 17:50
Subject: Re: Performances issues with SSD volume ?
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)?
I also add megacli parameters :
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
Cache if Bad BBU
Current Cache Policy: WriteThrough, ReadAheadNone, Direct, No Write
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
Is VD Cached: No
Not using your raid controllers write cache then? Not sure just how important
that is with SSDs these days, but if you've got a BBU set it to
"WriteBack". Also change "Cache if Bad BBU" to "No
Write Cache if Bad BBU" if you do that.
Other outputs :
fdisk -l
Disk /dev/sdc: 959.1 GB, 959119884288 bytes
255 heads, 63 sectors/track, 116606 cylinders, total 1873281024 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disk identifier: 0x00000000
Disk /dev/mapper/vg_datassd-lv_datassd: 751.6 GB, 751619276800 bytes
255 heads, 63 sectors/track, 91379 cylinders, total 1468006400 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disk identifier: 0x00000000
cat /etc/mtab
/dev/mapper/vg_datassd-lv_datassd /datassd ext4
rw,relatime,discard,nobarrier,data=ordered 0 0
(I added nobarrier option)
cat /sys/block/sdc/queue/scheduler
noop [deadline] cfq
You could swap relatime for noatime,nodiratime.
