From: John Arbash Meinel <john@arbash-meinel.com>
To: pgsql-performance@postgresql.org
Subject: Re: fine tuning for logging server
Date: Wed, 30 Mar 2005 19:59:30 -0600
Message-ID: <424B5982.3090809@arbash-meinel.com> (raw)
In-Reply-To: <1112233809.10313.31.camel@stantz.corp.sgi.com>
References: <1112233809.10313.31.camel@stantz.corp.sgi.com>
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com
Subject: Re: fine tuning for logging server
In-Reply-To: <424B5982.3090809@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox