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 661865649A for ; Tue, 1 Mar 2005 16:27:06 +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 05096-08 for ; Tue, 1 Mar 2005 16:27:01 +0000 (GMT) Received: from mail.logi-track.com (burro.logi-track.com [213.239.193.212]) by svr1.postgresql.org (Postfix) with ESMTP id 26F6356482 for ; Tue, 1 Mar 2005 16:27:00 +0000 (GMT) Received: from kingfisher.intern.logi-track.com (pD95DCE38.dip0.t-ipconnect.de [217.93.206.56]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logi-track.com (Postfix) with ESMTP id 0F43A307FB; Tue, 1 Mar 2005 17:27:03 +0100 (CET) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with ESMTP id 0B51FAB388; Tue, 1 Mar 2005 17:26:52 +0100 (CET) Message-ID: <422497C8.4040109@logi-track.com> Date: Tue, 01 Mar 2005 17:26:48 +0100 From: Markus Schaber User-Agent: Mozilla Thunderbird 1.0 (X11/20050116) X-Accept-Language: de-DE, de, en-us, en MIME-Version: 1.0 To: John Arbash Meinel Cc: Ramon Bastiaans , pgsql-performance@postgresql.org Subject: Re: multi billion row tables: possible or insane? References: <42243725.6060804@sara.nl> <422487E4.6040107@arbash-meinel.com> In-Reply-To: <422487E4.6040107@arbash-meinel.com> X-Enigmail-Version: 0.90.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.109 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/13 X-Sequence-Number: 10718 Hi, John, John Arbash Meinel schrieb: >> 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. I hope that he does not use one transaction per inserted row. In your in-house tests, we got a speedup factor of up to some hundred when bundling rows on insertions. The fastest speed was with using bunches of some thousand rows per transaction, and running about 5 processes in parallel. Regard the usual performance tips: Use a small, but fast-writing RAID for transaction log (no RAID-5 or RAID-6 variants), possibly a mirroring of two harddisk-backed SSD. Use different disks for the acutal data (here, LVM2 with growing volumes could be very handy). Have enough RAM. Use a fast file system. BTW, as you read about the difficulties that you'll face with this enormous amount of data: Don't think that your task will much be easier or cheaper using any other DBMS, neither commercial nor open source. For all of them, you'll need "big iron" hardware, and a skilled team of admins to set up and maintain the database. Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com