Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8B9DE1337BB8 for ; Sun, 10 Oct 2010 21:52:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 25358-03 for ; Mon, 11 Oct 2010 00:51:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by mail.postgresql.org (Postfix) with ESMTP id E804A1337B8D for ; Sun, 10 Oct 2010 21:51:54 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 1C8C8AE259; Mon, 11 Oct 2010 08:51:54 +0800 (WST) X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au Received: from mail.postnewspapers.com.au ([127.0.0.1]) by localhost (mail.postnewspapers.com.au [127.0.0.1]) (amavisd-new, port 10024) with LMTP id gdB6sH156fDX; Mon, 11 Oct 2010 08:51:47 +0800 (WST) Received: from [10.1.1.6] (124-148-209-200.dyn.iinet.net.au [124.148.209.200]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "Craig Ringer", Issuer "POST Certificate Authority" (verified OK)) by mail.postnewspapers.com.au (Postfix) with ESMTPSA id AB5AAAE46E; Mon, 11 Oct 2010 08:51:46 +0800 (WST) Message-ID: <4CB25F9F.4050204@postnewspapers.com.au> Date: Mon, 11 Oct 2010 08:51:43 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.9) Gecko/20100921 Fedora/3.1.4-1.fc14 Thunderbird/3.1.4 MIME-Version: 1.0 To: Joshua Tolley CC: mladen.gogala@vmsinfo.com, "pgsql-performance@postgresql.org" Subject: Re: Slow count(*) again... References: <201010091626.18636.neil.whelchel@gmail.com> <4CB11CC7.8070300@vmsinfo.com> <4CB167CA.9070109@postnewspapers.com.au> <4CB1F46E.80205@vmsinfo.com> <4CB2410C.6010603@postnewspapers.com.au> <4cb25a0e.2a978e0a.7797.33d6@mx.google.com> In-Reply-To: <4cb25a0e.2a978e0a.7797.33d6@mx.google.com> Content-Type: multipart/mixed; boundary="------------060304020409020201060600" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/104 X-Sequence-Number: 40523 This is a multi-part message in MIME format. --------------060304020409020201060600 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 10/11/2010 08:27 AM, Joshua Tolley wrote: > One thing a test program would have to take into account is multiple > concurrent users. What speeds up the single user case may well hurt the > multi user case, and the behaviors that hurt single user cases may have been > put in place on purpose to allow decent multi-user performance. Of course, all > of that is "might" and "maybe", and I can't prove any assertions about block > size either. But the fact of multiple users needs to be kept in mind. Agreed. I've put together a simple test program to test I/O chunk sizes. It only tests single-user performance, but it'd be pretty trivial to adapt it to spawn a couple of worker children or run several threads, each with a suitable delay as it's rather uncommon to have a bunch of seqscans all fire off at once. From this test it's pretty clear that with buffered I/O of an uncached 700mb file under Linux, the I/O chunk size makes very little difference, with all chunk sizes taking 9.8s to read the test file, with near-identical CPU utilization. Caches were dropped between each test run. For direct I/O (by ORing the O_DIRECT flag to the open() flags), chunk size is *hugely* significant, with 4k chunk reads of the test file taking 38s, 8k 22s, 16k 14s, 32k 10.8s, 64k - 1024k 9.8s, then rising a little again over 1024k. Apparently Oracle is almost always configured to use direct I/O, so it would benefit massively from large chunk sizes. PostgreSQL is almost never used with direct I/O, and at least in terms of the low-level costs of syscalls and file system activity, shouldn't care at all about read chunk sizes. Bumping readahead from 256 to 8192 made no significant difference for either case. Of course, I'm on a crappy laptop disk... I'm guessing this is the origin of the OP's focus on I/O chunk sizes. Anyway, for the single-seqscan case, I see little evidence here that using a bigger read chunk size would help PostgreSQL reduce overheads or improve performance. OP: Is your Oracle instance using direct I/O? -- Craig Ringer --------------060304020409020201060600 Content-Type: text/plain; name="testio.c" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="testio.c" #define _GNU_SOURCE #include #include #include #include #include #include // 4k alignment static const unsigned int ALIGN_SHIFT = 12; static void usage() { printf("Usage: test chunksize_kb filename\n"); exit(1); } int main(int argc, char * argv[]) { if (argc != 3) { usage(); } char * end; long chunksize_kb = strtol(argv[1], &end, 10); if (end == argv[1]) { printf("Cannot parse chunk size as number"); usage(); } long chunksize_bytes = chunksize_kb * 1024; int fd = open(argv[2], O_RDONLY|O_NOATIME|O_DIRECT); if (fd == -1) { perror("Unable to open input file"); usage(); } void * buf = malloc(chunksize_bytes + (1<>ALIGN_SHIFT)< 0); perror("read"); } --------------060304020409020201060600 Content-Type: text/plain; name="results_buffered_ra256" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="results_buffered_ra256" [craig@ayaki tmp]$ echo -n "direct=f Readahead: "; sudo blockdev --getra /dev/sda; for (( x = 2; x < 14; x++ )); do echo $((1</dev/null; time ./io $((1</dev/null; time ./io $((1<