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 AA63453A8F for ; Wed, 30 Mar 2005 12:03:06 +0100 (BST) 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 46964-06 for ; Wed, 30 Mar 2005 11:02:57 +0000 (GMT) Received: from mx0.ffm0.de.carpe.net (mx0.ffm0.de.carpe.net [212.96.133.12]) by svr1.postgresql.org (Postfix) with ESMTP id B8E2353A87 for ; Wed, 30 Mar 2005 12:02:55 +0100 (BST) Received: by mx0.ffm0.de.carpe.net (Postfix, from userid 2000) id 204E75619; Wed, 30 Mar 2005 13:02:52 +0200 (CEST) Received: from [192.168.0.102] (host-217-159-10-74.satlynx.net [217.159.10.74]) by mx0.ffm0.de.carpe.net (Postfix) with ESMTP id CBFAE55E7; Wed, 30 Mar 2005 13:02:47 +0200 (CEST) Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Message-Id: Content-Transfer-Encoding: quoted-printable From: Marc Burgauer Subject: Reading recommendations Date: Wed, 30 Mar 2005 12:07:29 +0100 To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/647 X-Sequence-Number: 11352 Hi I am looking for some references to literature. While we have used=20 PostgreSQL in the past for a fair number of smaller projects, we are=20 now starting to use it on a larger scale and hence need to get into=20 issues of performance optimisation and tuning. While I am OK with using=20= the EXPLAIN features, I am getting quite insecure when facing things=20 like the options in the postgresql.conf file. For example reading the=20 man page on fsync option, it tells me to "read the detailed=20 documentation before using this!" I then read the Admin guide where I=20 get told that the benefits of this feature are issue of debate, leaving=20= me with little help as to how to make up my mind on this issue. So I=20 turn to this mailing list, but starting reading in the archive realise=20= that compared to the knowledge standard here, I am as wise as a baby. I have read most of Bruce Momjian's book on PostgreSQL (Can I update my=20= 2001 edition somehow? ;-) I have Sams' PostgreSQL Developer's Handbook (which is OK too), but=20 offers little to nothing on operational issues. I have read most of the admin (and user) guide, but it does not help me=20= really understand the issues: > CPU_INDEX_TUPLE_COST (floating point) Sets the query optimizer=92s=20 > estimate of the cost of processing each index tuple during an index=20 > scan. This is measured as a fraction of the cost of a sequential page=20= > fetch. No idea what this means! (And should I feel bad for it?) I am an application programmer with a little UNIX know-how. What books or sources are out there that I can buy/download and that I=20= should read to get to grips with the more advanced issues of running=20 PostgreSQL? More on what we do (for those interested): We use PostgreSQL mainly with its PostGIS extension as the database=20 backend for Zope-based applications. Adding PostGIS features is what=20 has started to cause noticeable increase in the server load. We're using the GIS enabled system on this platform: PostgreSQL 7.3.4 PostGIS 0.8 Zope 2.7.5 Python 2.3.5 (Database-based functions are written in PL/PGSQL, not python!!) on a 2-CPU (450MHz Intel P3) Compaq box (some Proliant flavour) With a SCSI 4-disk RAID system (mirrored and striped) SunOS 5.8 (Which I think is Solaris 8) The server is administrated by my host (co-located). We cannot easily=20 upgrade to a newer version of Solaris, because we could not find a=20 driver for the disk controller used in this server. (And our host did=20 not manage to write/patch one up.) As a business, we are creating and operating on-line communities, (for=20= an example go to http://www.theguidlife.net) not only from a technical=20= point of view, but also supporting the communities in producing=20 content. BTW. If you are a SQL/python programmer in (or near) Lanarkshire,=20 Scotland, we have a vacancy. ;-) Cheers Marc -- Marc Burgauer Sharedbase Ltd http://www.sharedbase.com Creating and supporting on-line communities=