[Snort-devel] database schema: postgresql

Edward Balas ebalas at ...1183...
Thu Mar 7 13:43:11 EST 2002


On Thu, 7 Mar 2002, Roman Danyliw wrote:

> - What were the changes you made to the schema?
> - As a baseline, do you know how long it takes to delete 44k of alerts with the
> current schema after you VACCUM ANALYZE?
> - How were you deleting the events?
> - What version of PostgreSQL?
> - Hardware?
> - Relevant kernel/postgresql.conf settings?
>

My prime motivation for cascading deletes was to simplify the delete
process.  So for the test I was deleting all alerts of a specific type
of signature, and all related iphdr, data and other rows.  To do that
I issued the command "delete from signature where sig_id = '13'; " and
the cascading took care of the rest.

I will do a more complete writeup including another
running of the tests,  documented schema changes etc.  But for now
I can say that all changes focused on which columns where indexed.

The initial delete that took 4 hours was done without doing a vaccum...
so I need to re run the "test" to eliminate confounding variables.

The hardware is a 1ghz single cpu with 2G of ram and a 10krpm scsi drive,
it is running freebsd4.5 with some kernel tuning and postgres 7.1.3.

anywho Ill try to have that out by the end of the week, presuming that the
weather doesnt get any nicer ;-)

Ed





More information about the Snort-devel mailing list