How MySQL Uses Disk Space and Memory

A MySQL installation includes a number of programs that work together using a client/server architecture. The MySQL database system operates using a client/server architecture. The server is the central program that manages database contents, and client programs connect to the server to retrieve or modify data. MySQL also includes non-client utility programs and scripts.

Also Read:

Thus, a complete MySQL installation consists of three general categories of programs; MySQL Server, Client Programs and Non-client Utility Programs.

The MySQL database system has several important characteristics that enable it to be used in many computing environments:

MySQL is supported on multiple operating systems, and runs on many varieties of Windows, Unix, and Linux.
MySQL works in distributed environments. A client program can connect locally to a server running on the same computer or remotely to a server running on a different computer.
MySQL provides cross-platform interoperability and can be used in heterogeneous networks. Client computers need not run the same operating system as the server computer. For example, a client running on Windows can use a server running on Linux, or vice versa.

How MySQL Uses Disk Space

MySQL Server uses disk space in several ways, primarily for directories and files that are found under a single location known as the server’s data directory. The server uses its data directory to store all the following:

Database directories. Each database corresponds to a single directory under the data directory, regardless of what types of tables you create in the database. For example, a given database is represented by one directory whether it contains MyISAM tables, InnoDB tables, or a mix of the two.
Table format files (.frm files) that contain a description of table structure. Every table has its own .frm file, located in the appropriate database directory. This is true no matter which storage engine manages the table.
Data and index files are created for each table by some storage engines and placed in the appropriate database directory. For example, the MyISAM storage engine creates a data file and an index file for each table.
The InnoDB storage engine has its own tablespace and log files. The tablespace contains data and index information for all InnoDB tables, as well as the undo logs that are needed if a transaction must be rolled back. The log files record information about committed transactions and are used to ensure that no data loss occurs. By default, the tablespace and log files are located in the data directory. The default tablespace file is named ibdata1 and the default log files are named ib_logfile0 and ib_logfile1. (It is also possible to configure InnoDB to use one tablespace file per table. In this case, InnoDB creates the tablespace file for a given table in the table’s database directory.)
Server log files and status files. These files contain information about the statements that the server has been processing. Logs are used for replication and data recovery, to obtain information for use in optimizing query performance, and to determine whether operational problems are occurring.

How MySQL Uses Memory

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:

1. 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.

2. 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.
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.

3. 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.

4. 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.

5. 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.

You may also like: