Florin Andrei wrote:
On Wed, 2005-03-30 at 17:50 -0800, Florin Andrei wrote:
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.
For performance reasons, i was thinking to keep the tables append-only, and simply rotate them out every so often (daily?) and delete those tables that are too old. Is that a good idea?
If you aren't doing updates, then I'm pretty sure the data stays packed pretty well. I don't know that you need daily rotations, but you certainly could consider some sort of rotation schedule.
The biggest performance improvement, though, is probably to group inserts into transactions. I had an application (in a different db, but it should be relevant), where using a transaction changed the time from 6min -> 6 sec. It was just thrashing on all the little inserts that it had to fsync to disk.
How fast is fast? How many log messages are you expecting? 1/s 100/s 1000/s? I think the hardware should be capable of the 10-100 range if things are properly configured. Naturally that depends on all sorts of factors, but it should give you an idea.
John =:->
signature.asc
Description: OpenPGP digital signature
