Storage Engine Configuration
The MySQL server supports multiple storage engines and is highly configurable in terms of which engines to make available. General storage engine characteristics are discussed in Chapter 2, "MySQL SQL Syntax and Use." This section discusses how to configure which engines your server uses. It also provides specific configuration information for the MyISAM and InnoDB storage engines.
Selecting Storage Engines
The MySQL server provides flexible control over which storage engines to make available:
The following list describes how to include or exclude storage engines when building MySQL from source. It also indicates how to disable each engine at startup time if it is built in but you do not want to use it. (The terms "included" and "excluded" refer here to configuration choices, whereas "enabled" and "disabled" refer to runtime choices. "Available" means "can be used when the server is running.")
As the server runs, it designates one storage engine as the default, which it uses for tables that are created without an explicit ENGINE = engine_name option. MyISAM is compiled in as the default engine, but you can select a different default at startup time or runtime:
To determine which storage engines are available, use the SHOW ENGINES statement. Appendix E describes the output produced by that statement.
Configuring the MyISAM Storage Engine
MyISAM tables have separate data and index files, which are handled differently:
This section describes the general operation of the key cache and the system variables that you use to configure it.
Values not found in the cache when needed are "misses" and must be read from disk. Values that are found are "hits." The purpose of the key cache is to reduce disk access (that is, to minimize the ratio of misses to hits). It boosts performance greatly because disk access is much slower than memory access.
Index buffers containing frequently used values tend to stay in the key cache, but a larger cache increases the chance of a hit. This decreases the need to discard buffers to make room for new ones and minimizes the number of disk accesses needed for index processing. If the key cache currently is small and you have memory available, making the cache larger generally is one of the single easiest and best configuration changes you can make.
To configure the key cache size, set the key_buffer_size system variable. Its default value is 8MB, but can be increased up to 4GB if you have the memory available. For example, to set the key cache to 512MB, you can put these lines in an option file:
[mysqld] key_buffer_size = 512M
Be careful not to set the key cache size so large that you use all available memory. That can cause the key cache itself to get paged out, which defeats the purpose of using a cache to hold information in memory. Remember too that other storage engines use their own buffers for which memory must be allocated, and that generally there are other processes running on the server host that require memory.
The preceding discussion is written as though there is a single key cache. That is true up through MySQL 4.1.0, and remains true by default. However, as of MySQL 4.1.1, it is possible to create multiple key caches. The revised caching mechanism also provides more control over cache operation. Here is a summary of its features:
Multiple key caches can serve to reduce cache contention. If you have a table or set of tables that are heavily used, you can assign them to a separate key cache so that index caching for them does not have to compete with all the other tables that are processed through the default cache.
Each key cache is associated with a set of system variables. Because these variables are related, they are grouped as components that form a structured system variable. Structured variables are an extension of simple system variables, so they are accessed using a syntax that combines a cache name and a variable name:
Each key cache structured variable has these components:
One key cache is the default and has a name of default. If you refer to a key cache component variable without using a cache name, MySQL uses the default cache. Thus, key_buffer_size and default.key_buffer_size refer to the same variable. Key cache names must be legal identifiers and are not case sensitive. They can be quoted like any other identifier using backtick ('`') characters, or, if the ANSI_QUOTES SQL mode is enabled, with double quote ('"') characters.
To create a new key cache, simply assign a value to one of its components. For example, to create a cache named my_cache with a size of 24MB, at server startup, add these lines to the server option file:
[mysqld] my_cache.key_buffer_size = 24M
To create the cache at runtime, use this statement:
SET GLOBAL my_cache.key_buffer_size = 24*1024*1024;
After you create a key cache, you can assign MyISAM tables to it with CACHE INDEX. This statement names a key cache and one or more tables to be assigned to it. The following statement assigns the member and president tables from the sampdb database to the cache named my_cache:
CACHE INDEX member, president IN my_cache;
You can also preload table indexes into their assigned cache with LOAD INDEX INTO CACHE if you choose:
LOAD INDEX INTO CACHE member, president;
It is not necessary to preload the indexes, but the server reads index blocks sequentially if you do. This is more efficient than waiting for them to be fetched as needed.
To destroy a key cache, set its size to zero. Any tables assigned to the cache are reassigned to the default cache. If you set the size of the default key cache to zero, indexes of tables assigned to it are processed using filesystem caching the same way as for MyISAM data files.
Key cache assignments last only until the server shuts down. To make assignments each time the server starts, place the appropriate CACHE INDEX and LOAD INDEX INTO CACHE statements in a file and start the server with an --init-file option that names the file.
Configuring the InnoDB Storage Engine
The InnoDB storage engine manages a shared tablespace for storing table contents and its data dictionary. You have the option of configuring InnoDB to use one tablespace per table. InnoDB also has its own log files and memory buffers.
Configuring the InnoDB Tablespace
By default, the InnoDB storage engine does not use separate files for each table the way that other storage engines such as MyISAM and BDB do. Instead, it manages all InnoDB tables within a single shared tablespace, which is a logically unified block of storage that the engine treats as a giant data structure. (In a sense, the tablespace is something like a virtual filesystem.) For an InnoDB table stored in the shared tablespace, the only file uniquely associated with the table is the .frm format file that is stored in the database directory of the database that the table belongs to. The shared tablespace also contains the InnoDB data dictionary that records information about table structure.
It is also possible to configure InnoDB to represent each table using its own tablespace file. In other words, tables are created using individual tablespaces. To use individual tablespaces, start the server with the ---innodb-file-per-table option. The shared tablespace still is needed even in this case because it contains the InnoDB data dictionary, although it need not be as large.
Shared Tablespace Configuration Parameters
The InnoDB shared tablespace, although logically a single storage area, comprises one or more files on disk. Each component can be a regular file or a raw partition. This section describes the configuration options that you use to set up and manage the shared tablespace. It's possible to specify these options on the server command line, but in practice this is rarely done. Instead, you should configure the tablespace using an appropriate server group in an option file (for example, the [mysqld] or [server] group), so that the server uses the same configuration consistently each time it starts. Two options are the most important:
If you provide no values for either option, the InnoDB storage engine creates a default tablespace consisting of a single 10MB auto-extending file named ibdata1 in the server's data directory. By using these options, you can explicitly control the number, size, and placement of the files in the shared tablespace.
As a simple example, suppose that you want to create a tablespace consisting of two 10MB files named innodata1 and innodata2 in the data directory. Configure the files as follows:
[mysqld] innodb_data_file_path = innodata1:10M;innodata2:10M
Based on the preceding rules, the following three configurations all specify the same set of tablespace files, assuming that the data directory is /var/mysql/data:
[mysqld] innodb_data_home_dir= innodb_data_file_path=/var/mysql/data/ibdata1:10M;/var/mysql/data/ibdata2:10M [mysqld] innodb_data_home_dir=/var/mysql/data innodb_data_file_path=ibdata1:10M;ibdata2:10M [mysqld] innodb_data_file_path=ibdata1:10M;ibdata2:10M
The innodb_data_file_path value consists of file specifications that are separated by semicolons. The parts of each specification are separated by colons. The simplest file specification syntax consists of a filename and a size, but other syntaxes are legal:
path:size path:size:autoextend path:size:autoextend:max:maxsize
The first format specifies a file with a fixed size of size. A size value should be a positive integer followed by M or G to indicate units of megabytes or gigabytes, respectively. The second format specifies an auto-extending file; if the file fills up, InnoDB extends it incrementally. The third format is similar, but includes a value indicating the maximum size to which the auto-extending file is allowed to grow. Only the final component of the tablespace may be listed as auto-extending.
Initial Configuration of the Shared Tablespace
In the usual case, the shared tablespace consists only of regular files and does not include any raw partitions (device files). To perform the initial setup for a shared tablespace that contains only regular files, use this procedure:
If you've already started the server without configuring InnoDB explicitly, InnoDB will have created a shared tablespace using the default configuration. To configure the tablespace explicitly, first stop the server and remove the InnoDB-related files (tablespace and log files). Then specify the configuration options you want to use and restart the server. (You should do this before creating any InnoDB tables. Otherwise, dump them with mysqldump before reconfiguring and reload them afterward.)
A factor that counts against using raw partitions in the InnoDB tablespace is that your system backup software might be oriented toward use with filesystems rather than partitions. In this case, using partitions would make it more difficult to perform system backups.
Including a raw partition in the tablespace is a two-step procedure. Suppose that you want to use a 20GB partition on a Unix system that has a pathname of /dev/rdsk8. In this case, it's necessary to specify a value for innodb_data_home_dir because the partition doesn't lie under the data directory. For example, if you set innodb_data_home_dir to an empty value, you can list the full pathname of the device file in innodb_data_file_path to configure the partition as follows:
If you specify a raw partition as part of the InnoDB tablespace, make sure its permissions are set so that the server has read/write access to it. Also, make sure the partition is being used for no other purpose. Otherwise you will have competing processes each thinking that they own the partition and can use it as they please, with the result that they'll stomp all over each other's data. For example, if you mistakenly specify a swap partition for use by InnoDB, your system will behave quite erratically!
When configuring the InnoDB shared tablespace on Windows systems, backslashes in pathnames can be specified using either single forward slashes ('/') or doubled backslashes ('\\'). Also, you should still separate the parts of each file specification with colons, even though colons may also appear in filenames (full Windows pathnames begin with a drive letter and a colon). When it encounters a colon, InnoDB resolves this ambiguity by looking at the following character. If it is a digit, the next part of the specification is taken to be a size. Otherwise, it's taken as part of a pathname. For example, the following configuration sets up a tablespace consisting of files on the C and D drives with sizes of 50MB and 60MB:
[mysqld] innodb_data_home_dir = innodb_data_file_path = C:/ibdata1:50M;D:/ibdata2:60M
When you're setting up the initial tablespace, if startup fails because InnoDB cannot create some necessary file, check the error log to see what the problem was. Then remove all the files that InnoDB created (excluding any raw partitions you may be using), correct the configuration error, and start the server again.
Reconfiguring the Shared Tablespace
After the shared tablespace has been initialized and you have begun to use it, you cannot change the size of its component files. However, you can add another file at the end of the list of existing files, which may be helpful if the tablespace fills up. One symptom of a full tablespace is that InnoDB transactions consistently fail and roll back when they should succeed. You can determine the amount of free space with the following statement, where tbl_name is the name of any InnoDB table that is located in the shared tablespace:
mysql> SHOW TABLE STATUS LIKE 'tbl_name';
To make the shared tablespace larger by adding another component, use this procedure:
Using Individual Tablespaces
To use one tablespace per InnoDB table, start the server with the --innodb-file-per-table option. In this case, each InnoDB table has an .frm format file and an .ibd data file, both stored in the database directory for the database that contains the table.
The choice of whether to use individual tablespaces affects only how InnoDB creates new tables. InnoDB can always access tables already created in the shared tablespace or with individual tablespaces, regardless of whether the --innodb-file-per-table option is used.
InnoDB Storage Engine Variables
The preceding section discusses how to configure InnoDB's tablespace. InnoDB also has its own log files and memory buffers, and several other configuration parameters. The following list describes a few parameters that commonly are used to affect the operation of the InnoDB storage engine.