(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)

                                                    previous.gif (4291 bytes)        next.gif (4312 bytes)