From: sachin kotwal <kotsachin@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
Date: Fri, 24 May 2013 06:22:22 -0700 (PDT)
Message-ID: <1369401742323-5756740.post@n5.nabble.com> (raw)
In-Reply-To: <519F5B4B.5010101@2ndQuadrant.com>
References: <CA+N_YAdDUXkzX9Z5p0G7An_f-=gc8p2ePJe4_aDijWAFQzwYWQ@mail.gmail.com>
<519F5B4B.5010101@2ndQuadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Thanks for reply.
>This could easily be caused by something outside of the test itself.
Background processes. A monitoring system kicking in to write some data to
disk will cause a drop like this.
>Three things you could look into to try and track down the issue:
>-Did you turn on log_checkpoints to see if one happens to run when the rate
drops?
Yes. I have turn on log_checkpoints.
Here i am sending my postgrsql.conf details:
postgresql.conf for pgbench-client and server machine.
Parameter: Value
Connection setup: Connection Settings
listen_addresses: *
max_connections: 150
superuser_reserved_connections: 10
tcp_keepalives_idle: 60
tcp_keepalives_interval: 5
tcp_keepalives_count: 5
Resource Consumption (Memory)
shared_buffers: 4 GB
maintenance_work_mem: 800 MB
Write Ahead Log
wal_level: archive
wal_sync_method: fdatasync
wal_buffers: 16 MB
synchronous_commit: on
Checkpoint
checkpoint_segments: 300
checkpoint_timeout:15 min
archive_mode: on
archive_command: cp %p /archive/archive_pg_hint_plan/%f
Query Planning
random_page_cost: 2
effective_cache_size: 8 GB
default_statistics_target: 10
Error Reporting and Logging
logging_collector: on
log_directory: /var/log/pgsql
log_filename: postgresql.log
log_rotation_age: 1d
log_rotation_size: 0
log_truncate_on_rotation: off
log_min_duration_statement: 20s
log_checkpoints: on
log_error_verbosity : verbose
log_connections: on
log_line_prefix: %t %p %c-%l %x %q %u, %d, %r, %a
log_lock_waits: on
Automatic Vacuuming
log_autovacuum_min_duration:1 min
autovacuum_max_workers: 4
autovacuum_freeze_max_age: 2000000000
autovacuum_vacuum_cost_limit: 400
>If so, that could be the explanation. Looks like you setup the test to
make this less likely, but checkpoints are tough to eliminate altogether.
>-Does this happen on every test run?
Yes. It happen on every test run.
>Is it at the same time?
No. It is not occurring at same time.There is no as such pattern.
>-You can run "top -bc" to dump snapshots of what the system is doing every
second. With some work you can then figure out what was actually happening
during the two seconds around when the throughput dropped.
I have used vmstat, iostat, sar, pidstat, top etc.
here i am sending some snaps from above tolls logs.
1. postgresql log
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kotsachin@gmail.com
Subject: Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
In-Reply-To: <1369401742323-5756740.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox