Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
Hardware: Intel x86_64 with 192GB of RAM.
OS: CentOS 5.4 x86_64.
DBMS: DB2 v. 9.7.1 64 bit.

During certain special workloads (e.g. parallel REORGs/RUNSTATs), I've seen the server transporting 450MB/s with 25000IO/s (yes, there is probably some storage system caching happening here) while all CPU cores were happily working in an even mix of usermode/wait. And disk benchmark tools can also bring some very satisfying bandwith and IO/s numbers to the table.

On the other hand, we also have another scenario: A single rather complex query with at least one large table scan. db2's "list applications" reports that the query is Executing (not locked). IO: At most 10MB/s, 500 IO/s; CPU: two cores in 99.9% wait state, all other cores 100% idle. The tables which the query reads from have been altered to have LOCKSIZE=TABLE, so I would think that lock list work is zero.

What's going on in such a situation? What tools/snapshots/... can I use to gain better insight in such a case?

share|improve this question
    
After a while of analysis, we have concluded that the system wasn't disk bound, but probably some of the queries were creating some large temporary structures in memory which were then used inefficiently. - Meaning that RAM accesses were probably throttling the database. –  Troels Arvin Mar 17 '10 at 10:58

1 Answer 1

Take a look at the detailed process listing for the query. Use ps -lp <pid> A few things to note: A) the status. R means it's running, D means it's waiting. You'll probably see your process with D a good amount of the time. If that's the case, the w_chan value should tell you more about what it's waiting on.

If I were a betting man, I'd assume it's disk bound. Hence, the wait state of the CPUs.

Try using `iostat -x and see how much I/O you have, and how much time is spent waiting.

It should be noted that different types of queries have vastly different performance profiles. This is normal. Sequentially reading a database will go very fast. In fact, the database is probably optimized for such a thing with disk readaheads, and the like. However, a random smattering of reads/writes all over the disk is going to be abysmal. And there's nothing the database can do. Your only real prayer is via upgraded IO. Flash memory, RAID arrays, or proper NAS/SAN storage systems.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.