Re: Performances issues with SSD volume ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Le 21/05/2015 14:57, Glyn Astill a écrit :
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++?
Yes, I think the same thing. Probably I will doing this tomorrow early in the morning. I tried to reproduce disk issues with different stress tests like bonnie, fio, tsung, and I use a more realistic scenario with pgreplay to reproduce my production trafic from postgresql logfile.
However, I'm note sure how to diagnostic performance issues.
I mean, if I see ssd are 100% full, how can I figure out why their behavior changes ?

I'm asking myself another question, about master/slave configuration.
For doing my test, I will put my ssd server as slave of hdd server. After that, I will promote him as master. In case I still have performance issues and I must do a rollback, am I necessarily forced to reconstruct completely my new slave (hdd) with pg_basebackup (and wait some hours file are transferer), or can I promote directly this old master as a slave without pending time to reconstruct (as files should be the same on both servers) ?

Thanks
Thomas


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




[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux