473,594 Members | 2,996 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

low overhead select to determine current server load

On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows per
hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.
System: db2 v 9.1 64bit on Centos 4.3 64 bit.

Thank you.
nat
Aug 22 '06 #1
19 2276
natG wrote:
On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows per
hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.
I don't think that DB2 provides an interface to retrieve that kind of
information. But you could resort to a user-defined function (UDF). The
function can be called using "VALUES <fct>()", which is as light-weight as
it can possible get.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 28 '06 #2
On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:
natG wrote:
>On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows per
hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.

I don't think that DB2 provides an interface to retrieve that kind of
information. But you could resort to a user-defined function (UDF). The
function can be called using "VALUES <fct>()", which is as light-weight as
it can possible get.
hmmm... But I have no idea what the function needs to do. Is there a db2
function that reports server load? Or how many inserts per second its
doing *now*, or how many tps its doing.

Thanks
nat
Aug 28 '06 #3
natG wrote:
On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:
>natG wrote:
>>On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows
per hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.

I don't think that DB2 provides an interface to retrieve that kind of
information. But you could resort to a user-defined function (UDF). The
function can be called using "VALUES <fct>()", which is as light-weight
as it can possible get.

hmmm... But I have no idea what the function needs to do. Is there a db2
function that reports server load? Or how many inserts per second its
doing *now*, or how many tps its doing.
I thought you were interested in the CPU utilization. For the
inserts/transactions per second thing, you could employ the DB2 snapshot
and event monitor facilities.

If it is the CPU load you're after, we are really talking about a system
call. The question is then which OS platform your DB2 server is running
on.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 28 '06 #4
On Tue, 29 Aug 2006 00:34:48 +0200, Knut Stolze wrote:
natG wrote:
>On Mon, 28 Aug 2006 12:19:27 +0200, Knut Stolze wrote:
>>natG wrote:

On a warehouse app, our Java clients [can] constantly load/insert rows
into the db. I would like to throttle these inserts (1.5 million rows
per hr) from the Java app, based on current 'busy state' of the server.
It would be nice if I can get the server CPU utilization (and other key
factors) from db2 via jdbc. However, it needs to be low cost, since it
would likely run every x seconds.

I don't think that DB2 provides an interface to retrieve that kind of
information . But you could resort to a user-defined function (UDF). The
function can be called using "VALUES <fct>()", which is as light-weight
as it can possible get.

hmmm... But I have no idea what the function needs to do. Is there a db2
function that reports server load? Or how many inserts per second its
doing *now*, or how many tps its doing.

I thought you were interested in the CPU utilization. For the
inserts/transactions per second thing, you could employ the DB2 snapshot
and event monitor facilities.

If it is the CPU load you're after, we are really talking about a system
call. The question is then which OS platform your DB2 server is running
on.
Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)
The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
Thank you much.
nat

Aug 29 '06 #5
natG wrote:
Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)
Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.
The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.
So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 29 '06 #6
On Wed, 30 Aug 2006 00:51:09 +0200, Knut Stolze wrote:
natG wrote:
>Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)

Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.
>The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.

So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...
Thats very nice of you. Thank you much.
nat

Aug 29 '06 #7
Ian
Knut Stolze wrote:
natG wrote:
>Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)

Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.
>The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.

So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...
You can just read /proc/loadavg.

[ianbjor@kwaiken ~]$ cat /proc/loadavg
0.18 0.07 0.02 1/133 20942

The first 3 columns are the 1, 5 and 15 minute load averages.

Aug 30 '06 #8
On Tue, 29 Aug 2006 20:00:43 -0700, Ian wrote:
Knut Stolze wrote:
>natG wrote:
>>Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)

Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.
>>The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.

So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...

You can just read /proc/loadavg.

[ianbjor@kwaiken ~]$ cat /proc/loadavg
0.18 0.07 0.02 1/133 20942

The first 3 columns are the 1, 5 and 15 minute load averages.
Aye. Thanks for the tip. Three questions.

1. Is there something more fine-grained, closer to the last few seconds or
preferably at the exact moment it was called.
2. How do I wrap it in a UDF in a way that I don't need to echo the cat to
a temp file? Writing to a temp file every few seconds will in itself cause
overhead.
3. Would you know, if in a dual-processor system, the loadavg is a
combination of both processors?

Thank you much.
-nat
Aug 30 '06 #9
Ian wrote:
Knut Stolze wrote:
>natG wrote:
>>Indeed, it's the cpu utilization I'm after. (But if that's too tough or
costly to get, inserts /ps or tps will do.)

Getting the CPU load would be _much_ easier than the other metrics you
mentioned. The CPU load can be retrieved with a system call; the others
require some accounting work to be done by DB2.
>>The server is a 64bit dual-core Linux-Centos 4.3 running db2 9.1-64bit.

So you're on Linux. I'll see if I can figure out how the CPU load can be
collected on that platform and get back to you in a couple days...

You can just read /proc/loadavg.
Exactly...

So here is the code to do that:
--------------------------------------------------------------------
#include <sqludf.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <errno.h>

/*
* Function to extract the CPU load for the past 1 minute (Linux-specific).

CREATE FUNCTION getCPULoad()
RETURNS DOUBLE
EXTERNAL NAME 'load_udf!getCP ULoad'
LANGUAGE C
PARAMETER STYLE SQL
SPECIFIC getCPULoad
DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED
NO FINAL CALL
RETURNS NULL ON NULL INPUT
ALLOW PARALLEL@
*/
void SQL_API_FN getCPULoad(
SQLUDF_DOUBLE *load,
SQLUDF_NULLIND *load_ind,
SQLUDF_TRAIL_AR GS)
{
char str[100] = { '\0' };
char *endPtr = NULL;
FILE *f = NULL;

/* initialize output parameters */
*load = 0.0;
*load_ind = -1;

/* open file and read its contents */
f = fopen("/proc/loadavg", "r");
if (!f) {
memcpy(SQLUDF_S TATE, "38L01", SQLUDF_SQLSTATE _LEN);
snprintf(SQLUDF _MSGTX, SQLUDF_MSGTEXT_ LEN,
"Could not open file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_ LEN-1] = '\0';
goto exit;
}
fread(str, 1, sizeof str, f);
if (ferror(f)) {
memcpy(SQLUDF_S TATE, "38L02", SQLUDF_SQLSTATE _LEN);
snprintf(SQLUDF _MSGTX, SQLUDF_MSGTEXT_ LEN,
"Failure reading file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_ LEN-1] = '\0';
goto exit;
}

/* extract the CPU load information */
*load = strtod(str, &endPtr);
if (endPtr == str || errno == ERANGE) {
memcpy(SQLUDF_S TATE, "38L03", SQLUDF_SQLSTATE _LEN);
snprintf(SQLUDF _MSGTX, SQLUDF_MSGTEXT_ LEN,
"Failure extracting CPU load from file '/proc/loadavg");
SQLUDF_MSGTX[SQLUDF_MSGTEXT_ LEN-1] = '\0';
goto exit;
}
*load_ind = 0;

exit:
if (f != NULL) {
fclose(f);
}
}
--------------------------------------------------------------------

This can be compiled with the "bldrtn" script shipped with the DB2 samples
and then the above CREATE FUNCTION statement registers the UDF in the
database. Then you just have to call:

VALUES getCPULoad()

and process the output like any other result set.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 31 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5646
by: Richard | last post by:
Hi all, I am looking for some help on understanding the overhead associated with object creation in Java. I am writing an application where I have written a class to encapsulate some text data. The class is contains these private variables:
2
2546
by: Dave Williamson | last post by:
When a ASPX page is created with dynamic controls based on what the user is doing the programmer must recreate the dynamic controls again on PostBack in the Page_Load so that it's events are wired and are called like a static control. Here is the problem that I need to solve. The processing overhead that occurs to determine what dynamic controls need to be added involves business logic and a query or queries of data in a sql server...
4
8360
by: Jason Kumpf | last post by:
OK I've been staring at this code all day and still with everything I have tried I cannot figure out two problems I am having. Once is why the space limit for the directory I create in the code fails. Second, why the data reader is reading every other record. Here is all of the source code for my little application followed by the contents of the log file that it dumps out:...
7
14141
by: Doru Roman | last post by:
Hi, What is the fastest way to evaluate manually the result in this case: int a, b, c; a = 255; b = 122; c = a & b; The only way I know is transforming each number into the binary value and
3
11570
by: Developer in California | last post by:
I am working on developing a generic Web framework using Master Pages in ASP.NET 2.0. What I have done is created a PageRenderer class which has a public method which will retrieve the path of the content I want to execute based on the name of the asp:Content control. As shown in the code snippet below, to get the content I want to display, I call the GetContentPagePath public method in PageRenderer passing a string duplicating the value...
3
1431
by: mwhite | last post by:
Hi, I have an old .htm site that I need to protect with Forms Authentication. I know that I can change the filetype of all these files to .aspx, and this lets the Forms Authentication protect them, however I saw on msdn.microsoft.com that this incurs some additional overhead. Does anyone know how severe the overhead will be? I cannot find any helpful info, and the client does not wish to have the entire site redeveloped (the alternative)....
2
1523
by: mark | last post by:
Hi, I am in the process of developing a small application that sits in the task bar and at a set interval polls an IIS web server for information. The process involves the client sending anything between 3 and 30 characters of data and the server returning anything up to 50 characters of data. I need to identify the overhead on the network so I can determine the network utilisation of the application. The sort of questions I need to be...
2
3099
by: =?Utf-8?B?YWxiZXJ0b3Nvcmlh?= | last post by:
Hi, I'm using Threads, and when I try to do Server.Transfer, I recieved an error. (child object does not exist...) My Code: Dim t As New Thread(AddressOf Hilo) Private Sub Hilo() Thread.Sleep(1000)
6
3401
by: BA | last post by:
Hi Everyone, I have an application that sits behind a server farm, the application needs to pass its NLB IP address in the message that it sends to another service. From C# code, how can I determine the IP address of the network load balanced machine that the message is generated from? So, in essence, I have server1, server2 and server3 sitting behind the NLB IP address 100.1.2.100, then I have server4 and server5 sitting behind NLB...
0
8255
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8374
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8242
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6665
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5739
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5413
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3868
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
1486
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1217
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.