[Snort-devel] database schema: postgresql

Edward Balas ebalas at ...1183...
Mon Mar 11 08:34:07 EST 2002


Hey Roman,

Here is the more complete data on the test I performed, it
just so happened the weather was a little to nice on friday ;-)


Overview:

I wanted to find a way to improve the performance of a number of
operations on the snort database, specifically deletes.  As I
am a user of postgres I wanted to see if I could tighten up the
database while at the same time improving performance. So I
considered a number of operations, adding referential integrity,
using cascading deletes, creating views into the database to
simplify client queries.  For now I setted on the first 2.
I generated 2 new schemas, one that took the orginal snort schema
and added referential integrity with cascading deletes, and another
that took that schema and modified the indexes to attempt to improve
the exection performance of the queries.

The System:

The server is a 997Mhz P3 with 2G ram, and a 10k scsi disk.  I am running
FreeBSD 4.5.  I am running postgresql 7.1.3 with fsynch off, sort_mem =
1024, shared_buffers = 4096.  I am not sure if these are the best settings
but I am including them here for completeness.  The tests where performed local
to the server using psql.  Attached are the 2 schemas.


The Test:

Here are the steps I followed in performing the test.

1.  created 2 seperate databases, Both had Referential integrity and
    cascading deletes:

	ST1: was the first, it had no additional modifications to the
        indexes.

	ST2: was the secod, it had modifications to the indexes.
        My focus was on the cid/sid of each table and the any other
        F-key.

2.  modified the snort config to log to both ST1 and ST2.

3.  Generating 2000 alerts by sending large icmp pkts at test host to
    cause speedra alerts.

4.  For test I was measuring the time it took to complete the following
    query: "delete from signature where sig_id = '1'; ".  This caused all
    event rows, iphdr rows, etc that were related to
    to a signature with sig_id of 1 to be deleted.

5.  I performed the initial test by timing the execution of the test query
    on ST1 then ST2.

6.  ST1: 13.53 sec  ST2: 30.77

7.  repeating after running vaccum and vacuum analyze.

8.  ST1: 13.45 sec ST2: 0.95

9.  repeading with 25013 alerts, re vaccum and vacuum analyzed.

10. ST1: 1974.90 sec ST2: 12.62, ST1: 12.6 delps  ST2: 1982 delps



The results:

Without running vaccum analyze, ST1 saw 147.8 events deleted per
second(delps), ST2 on the other hand provided a meager 64.9 delps.
Next I reran the tests but having run vaccum analyze on both databases,
this time ST1 saw 138 delps and ST2 saw 2105.2 delps.

I was stunned by not only the slowness of ST2 in the first test but
more so by the improvement gained by vacuum analyzing the database.  In
the last test I increased the number of events to 25013 and re analyzed
the dbs, this time ST1 dropped to 12.63 delps and ST1 had only a slight
dropoff to 1982 delps.

This tells me that providing the query optimizer with reliable
stats makes postgresql very happy.  As with anything I would take these
number with a grain of salt because in my test databases the composition
did not mimic what one might see in a typical database, ie I didnt have
dozens of unique signatures and a good mix of packet types etc.


What else this doesnt tell me:

I havent compared this to the current way that a delete would work in the
current schema, so I dont yet know if this is any better than what we
currently have, though based on my initial experience I am gonna guess
that it is faster.

I dont have a good sense of what the modifications to the indexes will do
to the rate at which we can insert into the database.  I didnt notice any
problems during my test, but I wasnt even doing a flood ping.


Anyhow, if anyone has any other questions I will attempt to answer them...


ebalas
-------------- next part --------------
-- Copyright (C) 2000 Carnegie Mellon University
--
-- Author(s): Jed Pickel <jed at pickel.net>
--            Roman Danyliw <rdd at cert.org>, <roman at danyliw.com>
--            Todd Schrubb <tls at cert.org>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
-- This has referential integrity w/ cascading deletes but
-- indexing has not been tuned

CREATE TABLE schema ( vseq        INT4     NOT NULL,
                      ctime       DATETIME NOT NULL,
                      PRIMARY KEY (vseq));
INSERT INTO schema  (vseq, ctime) VALUES ('666', now());

CREATE TABLE sig_class ( sig_class_id        SERIAL,
                         sig_class_name      TEXT NOT NULL,
                         PRIMARY KEY (sig_class_id) );
CREATE INDEX sig_class_name_idx ON sig_class (sig_class_name);



CREATE TABLE signature ( sig_id       SERIAL NOT NULL,
                         sig_name     TEXT NOT NULL,
                         sig_class_id INT8,
                         sig_priority INT8,
                         sig_rev      INT8,
                         sig_sid      INT8,
                         PRIMARY KEY (sig_id),
			 FOREIGN KEY (sig_class_id) REFERENCES sig_class (sig_class_id) ON DELETE cascade
			);

CREATE INDEX sig_name_idx ON signature (sig_name);
CREATE INDEX sig_class_id ON signature (sig_class_id);



CREATE TABLE reference (  ref_id        SERIAL,
                          ref_system_id INT4 NOT NULL,
                          ref_tag       TEXT NOT NULL,
                          PRIMARY KEY (ref_id)
                       );


CREATE TABLE sig_reference (sig_id  INT4  NOT NULL,
                            ref_seq INT4  NOT NULL,
                            ref_id  INT4  NOT NULL,
                            PRIMARY KEY(sig_id, ref_seq),
			    FOREIGN KEY(sig_id) REFERENCES signature (sig_id) ON DELETE cascade,
                            FOREIGN KEY(ref_id) REFERENCES reference (ref_id) ON DELETE cascade
			   );


CREATE TABLE reference_system ( ref_system_id   SERIAL,
                                ref_system_name TEXT,
                                PRIMARY KEY (ref_system_id)
                               );



-- store info about the sensor supplying data
CREATE TABLE sensor ( sid	  SERIAL,
                      hostname    TEXT,
                      interface   TEXT,
                      filter	  TEXT,
                      detail	  INT2,
                      encoding	  INT2,
                      PRIMARY KEY (sid)
		     );



CREATE TABLE event  ( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      signature   INT4 NOT NULL, 
                      timestamp   DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid),
		      FOREIGN KEY (signature) REFERENCES signature (sig_id)  ON DELETE cascade,
                      FOREIGN KEY (sid) REFERENCES sensor (sid)  ON DELETE cascade
                    );
CREATE INDEX signature_idx ON event (signature);
CREATE INDEX timestamp_idx ON event (timestamp);


-- All of the fields of an ip header
CREATE TABLE iphdr  ( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      ip_src      INT8 NOT NULL,
                      ip_dst      INT8 NOT NULL,
                      ip_ver      INT2,
                      ip_hlen     INT2,
                      ip_tos  	  INT2,
                      ip_len 	  INT4,
                      ip_id    	  INT4,
                      ip_flags    INT2,
                      ip_off      INT4,
                      ip_ttl   	  INT2,
                      ip_proto 	  INT2 NOT NULL,
                      ip_csum 	  INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                     );
CREATE INDEX ip_src_idx ON iphdr (ip_src);
CREATE INDEX ip_dst_idx ON iphdr (ip_dst);

-- All of the fields of a tcp header
CREATE TABLE tcphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      tcp_sport   INT4 NOT NULL,
                      tcp_dport   INT4 NOT NULL,
                      tcp_seq     INT8,
                      tcp_ack     INT8,
                      tcp_off     INT2,
                      tcp_res     INT2,
                      tcp_flags   INT2 NOT NULL,
                      tcp_win     INT4,
                      tcp_csum    INT4,
                      tcp_urp     INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                     );
CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);

-- All of the fields of a udp header
CREATE TABLE udphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      udp_sport   INT4 NOT NULL,
                      udp_dport   INT4 NOT NULL,
                      udp_len     INT4,
                      udp_csum    INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade );
CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
CREATE INDEX udp_dport_idx ON udphdr (udp_dport);

-- All of the fields of an icmp header
CREATE TABLE icmphdr( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      icmp_type   INT2 NOT NULL,
                      icmp_code   INT2 NOT NULL,
                      icmp_csum   INT4, 
                      icmp_id     INT4,
                      icmp_seq    INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade);
CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);

-- Protocol options
CREATE TABLE opt    ( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      optid       INT2 NOT NULL,
                      opt_proto   INT2 NOT NULL,
                      opt_code    INT2 NOT NULL,
                      opt_len     INT4,
                      opt_data    TEXT,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade);

-- Packet payload
CREATE TABLE data   ( sid          INT4 NOT NULL,
                      cid          INT8 NOT NULL,
                      data_payload TEXT,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade);

-- encoding is a lookup table for storing encoding types
CREATE TABLE encoding(encoding_type INT2 NOT NULL,
                      encoding_text TEXT NOT NULL,
                      PRIMARY KEY (encoding_type));
INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');

-- detail is a lookup table for storing different detail levels
CREATE TABLE detail  (detail_type INT2 NOT NULL,
                      detail_text TEXT NOT NULL,
                      PRIMARY KEY (detail_type));
INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');

-- be sure to also use the snortdb-extra tables if you want
-- mappings for tcp flags, protocols, and ports
-------------- next part --------------
-- Copyright (C) 2000 Carnegie Mellon University
--
-- Author(s): Jed Pickel <jed at pickel.net>
--            Roman Danyliw <rdd at cert.org>, <roman at danyliw.com>
--            Todd Schrubb <tls at cert.org>
--
-- Modified by: ebalas at indiana.edu 03/08/02
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
-- This a version of the snort schema that includes revised indicies 
--  and referential integrity with cascading deletes


CREATE TABLE schema ( vseq        INT4     NOT NULL,
                      ctime       DATETIME NOT NULL,
                      PRIMARY KEY (vseq));

INSERT INTO schema  (vseq, ctime) VALUES ('666', now());





CREATE TABLE sig_class ( sig_class_id        SERIAL,
                         sig_class_name      TEXT NOT NULL,
                         PRIMARY KEY (sig_class_id) 
                       );


CREATE TABLE signature ( sig_id       SERIAL NOT NULL,
                         sig_name     TEXT NOT NULL,
                         sig_class_id INT8 ,
                         sig_priority INT8,
                         sig_rev      INT8,
                         sig_sid      INT8,
                         PRIMARY KEY (sig_id),
			 FOREIGN KEY (sig_class_id) REFERENCES sig_class (sig_class_id) ON DELETE cascade
			);


CREATE TABLE reference_system ( ref_system_id   SERIAL,
                                ref_system_name TEXT,
                                PRIMARY KEY (ref_system_id)
                              );
CREATE INDEX ref_sys_id_idx ON reference_system (ref_system_id);


CREATE TABLE reference (  ref_id        SERIAL,
                          ref_system_id INT4 NOT NULL,
                          ref_tag       TEXT NOT NULL,
                          PRIMARY KEY (ref_id),
	                  FOREIGN KEY (ref_system_id) REFERENCES reference_system (ref_system_id) ON DELETE cascade
                       );

CREATE INDEX ref_id_idx ON reference (ref_id);


CREATE TABLE sig_reference (sig_id  INT4  NOT NULL,
                            ref_seq INT4  NOT NULL,
                            ref_id  INT4  NOT NULL,
                            PRIMARY KEY(sig_id, ref_seq),
	                    FOREIGN KEY(sig_id) REFERENCES signature (sig_id) ON DELETE cascade,
			    FOREIGN KEY(ref_id) REFERENCES reference (ref_id) ON DELETE cascade		    
                           );

CREATE INDEX sig_ref_seq_idx ON sig_reference (ref_seq);


-- store info about the sensor supplying data
CREATE TABLE sensor ( sid         SERIAL,
                      hostname    TEXT,
                      interface   TEXT,
                      filter      TEXT,
                      detail      INT2,
                      encoding    INT2,
                      PRIMARY KEY (sid)
                    );


CREATE TABLE event  ( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      signature   INT4 NOT NULL, 
                      timestamp   DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid),
		      FOREIGN KEY (signature) REFERENCES signature (sig_id)  ON DELETE cascade,
		      FOREIGN KEY (sid) REFERENCES sensor (sid)  ON DELETE cascade
                    );

CREATE INDEX event_stamp_idx ON event (timestamp);
CREATE INDEX event_sig_idx ON event (signature);
CREATE INDEX event_sid_idx ON event (sid);
CREATE INDEX event_cid_idx ON event (cid);


-- All of the fields of an ip header
CREATE TABLE iphdr  ( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      ip_src      INT8 NOT NULL,
                      ip_dst      INT8 NOT NULL,
                      ip_ver      INT2,
                      ip_hlen     INT2,
                      ip_tos  	  INT2,
                      ip_len 	  INT4,
                      ip_id    	  INT4,
                      ip_flags    INT2,
                      ip_off      INT4,
                      ip_ttl   	  INT2,
                      ip_proto 	  INT2 NOT NULL,
                      ip_csum 	  INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );

CREATE INDEX ip_cid_idx ON iphdr (cid);
CREATE INDEX ip_sid_idx ON iphdr (sid);
CREATE INDEX ip_src_idx ON iphdr (ip_src);
CREATE INDEX ip_dst_idx ON iphdr (ip_dst);




-- All of the fields of a tcp header
CREATE TABLE tcphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      tcp_sport   INT4 NOT NULL,
                      tcp_dport   INT4 NOT NULL,
                      tcp_seq     INT8,
                      tcp_ack     INT8,
                      tcp_off     INT2,
                      tcp_res     INT2,
                      tcp_flags   INT2 NOT NULL,
                      tcp_win     INT4,
                      tcp_csum    INT4,
                      tcp_urp     INT4,
		      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );

CREATE INDEX tcp_cid_idx ON tcphdr (cid);
CREATE INDEX tcp_sid_idx ON tcphdr (sid);

CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);





-- All of the fields of a udp header
CREATE TABLE udphdr(  sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      udp_sport   INT4 NOT NULL,
                      udp_dport   INT4 NOT NULL,
                      udp_len     INT4,
                      udp_csum    INT4,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );

CREATE INDEX udp_cid_idx ON udphdr (cid);
CREATE INDEX udp_sid_idx ON udphdr (sid);

CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
CREATE INDEX udp_dport_idx ON udphdr (udp_dport);




-- All of the fields of an icmp header
CREATE TABLE icmphdr( sid 	  INT4 NOT NULL,
                      cid 	  INT8 NOT NULL,
                      icmp_type   INT2 NOT NULL,
                      icmp_code   INT2 NOT NULL,
                      icmp_csum   INT4, 
                      icmp_id     INT4,
                      icmp_seq    INT4,
	              FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );

CREATE INDEX icmp_cid_idx ON icmphdr (cid);
CREATE INDEX icmp_sid_idx ON icmphdr (sid);

CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);




-- Protocol options
CREATE TABLE opt    ( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      optid       INT2 NOT NULL,
                      opt_proto   INT2 NOT NULL,
                      opt_code    INT2 NOT NULL,
                      opt_len     INT4,
                      opt_data    TEXT,
		      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );
CREATE INDEX opt_cid_idx ON opt (cid);
CREATE INDEX opt_sid_idx ON opt (sid);




-- Packet payload
CREATE TABLE data   ( sid          INT4 NOT NULL,
                      cid          INT8 NOT NULL,
                      data_payload TEXT,
                      FOREIGN KEY (sid,cid) REFERENCES event (sid,cid)  ON DELETE cascade
                    );

CREATE INDEX data_cid_idx ON data (cid);
CREATE INDEX data_sid_idx ON data (sid);




-- encoding is a lookup table for storing encoding types
CREATE TABLE encoding(encoding_type INT2 NOT NULL,
                      encoding_text TEXT NOT NULL,
                      PRIMARY KEY (encoding_type));
INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');

-- detail is a lookup table for storing different detail levels
CREATE TABLE detail  (detail_type INT2 NOT NULL,
                      detail_text TEXT NOT NULL,
                      PRIMARY KEY (detail_type));
INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');

-- be sure to also use the snortdb-extra tables if you want
-- mappings for tcp flags, protocols, and ports


More information about the Snort-devel mailing list