Make use of performance analysis tools offered by your database and application server vendor. These tools typically enable you to measure transactions per second and memory, cache, and disk utilization. Check the CPU utilization and I/O utilization of your database server. If they are near maximum levels, this is a strong indication that the database is limiting the performance of your site.

To understand database performance, you must know your data and the operations you are performing on it. The first step is to get a copy of the DDL for all the tables in your database and get a good estimate of how many rows are in each table. Most major database systems have tools that can tell you this quickly. In a pinch, you can issue the following query for each table:

SELECT count(*) FROM <table-name>

This query might take some time for large tables, so it is best to use the vendor-supplied tools or commands. In addition to this information, you’ll need a list of the indexes on each table.

loading table of contents...