SEO

Hello,

I have a Linux box (RH 4 Update 2 / kernel 2.6) running DB2 V8 FP 10.

My application is continuosly inserting data to a table. The statement
is a simple "insert into table values (a,b), ... (c,d)". 50 rows are
inserted per transaction, and a Commit is issued after the insert.

I created event monitor and got odd insert times. They vary from 0.5s
to 40+s !

I have taken snapshots and there does not seems to exist any lock-wait
that could explain this delay. Also, examining the operating system
performance counters, I can see that my CPU spends about 50% on iowait
operations. Memory is fairly constrained, but eventually there is about
1GB of free RAM available. (out of 6Gb + 2Gb/swap)

Can anyone suggest something that may be looked within DB2 to
understand why my system is spending too much time doing IO operations?
I have taken explain snapshots on the INSERT statement and all costs
seems very low.

I just need to make sure this is not a DB2 problem, before contacting
our system admins.

db2diag.log does not have a single error message.

Thanks in advance,

Not a db2 expert (yet) but a few things come to mind:

is there aggresive indexing on the table?
(Possibly re-org the indices... etc.)

runstats? would that help?

Possibly some serious table constraints, doing lookups for
foreign key constraints, etc.

Also, what type of hard disk are you utilizing? If i understand the
situation,
multiple smaller hard disks would perform better. (Splice the disk
I/O, etc.)
(How much cache on the disk controller? )


Also, what type of application is providing these inserts...
Is it ODBC? could the latency be on the client app?

__________________________________________________ __________

Why it is not consistently slow on the other hand, i'm not sure. We
have a DB2 UDB 8.2 box receiving up to about 120 inserts per second (on
busy days) and have to purge records with a timestamp over a few
days.... so i'm very interested in the eventual solution to this.
(Data warehousing project... etc.)


Michel Esber wrote:
> Hello,
>
> I have a Linux box (RH 4 Update 2 / kernel 2.6) running DB2 V8 FP 10.
>
> My application is continuosly inserting data to a table. The statement
> is a simple "insert into table values (a,b), ... (c,d)". 50 rows are
> inserted per transaction, and a Commit is issued after the insert.
>
> I created event monitor and got odd insert times. They vary from 0.5s
> to 40+s !
>
> I have taken snapshots and there does not seems to exist any lock-wait
> that could explain this delay. Also, examining the operating system
> performance counters, I can see that my CPU spends about 50% on iowait
> operations. Memory is fairly constrained, but eventually there is about
> 1GB of free RAM available. (out of 6Gb + 2Gb/swap)
>
> Can anyone suggest something that may be looked within DB2 to
> understand why my system is spending too much time doing IO operations?
> I have taken explain snapshots on the INSERT statement and all costs
> seems very low.
>
> I just need to make sure this is not a DB2 problem, before contacting
> our system admins.
>
> db2diag.log does not have a single error message.
>
> Thanks in advance,

This may help:

http://www-128.ibm.com/developerwork...m-0403wilkins/

Larry Edelstein

Michel Esber wrote:
> Hello,
>
> I have a Linux box (RH 4 Update 2 / kernel 2.6) running DB2 V8 FP 10.
>
> My application is continuosly inserting data to a table. The statement
> is a simple "insert into table values (a,b), ... (c,d)". 50 rows are
> inserted per transaction, and a Commit is issued after the insert.
>
> I created event monitor and got odd insert times. They vary from 0.5s
> to 40+s !
>
> I have taken snapshots and there does not seems to exist any lock-wait
> that could explain this delay. Also, examining the operating system
> performance counters, I can see that my CPU spends about 50% on iowait
> operations. Memory is fairly constrained, but eventually there is about
> 1GB of free RAM available. (out of 6Gb + 2Gb/swap)
>
> Can anyone suggest something that may be looked within DB2 to
> understand why my system is spending too much time doing IO operations?
> I have taken explain snapshots on the INSERT statement and all costs
> seems very low.
>
> I just need to make sure this is not a DB2 problem, before contacting
> our system admins.
>
> db2diag.log does not have a single error message.
>
> Thanks in advance,
>

Thanks for the input guys.

Working with the system admin, we found out that after Operating System
migration (RH 2.1 to RH 4.0) the daemon bdflush (deprecated on 4.0) was
still running and was the main responsible for IO usage. Fortunately,
it was not a DB2 problem.

Thanks again.


Larry escreveu:

> This may help:
>
> http://www-128.ibm.com/developerwork...m-0403wilkins/
>
> Larry Edelstein
>
> Michel Esber wrote:
> > Hello,
> >
> > I have a Linux box (RH 4 Update 2 / kernel 2.6) running DB2 V8 FP 10.
> >
> > My application is continuosly inserting data to a table. The statement
> > is a simple "insert into table values (a,b), ... (c,d)". 50 rows are
> > inserted per transaction, and a Commit is issued after the insert.
> >
> > I created event monitor and got odd insert times. They vary from 0.5s
> > to 40+s !
> >
> > I have taken snapshots and there does not seems to exist any lock-wait
> > that could explain this delay. Also, examining the operating system
> > performance counters, I can see that my CPU spends about 50% on iowait
> > operations. Memory is fairly constrained, but eventually there is about
> > 1GB of free RAM available. (out of 6Gb + 2Gb/swap)
> >
> > Can anyone suggest something that may be looked within DB2 to
> > understand why my system is spending too much time doing IO operations?
> > I have taken explain snapshots on the INSERT statement and all costs
> > seems very low.
> >
> > I just need to make sure this is not a DB2 problem, before contacting
> > our system admins.
> >
> > db2diag.log does not have a single error message.
> >
> > Thanks in advance,
> >