Just out of curiosity, are you using transparent huge pages? On Feb 5, 2013 5:03 PM, "Johnny Tan" <johnnyd...@gmail.com> wrote:
> Server specs: > Dell R610 > dual E5645 hex-core 2.4GHz > 192GB RAM > RAID 1: 2x400GB SSD (OS + WAL logs) > RAID 10: 4x400GB SSD (/var/lib/pgsql) > > > /etc/sysctl.conf: > kernel.msgmnb = 65536 > kernel.msgmax = 65536 > kernel.shmmax = 68719476736 > kernel.shmall = 4294967296 > vm.overcommit_memory = 0 > vm.swappiness = 0 > vm.dirty_background_bytes = 536870912 > vm.dirty_bytes = 536870912 > > > postgresql.conf: > listen_addresses = '*' # what IP address(es) to listen on; > max_connections = 150 # (change requires restart) > shared_buffers = 48GB # min 128kB > work_mem = 1310MB # min 64kB > maintenance_work_mem = 24GB # min 1MB > wal_level = hot_standby # minimal, archive, or hot_standby > checkpoint_segments = 64 # in logfile segments, min 1, 16MB each > checkpoint_timeout = 30min # range 30s-1h > checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 > max_wal_senders = 5 # max number of walsender processes > wal_keep_segments = 2000 # in logfile segments, 16MB each; 0 disables > hot_standby = on # "on" allows queries during recovery > max_standby_archive_delay = 120s # max delay before canceling queries > max_standby_streaming_delay = 120s # max delay before canceling queries > effective_cache_size = 120GB > constraint_exclusion = partition # on, off, or partition > log_destination = 'syslog' # Valid values are combinations of > logging_collector = on # Enable capturing of stderr and csvlog > log_directory = 'pg_log' # directory where log files are written, > log_filename = 'postgresql-%a.log' # log file name pattern, > log_truncate_on_rotation = on # If on, an existing log file with the > log_rotation_age = 1d # Automatic rotation of logfiles will > log_rotation_size = 0 # Automatic rotation of logfiles will > log_min_duration_statement = 500 # -1 is disabled, 0 logs all statements > log_checkpoints = on > log_line_prefix = 'user=%u db=%d remote=%r ' # special values: > log_lock_waits = on # log lock waits >= deadlock_timeout > autovacuum = on # Enable autovacuum subprocess? 'on' > log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and > autovacuum_max_workers = 5 # max number of autovacuum subprocesses > datestyle = 'iso, mdy' > lc_messages = 'en_US.UTF-8' # locale for system error message > lc_monetary = 'en_US.UTF-8' # locale for monetary formatting > lc_numeric = 'en_US.UTF-8' # locale for number formatting > lc_time = 'en_US.UTF-8' # locale for time formatting > default_text_search_config = 'pg_catalog.english' > deadlock_timeout = 300ms > > > per pgtune: > > #------------------------------------------------------------------------------ > # pgtune wizard run on 2013-02-05 > # Based on 198333224 KB RAM in the server > > #------------------------------------------------------------------------------ > default_statistics_target = 100 > maintenance_work_mem = 1GB > checkpoint_completion_target = 0.9 > effective_cache_size = 128GB > work_mem = 1152MB > wal_buffers = 8MB > checkpoint_segments = 16 > shared_buffers = 44GB > max_connections = 80 > > We use pgbouncer (set to 140 connections) in transaction pooling mode in > front of our db. > > > The problem: > > For the most part, the server hums along. No other applications run on > this server other than postgres. Load averages rarely break 2.0, it never > swaps, and %iowait is usually not more than 0.12 > > But periodically, there are spikes in our app's db response time. > Normally, the app's db response time hovers in the 100ms range for most of > the day. During the spike times, it can go up to 1000ms or 1500ms, and the > number of pg connections goes to 140 (maxed out to pgbouncer's limit, where > normally it's only about 20-40 connections). Also, during these times, > which usually last less than 2 minutes, we will see several thousand > queries in the pg log (this is with log_min_duration_statement = 500), > compared to maybe one or two dozen 500ms+ queries in non-spike times. > > Inbetween spikes could be an hour, two hours, sometimes half a day. There > doesn't appear to be any pattern that we can see: > * there are no obvious queries that are locking the db > * it doesn't necessarily happen during high-traffic times, though it can > * it doesn't happen during any known system, db, or app > regularly-scheduled job, including crons > * in general, there's no discernible regularity to it at all > * it doesn't coincide with checkpoint starts or completions > * it doesn't coincide with autovacuums > * there are no messages in any system logs that might indicate any system > or hardware-related issue > > Besides spikes in our graphs, the only other visible effect is that > %system in sar goes from average of 0.7 to as high as 10.0 or so (%iowait > and all other sar variables remain the same). > > And according to our monitoring system, web requests get queued up, and > our alerting system sometimes either says there's a timeout or that it had > multiple web response times greater than 300ms, and so we suspect (but have > no proof) that some users will see either a long hang or possibly a > timeout. But since it's almost always less than two minutes, and sometimes > less than one, we don't really hear any complaints (guessing that most > people hit reload, and things work again, so they continue on), and we > haven't been able to see any negative effect ourselves. > > But we want to get in front of the problem, in case it is something that > will get worse as traffic continues to grow. We've tweaked various configs > on the OS side as well as the postgresql.conf side. What's posted above is > our current setup, and the problem persists. > > Any ideas as to where we could even look? > > Also, whether related or unrelated to the spikes, are there any > recommendations for our postgresql.conf or sysctl.conf based on our > hardware? From pgtune's output, I am lowering maintenance_work_mem from > 24GB down to maybe 2GB, but I keep reading conflicting things about other > settings, such as checkpoints or max_connections. > > johnny > >
