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 2B3EB5352E for ; Thu, 31 Mar 2005 02:59:35 +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 32489-05 for ; Thu, 31 Mar 2005 01:59:33 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 8952A53514 for ; Thu, 31 Mar 2005 02:59:29 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j2V1xWOX015443 for ; (envelope-from ) Wed, 30 Mar 2005 19:59:32 -0600 (CST) Received: from [192.168.1.13] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j2V1xV24011792 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256) for ; (envelope-from ) Wed, 30 Mar 2005 19:59:32 -0600 (CST) Message-ID: <424B5982.3090809@arbash-meinel.com> Date: Wed, 30 Mar 2005 19:59:30 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: fine tuning for logging server References: <1112233809.10313.31.camel@stantz.corp.sgi.com> In-Reply-To: <1112233809.10313.31.camel@stantz.corp.sgi.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigAB399C062623FE0A1E9D2019" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean 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/667 X-Sequence-Number: 11372 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigAB399C062623FE0A1E9D2019 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Florin Andrei wrote: >Hardware: relatively modern Intel CPU, OS and database each on its own >IDE hard-drive (separate IDE cables). Enough memory, i think, but i >can't add too much (not beyond 1GB). >Software: Linux-2.6, pgsql-8.0.1 > >Function: Essentially a logging server. There are two applications (like >syslog) on the same box that are logging to pgsql, each one to its own >database. There are a few tables in one DB, and exactly one table in the >other. >Most of the time, the apps are just doing mindless INSERTs to the DB. >Every now and then, an admin performs some SELECTs via a PHP interface. > >Objective: Make the DB as fast as possible. Of course i'd like the >SELECTs to be fast, but the INSERTs take precedence. It's gotta be able >to swallow as many messages per second as possible given the hardware. > >Question: What are the pgsql parameters that need to be tweaked? What >are the guidelines for such a situation? > > > Put pg_xlog onto the same drive as the OS, not the drive with the database. Do as many inserts per transaction that you can get away with. 100-1000 is pretty good. Keep the number of indexes and foreign key references low to keep INSERTS fast. Keep a few indexes around to keep SELECTs reasonable speedy. If you are doing lots and lots of logging, need only archival and slow access for old data, but fast access on new data, consider partitioning your table, and then using a view to join them back together. If you are only having a couple processing accessing the db at any given time, you can probably increase work_mem and maintenance_work_mem a bit. If you have 1G ram, maybe around 50M for work_mem. But really this is only if you have 1-3 selects going on at a time. With 2 disks, and fixed hardware, it's a lot more about configuring your schema and the application. If you want more performance, adding more disks is probably the first thing to do. John =:-> --------------enigAB399C062623FE0A1E9D2019 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCS1mCJdeBCYSNAAMRArkmAKCT0JJdeTokR5ULUxNiOabH9o28tACguH9d dYbyOHUGAB57qw5nAuyYnus= =TVeo -----END PGP SIGNATURE----- --------------enigAB399C062623FE0A1E9D2019--