|
|
|
|
|
(Continued from page 5)
a small amount of
overhead due to increased context switching.
The QEF sort_size was reduced from 10 MB to the default of 16 KB. A large
sort buffer skews the server toward in-memory sorting. Normally this would be a good
use of memory but in Ingres 6.4 the algorithm used to sort in memory (insertion sort--N *
N) is less efficient than that used to sort on disk (quicksort--N log N). For large
data sets, it is actually quicker to sort on disk.
The servers' process priority was reduced from 5 to 4 so server CPU utilization
would not impact client response time.
A separate database server was
|
|
|
|
|
|
|
|
|
|
|
created for the historical database.
This allowed the resource-intensive queries to be isolated. It also caused the QSF
pool flushing to disappear since the Query Execution Plans for the two databases were now
in separate QSF pools.
After these changes were made, the CPU utilization and computable queues returned to
acceptable levels. More importantly, the users stopped complaining. This was
not the only bottleneck on the system but it was the main one.
The key to tuning
Often you will hear someone say that tuning system parameters will not gain much in the
way of performance, maybe 10%. But it all |
|
|
|
|
|
|
|
|
|
|
depends on where the bottleneck is!
In this case, I was able to restore adequate response time to the system without touching
a line of code or adding a secondary index. The key is to find and eliminate the
bottlenecks.
System tuning is not magic, nor is it a science in which you feed a bunch of data into a
computer and it spits out the answer. It is still very much an art that is mostly
intuitive. Based on the symptoms observed and your understanding of how all the
pieces work together, you try to identify the source of the problem and some possible
solutions. Sometimes you have to try more than once to get it right.
The fun part about consulting is seeing the fruit of your labor and knowing that you've
really helped someone. Besides the financial rewards, you usually re
(Continued on
page 12)
|
|
|
|
|
|
|
