Date: Fri, 24 May 2013 06:22:22 -0700 (PDT)
From: sachin kotwal <kotsachin@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1369401742323-5756740.post@n5.nabble.com>
In-Reply-To: <519F5B4B.5010101@2ndQuadrant.com>
References: 
 <CA+N_YAdDUXkzX9Z5p0G7An_f-=gc8p2ePJe4_aDijWAFQzwYWQ@mail.gmail.com>
	<519F5B4B.5010101@2ndQuadrant.com>
Subject: Re: pgbench: spike in pgbench results(graphs) while testing
	pg_hint_plan performance
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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