Re: optimizing for fast INSERTs

On Wednesday, December 5, 2001, at 02:58 PM, Florin Andrei wrote:

> After running several tests with PostgreSQL-7.1.3 versus some other
> opensourced SQL servers, i was very pleased by its performance when it
> comes to SELECTs on multiple large tables.
> But there's a problem: if i use PostgreSQL as a SQL backend for Snort,
> running Snort as a network sniffer and logging all relevant information
> about IP packets into the database, i see a lot of packet drops.
> Basically, once the traffic is higher that a certain limit, and once
> PostgreSQL starts eating up 100% CPU time, it starts missing packets.
> Snort is using just one database, and has several tables in it; each
> table has 5...10 columns, each column is an integer type. In this SQL
> structure, Snort is doing a lot of INSERTs, very fast.
> In these conditions, i need to make PostgreSQL as fast as it can be.

Logging packets is a tough job for a database...I can only think
of two things:

1) Instead of logging directly to the database, log to a plain text file
and then
periodically run a separate program which pulls records from that file
into
the database.

2) Dig through the Snort source code and see if you can pool writes by
using a transaction...though that may not help your CPU overhead problem.

--Jeremy