MySQL Database Tuning


Careful index design is one technique that improves the speed of a DBMS and can reduce the resource requirements of a database. However, comprehensive database tuning is a complex topic that fills many books. We include in this section only a few additional practical ideas to begin to improve the performance of a database system.

Here are some ways to improve DBMS performance:

  • Carefully choose attribute types and lengths. Where possible, use small variants such as SMALLINT or MEDIUMINT rather than the regular choice INT. When using fixed-length attributes, such as CHAR, specify a length that is as short as practical.

  • Use fixed-length attributes; that is, try to avoid types such as VARCHAR or BLOB. While fixed-length text attributes may waste space, scanning fixed-length rows in a query is much faster than scanning variable-length rows.

  • Design indexes with care. Keep the primary key index as small as possible, create only indexes that are needed, and use prefixes of attributes where possible. Ensure that the leftmost attribute in the index is the most frequently used in queries and, if all attributes are used, make sure the leftmost attribute is the one with the highest number of duplicate entries.

  • Create a statistics table if aggregate functions such as COUNT( ) or SUM( ) are frequently used in queries on large tables. A statistics table stores only one row that is manually updated with the aggregate values of another table. For example, if the statistics table maintains the count of rows in a large customer table, each time a row is inserted or deleted in the customer table, the count is updated in the statistics table. For large tables, this is often faster than calculating aggregate functions with the slow built-in functions that require complete processing of all rows.

  • If large numbers of rows are deleted from a table, or a table containing variable-length attributes is frequently modified, disk space may be wasted. MySQL doesn't usually remove deleted or modified data; it only marks the location as being no longer in use. Wasted space can affect access speed.

    To reorganize a copying data to a temporary location and back again. MySQL provides the OPTIMIZE TABLE command, which should be used periodically. For example:

    OPTIMIZE TABLE customer;

    The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL.

  • It is possible to create different table types for specific tasks. The default in MySQL is the MyISAM type. For small, temporary, frequently used lookup tables, a different type, the heap table type, can be used. There are other types like InnoDB, BDB, etc.

Another aspect of database tuning is optimizing the performance of the DBMS itself. Included with the MySQL installation is the mysqladmin tool for database administration. Details of the system setup can be found by running the following command from a Linux shell:

root@host# mysqladmin -p variables
Enter password:*******

Above command will display many variables. The important parameters are those that impact disk use. MySQL has several main-memory buffer parameters that control how much data is kept in memory for processing. These include:

  • The record_buffer for scanning all rows in a table

  • The sort_buffer for ORDER BY and GROUP BY operations

  • The key_buffer for storing indexes in main memory

  • The join_buffer for joins that don't use indexes

In general, the larger these buffers, the more data from disk is cached or stored in memory and the fewer disk accesses are required. However, if the sum of these parameters is near to exceeding the size of the memory installed in the server, the underlying operating system will start to swap data between disk and memory, and the DBMS will be slow.

In any case, careful experimentation based on the application is likely to improve DBMS performance.

Section 10.2.3 of the MySQL manual suggests parameter settings when starting the MySQL server. First, for machines with at least 64 MB of memory, large tables in the DBMS, and a moderate number of users, use:

safe_mysqld -O key_buffer=16M -O table_cache=128 \
-O sort_buffer=4M -O record_buffer=1M &

Second, if there is less than 64 MB of memory available, and there are many users, try the following:

safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
-O record_buffer=100k &

The following setting might be appropriate for the winestore, because many users are expected, the queries are largely index-based, and the database is small:

safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
-O table_cache=32 -O record_buffer=8k
-O net_buffer=1K &

Even more conservative settings might also be acceptable.

There are two other parameters we have not discussed. The table_cache parameter manages the maximum number of open tables per user connection, while the net_buffer parameter sets the minimum size of the network query buffer in which incoming queries are kept before they are executed.

The mysqladmin utility can report the status of the DBMS:

root@host# mysqladmin -p status
Enter password:*******

The output has the following format:

Uptime: 5721024 Threads: 14 Questions: 7874982
Slow queries: 6 Opens: 115136 Flush tables: 1
Open tables: 62

This gives a brief point-in-time summary of the DBMS status and can help find more about the number of user connections, queries, and table use. Similar output can be generated by running the commands SHOW STATUS and SHOW VARIABLES through the MySQL command interpreter.

Information about query performance can be gained with the benchmark( ) function, which can be used iteratively for tuning when altering table design or DBMS system parameters. The following statement illustrates benchmarking:

SELECT benchmark(10000, COUNT(*))
FROM table_name;

This statement reports the time taken to evaluate 10,000 calls to COUNT() on the table_name table.