| MySQL Server memory use includes data structures that the server sets up to manage communication with clients and to process the contents of databases. The server allocates memory for many kinds of information as it runs: Thread handlers. The server is multi-threaded, 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. 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. Threads also may be created for other purposes. Individual storage engines might create their own threads, and replication uses threads. The server uses several buffers (caches) to hold information in memory for the purpose of avoiding disk access when possible: Grant table buffers. The grant tables store information about MySQL user accounts and the privileges they have. 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 holds index blocks for MyISAM tables. 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. In contrast to the handling of MyISAM indexes, there are no buffers specifically for caching MyISAM table data rows because MySQL relies on the operating system to provide efficient caching when reading data from tables. 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 speeds up processing of queries that are issued repeatedly. This feature is discussed in detail in Section 39.4, "Using the Query Cache." 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 is lost due to a crash. If the transaction rolls back instead, the flush to disk need not be done at all.
The 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 access their contents. 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. The server maintains several buffers for each client connection. One is used as a communications buffer for exchanging information with the client. Other buffers are maintained per client for reading tables and for performing join and sort operations.
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 configured. 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 39, "Optimizing the Server." |
