ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


Quick and Dirty RDBMS Tuning

by Steven Hauser
11/13/2003

Oracle tuning. Informix tuning. DB2 tuning. PostgreSQL tuning. It doesn't matter which database you use -- RDBMS tuning is all pretty much the same, because database software is all pretty much the same these days. All major relational database systems have an engine, an optimizer, and a SQL interpreter. They all run over an OS, provide an applications interface to the RDBMS, and have their own backup utilities. If one does not have a particular feature yet, it soon will. Features in one product soon show up in the others, if they prove valuable in the market.

You'd like to tune your database, but don't have much time. The point of this article is to find quick and effective results, not the optimum effect. Application design is not covered in detail. Here is how to tune with the least effort.

Analyze the Whole System, Not Just One Part

Switch your point of view to examine the whole system, not just the database. This may be the easiest and most effective tuning technique.

You may not have to tune the database at all. At one customer site, shipping labels were not generated fast enough; the four-CPU Unix system was maxed for hours, using massive I/O and CPU resources. "The database is too slow," the customer told me. "Go tune it." I looked at the database statistics and also the operating system statistics. That's when I noticed many processes spawning per minute from the Unix at command (a batch scheduling utility).

It was clear that the database was not the problem. The application scheduled a batch job for each label with the at command, which had the system thrashing when it tried to generate a hundred thousand labels. After changing to a shell script to remove at, the application took a tenth of the time. The database was not changed.

Look at net traffic, OS statistics, database statistics, I/O statistics, backups, and so on. The system is a whole, not just a database or a single application on that database. Compare the stats together to get a view of what the system is doing over an interval of time.

Related Reading

SQL Tuning
By Dan Tow

Track All of the Problems in the System

Make a list of system downtimes, batch job failures, backup failures, slow performance, et cetera, in the network, OS, applications, and the database. Look for patterns and relationships in the statistics and the failures.

A system is a house of cards that needs a solid foundation. Lack of backups, lack of OS resources, network problems, lack of security, or poor configuration management (the process of changing a production system) may mask or compound any database performance problems. Tuning the database is futile if the system crashes every hour. Also note that changing something in the OS or adding a resource-heavy application to a server will affect whatever else is on the server, especially the database.

From the problem-tracking list, find the easiest things to fix with the most payoff. Usually there are a couple quick and effective things that can be done. Many times the effective fixes are not database problems, but problems in other parts of the system or configuration management.

I have seen full filesystems, shared memory misconfiguration, and software integration problems between the OS, database, and third-party applications, such as missing software patches to a backup software suite. Conflicting processes and applications contend for the same resources at the same time and slow the whole system. Lack of configuration management of software going into production systems is an often-overlooked problem that can degrade performance or stop systems altogether.

Check the Layer Interfaces

Keep in mind that the application is separate from the database and the database is separate from the OS it runs on, just as that OS is separate from the network. This layered architecture is a way I think of the performance problems that tuning will fix. Many problems occur where the layers come together.

Check Configuration Files for Problems

Configuration files are one place the layers of a system interface and interact. Check the OS, network, application configurations, and user configurations, not just the database configuration files. Look up parameters that you do not understand. Many "out of tune" installations I have seen used old configuration files from previous releases with outdated parameters, or lacked new parameters that would have improved performance. OS and server changes mean rechecking the configuration. I have seen new fast NIC cards set to run at the old low speed. New RAM added to the server should be configured in the OS to help resource problems.

Check the release notes that come with the database software for OS configuration parameters and needed OS and software patches. Make sure the database and OS match the release notes of the applications you run on the RDBMS. Software upgrades of the OS, RDBMS, and applications may mean changes to configuration files in several places, not just the database.

A Few Bad SQL Statements Can Affect Performance by an Order of Magnitude

SQL is the interface layer between the application and the RDBMS. Check that the database optimizer statistics are run as recommended. PostgreSQL ANALYZE, Oracle ANALYZE, and Informix UPDATE STATISTICS commands create optimizer statistics to choose methods of fast access to the data. These statistics are stored in the system catalog tables. An example of the data statistics is the pg_statistic table in the system catalog of PostgreSQL 7.3. Performance can seem crippled unless the data statistics jobs run properly. Performance may improve dramatically when the optimizer has good data statistics.

Most RDBMSes collect statistics from the server's process memory to monitor the database while it is running. These statistics are accessed with functions that are presented as views and can be accessed with SQL. With PostgreSQL 7.3, the monitoring statistics functions and views have information about the SQL statements run, the number of times a table or index is scanned, and more. Use these monitoring statistics to find the SQL statements that are the real system problems. Typically, plenty of SQL statements perform badly, but only a few are the real resource problems. Fix those first.

What do you do with the problem SQL statements? The easiest trick may be to schedule these to run at times that do not conflict with other processes on the rest of the system.

Fix Indexes That Affect Bad SQL

Sometimes indexes are controlled by applications and cannot be changed. Other times, there is no problem manipulating the indexes on the data. Run EXPLAIN (PostgreSQL), set explain on (Informix) or set autotrace on;set timing on (Oracle) to check what the optimizer does with your worst SQL statements. If the optimizer yields a data access plan that scans an entire large table, check that the tables have appropriate indexes for the queries. Many times there is no index. Another problem is that, if the index contains an attribute that includes nulls in the domain, the index may not be effective in comparisons. Sometimes indexes need rebuilding in volatile tables.

Bitmap indexes are good for attributes with low cardinality, especially if there is an uneven distribution across the attribute domain. Because of costly updates, this is mostly used in tables that do not get updated, such as those in a database warehouse. Beware, though — I have had problems with the Oracle 7.3.4 optimizer recognizing a bitmap index without a hint, which makes it almost worthless without changing application code.

Parallelism is Usually Disabled

These days, SMP machines are a cheap commodity. If you have multiple CPUs and I/O channels, some degree of parallelism can be easily configured, but the specifics are too detailed to cover here. Check your documentation, environment variables, and configuration files for both sorting and SQL parallelism.

Spread the Database Over Disks and I/O Channels

If you have a huge Hitachi or EMC-type disk array, put gobs of cache memory on the disk array. Remember the whole system approach and make sure you do not contend with other servers and applications pounding the I/O on the array. Disk arrays and Storage Area Networks (SANs) may attach many servers to the same storage resources. The usual advice is to distribute everything evenly over the array to spread out the I/O contention and use the array I/O and cache capacity. A quick and dirty tuning solution is to see if multiple servers are contending for the same resources. If so, arbitrate time slots or devices to minimize conflict and to improve performance.

If you have a smaller set of disks attached to the server, use the traditional database disk configuration. Separate the system catalogs from the various types of logs in different database partitions. With Informix, separate disk and I/O channels for logical and physical logs. With Oracle, separate disks and I/O channels for rollback, redo, and archive logs. Separate active tables from each other. Separate indexes from tables. Partitioning tables and indexes over multiple disks will help parallelism and I/O performance.

Disk reorganization is a lot of work. Separating I/O channels and disks for the logs will probably have the most payoff, followed by segregating the most active tables from each other.

Prevention is Easier Than Cures

Help your developers to stop future problems. Get involved in design and code reviews. Look for such things as multiple database connections made and terminated when one connection can be made, lack of database error trapping, and huge joins with gobs of I/O that are sent over networks to fat clients for additional processing. Show the developers how to analyze SQL for performance and how to avoid long transactions.

Help the system administrators and system architects design the whole system with the resources to run the RDBMS effectively. Look for conflicts with other processes and promote reasonable resource allocation before everything is thrown together in a heap to fight for resources in the architecture.

Your Perfectly Tuned System Just Changed

Systems change. Software is upgraded. New hardware and applications are added. Old parts of the system are removed. A whole-system approach to RDBMS tuning and administration helps you be aware of the changes, calculate the risks, and anticipate problems.

References

Steven Hauser contracts as a developer and systems/database administrator and feels that open source databases like Postgresql and MySql are now ready to do most commercial business computing.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.