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 MSSQL, 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
// CREATE TABLE EVENT (
// ID INT PRIMARY KEY NOT NULL,
// IPSOURCE INT NOT NULL,
// IPDEST INT NOT NULL,
// UNIXTIME BIGINT NOT NULL,
// TYPE TINYINT NOT NULL,
// DEVICEID SMALLINT NOT NULL,
// PROTOCOL TINYINT NOT NULL
// )
//
// 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 < ?"; 2 4239
Hi,
Refer www.SQL-Server-Performance.com web site for SQL Server Performance issues.
Almost all yours queries id answered in perfect articles and example wise.
Thanks, Amit pw**********@nitrodata.com (rocky) wrote in message news:<39**************************@posting.google. com>... 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 MSSQL, 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
// CREATE TABLE EVENT ( // ID INT PRIMARY KEY NOT NULL, // IPSOURCE INT NOT NULL, // IPDEST INT NOT NULL, // UNIXTIME BIGINT NOT NULL, // TYPE TINYINT NOT NULL, // DEVICEID SMALLINT NOT NULL, // PROTOCOL TINYINT NOT NULL // ) // // 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 < ?";
Some mixed advice:
1) Make sure that you have plenty of memory in the server. Query2 will
always generate a full index scan, and you need to be able to fit all of the
data for that index in memory to avoid excessive disk I/O. 100 million rows
* 13 bytes = 1.3 GB of memory. I would suggest having at least 2GB memory in
the server.
2) Make sure you have a disk subsystem with good performance for both
reading and writing. Use several striped disks for the data (not RAID5), and
a separate disk for log. Use a disk controller with a battery-backed write
cache. The disk performance is important even if you have plenty of memory,
but it is absolutely vital if you can not fit all the data in memory.
3) Add a NOLOCK hint to all SELECT queries. Otherwise, all inserts will be
blocked while executing the SELECTs.
4) Change the index to IPSOURCE,TYPE,IPDEST to optimize Query3
5) Make sure that you batch inserts. Have the INSERT thread generate batches
of inserts and send them all to the server in a single batch as a single
transaction. Having a transaction for each inserted row will kill
performance unless you have a really good disk controller.
/SG
"rocky" <pw**********@nitrodata.com> wrote in message
news:39**************************@posting.google.c om... 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 MSSQL, 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
// CREATE TABLE EVENT ( // ID INT PRIMARY KEY NOT NULL, // IPSOURCE INT NOT NULL, // IPDEST INT NOT NULL, // UNIXTIME BIGINT NOT NULL, // TYPE TINYINT NOT NULL, // DEVICEID SMALLINT NOT NULL, // PROTOCOL TINYINT NOT NULL // ) // // 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 < ?"; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alex Vinokur |
last post by:
For instance, we need to measure performance
of assignment 'ch1 = ch2' where ch1 and ch2 are of char type.
We need to do that for different optimization levels of the same compiler.
Here is...
|
by: Rune |
last post by:
Is it best to use double quotes and let PHP expand variables inside strings,
or is it faster to do the string manipulation yourself manually?
Which is quicker?
1)
$insert = 'To Be';...
|
by: AC Slater |
last post by:
Whats the simplest way to change a single stored procedures query
optimization level?
In UDB8 that is.
/F
|
by: Eugene |
last post by:
I am trying to set query optimization class in a simple SQL UDF like
this:
CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT,...
|
by: WantedToBeDBA |
last post by:
Hi all,
db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced...
|
by: Kunal |
last post by:
Hello,
I need help in removing if ..else conditions inside for loops. I have
used the following method but I am not sure whether it has actually
helped.
Below is an example to illustrate what I...
|
by: mjbackues at yahoo |
last post by:
Hello.
I'm having a problem with the Visual Studio .net (2003) C++ speed
optimization, and hope someone can suggest a workaround.
My project includes many C++ files, most of which work fine...
|
by: wkaras |
last post by:
I've compiled this code:
const int x0 = 10;
const int x1 = 20;
const int x2 = 30;
int x = { x2, x0, x1 };
struct Y
{
|
by: db2admin |
last post by:
hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i...
|
by: Ravikiran |
last post by:
Hi Friends,
I wanted know about whatt is ment by zero optimization and
sign optimization and its differences....
Thank you...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |