Database performance depends on several factors at the database level, such as tables, queries, and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must minimize and make as efficient as possible. As you work on database performance, you start by learning the high-level rules and guidelines for the software side, and measuring performance using wall-clock time. As you become an expert, you learn more about what happens internally, and start measuring things such as CPU cycles and I/O operations.
Typical users aim to get the best database performance out of their existing software and hardware configurations. Advanced users look for opportunities to improve the MySQL software itself, or develop their own storage engines and hardware appliances to expand the MySQL ecosystem.
The most important factor in making a database application fast is its basic design:
Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns.
Are the right indexes in place to make queries efficient?
Are you using the appropriate storage engine for each table,
and taking advantage of the strengths and features of each
storage engine you use? In particular, the choice of a
nontransactional storage engine such as
MyISAM or a transactional one such as
InnoDB can be very important for
performance and scalability.
Does each table use an appropriate row format? This choice
also depends on the storage engine used for the table. In
particular, compressed tables use less disk space and so
require less disk I/O to read and write the data. Compression
is available for read-only
and for all kinds of workloads with
Does the application use an appropriate
locking strategy? For
example, by allowing shared access when possible so that
database operations can run concurrently, and requesting
exclusive access when appropriate so that critical operations
get top priority. Again, the choice of storage engine is
InnoDB storage engine
handles most locking issues without involvement from you,
allowing for better concurrency in the database and reducing
the amount of experimentation and tuning for your code.
Are all memory areas used
for caching sized correctly? That is, large enough to
hold frequently accessed data, but not so large that they
overload physical memory and cause paging. The main memory
areas to configure are the
InnoDB buffer pool, and the
MySQL query cache.
Any database application eventually hits hardware limits as the database becomes more and more busy. A DBA must evaluate whether it is possible to tune the application or reconfigure the server to avoid these bottlenecks, or whether more hardware resources are required. System bottlenecks typically arise from these sources:
Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.
Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.
CPU cycles. When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.
Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.
Because all SQL servers implement different parts of standard SQL, it takes work to write portable database applications. It is very easy to achieve portability for very simple selects and inserts, but becomes more difficult the more capabilities you require. If you want an application that is fast with many database systems, it becomes even more difficult.
All database systems have some weak points. That is, they have different design compromises that lead to different behavior.
To make a complex application portable, you need to determine which SQL servers it must work with, and then determine what features those servers support. You can use the MySQL crash-me program to find functions, types, and limits that you can use with a selection of database servers. crash-me does not check for every possible feature, but it is still reasonably comprehensive, performing about 450 tests. An example of the type of information crash-me can provide is that you should not use column names that are longer than 18 characters if you want to be able to use Informix or DB2.
The crash-me program and the MySQL benchmarks
are all very database independent. By taking a look at how they
are written, you can get a feeling for what you must do to make
your own applications database independent. The programs can be
found in the
sql-bench directory of MySQL
source distributions. They are written in Perl and use the DBI
database interface. Use of DBI in itself solves part of the
portability problem because it provides database-independent
access methods. See Section 8.12.2, “The MySQL Benchmark Suite”.
If you strive for database independence, you need to get a good
feeling for each SQL server's bottlenecks. For example, MySQL is
very fast in retrieving and updating rows for
MyISAM tables, but has a problem in mixing slow
readers and writers on the same table. Transactional database
systems in general are not very good at generating summary tables
from log tables, because in this case row locking is almost
To make your application really database independent, you should define an easily extendable interface through which you manipulate your data. For example, C++ is available on most systems, so it makes sense to use a C++ class-based interface to the databases.
If you use some feature that is specific to a given database
system (such as the
statement, which is specific to MySQL), you should implement the
same feature for other SQL servers by coding an alternative
method. Although the alternative might be slower, it enables the
other servers to perform the same tasks.
To use performance-oriented SQL extensions in a portable MySQL
program, you can wrap MySQL-specific keywords in a statement
/*! */ comment delimiters. Other SQL
servers ignore the commented keywords. For information about
writing comments, see Section 9.6, “Comment Syntax”.
If high performance is more important than exactness, as for some Web applications, it is possible to create an application layer that caches all results to give you even higher performance. By letting old results expire after a while, you can keep the cache reasonably fresh. This provides a method to handle high load spikes, in which case you can dynamically increase the cache size and set the expiration timeout higher until things get back to normal.
In this case, the table creation information should contain information about the initial cache size and how often the table should normally be refreshed.
An attractive alternative to implementing an application cache is to use the MySQL query cache. By enabling the query cache, the server handles the details of determining whether a query result can be reused. This simplifies your application. See Section 8.9.3, “The MySQL Query Cache”.