This post was originally written by Glenn Paulley in May of 2008 and published to sybase.com.
In a previous post I stated that I do not believe that many firms go to the trouble of detailed performance tuning, for parameters such as buffer pool configuration, simply because it is too labour-intensive: static tuning analysis requires a workload that is (1) known and (2) fairly constant. Doing this manual performance tuning was the only option with systems such as IMS in the 1970's, but today with relational database systems I would argue that tuning a system is an order of magnitude more complicated, simply because the application environment is that much more complicated.
The observation above unfortunately runs counter to typical practice from relational database system vendors: and that is, to deal with this complexity from a performance tuning perspective, vendors add tuning "knobs" to their products. I know from my own experience here at iAnywhere that it is sometimes very difficult to conquer the urge to add a "knob". "Knobs" are expedient, and they can be used as "quick fixes" to workaround performance problems in shipping code. But avoiding the introduction of additional knobs is a worthwhile goal because "knobs" are not in the best interests of our customers.
It is all too easy, in this forum, to be critical of other commercial products in this regard. All database management systems have tuning knobs, including SQL Anywhere. One of SQL Anywhere's "knobs" is the server's multiprogramming level, and most of the other commercial products permit the DBA to establish the multiprogramming level through a command-line switch or a .ini option.
With most database system products, manual configuration settings abound. I'm sure that Oracle engineers had sound reasons to refrain from automatically sizing the redo log buffer pool when some of the other pools within an Oracle server can adjust their size dynamically. I'm sure that Sybase ASE engineers had good reasons to split ULC tuning parameters in ASE 15.0.2, as discussed recently by Derek Asirvadem of Software Gems Pty in a thread in the sybase.public.ase.performance+tuning newsgroup:
In 15.0.2 the ULC has been "split" into a user-db-ULC and a separate tempdb-ULC. This parm identifies the size of the latter. It should be set to the same size as whatever you get when you run sp_logiosize on that tempdb, which for 15.x now defaults to 4k (hopefully in future versions the default setting for this parm will be 4k and thus this mannual step can be avoided). It is a per-connection or per-session unit, the memory required = connections x STLCS, just as memory reqd for ULC is connections x ULC size. (15.x is MUCH faster, but nothing is free, and it needs more memory to provide that speed.) It is one piece of a whole set of optimisations for all I/O, and in particular, for tempdb. The term "lazy write" is misleading, one is better off understanding each of the various enhancements singularly (and importantly, identifying what parm/resuorce that needs to be allocated in order to take advantage of it), then forming an understanding of the effect of the set of optimisations as a whole.
And then there is DB2 UDB: the IBM DB2 UDB 9.5.1 Database Tuning manual is, simply, breathtaking in its 459 pages: a plethora of options from buffer pool configuration to logging configuration to insert behaviour with page utilization.
It would be interesting to attempt to estimate the effect these "knobs" have, both on the development cost of the software, and customers' Total Cost of Ownership (TCO), for each database management system. For customers, their TCO is going to be negatively affected through additional training, experimentation and diagnosis, and longer episodes of performance evaluation due to the increase in the number of performance factors.
My point is this: I believe the percentage of commercial database installations that go into significant performance and tuning details with system configuration to be exceedingly small - and it doesn't matter whether or not the system is ASE, DB2, Oracle, or anything else. In my view, adding additional performance knobs of the types described above is simply counterproductive: the answer in the longer term is self-managing database systems.
