Re: Queries seldomly take >4s while normally take <1ms?

Hello

On Tue, 9 Apr 2013 03:53:13 -0700 (PDT)
Greg Williamson <gwilliamson39(at)yahoo(dot)com> wrote:

> Christian --
>
> <original text snip because this POS editor won't let me properly edit>
>
> postgres version ?

9.2.3

> type of replication ?

As written, one master does streaming replication to two slaves.

> changes from postgres config defaults ?

max_connections = 1000 # (change requires restart)
shared_buffers = 20GB # min 128kB
wal_level = hot_standby # minimal, archive, or hot_standby
archive_mode = on # allows archiving to be done
archive_command = 'test ! -f /srv/postgresql-data/archivedir/%f && cp %p /srv/postgresql-data/archivedir/%f' # command to use to archive a logfile segment
max_wal_senders = 3 # max number of walsender processes
hot_standby = on # "on" allows queries during recovery
max_standby_archive_delay = 1h # max delay before canceling queries
max_standby_streaming_delay = 1h # max delay before canceling queries
hot_standby_feedback = on # send info from standby to prevent
effective_cache_size = 1024MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = '/var/log/postgresql/' # directory where log files are written,
log_filename = 'postgresql-9.2-data.log' # log file name pattern,
log_file_mode = 0640 # creation mode for log files,
log_rotation_age = 0 # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_min_messages = notice
log_min_duration_statement = 500 # -1 is disabled, 0 logs all statements
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t [%p] %u(at)%d ' # special values:
log_statement = 'all' # none, ddl, mod, all
log_timezone = 'Europe/Berlin'
track_activities = on
track_counts = on
track_io_timing = on
datestyle = 'iso, dmy'
timezone = 'Europe/Berlin'
lc_messages = 'en_GB.UTF-8' # locale for system error message
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all

effective_cache_size is way too low, I noticed, can that be the cause?

> Do they happen more at peak usage, semi regularly or sporadically ?

Sporadically. Not at a specific hour or minute-of-hour. Not only at peak
times either. Sometimes at 05:xx in the morning, sometings at 10:xxh.

> Possibly some sporadic postgres process such as checkpoints of
> autovac processes kicking off. Do your logs show anything ?

As far as I understood, Auto-Vacuum and Auto-Cleaning only happen on
the master and their results get to the slaves via streaming
replication. I therefore checked the master logs and, as written,
all occurences of Auto-* are at least half an hour before or after.

> HTH,
>
> Greg W.

bye,

-christian-