X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 823B79FA236 for ; Tue, 12 Sep 2006 18:07:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 80348-02 for ; Tue, 12 Sep 2006 18:07:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moonunit3.moonview.localnet (wsip-70-167-125-69.sd.sd.cox.net [70.167.125.69]) by postgresql.org (Postfix) with ESMTP id 3885B9FA1E2 for ; Tue, 12 Sep 2006 18:07:08 -0300 (ADT) Received: from [127.0.0.1] (localhost.localdomain [127.0.0.1]) by moonunit3.moonview.localnet (8.13.1/8.13.1) with ESMTP id k8CL5rkc024705; Tue, 12 Sep 2006 14:05:54 -0700 Message-ID: <45072130.6020508@modgraph-usa.com> Date: Tue, 12 Sep 2006 14:05:52 -0700 From: "Craig A. James" User-Agent: Thunderbird 1.5.0.5 (X11/20060719) MIME-Version: 1.0 To: Tom Lane CC: Laszlo Nagy , pgsql-performance@postgresql.org Subject: Re: Poor performance on seq scan References: <450684EC.4090304@designaproduct.biz> <17034.1158079926@sss.pgh.pa.us> In-Reply-To: <17034.1158079926@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.185 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200609/103 X-Sequence-Number: 20789 >> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id >> 1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0 >> 0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0 >> 0 12 125.66 128 15.75 125.26 128 15.68 10 0 85 6 0 >> 0 12 124.66 129 15.67 124.39 129 15.64 12 0 85 3 0 >> 0 12 117.13 121 13.87 117.95 121 13.96 12 0 84 5 0 >> 0 12 104.84 118 12.05 105.84 118 12.19 10 0 87 2 0 > > Why is that showing 85+ percent *system* CPU time?? I could believe a > lot of idle CPU if the query is I/O bound, or a lot of user time if PG > was being a hog about doing the ~~ comparisons (not too unlikely BTW). > But if the kernel is eating all the CPU, there's something very wrong, > and I don't think it's Postgres' fault. There IS a bug for SATA disk drives in some versions of the Linux kernel. On a lark I ran some of the I/O tests in this thread, and much to my surprise discovered my write speed was 6 MB/sec ... ouch! On an identical machine, different kernel, the write speed was 54 MB/sec. A couple of hours of research turned up this: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=168363 The fix for me was to edit /boot/grub/grub.conf, like this: kernel /vmlinuz-2.6.12-1.1381_FC3 ro root=LABEL=/ rhgb quiet \ ramdisk_size=12000000 ide0=noprobe ide1=noprobe Notice the "ideX=noprobe". Instant fix -- after reboot the disk write speed jumped to what I expected. Craig