Re: Queries seldomly take >4s while normally take <1ms? - Mailing list pgsql-general

Hello

On Tue, 9 Apr 2013 03:53:13 -0700 (PDT)
Greg Williamson <gwilliamson39@yahoo.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@%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-