Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 7EE382E0048 for ; Mon, 7 Apr 2008 09:18:16 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24637-06 for ; Mon, 7 Apr 2008 09:18:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fabaglexfr.fabasoft.com (fabaglexfr.fabasoft.com [192.84.221.196]) by developer.postgresql.org (Postfix) with ESMTP id 565C12E0038 for ; Mon, 7 Apr 2008 09:18:13 -0300 (ADT) Received: from FABAMAIL.fabagl.fabasoft.com ([10.10.5.137]) by fabaglexfr.fabasoft.com with InterScan Messaging Security Suite; Mon, 07 Apr 2008 14:17:31 +0200 x-mimeole: Produced By Microsoft Exchange V6.5 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable x-cr-puzzleid: {4D3D696A-142C-4B4B-BF2F-F9427E3A0EBA} Content-class: urn:content-classes:message x-cr-hashedpuzzle: ARgm ATRH A5Go CBwn DnIn Dv8p Es63 FFgp FZ5y FzqP HRak Impz I+4A J7+r KF2f KKnw; 1; cABnAHMAcQBsAC0AcABlAHIAZgBvAHIAbQBhAG4AYwBlAEAAcABvAHMAdABnAHIAZQBzAHEAbAAuAG8AcgBnAA==; Sosha1_v1; 7; {4D3D696A-142C-4B4B-BF2F-F9427E3A0EBA}; cgBvAGIAZQByAHQALgBoAGUAbABsAEAAZgBhAGIAYQBzAG8AZgB0AC4AYwBvAG0A; Mon, 07 Apr 2008 12:16:11 GMT; TABvAG8AawBpAG4AZwAgAGYAbwByACAAYgBvAHQAdABsAGUAbgBlAGMAawAgAGQAdQByAGkAbgBnACAAbABvAGEAZAAgAHQAZQBzAHQA Subject: Looking for bottleneck during load test Date: Mon, 7 Apr 2008 14:16:11 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Looking for bottleneck during load test Thread-Index: AciYqSq7ail109WfR46PRvZukuANIw== From: "Hell, Robert" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/53 X-Sequence-Number: 29546 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=B2 CLARiiON on RAID 1/0 (8 x 146 GB 15k rpm).=20 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=3D0.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=3D0.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