| Articles Home |
Quick results are the point of this article, not optimum effect, here are quick and dirty database performance tuning methods. Oracle database tuning, Informix tuning, DB2 tuning, Postgres, Mysql tuning? It does not matter what RDBMS you use these days database performance tuning is all pretty much the same or at least my approach to finding and fixing database performance problems is the same. I would use the same approach for Sybase tuning and SQL Server tuning, though I have never administered either. RDBMS tuning is pretty much all the same because database software is pretty much the same these days. Any feature that is useful in one RDBMS is quickly copied by other database software.
All RDBMS (Relational Database Management Sytems) have an engine, an optimizer, SQL, run on some OS, and applications interface to the RDBMS, they are more similar than different. Database configuration, disk layout and the operating system is usually under the configuration management control of the database administrator and system administrator, many applications are subject to processes that are outside the administrator's control so applications are not addressed in detail in this article and are harder to fix.
Here are the heuristics listed in order of effort expended:
Keep in mind that the application is separate from the database and the database engine is separate from the OS it runs on and that OS connects to a network. This layer architecture is a way I think of the performance problems that tuning will fix. Many problems are where the layers interface, or how well the system is integrated.
Look at net traffic, OS stats, DB stats, I/O stats. The system is a whole, so compare the stats together to get a view of what the system is doing over an interval of time. It is the whole system that must perform well, not just the database.
Make a list of down times, batch job failure, backup failure, slow performance, etc., in the network, OS, applications and the database engine. 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, a lack of security and poor configuration management may mask or compound any database performance problems. Tuning just the database is futile if the system crashes every hour. From the problem tracking list find the easiest things to fix with the most payoff. Usually there are a couple quick, effective things that can be done.
Many times the quick and effective fixes may not be database problems, but a problem or mix of problems in other parts of the system or configuration management. Examples: OS file systems that are too full, shared memory misconfiguration, software integration problems between the OS, database and third party applications such as missing software patches. Lack of configuration management of software going into production systems is a problem that many overlook but can degrade performance or stop systems altogether.
Again, check OS, network, application configurations and user configurations, not just the database configuration. Look up parameters that you do not understand. Many bad installations I have seen used old configuration files from previous releases with out of date parameters or lack new parameters that make the software perform well such as new fast NIC cards set to run at an old low speed.
Read the release notes that come with the database software for OS configuration parameters and needed OS and applictation patches. And make sure the database and OS match the release notes of the applications that are run on the system.
One basic improvement to performance in databases such as Postgresql, Oracle, DB2 and Informix is the cost based optimizers that generate query plans by looking at statistics from the data such as number of rows in a table and distributions of values. But the statistics used by the optimizers are not usually generated by default, many times I have seen no statistics run in a database installation and poor performance results.
Check that the database optimizer statistics are run as recommended. Oracle "ANALYZE" (release 7), "DBMS_STATS" (8+), Informix "UPDATE STATISTICS", Postgesql "vacuum analyze" commands create the statistics used by the SQL optimizers to choose fast access methods to the data. Statistics only have to be run when the data changes, running statistics jobs is resource intensive so remember to run statistics not more than needed, the object is to improve and tune database performance, not cripple it.
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, 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. Same with Oracle, DB2, Informix. 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, find those few that cause the most problem and put effort into fixing those.
Identify what SQL is run and why the SQL is run. Sometimes reports are run at the wrong times on an OLTP application system or a batch SQL job can be easily rescheduled to not conflict with another application.
Identify the SQL statements that are slow and schedule these to run at times that do not crush the rest of the system. Run 'set explain on'(informix) or 'set autotrace on;set timing on'(oracle) to see what the optimizer is doing to SQL that you are checking.
Sometimes indexes are controlled by applications and cannot be administered easily. But sometimes there is no problem manipulating the indexing. The first thing is to check that indexes are set up for slow SQL statements, many times there is no index used by the problem SQL statement because the application has changed but not indexing on the tables affected.
There are lots of quick fixes that can be checked for indexes. Does the index contain an attribute which has nulls in the domain? The index will not be as effective in comparisons, maybe a default value can be used to get rid of the nulls. Attributes that have a small domain, uneven distribution of values and nulls are not usually good choices for attributes of an index. Sometimes indexes need rebuilding if they are in tables that have changed a lot in a short time.
There are different types of indexes and index organization. Bitmap indexes can be used for attributes with low cardinality especially if there is an uneven distribution across the attribute domain. Because of costly updates they are mostly used in tables that do not get updated such as a database warehouse application. 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 the code in an application which may not be under the control of a database administrator.
If you have multiple CPUs and many disks some degree of parallelism can be easily configured, but it is too much detail for each database to cover here. Check your manuals, environment variables, configuration files for both sorting and SQL parallelism. I have seen database installations where there was easy to use parallelism features that noticably improved performance after a configuration change and a restart.
Table partitioning is a method of storing tables in separate parts to reduce the size of the data that needs to be accessed. Analysis of SQL that accesses large tables may reveal a table or index partition scheme that can get rid of a bottleneck by shrinking access to a small part of the table, the rest is partitioned into rarely accessed pieces that are basically dormant. An example partioning a table on a "date" attribute, the more developed RDBMS products have several table and index partitioning methods.
- If you have a smaller set of disks then do the traditional database disk configuration. Separate the system catalogs from the various types of logs in different database partions. (Informix- logical and physical logs) (Oracle- rollback, redo and archive logs) Separate very active tables from each other. Separate indexes from tables. Table and index partioning over multiple disks will help parallelism and i/o performance.
But disk reorganization is a lot of work. Separate spaces and disks for the database logs is probably the most payoff, followed by the most active tables.
Help the developers to stop future problems by getting involved in design and code reviews. Look for such things as many connections made and terminated to the database over a network when one connection can be made, lack of database error trapping, 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 avoid long transactions.
An older version of this article: Quick and Dirty RDBMS Tuning is on O'Reilly's Onlamp.com site.