X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 66EE6D1B538 for ; Wed, 10 Sep 2003 11:47:53 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 30471-10 for ; Wed, 10 Sep 2003 08:47:24 -0300 (ADT) Received: from mail2.fbab.net (spectre.fbab.net [195.54.134.139]) by svr1.postgresql.org (Postfix) with SMTP id B01F7D1B52B for ; Wed, 10 Sep 2003 08:47:20 -0300 (ADT) Received: (qmail 25383 invoked by uid 136); 10 Sep 2003 11:47:22 -0000 Received: from mag@fbab.net by mail2.fbab.net by uid 133 with qmail-scanner-1.20rc1 (avp: 4.0.3.0. Clear:RC:1:. Processed in 0.061223 secs); 10 Sep 2003 11:47:22 -0000 Received: from unknown (HELO mail2.fbab.net) (195.54.134.228) by mail2.fbab.net with SMTP; 10 Sep 2003 11:47:22 -0000 Received: from 129.178.88.66 (SquirrelMail authenticated user magimap1) by mail2.fbab.net with HTTP; Wed, 10 Sep 2003 13:47:22 +0200 (CEST) Message-ID: <51478.129.178.88.66.1063194442.squirrel@mail2.fbab.net> In-Reply-To: <91D7A447-E361-11D7-A142-000393011B1A@bangjafwac.com> References: <200309091711.32275.josh@agliodbs.com> <91D7A447-E361-11D7-A142-000393011B1A@bangjafwac.com> Date: Wed, 10 Sep 2003 13:47:22 +0200 (CEST) Subject: Re: Reading data in bulk - help? From: "Magnus Naeslund(w)" To: "Chris Huston" Cc: josh@agliodbs.com, pgsql-performance@postgresql.org Reply-To: mag@fbab.net User-Agent: SquirrelMail/1.4.1 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/169 X-Sequence-Number: 3535 Chris Huston said: > Thanks Josh that helped. I had gone looking for some kind of cluster > option but was looking under create database, create index and > initlocation - didn't see the CLUSTER index ON table. > > I ran the CLUSTER which took about 2 1/2 hours to complete. That > improved the query performance about 6x - which is great - but is still > taking 26 minutes to do what a serial read does in about 2 1/2 minutes. > > At this point I'm ok because each fetch is taking around 200 > milliseconds from call to the time the data is ready. The processing > takes 300-600ms per batch. I've got the fetch and the processing > running in separate threads so even if postgres was running faster it > wouldn't help this implementation. > > However, "iostat" is still reporting average size per transfer of about > 10kB and total thru-put of about 1MB/s. The transfers per second went > from >200/s to about 80/s. It still seams like it ought to be a faster. > > The system is currently running on a single processor 500Mhz G4. We're > likely to move to a two processor 2Ghz G5 in the next few months. Then > each block may take only a 30-60 milliseconds to complete and their can > be two concurrent blocks processing at once. > > Sometime before then I need to figure out how to cut the fetch times > from the now 200ms to something like 10ms. There are currently > 1,628,800 records in the single data table representing 6817 groups. > Each group has 2 to 284 records - with 79% having the max 284 (max > grows by 1 every day - although the value may change throughout the > day). Each record is maybe 1 or 2k so ideally each batch/group should > require 284-568k - at 10MB/s - that'd be > > RELATED QUESTION: How now do I speed up the following query: "select > distinct group_id from datatable"? Which results in a sequential scan > of the db. Why doesn't it use the group_id index? I only do this once > per run so it's not as critical as the fetch speed which is done 6817 > times. > > Thanks for the help! > - Chris > How are you fetching the data? If you are using cursors, be sure to fetch a substatial bit at a time so that youre not punished by latency. I got a big speedup when i changed my original clueless code to fetch 64 rows in a go instead of only one. Magnus