Re: Server/Database/Table Configuration

#1

pwhittington (AT) nitrodata (DOT) com (rocky) wrote in message news:<3961374c.0307041001.4f5e4708 (AT) posting (DOT) google.com>...

Quote:

We're building a company wide network monitoring system
in Java, and need some advice on the database design and
tuning.

The application will need to concurrently INSERT,
DELETE, and SELECT from our EVENT table as efficiently as
possible. We plan to implement an INSERT thread, a DELETE
thread, and a SELECT thread within our Java program.

The EVENT table will have several hundred million records
in it at any given time. We will prune, using DELETE, about
every five seconds to keep the active record set down to
a user controlled size. And one of the three queries will
be executed about every twenty seconds. Finally, we'll
INSERT as fast as we can in the INSERT thread.

Being new to PostgreSQL, we need advice on

1) Server Tuning - Memory allocations, etc.
2) Table Tuning - Field types
3) Index Tuning - Are the indexes right
4) Query Tuning - Hints, etc.
5) Process Tuning - Better ways to INSERT and DELETE, etc.

Thanks, in advance, for any suggestions you can make :-)

The table is

//================================================== ===================
// Database Creation Instructions
//================================================== ===================
// CREATE TABLE EVENT (
// ID INT4 PRIMARY KEY NOT NULL, -- 32-bit unsigned int
// IPSOURCE INT4 NOT NULL, -- 32-bit unsigned int
// IPDEST INT4 NOT NULL, -- 32-bit unsigned int
// UNIXTIME INT8 NOT NULL, -- 64-bit unsigned int
// TYPE INT2 NOT NULL, -- 8-bit unsigned int
// DEVICEID INT2 NOT NULL, -- 16-bit unsigned int
// PROTOCOL INT2 NOT NULL -- 8-bit unsigned int
// );
//
// CREATE INDEX INDEX_SRC_DEST_TYPE
// ON EVENT (
// IPSOURCE,IPDEST,TYPE
// );

The SELECTS are

private static String QueryString1 =
"SELECT ID,IPSOURCE,IPDEST,TYPE "+
"FROM EVENT "+
"WHERE ID >= ? "+
" AND ID <= ?";

private static String QueryString2 =
"SELECT COUNT(*),IPSOURCE "+
"FROM EVENT "+
"GROUP BY IPSOURCE "+
"ORDER BY 1 DESC";

private static String QueryString3 =
"SELECT COUNT(*),IPDEST "+
"FROM EVENT "+
"WHERE IPSOURCE = ? "+
" AND TYPE = ? "+
"GROUP BY IPDEST "+
"ORDER BY 1 DESC";

The DELETE is

private static String DeleteIDString =
"DELETE FROM EVENT "+
"WHERE ID < ?";

As one postgres newbie to another, this is what I can tell based on
experience with other database systems (oracle, sybase, db2,
informix).

Your approach seems somewhat unworkable. For instance running
simultaneous transactions (insert, update, delete) on the same table
(also note that Postgres is single-threaded at the query level) while
simultaneously expecting great performance is probably not going to
happen. My suspicion is this baby will hang like a cattle rustler!!!
Think instead of inserting all events into daily or hourly tables (new
table per time period) and calculating summaries once only. This
solution is time-worn and most seasoned DB pros will recommend it. I
suggest not deleting events, just roll them offline when you're no
longer interested.

Feel free to send me questions ... but as far as Postgres specific
stuff give me a while, I'm still trying to create a freaking trigger

-bret

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Server/Database/Table Configuration

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Server/Database/Table Configuration - 07-10-2003 , 05:17 PM