From: Hell, Robert <Robert.Hell@fabasoft.com>
To: pgsql-performance@postgresql.org
Subject: Looking for bottleneck during load test
Date: Mon, 7 Apr 2008 14:16:11 +0200
Message-ID: <B710F3299F04664DB6B37C258FDEEB94015F1BA1@FABAMAIL.fabagl.fabasoft.com> (raw)
We have a PostgreSQL 8.2.7 database (~230 GB) running on a machine with 8 Intel Xeon Cores and 32 GB RAM (64-bit Linux 2.6.18). Data is stored on an EMC² CLARiiON on RAID 1/0 (8 x 146 GB 15k rpm).
When we do random I/O with a small test tool (reading random 8k blocks from big files in 200 threads) on the disk we retrieve data with about 25 MB/s.
For testing purpose a test set of about 700.000 queries (those were logged during a problem situation) are executed against the database in 180 concurrent threads.
Some of the queries are very small and fast - other ones read more than 50000 blocks. All queries are selects (using cursors) - there is only read activity on the database.
By setting tuple_fraction for cursors to 0.0 instead of 0.1 (http://archives.postgresql.org/pgsql-performance/2008-04/msg00018.php) we reduced reads during the test (pg_statio_all_tables):
- 8.2.7
reads from disk: 4.395.276, reads from cache: 471.575.925
- 8.2.7 cursor_tuple_fraction=0.0
Reads from disk: 3.406.164, reads from cache: 37.924.625
But the duration of the test was only reduced by 18 % (from 110 minutes to 90 minutes).
When running the test with tuple_fraction=0.0 we observe the following on the server:
- avg read from disk is at 7 MB/s
- when we start the random I/O tool during the test we again read data with about 25 MB/s from disk (for me it seems that disk isn't the bottleneck)
- cpu time is divided between idle and iowait - user and system cpu are practically zero
- there are from 5000 to 10000 context switches per second
I can't see a bottleneck here. Does anyone has an explanation for that behavior?
Regards,
Robert
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: Robert.Hell@fabasoft.com
Subject: Re: Looking for bottleneck during load test
In-Reply-To: <B710F3299F04664DB6B37C258FDEEB94015F1BA1@FABAMAIL.fabagl.fabasoft.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