pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
3+ messages / 3 participants
[nested] [flat]
* pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
@ 2013-05-23 11:39 Sachin D. Bhosale-Kotwal <kotsachin@gmail.com>
2013-05-24 12:21 ` Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance Greg Smith <greg@2ndQuadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sachin D. Bhosale-Kotwal @ 2013-05-23 11:39 UTC (permalink / raw)
To: pgsql-performance
Hello,
I am testing performance of postgresql application using pgbench.
I am getting spike in results(graphs) as shown in attached graph due to
throughput drop at that time.
pgbench itself doing checkpoint on server (where queries are running)
before and after test starts.
pgbench is running on separate client machine.
actual queries are running on separate server machine.
*Test configurations:*
test duration is 5 minutes
numbers clients are 120
scale is 100
query mode is prepared
only select queries are used.
result graph: see attachment tps.png
*spike is at 12:09:14*
*My Observatios:*
*
*
In vmstat, sar , iostat, top logs i found that at the time of spike there
is more iowait on pgbench-client.
there is similar iowait at another timestamp present but there is no spike.
So i am not getting why spike occure at *12:09:14 *only*.*
If anyone find solution of this problem please reply.
Also i am working to get context switches at the time of spike occurred.
Please reply if any clue.
------------------------------------------------------------------------------
*pgbench Client machine configuration:*
Hardware and OS specifications for pgbench-client
Parameter:Value
Processor: INTEL XEON (E5645) 2.40GHz*2 PROCESSOR
Total Cores:12. 6 cores per processor
RAM: 8 GB RAM (4GB*2)
HDD: 300GB*2 SAS HDD. RAID 1 configured. So only one disk in use at a time.
2nd disk is used for mirroring.
Operating System:GNU/Linux
Red Hat release: Red Hat Enterprise Linux Server release 6.3 (Santiago)
*Server machine configuration and environment setup:*
Hardware and OS specifications for server:
Parameter: Value
Processors: Xeon E5-2650 Processor Kit , Intel® Xeon ® Processor E5-2650 (2
GHz, 8C/16T, 20 MB) * 2 nos (Part No. N8101-549F)
RAM: 32GB DDR3-1600 REG Memory Kit , 8x 4GB Registered ECC DIMM,
DDR3L-1600(PC3L-12800) (Part No. N8102-469F)
HDD: 450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos 1 x 450 GB SAS HDD,
2.5-inch, 6Gb/s, 10,000 rpm (Part No. N8150-322)
Operating System: GNU/Linux
Red Hat release: Red Hat Enterprise Linux Server release 6.3 (Santiago)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
tps.png (image/png, 32.4K) download
view image
* Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
2013-05-23 11:39 pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance Sachin D. Bhosale-Kotwal <kotsachin@gmail.com>
@ 2013-05-24 12:21 ` Greg Smith <greg@2ndQuadrant.com>
2013-05-24 13:22 ` Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance sachin kotwal <kotsachin@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Smith @ 2013-05-24 12:21 UTC (permalink / raw)
To: pgsql-performance
On 5/23/13 7:39 AM, Sachin D. Bhosale-Kotwal wrote:
> So i am not getting why spike occure at *12:09:14 *only*.*
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? 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? Is it at the same time?
-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.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance
2013-05-23 11:39 pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance Sachin D. Bhosale-Kotwal <kotsachin@gmail.com>
2013-05-24 12:21 ` Re: pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance Greg Smith <greg@2ndQuadrant.com>
@ 2013-05-24 13:22 ` sachin kotwal <kotsachin@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: sachin kotwal @ 2013-05-24 13:22 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-05-24 13:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-23 11:39 pgbench: spike in pgbench results(graphs) while testing pg_hint_plan performance Sachin D. Bhosale-Kotwal <kotsachin@gmail.com>
2013-05-24 12:21 ` Greg Smith <greg@2ndQuadrant.com>
2013-05-24 13:22 ` sachin kotwal <kotsachin@gmail.com>
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