Eight Steps to Fix Your Database Performance Problem

Christopher Browne, Database Administrator, Afilias Canada

Track: Databases
Date: Thursday, July 27
Time: 2:35pm - 3:20pm
Location: Portland 252

We examine eight methods to "attack" database performance problems. Frequently, minor system configuration problems can cause performance to suffer. This session presents various classes of these problems, with a view to addressing them without needing to modify the application.

  1. Check your logs. They often report information about what sorts of queries are running slowly, and OS logs may tell you if hardware failures are causing the slowdown.
  2. RAM, Disk or CPU: Determining which resource is being abused
  3. Database Configuration: Choosing what sizes of buffers to use; defaults are often not optimal
  4. Database Maintenance: Making sure queries aren't spinning their wheels reading empty space
  5. Table Analysis: Modern cost-based query optimizers require statistics on data distribution
  6. Disk Configuration: Disk arrays and smart RAID controllers can solve some performance woes
  7. Connection Pool management. Efficient use of database connections is often key, as connections can be expensive to create, and consume memory simply by existing.
  8. Analyzing test queries can suggest additional indexes that would improve performance