X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9EF6C54CF8 for ; Sat, 5 Mar 2005 00:15:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46862-09 for ; Sat, 5 Mar 2005 00:15:53 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.199]) by svr1.postgresql.org (Postfix) with ESMTP id 5009754C55 for ; Sat, 5 Mar 2005 00:15:52 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so983133wra for ; Fri, 04 Mar 2005 16:15:55 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=nflCwM70N/60i38w5YKQy+40L0HfPod3sgR6kTvbaIXGflwpBzpjOxJII9hBrVw6Dx9vMNDYeoeFaBirfHtJXmHzgcj04kZepJO/yeWAOPMbkZii5piFQUwJhSsWdK6cW9OwFnfXsuB19QouWafq1b2O7fwFHxe0I+wr7jyTTFc= Received: by 10.54.10.36 with SMTP id 36mr19906wrj; Fri, 04 Mar 2005 16:15:55 -0800 (PST) Received: by 10.54.18.59 with HTTP; Fri, 4 Mar 2005 16:15:55 -0800 (PST) Message-ID: <33c6269f05030416151b826a9c@mail.gmail.com> Date: Fri, 4 Mar 2005 19:15:55 -0500 From: Alex Turner Reply-To: Alex Turner To: "Vig, Sandor (G/FI-2)" Subject: Re: multi billion row tables: possible or insane? Cc: John Arbash Meinel , "pgsql-performance@postgresql.org" In-Reply-To: <977921B17B2F2048AA5AAE9B4CBB713E01423D75@huaudigs0035.audi.de> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <977921B17B2F2048AA5AAE9B4CBB713E01423D75@huaudigs0035.audi.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.033 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/85 X-Sequence-Number: 10790 Not true - with fsync on I get nearly 500 tx/s, with it off I'm as high as 1600/sec with dual opteron and 14xSATA drives and 4GB RAM on a 3ware Escalade. Database has 3 million rows. As long as queries use indexes, multi billion row shouldn't be too bad. Full table scan will suck though. Alex Turner netEconomist On Tue, 1 Mar 2005 16:40:29 +0100, Vig, Sandor (G/FI-2) wrote: > 385 transaction/sec? > > fsync = false > > risky but fast. > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of John Arbash > Meinel > Sent: Tuesday, March 01, 2005 4:19 PM > To: Ramon Bastiaans > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] multi billion row tables: possible or insane? > > Ramon Bastiaans wrote: > > > Hi all, > > > > I am doing research for a project of mine where I need to store > > several billion values for a monitoring and historical tracking system > > for a big computer system. My currect estimate is that I have to store > > (somehow) around 1 billion values each month (possibly more). > > > If you have that 1 billion perfectly distributed over all hours of the > day, then you need 1e9/30/24/3600 = 385 transactions per second. > > Which I'm pretty sure is possible with postgres, you just need pretty > beefy hardware. And like Jeff said, lots of disks for lots of IO. > Like a quad opteron, with 16GB of ram, and around 14-20 very fast disks. > raid10 not raid5, etc. To improve query performance, you can do some > load balancing by having replication machines by using Slony. > > Or if you can do batch processing, you could split up the work into a > few update machines, which then do bulk updates on the master database. > This lets you get more machines into the job, since you can't share a > database across multiple machines. > > > I was wondering if anyone has had any experience with these kind of > > big numbers of data in a postgres sql database and how this affects > > database design and optimization. > > > Well, one of the biggest things is if you can get bulk updates, or if > clients can handle data being slightly out of date, so you can use > cacheing. Can you segregate your data into separate tables as much as > possible? Are your clients okay if aggregate information takes a little > while to update? > > One trick is to use semi-lazy materialized views to get your updates to > be fast. > > > What would be important issues when setting up a database this big, > > and is it at all doable? Or would it be a insane to think about > > storing up to 5-10 billion rows in a postgres database. > > I think you if you can design the db properly, it is doable. But if you > have a clients saying "I need up to the second information on 1 billion > rows", you're never going to get it. > > > > > The database's performance is important. There would be no use in > > storing the data if a query will take ages. Query's should be quite > > fast if possible. > > > Again, it depends on the queries being done. > There are some nice tricks you can use, like doing a month-by-month > partitioning (if you are getting 1G inserts, you might want week-by-week > partitioning), and then with a date column index, and a union all view > you should be able to get pretty good insert speed, and still keep fast > *recent* queries. Going through 1billion rows is always going to be > expensive. > > > I would really like to hear people's thoughts/suggestions or "go see a > > shrink, you must be mad" statements ;) > > > > Kind regards, > > > > Ramon Bastiaans > > I think it would be possible, but there are a lot of design issues with > a system like this. You can't go into it thinking that you can design a > multi billion row database the same way you would design a million row db. > > John > =:-> > > The information transmitted is intended only for the person or entity to > which it is addressed and may contain confidential and/or privileged > material. Any review, retransmission, dissemination or other use of, or > taking of any action in reliance upon, this information by persons or > entities other than the intended recipient is prohibited. If you received > this in error, please contact the sender and delete the material from any > computer. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >