Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6C2C06348BC for ; Tue, 10 Mar 2009 15:01:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 14478-01 for ; Tue, 10 Mar 2009 15:01:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-2.exch018.msoutlookonline.net (exhub018-2.exch018.msoutlookonline.net [64.78.17.17]) by mail.postgresql.org (Postfix) with ESMTP id 24BA4634B8C for ; Tue, 10 Mar 2009 15:01:02 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-2.exch018.msoutlookonline.net ([64.78.17.17]) with mapi; Tue, 10 Mar 2009 11:01:01 -0700 From: Scott Carey To: Matthew Wakeling , henk de wit CC: "pgsql-performance@postgresql.org" Date: Tue, 10 Mar 2009 11:01:00 -0700 Subject: Re: When does sequential performance matter in PG? Thread-Topic: [PERFORM] When does sequential performance matter in PG? Thread-Index: AcmhjH1/oq84KqVuRK6YpbrBokQKzQAHawIJ Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DBF8E93248scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/83 X-Sequence-Number: 33005 --_000_C5DBF8E93248scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/10/09 6:28 AM, "Matthew Wakeling" wrote: On Tue, 10 Mar 2009, henk de wit wrote: > It is frequently said that for PostgreSQL the number 1 thing to pay > attention to when increasing performance is the amount of IOPS a storage > system is capable of. Now I wonder if there is any situation in which > sequential IO performance comes into play. E.g. perhaps during a > tablescan on a non-fragmented table, or during a backup or restore? Yes, up to a point. That point is when a single CPU can no longer handle the sequential transfer rate. Yes, there are some parallel restore possibilities which will get you further. Generally it only takes a few discs to max out a single CPU though. This is not true if you have concurrent sequential scans. Then an array c= an be tuned for total throughput with concurrent access. Single thread seq= uential measurements are similarly useful to single thread random i/o measu= rement - not really a test like the DB will act, but useful as a starting p= oint for tuning. I'm past the point where a single thread can not keep up with the disk on a= sequential scan. For the most simple select * queries, this is ~ 800MB/se= c for me. For any queries those with more complicated processing/filtering, its much = less, usually 400MB/sec is a pretty good rate for a single thread. However our raw array does about 1200MB/sec, and can get 75% efficiency on = this or so with between 4 and 8 concurrent sequential scans. It took some = significant tuning and testing time to make sure this worked, and to balanc= e that with random i/o requirements. Furthermore, higher sequential rates help your random IOPS when you have se= quential access concurrent with random access. You can tune OS parameters = (readahead in linux, I/O scheduler types) to bias throughput or latency tow= ards random iops throughput or sequential MB/sec throughput. Having faster= sequential disk access means less % of time doing sequential I/O, meaning = more time left for random I/O. It only goes so far, but it does help with = mixed loads. Overall, it depends a lot on how important sequential scans are to your use= case. --_000_C5DBF8E93248scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] When does sequential performance matter in PG?


On 3/10/09 6:28 AM, "Matthew Wakeling" <matthew@flymine.org> wrote:

On Tue, 10 Mar 2009, henk de wit wrote:
> It is frequently said that for PostgreSQL the number 1 thing to pay > attention to when increasing performance is the amount of IOPS a stora= ge
> system is capable of. Now I wonder if there is any situation in which<= BR> > sequential IO performance comes into play. E.g. perhaps during a
> tablescan on a non-fragmented table, or during a backup or restore?
Yes, up to a point. That point is when a single CPU can no longer handle the sequential transfer rate. Yes, there are some parallel restore
possibilities which will get you further. Generally it only takes a few
discs to max out a single CPU though.




 --_000_C5DBF8E93248scottrichrelevancecom_--