10.5 How MySQL Uses Memory

The MySQL server allocates memory for various kinds of information as it runs:

• Thread handlers. The server is multithreaded, and a thread is like a small process running inside the server. For each client that connects, the server allocates a thread to it to handle the connection. Thus, thread is roughly synonymous with client connection. For performance reasons, the server maintains a small cache of thread handlers. If the cache is not full when a client disconnects, the thread is placed in the cache for later reuse. If the cache is not empty when a client connects, a thread from the cache is reused to handle the connection. Thread handler reuse avoids the overhead of repeated handler setup and teardown.

• The server uses several buffers (caches) to hold information in memory for the purpose of avoiding disk access when possible. Some of these buffers are

  • Grant tables. The server loads a copy of the grant tables into memory for fast access-control checking. Client access is checked for every query, so looking up privilege information in memory rather than from the grant tables on disk results in a significant reduction of disk access overhead.

  • A key buffer is used to hold index blocks. By caching index blocks in memory, the server often can avoid reading index contents repeatedly from disk for index-based retrievals and other index-related operations such as sorts.

  • The table cache holds descriptors for open tables. For frequently used tables, keeping the descriptors in the cache avoids having to open the tables again and again.

  • The server supports a query cache that is used to speed up processing of queries that are issued repeatedly.

  • The host cache holds the results of hostname resolution lookups. These results are cached to minimize the number of calls to the hostname resolver.

  • The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits, the log buffer is flushed to the InnoDB log files, providing a record on disk that can be used to recommit the transaction if it's lost due to a crash. If the transaction rolls back instead, the flush to disk need not be done at all.

  There are no buffers specifically for caching table data because MySQL relies on the operating system to provide efficient caching when reading data from tables.

• The server maintains several buffers for each client connection. It uses a communications buffer for exchanging information with the client. It also maintains client-specific buffers for reading tables and for performing join and sort operations.

• The HEAP (MEMORY) storage engine creates tables that are held in memory. These tables are very fast because no transfer between disk and memory need be done to process queries on them.

• The server might create internal temporary tables in memory during the course of query processing. If the size of such a table exceeds the value of the tmp_table_size system variable, the server converts it to a MyISAM-format table on disk and increments its Created_tmp_disk_tables status variable.

Several SHOW statements enable you to check the sizes of various memory-related parameters. SHOW VARIABLES displays server system variables so that you can see how the server is set up. SHOW STATUS displays server status variables. The status indicators enable you to check the runtime state of caches, which can be useful for assessing the effectiveness with which they are being used and for determining whether you would be better off using larger (or in some cases smaller) buffers.

Server memory use can be tuned by setting buffer sizes using command-line options or in an option file that the server reads at startup time. For more information, see Chapter 16, "Advanced Server Features."