Unanswered: Need help for Database Tunning Basic Level
I am the Linux System Administrator. I have installed DB2 V7.1 on Linux 7.2. Now I'm interesting in taking up DB2 performance tunning on Linux. I'm very begineer on DB2 Tunning. I have a database called BILL. I want to Tunning my database. In this database userspace (tablespace is frequently access for reading and writing).
I would appreciate if i get valuable suggestions and guidence from U.
Can anyone guide me basic tips to start db2 tunning as initial level.
First, identify your problem. What is it (write it down!) that is causing you to conclude that "the database needs tuning?" Now, what are all (write them down!) the possible causes for what you are seeing; including, but not limited to, "the database needs tuning?"
You can easily "bark up the wrong tree" if you don't be very systematic. Performance problems perceived by a user could be caused by:
Hardware or network problems... many retries for communication... misbehaving NIC is flooding the net...
The I/O subsystem is too slow or is throwing errors.
There's only one disk-drive so it is having to do everything for everyone.
The DB2 server processes can't get enough memory.
I/O operations are "clogging up the wire" and there is excessive waiting.
Bit-bucket overflow. (You'll find the bucket underneath the main compartment; it's where all the deleted rows go. Wear gloves...)
The queries and/or the database-design of BILL are poorly designed, so that DB2 is having to do the impossible. (Needs indexes; trying to do massive outer-joins; etc.)
The database is offended at being called BILL [Gates] and wants to be renamed THOMASJWATSON.
Finally, maybe, "it really does 'need tuning.'" The DB2 processes have enough memory and I/O-capacity... they're running promptly whenever they need to run, without too much contention, but the present mix of queries is causing a condition that could be improved by "tweaking a knob."
I list the last choice last because it's frankly pretty unlikely. DB2 is a pretty darned intelligently-designed subsystem. With its default tuning settings it runs very well. In my experience, most performance problems in a database (no matter what the server) can be linked directly back to the design of the database itself, and the lack-of-design of the queries that are run against it. (Let's just say that developers tend to believe that, "if it runs, it works." They write gargantuan queries to get the results they want, "all at once," and I am often amazed that those queries run at all. Makes me want to pat the server on the head and say, "poor thing!")
Rig up a performance monitor and collect some statistics about what processes in your system, particuarly DB2's, are actually waiting-on. Proceed from those clues.