optimizing for fast INSERTs

Поиск
Список
Период
Сортировка
Искать

optimizing for fast INSERTs

От:
Florin Andrei <florin@sgi.com>
Дата:
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.
On the same hardware, sniffing the same traffic, using other SQL servers
as backends, the CPU usage is smaller and i don't see any packet drops
(but then i'm missing PostgreSQL capabilities).

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.

I already tried "fsync = false", but it's not enough. It looks like i
have to use every trick in the book to speed it up. Can you give me some
suggestions on something else i can try?

-- 
Florin Andrei

"Engineering does not require science." - Linus Torvalds

Re: optimizing for fast INSERTs

От:
Jeremy Buchmann <jeremy@wellsgaming.com>
Дата:
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
FAQ