AppletTalk.com

rocky · Guest

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 PointBase, 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 "PBPUBLIC"."EVENT" (
// "ID" DECIMAL(10,0) PRIMARY KEY NOT NULL, -- 32-bit uint
// "IPSOURCE" DECIMAL(10,0) NOT NULL, -- 32-bit uint
// "IPDEST" DECIMAL(10,0) NOT NULL, -- 32-bit uint
// "UNIXTIME" DECIMAL(20,0) NOT NULL, -- 64-bit uint
// "TYPE" SMALLINT NOT NULL, -- 8-bit uint
// "DEVICEID" SMALLINT NOT NULL, -- 16-bit uint
// "PROTOCOL" SMALLINT NOT NULL -- 8-bit uint
// );
//
// CREATE INDEX "PBPUBLIC"."INDEX_SRC_DEST_TYPE"
// ON "PBPUBLIC"."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 < ?";