Database Performance
This is the first of four articles about Database Management Systems’ (DBMS) performance. It presents an overview and some installation tips. The second article talks about building the database’s tables and indexes. Parts three and four get into the meat of accessing the database, with non-SQL tips in the third article and SQL tips in the fourth. Website Performance: Taxonomy of Tips introduced a classification scheme to help us organize the many performance tips found on the Internet. Database Management Systems fall into category #3.2 (the-server-side-script-accesses-a-service).Although this article is based on MySql experience, the concepts apply to other DBMS’s as well.
Become Intimate With Your DBMS
Whether we use Adabas, DB/2, Ingres, SQL Server, MySql, Oracle, PostgreSql, Progress, SAS, Sybase, or one of the many other DBMS’s out there, we need to know its internal details intimately. Merely installing it with its defaults and hammering out some quick SQL is a sure sign of a performance disaster just waiting to pounce.
Many products have performance documentation hidden away somewhere in their help files (or manuals). This is not a case of “when all else fails, read the manual.” This and the following three articles present generic concepts that will help, but system administrators, architects, coders, and all other techies on the project must be aware of the issues that affect their specific DBMS’s performance.
DBMS Installation
For the same reasons that were presented in Web Server Performance, our DBMS must be installed on a dedicated machine. If we want it to be lightning fast, we don’t want it competing with other software for system resources.
Keep in mind, though, that the networking between the DBMS and the web server is a critical performance factor. A dedicated, locally-connected, high-speed network is best. If the only configuration available to us offers a slow-speed or congested connection, installing the DBMS server and the web server on one machine may be the only reasonable option available.
DBMS Configuration
Configuring a DBMS installation for maximum performance is as individual as its collection of databases. It is impossible to provide a bunch of settings and say, “Here. Use these.” Instead, we need to start with information about how the DBMS is being used (or how we plan to use it if it’s a new installation). Different systems provide this information in different ways (e.g., logs, system variables).
Peter Zaitsev offered us some configuration tips in 2008. Although specific to MySql, they bring to light some performance concepts we need to consider. Many of his tips set appropriate sizes for buffers, pools, files, tables, and caches; and specify when to log and when to cache. [The only thing I can add to his list is to set max_write_lock_count low enough so that database reads don’t starve while waiting for locks to be released.]
Tuning for performance depends somewhat on the storage engines we are using. Each has its own characteristics, so each has its own requirements. Read-intensive and write-intensive tables may require different storage engines and/or different configurations.
Logging is helpful during development and testing, but don’t forget to turn it off (or minimize it) on the production system. Connection pooling may need to be tuned to match the production system’s actual experience. Compression and background processing, if available, need to be turned on.
Check the DBMS’s system variables or logs to find out whether caches, buffers, files, pools, and tables are being used properly.
Measure, Benchmark, and Monitor
I’m sure, by now, you are so used to hearing it from me that you would be disappointed if I didn’t say it, so here goes: Measure your DBMS’s performance and establish benchmarks. Know where you stand right now. Every time the website changes, re-measure performance and compare it to the benchmarks. This will let us know how the changes affected performance and it will give us a point of comparison for future use.
Network performance and database usage can be highly dynamic, so ongoing monitoring is essential. Use an internal monitoring agent with a MYSQLMON tester if using MySql. Otherwise, use an internal monitor. Both are available from Paid Monitor and both can be up and running without a lot of effort.
