Level 2: The Operating System

Typically, there's not much the user can do about the modern operating system. Operating systems are usually perfectly preconfigured for most use cases. A performance gain of more than 10 to 20 percent cannot be expected. Typical tuning actions involving the operating system for databases include: increasing the number of file handles per process, using a scalable modern filesystem (XFS or ext4), setting I/O schedulers to noop, and setting the swappiness to 1.

One noteworthy item is the trend toward consolidation that has made inroads into data centers. This trend started with the spread of the SAN and is now continuing with virtualization solutions of all kinds. Basically, there's nothing wrong with consolidating many (typically idle) systems on a large-scale host, but for a high-performance database working at full load, virtualization solutions are not the answer.

For one thing, consolidation solutions such as SAN and virtualization always come at the price of some overhead. For another, SAN systems are fairly distant from the database server itself, viewed geographically, which can lead to higher latencies. The biggest problem in consolidation, however, is overcommitting resources (you have too many consumers for not enough resources) and what is known as the noisy neighbor effect: Another virtual guest system causes so much load that it brings the database to its knees.

The noisy neighbor effect is particularly insidious because it is extremely difficult to identify from the perspective of the guest system. You only notice that your own processes are somehow slowing down. Another phenomenon is guest systems running more slowly with more virtual cores than with fewer. In this light, administrators should always avoid virtualization from a performance point of view for critical and heavily loaded database systems.

Level 3: The Database Configuration

The predefined configuration values for MySQL were poor for many years, but the defaults have improved greatly since MySQL 5.6. That said, many older MySQL configuration files still drag the ballast of unfavorable settings along with them.

The current MySQL 5.6 versions can be configured with some 435 variables. For overworked database administrators, the question is: Where do I start? (Figure 1). Fortunately, it is sufficient in most cases to configure seven MySQL variables correctly to boost performance. After doing so, most MySQL databases will probably run at a reasonable speed.

Figure 1: MySQL architecture – a schematic overview.

MySQL is highly flexible, thanks to its storage engine architecture. However, this architecture also requires some understanding about which parameters are responsible for which area and which storage engine. MyISAM was the default storage engine for MySQL before version 5.5. From MySQL version 5.5 on, InnoDB has been the storage engine.

Three variables are mainly responsible for the SQL layer – table_open_cache, table_definition_cache, and query_cache_size/query_cache_type  – whereas four variables tune storage engine behavior.

Table Open Cache

Table Open Cache controls the number of file descriptors that MySQL requests from the operating system. This value should be approximately the same size as the number of tables in your joins times the number of open connections. The commands shown in Listing 6 tell you if the value is big enough. The math is: (maximum number of concurrent open connections)  x (number of tables per join)=151  x  5 (assumption)=755. This means that table_open_cache, with a default value of 400, is likely to be insufficiently dimensioned given maximum system load. However, some old configurations still overtax the new defaults. (Note that MySQL 5.1 renamed table_cache to table_open_cache.)

mysql> SHOW GLOBAL STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 400   |
| Opened_tables | 70123 |
+---------------+-------+
mysql> SHOW GLOBAL VARIABLES WHERE variable_name = 'max_connections' OR variable_name = 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| max_connections  | 151   |
| table_open_cache | 400   |
+------------------+-------+