How MySQL Uses Disk Space?
The MySQL server uses disk space in several ways, primarily for directories and files that are located in the data directory. The server uses the 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). 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. These files are placed in the appropriate database directory. For example, the MyISAM storage engine creates a datafile and an index file for each table. The BDB storage engine creates one file per table that includes both data and indexes.
# 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. General log files and status files.
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.
