Advanced MySQL Database Administration

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss running multiple MySQL servers on the same machine, and the MySQL Query cache. The final installment of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator’s Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

4.9 Running Multiple MySQL Servers on the Same Machine

In some cases, you might want to run multiple mysqld servers on the same machine. You might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you may want to give different users access to different mysqld servers that they manage themselves. (For example, you might be an Internet Service Provider that wants to provide independent MySQL installations for different customers.)

To run multiple servers on a single machine, each server must have unique values for several operating parameters. These can be set on the command line or in option files. See Section 3.3, “Specifying Program Options.”

At least the following options must be different for each server:

  • --port=port_num

    --port controls the port number for TCP/IP connections.

  • --socket=path

    --socket controls the Unix socket file path on Unix and the name of the named pipe on Windows. On Windows, it’s necessary to specify distinct pipe names only for those servers that support named pipe connections.

  • --shared-memory-base-name=name

    This option currently is used only on Windows. It designates the shared memory name used by a Windows server to allow clients to connect via shared memory. This option is new in MySQL 4.1.

  • --pid-file=path

    This option is used only on Unix. It indicates the name of the file in which the server writes its process ID.

If you use the following log file options, they must be different for each server:

  • --log=path

  • --log-bin=path

  • --log-update=path

  • --log-error=path

  • --log-isam=path

  • --bdb-logdir=path

Log file options are described in Section 4.8.6, “Log File Maintenance.”

If you want more performance, you can also specify the following options differently for each server, to spread the load between several physical disks:

  • --tmpdir=path

  • --bdb-tmpdir=path

Having different temporary directories is also recommended, to make it easier to determine which MySQL server created any given temporary file.

Generally, each server should also use a different data directory, which is specified using the --datadir=path option.

Warning: Normally you should never have two servers that update data in the same databases! This may lead to unpleasant surprises if your operating system doesn’t support fault-free system locking! If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers will try to log to the same files.

This warning against sharing a data directory among servers also applies in an NFS environment. Allowing multiple MySQL servers to access a common data directory over NFS is a bad idea!

  • The primary problem is that NFS will become the speed bottleneck. It is not meant for such use.

  • Another risk with NFS is that you will have to come up with a way to make sure that two or more servers do not interfere with each other. Usually NFS file locking is handled by the lockd daemon, but at the moment there is no platform that will perform locking 100% reliably in every situation.

Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer that contains several CPUs and use an operating system that handles threads efficiently.

If you have multiple MySQL installations in different locations, normally you can specify the base installation directory for each server with the --basedir=path option to cause each server to use a different data directory, log files, and PID file. (The defaults for all these values are determined relative to the base directory.) In that case, the only other options you need to specify are the --socket and --port options. For example, suppose that you install different versions of MySQL using tar file binary distributions. These will install in different locations, so you can start the server for each installation using the command bin/mysqld_safe under its corresponding base directory. mysqld_safe will determine the proper --basedir option to pass to mysqld, and you need specify only the --socket and --port options to mysqld_safe. (For versions of MySQL older than 4.0, use safe_mysqld rather than mysqld_safe.)

As discussed in the following sections, it is possible to start additional servers by setting environment variables or by specifying appropriate command-line options. However, if you need to run multiple servers on a more permanent basis, it will be more convenient to use option files to specify for each server those option values that must be unique to it.

4.9.1 Running Multiple Servers on Windows

You can run multiple servers on Windows by starting them manually from the command line, each with appropriate operating parameters. On Windows NT-based systems, you also have the option of installing several servers as Windows services and running them that way. General instructions for running MySQL servers from the command line or as services are given in Section 2.2.1, “Installing MySQL on Windows.” This section describes how to make sure that you start each server with different values for those startup options that must be unique per server, such as the data directory. These options are described in Section 4.9, “Running Multiple MySQL Servers on the Same Machine.”

4.9.1.1 Starting Multiple Windows Servers at the Command Line

To start multiple servers manually from the command line, you can specify the appropriate options on the command line or in an option file. It’s more convenient to place the options in an option file, but it’s necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the filename with a --defaults-file option when you run it.

Suppose that you want to run mysqld on port 3307 with a data directory of C:mydata1, and mysqld-max on port 3308 with a data directory of C:mydata2. (To do this, make sure that before you start the servers, each data directory exists and has its own copy of the mysql database that contains the grant tables.)

Then create two option files. For example, create one file named C:my-opts1.cnf that looks like this:

[mysqld]
datadir = C:/mydata1
port = 3307

Create a second file named C:my-opts2.cnf that looks like this:

[mysqld]
datadir = C:/mydata2
port = 3308

Then start each server with its own option file:

C:> C:mysqlbinmysqld
--defaults-file=C:my-opts1.cnf
C:> C:mysqlbinmysqld-max
--defaults-file=C:my-opts2.cnf

On NT, each server will start in the foreground (no new prompt appears until the server exits later); you’ll need to issue those two commands in separate console windows.

To shut down the servers, you must connect to the appropriate port number:

C:> C:mysqlbinmysqladmin --port=3307 shutdown
C:> C:mysqlbinmysqladmin --port=3308 shutdown

Servers configured as just described will allow clients to connect over TCP/IP. If your version of Windows supports named pipes and you also want to allow named pipe connections, use the mysqld-nt or mysqld-max-nt servers and specify options that enable the named pipe and specify its name. Each server that supports named pipe connections must use a unique pipe name. For example, the C:my-opts1.cnf file might be written like this:

[mysqld]
datadir = C:/mydata1
port = 3307
enable-named-pipe
socket = mypipe1

Then start the server this way:

C:> C:mysqlbinmysqld-nt
--defaults-file=C:my-opts1.cnf

Modify C:my-opts2.cnf similarly for use by the second server.

{mospagebreak title=4.9.1.2 Starting Multiple Windows Servers as Services}

On NT-based systems, a MySQL server can be run as a Windows service. The procedures for installing, controlling, and removing a single MySQL service are described in Section 2.2.1.7, “Starting MySQL as a Windows Service.”

As of MySQL 4.0.2, you can install multiple servers as services. In this case, you must make sure that each server uses a different service name in addition to all the other parameters that must be unique per server.

For the following instructions, assume that you want to run the mysqld-nt server from two different versions of MySQL that are installed at C:mysql-4.0.8 and C:mysql-4.0.17, respectively. (This might be the case if you’re running 4.0.8 as your production server, but want to test 4.0.17 before upgrading to it.)

The following principles apply when installing a MySQL service with the --install or --install-manual option:

  • If you specify no service name, the server uses the default service name of MySQL and the server reads options from the [mysqld] group in the standard option files.

  • If you specify a service name after the --install option, the server ignores the [mysqld] option group and instead reads options from the group that has the same name as the service. The server reads options from the standard option files.

  • If you specify a --defaults-file option after the service name, the server ignores the standard option files and reads options only from the [mysqld] group of the named file.

Note: Before MySQL 4.0.17, only a server installed using the default service name (MySQL) or one installed explicitly with a service name of mysqld will read the [mysqld] group in the standard option files. As of 4.0.17, all servers read the [mysqld] group if they read the standard option files, even if they are installed using another service name. This allows you to use the [mysqld] group for options that should be used by all MySQL services, and an option group named after each service for use by the server installed with that service name.

Based on the preceding information, you have several ways to set up multiple services. The following instructions describe some examples. Before trying any of them, be sure that you shut down and remove any existing MySQL services first.

  • Approach 1: Specify the options for all services in one of the standard option files. To do this, use a different service name for each server. Suppose that you want to run the 4.0.8 mysqld-nt using the service name of mysqld1 and the 4.0.17 mysqld-nt using the service name mysqld2. In this case, you can use the [mysqld1] group for 4.0.8 and the [mysqld2] group for 4.0.17. For example, you can set up C:my.cnf like this:

    # options for mysqld1 service
    [mysqld1]
    basedir = C:/mysql-4.0.8
    port = 3307
    enable-named-pipe
    socket = mypipe1
    
    # options for mysqld2 service
    [mysqld2]
    basedir = C:/mysql-4.0.17
    port = 3308
    enable-named-pipe
    socket = mypipe2
  • Install the services as follows, using the full server pathnames to ensure that Windows registers the correct executable program for each service:

    C:> C:mysql-4.0.8binmysqld-nt
    --install mysqld1
    C:> C:mysql-4.0.17binmysqld-nt
    --install mysqld2
  • To start the services, use the services manager, or use NET START with the appropriate service names:

    C:> NET START mysqld1
    C:> NET START mysqld2
  • To stop the services, use the services manager, or use NET STOP with the appropriate service names:

    C:> NET STOP mysqld1
    C:> NET STOP mysqld2
  • Approach 2: Specify options for each server in separate files and use --defaults-file when you install the services to tell each server what file to use. In this case, each file should list options using a [mysqld] group.

    With this approach, to specify options for the 4.0.8 mysqld-nt, create a file C:my-opts1.cnf that looks like this:

    [mysqld]
    basedir = C:/mysql-4.0.8
    port = 3307
    enable-named-pipe
    socket = mypipe1
  • For the 4.0.17 mysqld-nt, create a file C:my-opts2.cnf that looks like this:

    [mysqld]
    basedir = C:/mysql-4.0.17
    port = 3308
    enable-named-pipe
    socket = mypipe2
  • Install the services as follows (enter each command on a single line):

    C:> C:mysql-4.0.8binmysqld-nt --install
    mysqld1
    --defaults-file=C:my-opts1.cnf C:> C:mysql-4.0.17binmysqld-nt --install
    mysqld2
    --defaults-file=C:my-opts2.cnf
  • To use a --defaults-file option when you install a MySQL server as a service, you must precede the option with the service name.

    After installing the services, start and stop them the same way as in the preceding example.

To remove multiple services, use mysqld --remove for each one, specifying a service name following the --remove option. If the service name is the default (MySQL), you can omit it.

{mospagebreak title=4.9.2 Running Multiple Servers on Unix}

The easiest way to run multiple servers on Unix is to compile them with different TCP/IP ports and Unix socket files so that each one is listening on different network interfaces. Also, by compiling in different base directories for each installation, that automatically results in different compiled-in data directory, log file, and PID file locations for each of your servers.

Assume that an existing server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new server to have different operating parameters, use a configure command something like this:

shell> ./configure --with-tcp-port=port_number 
--with-unix-socket-path=file_name
--prefix=/usr/local/mysql-4.0.17

Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file pathname, and the --prefix value should specify an installation directory different than the one under which the existing MySQL installation is located.

If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket filename:

shell> mysqladmin --host=host_name
--port=port_number variables

With the information displayed by that command, you can tell what option values not to use when configuring an additional server.

Note that if you specify localhost as a hostname, mysqladmin will default to using a Unix socket file connection rather than TCP/IP. In MySQL 4.1, you can explicitly specify the connection protocol to use by using the --protocol={TCP | SOCKET | PIPE | MEMORY} option.

You don’t have to compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to specify those values at runtime. One way to do so is by using command-line options:

shell> mysqld_safe --socket=file_name
--port=port_number

To start a second server, provide different --socket and --port option values, and pass a --datadir=path option to mysqld_safe so that the server uses a different data directory.

Another way to achieve a similar effect is to use environment variables to set the Unix socket filename and TCP/IP port number:

shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> mysql_install_db --user=mysql
shell> mysqld_safe --datadir=/path/to/datadir &

This is a quick way of starting a second server to use for testing. The nice thing about this method is that the environment variable settings will apply to any client programs that you invoke from the same shell. Thus, connections for those clients automatically will be directed to the second server!

Appendix B, “Environment Variables,” includes a list of other environment variables you can use to affect mysqld.

For automatic server execution, your startup script that is executed at boot time should execute the following command once for each server with an appropriate option file path for each command:

mysqld_safe --defaults-file=path

Each option file should contain option values specific to a given server.

On Unix, the mysqld_multi script is another way to start multiple servers. See Section 4.1.5, “The mysqld_multi Program for Managing Multiple MySQL Servers.”

4.9.3 Using Client Programs in a Multiple-Server Environment

When you want to connect with a client program to a MySQL server that is listening to different network interfaces than those compiled into your client, you can use one of the following methods:

  • Start the client with --host=host_name --port=port_number to connect via TCP/IP to a remote server, with --host=127.0.0.1 --port=port_number to connect via TCP/IP to a local server, or with --host=localhost --socket=file_name to connect to a local server via a Unix socket file or a Windows named pipe.

  • As of MySQL 4.1, start the client with --protocol=tcp to connect via TCP/IP, --protocol=socket to connect via a Unix socket file, --protocol=pipe to connect via a named pipe, or --protocol=memory to connect via shared memory. For TCP/IP connections, you may also need to specify --host and --port options. For the other types of connections, you may need to specify a --socket option to specify a Unix socket file or named pipe name, or a --shared-memory-base-name option to specify the shared memory name. Shared memory connections are supported only on Windows.

  • On Unix, set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the Unix socket file and TCP/IP port number before you start your clients. If you normally use a specific socket file or port number, you can place commands to set these environment variables in your .login file so that they apply each time you log in. See Appendix B, “Environment Variables.”

  • Specify the default Unix socket file and TCP/IP port number in the [client] group of an option file. For example, you can use C:my.cnf on Windows, or the .my.cnf file in your home directory on Unix. See Section 3.3.2, “Using Option Files.”

  • In a C program, you can specify the socket file or port number arguments in the mysql_real_connect() call. You can also have the program read option files by calling mysql_options().

  • If you are using the Perl DBD::mysql module, you can read options from MySQL option files. For example:

    $dsn = "DBI:mysql:
    test;mysql_read_default_group=client;" . "mysql_read_default_file=/usr/local/
    mysql/data/my.cnf"; $dbh = DBI->connect($dsn, $user, $password);
  • Other programming interfaces may provide similar capabilities for reading option files.

{mospagebreak title=4.10 The MySQL Query Cache}

From version 4.0.1 on, MySQL Server features a query cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If the identical query is received later, the server retrieves the results from the query cache rather than parsing and executing the query again.

The query cache is extremely useful in an environment where (some) tables don’t change very often and you have a lot of identical queries. This is a typical situation for many Web servers that generate a lot of dynamic pages based on database content.

Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

Note: The query cache does not work in an environment where you have many mysqld servers updating the same MyISAM tables.

Some performance data for the query cache follow. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2 x 500MHz system with 2GB RAM and a 64MB query cache.

  • If all the queries you’re performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.

  • Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.

To disable the query cache at server startup, set the query_cache_size system variable to 0. By disabling the query cache code, there is no noticeable overhead. Query cache capabilities can be excluded from the server entirely by using the --without-query-cache option to configure when compiling MySQL.

4.10.1 How the Query Cache Operates

This section describes how the query cache works when it is operational. Section 4.10.3, “Query Cache Configuration,” describes how to control whether or not it is operational.

Queries are compared before parsing, so the following two queries are regarded as different by the query cache:

SELECT * FROM tbl_name
Select * from tbl_name

Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select. See Section 4.10.4, “Query Cache Status and Maintenance.”

If a table changes, then all cached queries that use the table become invalid and are removed from the cache. This includes queries that use MERGE tables that map to the changed table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE.

Transactional InnoDB tables that have been changed are invalidated when a COMMIT is performed.

In MySQL 4.0, the query cache is disabled within transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache also works within transactions when using InnoDB tables (it uses the table version number to detect whether or not its contents are still current).

Before MySQL 5.0, a query that begins with a leading comment might be cached, but could not be fetched from the cache. This problem is fixed in MySQL 5.0.

The query cache works for SELECT SQL_CALC_FOUND_ROWS ... and SELECT FOUND_ROWS() type queries. FOUND_ROWS() returns the correct value even if the preceding query was fetched from the cache because the number of found rows is also stored in the cache.

A query cannot be cached if it contains any of the following functions:

BENCHMARK()

CONNECTION_ID()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

-ENCRYPT() with one parameter

FOUND_ROWS()

GET_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

RAND()

RELEASE_LOCK()

SYSDATE()

UNIX_TIMESTAMP()

USER()with no parameters

 


A query also will not be cached under these conditions:

  • It contains user-defined functions (UDFs).

  • It contains user variables.

  • It refers to the tables in the mysql system database.

  • It is of any of the following forms:

    SELECT ... IN SHARE MODE
    SELECT ... INTO OUTFILE ...
    SELECT ... INTO DUMPFILE ...
    SELECT * FROM ... WHERE autoincrement_col
    IS NULL
  • The last form is not cached because it is used as the ODBC workaround for obtaining the last insert ID value.

  • It uses TEMPORARY tables.

  • It does not use any tables.

  • The user has a column-level privilege for any of the involved tables.

  • Before a query is fetched from the query cache, MySQL checks that the user has SELECT privilege for all the involved databases and tables. If this is not the case, the cached result is not used.

4.10.2 Query Cache SELECT Options

There are two query cache-related options that may be specified in a SELECT statement:

  • SQL_CACHE

    The query result is cached if the value of the query_cache_type system variable is ON or DEMAND.

  • SQL_NO_CACHE

    The query result is not cached.

Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

4.10.3 Query Cache Configuration

The have_query_cache server system variable indicates whether the query cache is available:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Several other system variables control query cache operation. These can be set in an option file or on the command line when starting mysqld. The query cache-related system variables all have names that begin with query_cache_. They are described briefly in Section 4.2.3, “Server System Variables,” with additional configuration information given here.

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default cache size is 0; that is, the query cache is disabled.

If the query cache is enabled, the query_cache_type variable influences how it works. This variable can be set to the following values:

  • A value of 0 or OFF prevents caching or retrieval of cached results.

  • A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.

  • A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.

Setting the GLOBAL value of query_cache_type determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION value of query_cache_type. For example, a client can disable use of the query cache for its own queries like this:

mysql> SET SESSION query_cache_type = OFF;

To control the maximum size of individual query results that can be cached, set the query_cache_limit variable. The default value is 1MB.

The result of a query (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:

  • The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.

  • If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

  • If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

query_cache_min_res_unit is present from MySQL 4.1.

{mospagebreak title=4.10.4 Query Cache Status and Maintenance}

You can check whether the query cache is present in your MySQL server using the following statement:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+

Descriptions of each of these variables are given in Section 4.2.4, “Server Status Variables.” Some uses for them are described here.

The total number of SELECT queries is equal to:

 Com_select
+ Qcache_hits
+ queries with errors found by parser

The Com_select value is equal to:

 Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during columns/rights
check

The query cache uses variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains.

Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one block needs to be allocated.

The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Tuning information is given in Section 4.10.3, “Query Cache Configuration.”

Google+ Comments

Google+ Comments