query response time and cpu usage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shawno - 30 Nov 2006 16:17 GMT

Hello,

We are currently in the process of migrating our databases from a relic
of a server to a new 4 processor dual-core box with 4 gigs of RAM.
Overall, database performance is obviously dramatically improved.
However, I've noticed something odd with some of our larger tables
(which have between 1 and 1.5 million records). Performance can differ
greatly when performing simple LIKE queries such as the following:

select record_id from <tablename> where <fieldname> like '%blah%'

Sometimes a result set will be returned very quickly (2 seconds or
almost immediately) while other times the exact same query will take 20
seconds to a minute. There does not appear to be any pattern to when
it is fast and when it is not. (I've done a re-org and runstats)

Now here's where it gets interesting. If I bring up task manager on
the server and monitor the cpu usage when doing such queries, on the
queries that return fast there is an expected large spike in the graph
across all the cpus. On the queries that take a long time, the cpu
usage appears to be very minimal (just little bumps).

Keep in mind that I am no DBA, just a java developer so maybe I'm
missing something really simple here or totally misinterpreting what I
see. Any suggestions on what is going on here would be greatly
appreciated, thanks!

Reply to this Message

Art S. Kagel - 30 Nov 2006 16:26 GMT

> Hello,
>
[quoted text clipped - 17 lines]
> across all the cpus. On the queries that take a long time, the cpu
> usage appears to be very minimal (just little bumps).

Sounds to me like the slower queries are IO bound ie are not finding the
data and/or index pages they need in cache and so are going to disk. The
faster ones are simply finding the data already in the cache so CPU time is
burning filtering rows for the LIKE clause.

> Keep in mind that I am no DBA, just a java developer so maybe I'm
> missing something really simple here or totally misinterpreting what I
> see. Any suggestions on what is going on here would be greatly
> appreciated, thanks!

Art S. Kagel

Reply to this Message

shawno - 30 Nov 2006 16:52 GMT

Hi,
That was my initial thought, but sometimes doing the exact same query
takes just a long as the first time. Other times if I try querying on
something I have not tried before, its blazing fast, and than the next
time (same query) its slow which is just the opposite of what you would
expect.
The columns I am querying are not indexed since I am performing a full
like ('%whatever%') and it is my understanding that DB2 will not use an
index for such a query since it would not help.

> Sounds to me like the slower queries are IO bound ie are not finding the
> data and/or index pages they need in cache and so are going to disk. The
> faster ones are simply finding the data already in the cache so CPU time is
> burning filtering rows for the LIKE clause.

Reply to this Message

ChrisC - 30 Nov 2006 17:09 GMT

> Hi,
> That was my initial thought, but sometimes doing the exact same query
> takes just a long as the first time. Other times if I try querying on
> something I have not tried before, its blazing fast, and than the next
> time (same query) its slow which is just the opposite of what you would
> expect.

That can still happen, IF you are not the sole user of the system. So,
if another query from somewhere else does a full scan of the table, it
will put those rows into the db buffers, and when you run your first
new query, it will find all of the needed data in memory and run fast
(using lots of CPU). Then, some other query runs against other tables,
needs pages in the buffer, and so DB2 starts tossing data from you
table out of memory, and when you run the exact same query later, DB2
needs to pull that data back in off of disc, which is slow and doesn't
use as much CPU.

-Chris

Reply to this Message

shawno - 30 Nov 2006 17:30 GMT

Hi,
Actually, at this point I am the only user accessing the database as it
still in the development phase.
I did notice something else that maybe somebody can shed some light on
(maybe I'll start a new thread as well). In command editor, I tried
doing an 'execute and access plan' so that it created the little
flowchart diagram and there was a difference.
For the fast queries, the access plan looked like:

[database name] ---> [tbscan(3) 531,908] ---> [return(1) 531,908]

The slow queries were as follows:

[database name] ---> [tbscan(5) 1,013,551] ---> [tqueue(3)] --->
[return(1) 1,013,551]

The slower queries had a tqueue entry, whatever that is.

> That can still happen, IF you are not the sole user of the system. So,
> if another query from somewhere else does a full scan of the table, it
[quoted text clipped - 7 lines]
>
> -Chris

Reply to this Message