Additional Server Configuration Topics

This section discusses several topics that can help you configure the server more specifically to the way you want to run it or that can help you achieve higher server performance:

• Controlling how the server listens to network interfaces for client connections

• Enabling or disabling LOCAL capability for LOAD DATA

• Internationalization and localization issues, such as the server's time zone setting and the character sets that it supports

• Enabling or disabling handlers for specific table types

• Configuring the InnoDB table handler

• Tuning the server by setting its internal variables

Controlling How the Server Listens for Connections

The MySQL server listens for connections on several network interfaces, which you can control as follows:

• On all platforms, the server listens on a network port for TCP/IP connections, unless started with the --skip-networking option. The default port number is 3306; to specify a different number, use the --port option. If the server host has more than one IP address, you can specify which one the MySQL server should use when listening for connections by specifying a --bind-address option.

• Under UNIX, the server listens on a UNIX domain socket file for connections from local clients that connect to the special host-name localhost. The default socket file is /tmp/mysql.sock; to specify a different name, use the --socket option.

• When run on Windows NT-based systems, servers with -nt in their names include named pipe support. By default, the pipe name is MySql; to specify a different name, use the --socket option. Prior to 3.23.50, named pipe support is always enabled. After that, it is off by default and you must enable it explicitly with the --enable-named-pipe option.

If you run a single server, it's typical to let the server use its default network settings. If you run more than one server, it's necessary to make sure each one uses unique networking parameters. See the "Running Multiple Servers" section later in this chapter for more information.

The preceding discussion applies only to standalone servers that operate in a client/server environment. It does not apply to the embedded server, which communicates with the client program that it's linked into by means of an internal channel and does not listen to any external network interfaces at all.

Enabling or Disabling LOCAL Capability for LOAD DATA

As of MySQL 3.23.49, the LOCAL capability for the LOAD DATA statement can be controlled at build time and at runtime:

• At build time, LOCAL can be enabled or disabled by default by using the --enable-local-infile or --disable-local-infile option when you run configure.

• At runtime, the server can be started with the --local-infile or --disable-local-infile options to enable or disable LOCAL capability on the server side. (Prior to MySQL 4.0.2, disable it with --local-infile=0.)

If LOCAL is disabled in the server, clients cannot use this capability at all. If it is enabled, the client library may still have LOCAL disabled by default on the client side, but certain programs may allow it to be enabled on demand. For example, mysql supports a --local-infile option to allow LOCAL.

Internationalization and Localization Issues

Internationalization refers to the ability of software to be used according to local convention for any of a variety of locations. Localization refers to selecting a particular set of local conventions from among those sets that are supported. The following aspects of MySQL configuration relate to internationalization and localization:

• The server time zone

• The language used for displaying diagnostic and error messages

• The available character sets and the default character set

Selecting the Server Time Zone

If your server doesn't determine the local time zone properly, it will report times incorrectly (in GMT, for example). To correct this on UNIX, you can set the zone explicitly. But note that you indicate the time zone to the safe_mysqld or mysqld_safe startup script, not to the mysqld server itself.

To specify a time zone, use the --timezone option. It's probably best to specify this option in an option file, especially if you invoke safe_mysqld or mysqld_safe through mysql.server, which does not support command line options. For example, to specify the U.S. Central time zone for mysqld_safe, add the following to your option file:

 [mysqld_safe]  timezone=US/Central 

The example shows one widely used syntax (it works on Solaris, Linux, or Mac OS X, for example). Another common syntax is as follows:

 [mysqld_safe]  timezone=CST6CDT 

Use whatever syntax is appropriate for your system.

Prior to MySQL 4, mysqld_safe is called safe_mysqld, which also supports --timezone back to version 3.23.28. The command-line syntax is the same as for mysqld_safe, but if you use an option file, put the time zone setting in the [safe_mysqld] group. Prior to MySQL 3.23.28, mysqld_safe has no --timezone option, so unfortunately it's necessary to modify safe_mysqld itself. Do so by inserting a couple of lines that set the TZ environment variable somewhere prior to the line that starts the server. For example, add lines that look like this:

 TZ=U.S./Central  export TZ 

or like this:

 TZ=CST6CDT  export TZ 

Selecting the Error Message Language

The server has the ability to produce diagnostic and error messages in any of several languages. The default is english, but you can specify others. To see which are available, look under the share/mysql directory of your MySQL installation. The directories that have language names correspond to the available languages. To change the message language, use the --language startup option with an argument of either the language name or the pathname to the language directory. For example, to use French if your installation is located under /usr/local/mysql, you might use either --language=french or --language=/usr/local/mysql/share/mysql/french.

Configuring Character Set Support

MySQL can support any of a number of character sets. The choice of character set obviously affects which characters are allowed in string values, but it also affects operational characteristics such as the sort order used in string comparisons and the characters that are legal in table and column names. This section describes how to configure the MySQL's character set support. For information on using character sets from the client perspective, see Chapter 2, "Working with Data in MySQL."

To find out which character sets are available to your server as it is currently configured, look under the MySQL installation directory, for example, in the share/mysql/charsets directory. The Index file there lists which sets you can use. You can also find out the names by issuing the following query:

 mysql> SHOW VARIABLES LIKE 'character_sets';  

Or, as of MySQL 4.1, you can issue a SHOW CHARACTER SET statement to get the list of character set names and some additional information about each set.

To specify the default character set and the sets that are available to choose from, you can configure the server at build time using options to the configure script:

• The default character set is latin1. To select a different default, use the --with-charset option.

• To specify which character sets to include support for, use the --with-extra-charsets option. The argument to this option is a comma-separated list of character set names. For example, you can include support for the latin1, big5, and hebrew character sets as follows:

   % ./configure --with-extra-charsets=latin1,big5,hebrew  

Two special character set names can be used with the --with-extra-charsets option to select groups of character sets all includes all available character sets, and complex includes all complex character sets. A set is complex if it is either a multi-byte character set or if it requires special rules for sorting.

At runtime, the server uses its default built-in character set unless you specify otherwise. To select a different set, use the --default-character-set option when you start the server.

Although the server can use different character sets, it supports only a single set at a time prior to MySQL 4.1. As of 4.1, the configuration-time and runtime options for controlling which sets are available or used by default are the same as before, but support also is available at the SQL level for on-the-fly selection of character sets at the server, database, table, column, and string constant level. In other words, the server can support multiple character sets simultaneously. The availability of improved character set support makes it more likely that your users will want to use alternate character sets, so it's also more likely that you'll need to consider building in support for a larger number of sets. (For example, the availability of Unicode support is something for which many users have been waiting, so you may want to enable it when you build the server.)

Prior to MySQL 4.1, if you change your server's default character set after you've already created tables, the order in which key values are stored in the indexes may need to be updated to be correct for sort order of the new character set. To fix this for MyISAM tables, reorder the indexes by using myisamchk with the --recover and --quick options, together with a --set-character-set option that specifies the character set to use. To do this, the server must be down when you run myisamchk. You can also leave the server running and reorder indexes with a REPAIR TABLE ... QUICK statement or a mysqlcheck--repair--quick command. Another option, which is not specific to MyISAM tables, is to dump the tables, drop them, and reload them. As of MySQL 4.1, the improved character set support makes index rebuilding no longer necessary when you change sets. However, you should update older tables to 4.1 format to take advantage of this capability, as described in the next section, "Converting Older Tables to Enable MySQL 4.1 Character Set Support."

On the client side, you can specify the character set that you want a client program to use by giving the --default-character-set option. If the character set you want isn't available as part of your MySQL installation, but you do have the necessary character set files installed under another directory, you can use them by specifying the --character-sets-dir option to the client program.

Converting Older Tables to Enable MySQL 4.1 Character Set Support

When upgrading from a version of MySQL older than 4.1 to version 4.1 or later, the best thing to do is convert your tables to 4.1 format so that you can make full use of the improved character set support:

1. Make a backup of your databases using mysqldump:

    % mysqldump -p -u root --all-databases --opt > dumpfile.sql  

   --all-databases causes all databases to be dumped, and --opt optimizes the dump file to be smaller so that it can be processed more quickly when reloaded. (mysqldump is discussed further in Chapter 13.)

2. Bring down the server.

3. Upgrade your MySQL installation and restart the server, but do not change the server's default character set.

4. Convert your tables to 4.1 format by reloading them from the backup file:

    % mysql -p -u root < dumpfile.sql  

This procedure allows the server to install new character set support information into the tables, which has two important effects:

• Each column is assigned the server's character set as its own. This means you can change the server character set later and each column will retain its character set, unaffected by the change. (That is, the column becomes "insulated" from modifications to the server character set, so indexes on the column don't go out of whack.)

• If you subsequently modify the column's character set, the server will automatically reorder any indexes of which it is a part to reflect the collating sequence of the new character set.

It's also possible to upgrade and then convert your tables after upgrading the server by using ALTER TABLE, but the process is laborious, tedious, and error-prone. Suppose a table is defined like this:

 CREATE TABLE t  (     c1 CHAR(10),     c2 CHAR(10),     c3 CHAR(10) ); 

To convert the columns to have explicit character set information, use the following statement:

 ALTER TABLE t      MODIFY c1 CHAR(10) CHARACTER SET latin1,     MODIFY c2 CHAR(10) CHARACTER SET latin1,     MODIFY c3 CHAR(10) CHARACTER SET latin1; 

That's a lot of work, especially because it must be done for each table. It's easier to use the dump-and-reload method.

Selecting Table Handlers

MySQL supports multiple table handlers. Some of these can be built-in or omitted at configuration time, and some of those that are built in can be disabled at server startup time:

• Up until MySQL 4, the ISAM handler is always built-in. As of MySQL 4, the ISAM handler can be omitted with the --without-isam option to configure.

  For the embedded server, the ISAM handler is omitted by default. To include it, you must edit the mysql_embed.h file in the source distribution and rebuild the server. But, in general, it's better to convert ISAM tables to MyISAM tables and avoid continued reliance on the ISAM storage format. ISAM support will be phased out in the future.

• The BDB handler can be built-in with the --with-berkeley-db option to configure. If built in, it can be disabled at server startup time with the --skip-bdb option.

• Up until MySQL 4, the InnoDB handler can be built-in with the --with-innodb option to configure. As of MySQL 4, InnoDB is built-in by default but can be omitted with the --without-innodb option to configure. If built-in, the InnoDB handler can be disabled at server startup time with the --skip-innodb option.

• The MyISAM handler is always built-in as of MySQL 3.23, and can be neither omitted at configuration time nor disabled at server startup time.

Configuring the InnoDB Tablespace

The InnoDB table handler does not use separate files for each table the way that other table handlers do. Instead, it manages all InnoDB tables within a single tablespace, which is a logically unified block of storage that the handler treats as a giant data structure. (In a sense, the tablespace is something like a virtual file system.) The only file uniquely associated with an individual InnoDB table is the .frm description file that is stored in the database directory of the database that the table belongs to.

The InnoDB 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 InnoDB tablespace. It's possible to specify these options on the server command line, but this is rarely done in practice. 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 same configuration gets used consistently each time the server starts up. Two options are the most important:

• innodb_data_home_dir specifies the parent directory of all the component files that make up the tablespace. If you don't specify this option, its default value is the data directory.

• innodb_data_file_path indicates the specifications for the component files of the tablespace under the InnoDB home directory. The value of this option is a list of one or more file specifications, separated by semicolons. Each specification consists of a filename, a size, and possibly other options, separated by colons. The combined size of the tablespace components must be at least 10MB.

In MySQL 3.23, you must provide a value for innodb_data_file_path or the InnoDB handler will not start up properly. (One consequence of this is that the server will not start up, either. You can see if startup failure is InnoDB-related by checking the error log.) In MySQL 4, the server will create a default tablespace consisting of a single file named ibdata1. This tablespace is a 64MB non-auto-extending file in MySQL 4.0.0 and 4.0.1, and a 10MB auto-extending file thereafter.

As a simple example, suppose you want to create a tablespace consisting of two 10MB files named innodata1 and innodata2 in the data directory. Configure the files as follows:

 innodb_data_file_path = innodata1:10M;innodata2:10M  

No innodb_data_home_dir setting is required in this case because its default value is the server's data directory, the desired location for the files.

The following rules describe how the InnoDB handler combines the values of innodb_data_home_dir and innodb_data_file_path to determine the pathnames of the tablespace files:

• If innodb_data_home_dir is empty, all the file specifications in innodb_data_file_path are treated as absolute pathnames.

• If innodb_data_home_dir is not empty, it should name the directory under which all the file specifications in innodb_data_file_path should be found. In this case, those filenames are interpreted relative to the innodb_data_home_dir value.

• If innodb_data_home_dir is not specified, its default value is the pathname to the MySQL data directory, and the filenames in innodb_data_file_path are interpreted relative to the data directory.

Based on the preceding rules, if the data directory is /var/mysql/data, the following three configurations all specify the same set of tablespace files:

 innodb_data_home_dir=  innodb_data_file_path=/var/mysql/data/ibdata1:10M;/var/mysql/data/ibdata2:10M innodb_data_home_dir=/var/mysql/data innodb_data_file_path=ibdata1:10M;ibdata2:10M innodb_data_file_path=ibdata1:10M;ibdata2:10M 

The innodb_data_file_path value consists of file specifications that are separated by semicolons, and 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. The second format specifies an auto-extending file; if the file fills up, InnoDB extends it by 8MB at a time. The third format is similar, but includes a value indicating the maximum size to which the auto-extending file is allowed to grow. Auto-extending tablespace components can be used as of MySQL 3.23.50, but only the final component of the tablespace can be listed as auto-extending.

To set up the tablespace initially, add the appropriate lines to the option file (making sure that none of the component files already exist) and then start the server. InnoDB will notice that the files do not exist and will proceed to create and initialize them.

As of MySQL 3.23.41, it is possible to use raw partitions as components of the InnoDB tablespace. One reason to do this is that you can easily create very large tablespaces. A partition component can span the entire extent of the partition, whereas regular file components are limited in size to the maximum file size allowed by your operating system. In addition, raw partition files are guaranteed to be composed of entirely contiguous space on disk, whereas regular files are subject to file system fragmentation. When it initializes the tablespace, InnoDB tries to minimize fragmentation of regular files by writing enough zeros to the files to force space for them to be allocated all at once rather than incrementally. But this can only reduce fragmentation; it cannot guarantee that it will not occur.

Including a raw partition in the tablespace is a two-step procedure. Suppose you want to use a 2GB partition 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. Configure the partition as follows:

1. Configure the partition initially with a size value that has a newraw suffix. This indicates that the file is a raw partition that needs to be initialized:

    innodb_data_home_dir =  innodb_data_file_path = /dev/rdsk8:2Gnewraw 

   After adding these lines to your [mysqld] option group, start the server. InnoDB will see the suffix and initialize the partition. (It will also treat the tablespace as read-only because it knows that you have not completed the second step.) After the partition has been initialized, shut down the server.

2. Modify the configuration information to change the suffix from newraw to raw:

    innodb_data_home_dir =  innodb_data_file_path = /dev/rdsk8:2Graw 

   Then start the server again. InnoDB will see that new is not present, so it knows that the partition has been initialized and that it can use the tablespace in read/write fashion.

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 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:

 innodb_data_home_dir =  innodb_data_file_path = C:/ibdata1:50M;D:/ibdata2:60M 

Each time InnoDB starts up, it creates the tablespace data files if necessary. It also creates log files if they do not exist. By default, these logs are created in the data directory and have names that begin with ib_. Note that InnoDB will create only files, not directories. Any directories that will be needed by InnoDB must be created prior to starting the server. (You can indicate where to create the log files using the options described earlier in this chapter in the "Maintaining Log Files" section.)

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.

Once a tablespace has been initialized, you cannot change the size of its component files. However, you can add another file to the list of existing files, which may be helpful if the tablespace fills up. A symptom of a full tablespace is that InnoDB transactions that should succeed will begin rolling back. You can also check the free space explicitly with the following statement, where tbl_name is the name of any InnoDB table:

 mysql> SHOW TABLE STATUS LIKE 'tbl_name';  

To make the tablespace larger by adding another component, use the following procedure:

1. Shut down the server if it is running.

2. If the final component of the tablespace is an auto-extending file, you must change its specification to that of a fixed-size file before adding another file after it. To do this, determine the current actual size of the file. Then round the size down to the nearest multiple of 1 megabyte (measured as 1,048,576 bytes rather than as 1,000,000 bytes) and use that size in the file's specification. Suppose you have a file currently listed like this:

    innodb_data_file_path = ibdata1:100M:autoextend  

   If the file's actual size now is 121,634,816 bytes, that is 121,634,816 / 1,048,576 = 116 megabytes. Change the specification as follows:

    innodb_data_file_path = ibdata1:116M  

3. Add the specification for the new component to the end of the current file list. If the new component is a regular file, make sure that it does not already exist. If the component is a raw partition, add it using the two-step procedure described earlier for specifying a partition as part of the tablespace.

4. Restart the server.

If you want to reconfigure the tablespace in some way other than adding a new file to the end, you must dump and reconstruct it using the new configuration:

1. Use mysqldump to dump all your InnoDB tables.

2. Shut down the server, and delete your existing InnoDB tablespace and log files and the .frm files that correspond to InnoDB tables.

3. Reinitialize the tablespace according to the new configuration you want to use.

4. Reload the dump file into the server to re-create the InnoDB tables.

Tuning the Server

The MySQL server has several parameters (variables) that affect how it operates. If the default parameter values are not appropriate, you can change them to values that are better for the environment in which your server runs. For example, if you have plenty of memory, you can tell the server to use larger buffers for disk and index operations. This will hold more information in memory and decrease the number of disk accesses that need to be made. If your system is more modest, you can tell the server to use smaller buffers. This will likely make the server run more slowly, but may improve overall system performance by preventing the server from hogging system resources to the detriment of other processes.

The following sections discuss how to set or examine server variables and describe some of the variables that have application to the operation of the server as a whole or more specifically to the InnoDB table handler. A complete list of server variables is given in Appendix D under the description for the SHOW VARIABLES statement. You can also find additional discussion of server tuning in the optimization chapter of the MySQL Reference Manual.

Setting and Checking Server Variable Values

Server variables can be set at server startup time. Also, as of MySQL 4.0.3, many of these variables can be modified dynamically while the server is running.

The allowable syntax for setting a server variable at startup time depends on your version of MySQL. As of MySQL 4.0.2, you can treat a variable name as an option name and set it directly. For example, the size of the table cache is controlled by the table_cache variable. To set the table cache size to 128, you can do so using the following option on the command line:

 --table_cache=128  

You can also set the variable in an option file using the following syntax:

 [mysqld]  table_cache=128 

Another feature of the variable-as-option syntax is that underscores can be given as dashes so that the option looks more like other options:

 --table-cache=128  

You can also set the variable in an option file using the following syntax:

 [mysqld]  table-cache=128 

The other way to set a variable is by using the --set-variable or -O option, which can be used on the command line like this:

 --set-variable=table_cache=128  -O table_cache=128 

In option files, only the long-option form is allowable:

 [mysqld]  set-variable=table_cache=128 

If you need to set several variables, use one option for each.

Prior to MySQL 4.0.2, variable names cannot be treated as options, so only the --set-variable or -O option formats can be used. From 4.0.2 on, --set-variable and -O are still supported, but are deprecated.

Whichever syntax you use to set variables, it's usually easier to do so in an option file because you don't have to remember to set the variables each time you start the server.

Server variables can be set only at startup time prior to MySQL 4.0.3, and their values remain fixed for the duration of the server process. MySQL 4.0.3 introduces two changes with respect to server variable handling:

• Many variables can be set dynamically while the server is running. This gives you better control over its operation and can help you avoid bringing down the server under circumstances when that might otherwise be necessary. (For example, you can experiment with buffer sizes to see how that affects server performance without having to stop and restart the server for each change.) Changes made this way do not last beyond server exit time, but should you determine a value for a variable that is better than its current default, you can set the variable in an option file to cause the value to be used whenever the server starts in the future.

• Variables can exist at two levels global and session-specific. Global variables affect the operation of the server as a whole. Session-level variables affect only the behavior of a given client connection. For variables that exist at both levels, the global values are used to initialize the corresponding session variables. This happens only when a new connection begins; changing a global variable during a connection does not affect the current value of the connection's corresponding session variable.

To set a global variable named var_name, use a SET statement having one of the following formats:

 SET GLOBAL var_name = value;  SET @@GLOBAL.var_name = value; 

To set a session variable, similar formats apply:

 SET SESSION var_name = value;  SET @@SESSION.var_name = value; 

If no level indicator is present at all, the SET statement modifies the session level variable:

 SET var_name = value;  SET @@var_name = value; 

You can set several variables in a single SET statement by separating the assignments with commas:

 SET SESSION sql_warnings = 0, GLOBAL table_type = InnoDB;  

In all cases in which SESSION is allowed, you can substitute LOCAL as a synonym (this includes use of @@LOCAL for @@SESSION).

You must have the SUPER privilege to set a global variable. The setting persists until changed again or the server exits. No special privileges are needed to set a session variable. The setting persists until changed again or the current connection terminates.

To see the current values of server variables, use a SHOW VARIABLES statement. This statement allows you to display all variables or just those with names that match a given SQL pattern:

 SHOW VARIABLES;  SHOW VARIABLES LIKE 'pat'; 

As of 4.0.3, additional formats are allowed so that you can specifically request the values of global or session variables:

 SHOW GLOBAL VARIABLES;  SHOW GLOBAL VARIABLES LIKE 'pat'; SHOW SESSION VARIABLES; SHOW SESSION VARIABLES LIKE 'pat'; 

When no GLOBAL or SESSION keyword is used, the statement returns a variable's session value, if one exists at that level, and the global value if not.

From the command line, mysqladmin variables displays the current values of the server's global variables.

The entry for SHOW VARIABLES in Appendix D lists server variables, including which of them can be modified dynamically and at which levels.

General Purpose Server Variables

Several of the variables that are most likely to be useful for general performance tuning are described in the following list.

• back_log

  The number of incoming client connection requests that can be queued while processing requests from the current clients. If you have a very busy site, you may want to increase the value of this variable.

• delayed_queue_size

  This variable determines the number of rows from INSERT DELAYED statements that can be queued before clients performing additional INSERT DELAYED statements get blocked. If you have many clients that perform this kind of INSERT and you find that they are blocking, increasing this variable will allow more of them to continue more quickly. (INSERT DELAYED is discussed in the "Scheduling and Locking Issues" section of Chapter 4, "Query Optimization.")

• flush_time

  If your system has problems and tends to lock up or reboot often, setting this variable to a non-zero value causes the server to flush the table cache every flush_time seconds. Writing out table changes in this way degrades performance, but can reduce the chance of table corruption or data loss.

  You can start the server with the --flush option on the command line to force table changes to be flushed after every update.

• key_buffer_size

  The size of the buffer used to hold index blocks. Index-based retrievals and sorts are faster if you increase the value of this variable, as are operations that create or modify indexes. A larger key buffer makes it more likely that MySQL will find key values in memory, which reduces the number of disk accesses needed for index processing.

  This variable is called key_buffer in versions of MySQL prior to 3.23. The server recognizes both names as of 3.23.

• max_allowed_packet

  The maximum size to which the buffer used for client communications can grow. The largest value to which this variable can be set is 16MB prior to MySQL 4 and 1GB for MySQL 4 and later.

  If you have clients that send large BLOB or TEXT values, this server variable may need to be increased, and you'll also need to increase it on the client end. Clients currently use a default buffer size of 16MB. For example, to invoke mysql with a 64MB packet limit, do so as follows:

   % mysql --set-variable=max_allowed_packet=64M  

• max_connections

  The maximum number of simultaneous client connections the server will allow. If your server is busy, you may need to increase this value. For example, if your MySQL server is used by an active Web server to process lots of queries generated by DBI or PHP scripts, visitors to your site may find requests being refused if this variable is set too low.

• table_cache

  The size of the table cache. Increasing this value allows mysqld to keep more tables open simultaneously, and reduces the number of file open-and-close operations that must be done.

If you increase the values of max_connections or table_cache, the server will require a larger number of file descriptors. That may cause problems with operating system limits on the per-process number of file descriptors, in which case you'll need to increase the limit or work around it. Procedures vary for increasing the limit on the number of file descriptors. You may be able to do this at runtime using the --open-files-limit option to mysqld_safe, if you use that script to start up the server. Otherwise, you may need to reconfigure your system. Some systems can be configured simply by editing a system description file and rebooting. For others, you must edit a kernel description file and rebuild the kernel. Consult the documentation for your system to see how to proceed.

One way to work around per-process file descriptor limits is to split your data directory into multiple data directories and run multiple servers. This effectively multiplies the number of file descriptors available by the number of servers you run. On the other hand, other complications can cause you problems. To name two, you cannot access databases in different data directories from a single server, and you might need to replicate privileges in the grant tables across different servers for users that need access to more than one server.

Some variables pertain to resources that are allocated to each client, and increasing them has the potential to dramatically increase the server's resource requirements if you have many simultaneous clients. For example, two values that administrators sometimes increase in hopes of improving performance are those of the read_buffer_size and sort_buffer_size variables. (Prior to MySQL 4.0.3, these variables are called record_buffer and sort_buffer.) The values of these variables determine the size of the buffers that are used during join and sort operations. However, these buffers are allocated for each connection, so if you make the values of the corresponding variables quite large, performance may actually suffer due to exorbitant system resource consumption. Be cautious about changing the sizes of per-connection buffers. Increase them incrementally and then test your changes rather than bumping them up by a large amount all at once. This will allow you to assess the effect of each change with less likelihood of serious performance degradation. Be sure to use realistic test conditions as well. These buffers are allocated only as needed rather than as soon as a client connects. (For example, a client that runs no joins needs no join buffer.) Your test conditions should use clients that connect at the same time and run complex queries so that you can see the real effect on the server's memory requirements.

InnoDB Handler Variables

In addition to the general-purpose server variables, the server has several InnoDB-related variables when InnoDB support is enabled. The following list describes a few that commonly are used to affect the operation of the InnoDB handler.

• innodb_buffer_pool_size

  If you have the memory, making the InnoDB buffer pool larger can reduce disk usage for accessing table data and indexes.

• innodb_log_buffer_size

  Increasing the size of this buffer allows larger transactions to be buffered through to commit time without having to flush to disk partway through.

• innodb_log_file_size, innodb_log_files_in_group

  When its logs fill up, InnoDB checkpoints the buffer pool by flushing it to disk. Using larger InnoDB log files reduces the frequency with which the logs fill up, and thus reduces the number of times this flushing occurs. (The tradeoff is that with larger logs, the time for recovery after a crash will increase.) You can modify innodb_log_file_size to change the size of the log files or innodb_log_files_in_group to change the number of files. The important characteristic is the total size of the logs, which is the product of the two values. Note that the total size of the logs must not exceed 4GB.