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 9DD01D1B233 for ; Fri, 9 Jul 2004 06:28:26 -0300 (ADT) 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 84980-02 for ; Fri, 9 Jul 2004 09:28:25 +0000 (GMT) Received: from smtp-out4.blueyonder.co.uk (smtp-out4.blueyonder.co.uk [195.188.213.7]) by svr1.postgresql.org (Postfix) with ESMTP id C579CD1B1F8 for ; Fri, 9 Jul 2004 06:28:21 -0300 (ADT) Received: from lappy ([82.43.186.55]) by smtp-out4.blueyonder.co.uk with Microsoft SMTPSVC(5.0.2195.5600); Fri, 9 Jul 2004 10:28:39 +0100 Message-ID: <011301c46597$15d145c0$0300a8c0@lappy> From: "Andy Ballingall" To: "Christopher Kings-Lynne" Cc: References: <20040708101913.GA15871@uio.no> <00ac01c46516$3ec859e0$0300a8c0@lappy> <40EDF7DE.8080900@familyhealth.com.au> Subject: Re: Working on huge RAM based datasets Date: Fri, 9 Jul 2004 10:28:24 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-OriginalArrivalTime: 09 Jul 2004 09:28:39.0884 (UTC) FILETIME=[1E77A0C0:01C46597] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/52 X-Sequence-Number: 7443 Thanks, Chris. > > What is it about the buffer cache that makes it so unhappy being able to > > hold everything? I don't want to be seen as a cache hit fascist, but isn't > > it just better if the data is just *there*, available in the postmaster's > > address space ready for each backend process to access it, rather than > > expecting the Linux cache mechanism, optimised as it may be, to have to do > > the caching? > > Because the PostgreSQL buffer management algorithms are pitiful compared > to Linux's. In 7.5, it's improved with the new ARC algorithm, but still > - the Linux disk buffer cache will be very fast. > I've had that reply elsewhere too. Initially, I was afraid that there was a memory copy involved if the OS buffer cache supplied a block of data to PG, but I've learned a lot more about the linux buffer cache, so it now makes more sense to me why it's not a terrible thing to let the OS manage the lions' share of the caching on a high RAM system. On another thread, (not in this mailing list), someone mentioned that there are a class of databases which, rather than caching bits of database file (be it in the OS buffer cache or the postmaster workspace), construct a a well indexed memory representation of the entire data in the postmaster workspace (or its equivalent), and this, remaining persistent, allows the DB to service backend queries far quicker than if the postmaster was working with the assumption that most of the data was on disk (even if, in practice, large amounts or perhaps even all of it resides in OS cache). Though I'm no stranger to data management in general, I'm still in a steep learning curve for databases in general and PG in particular, but I just wondered how big a subject this is in the development group for PG at the moment? After all, we're now seeing the first wave of 'reasonably priced' 64 bit servers supported by a proper 64 bit OS (e.g. linux). HP are selling a 4 Opteron server which can take 256GB of RAM, and that starts at $10000 (ok - they don't give you that much RAM for that price - not yet, anyway!) This is the future, isn't it? Each year, a higher percentage of DB applications will be able to fit entirely in RAM, and that percentage is going to be quite significant in just a few years. The disk system gets relegated to a data preload on startup and servicing the writes as the server does its stuff. Regards, Andy