What's throttling the database?
- by Troels Arvin
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?
