엑셈

MySQL has a variety of memory areas for improving database performance, and you can improve MySQL performance by changing the values of each area. These memory areas vary by storage engine and the features in use, but they can generally be divided into global memory and local memory based on whether they can be shared.

Global Memory

The global memory area is a single memory space used in common regardless of the number of client threads. If needed, 2 or more spaces can be allocated, but even if 2 or more areas are created, all of them can be shared by every thread.

The global memory area is allocated from the operating system when the MySQL server starts, and changing its settings requires restarting the MySQL server.

The global memory area includes the following spaces, and we explain each in turn.

InnoDB Buffer Pool

InnoDB Change Buffer

InnoDB Adaptive Hash Index

InnoDB Log Buffer

MyISAM Key Cache

Query Cache

Table Cache

InnoDB : Buffer Pool

The buffer pool, the largest memory area of the InnoDB storage engine, stores data, indexes, locks, the data dictionary, and other information. (It is similar to Oracle's buffer cache.)

It is mainly used to cache frequently used data in memory for fast access.

📢 This is explained in detail in the Buffer Pool part of In-Memory Structures in InnoDB Architecture.

InnoDB : Change Buffer (older versions - Insert Buffer)

A memory area used for data change operations such as Insert, Update, and Delete.

When data changes, the change must also be reflected in the indexes, and if there are many related indexes, resource consumption can grow. The change buffer is the memory space designed to improve the performance of these index operations.

Before MySQL 5.5, it recorded changes for Insert operations only, so it was called the insert buffer.

📢 This is explained in detail in the Change Buffer part of In-Memory Structures in InnoDB Architecture.

InnoDB : Adaptive Hash Index

An InnoDB feature that compensates for the weaknesses of B-Tree indexes by defining frequently used columns as hashes so data can be accessed directly.

Here, hash values are created only for frequently used data values, not for all values. They are allocated in the adaptive hash index area, which is initialized to 1/64 of the total InnoDB buffer pool.

📢 This is explained in detail in the Adaptive Hash Index part of In-Memory Structures in InnoDB Architecture.

InnoDB : Log Buffer (older versions - Redo Log Buffer)

A memory area that holds content to be written to the transaction log (redo log) files on disk. The contents of the log buffer are periodically flushed to disk. (It plays a role similar to Oracle's redo log buffer.)

It was called the redo log buffer up to MySQL 5.5 and the log buffer in later versions.

📢 This is explained in detail in the Log Buffer part of In-Memory Structures in InnoDB Architecture.

MyISAM : Key Cache

MyISAM's key cache is a memory area similar in role to InnoDB's buffer pool, used to cache the indexes of MyISAM tables. (In the MyISAM storage engine, index blocks are cached in a special area called the key cache, but there is no special cache for data blocks; instead, the file system cache provided by the operating system is used.)

The key cache can be accessed by multiple sessions at the same time, and you can configure several key caches or assign indexes to a specific cache.

The size of the key cache can be set with the key_buffer_size system variable; setting it to 0 disables the key cache. Without the key cache, index files are accessed using only the OS cache provided by the operating system.

Key Cache Hit(%) = 100 - (key_reads / key_read_requests * 100)

key_reads : number of times an index was read from disk

key_read_requests : number of times an index was read from the key cache

Indexes of MyISAM tables can be searched quickly without disk I/O by using the key cache, but disk I/O still occurs for table access. In other words, read/write operations on MyISAM tables always involve physical I/O.

Common : Query Cache

📢 The query cache was introduced in MySQL 4.0.1, deprecated as of MySQL 5.7.20, and completely removed in MySQL 8.0.

The query cache stores the result set sent to the client together with the Select statement.

When the same query runs, the MySQL server looks up the result in the query cache instead of parsing the query. Because the query cache lives in the global memory area shared across sessions, a result produced for one client can be used as the result for another client.

The query cache is useful in environments where the same queries are run frequently against tables that rarely change. However, when a table changes, the results can no longer be guaranteed, so the related entries are removed from the query cache. Also, while removing query cache entries because of a table change, a lock is taken so invalid data cannot be read, and every thread accessing the query cache waits until the lock is released. For this reason, the more frequently tables change and the more Select queries use the query cache, the more severe the slowdown you can experience.

In the past, the query cache offered significant potential performance gains, but advances in storage technology have nearly eliminated that benefit. It also reduced database concurrency and caused some bugs, so it cannot be used from version 8.0.

Common : Table Cache

To read and write a table in MySQL, you always have to open it and close it after use. Because this creates overhead, information about opened tables is kept in a dedicated space. This space is called the table cache, and threads share it.

However, threads cannot share the same information (table), so the more threads access the same table, the more additional memory is needed to store that information.

Because MySQL runs with multiple threads, there can be many clients running queries against the same table at the same time, but the same table is not shared between threads. The more threads access the same table, the more tables have to be opened, which uses additional memory.

The size of the table cache is set automatically at server startup, but you can adjust it explicitly with table_open_cache.

table_open_cache is also related to max_connections. For example, with 200 concurrent connections, set the table cache size to at least 200 * N. (N is the maximum number of tables per join in the queries you run.)

In the following cases, unused tables are removed from the table cache to free space.

When the cache is full and a thread tries to open a table that is not in the cache

When the table cache holds more than the table_open_cache setting and there are tables no longer in use

When the flush tables command is run

Space is freed as follows.

First, tables not currently in use are released, starting with the least recently used, to make space.

If no table can be released, the cache is temporarily extended as needed to make space. While the cache is temporarily extended, when a table goes from in use to unused, the table is closed and released from the cache.

Local Memory

The local memory area is the individual memory space a client thread uses to process queries. Because it is used by a client thread, it is also called client memory, and because the connection between a client and the MySQL server is called a session, it is also called session memory.

The local memory area is allocated independently per thread and is not shared. So if you set local memory values large, the amount of memory each client thread uses grows and the server can run short of free memory. If needed, it is better to change them only for specific sessions.

Each local memory area is allocated per query only when needed and is not used otherwise. The connection buffer and result buffer remain allocated while the connection is open, but the sort buffer and join buffer are allocated when a query runs and then released.

The local memory area has the following spaces, and we explain each in turn.

Sort Buffer

Join Buffer

Read Buffer

Connection Buffer & Result Buffer

Binlog Cache

Sort Buffer

The sort buffer is the space used for a separate sort operation when sorting with an index is not possible. It is allocated only when sorting is needed and is returned to the system immediately after the query finishes.

The size of the sort buffer can be set with the system parameter sort_buffer_size. If the data to be sorted is larger than the sort buffer, the data is split into multiple chunks, and disk is used for temporary storage. The data stored on disk is then read back into memory and processed.

Because the sort buffer is allocated per session, setting it too large can cause memory shortages, and setting it too small causes disk I/O for most sort operations, so it should be set to an appropriate value.

Repeatedly writing to and reading from disk to sort data split into multiple chunks is called a multi-merge. The number of multi-merges accumulates in the status variable sort_merge_passes, so you can monitor it. When multi-merges occur, disk is used and actual query requests can slow down, so you should consider increasing the sort_buffer_size system variable.

📢 Before MySQL 8.0.12, the optimizer allocated a fixed amount of memory for filesort operations, but from MySQL 8.0.12 the allocation grows incrementally up to the size set in the sort_buffer_size system variable. This change avoids using excessive memory for small sorts, and if large sorts are needed, you can set sort_buffer_size to a larger value as needed.

📢 As a similar concept, in Oracle a sort that can be done with only the allocated memory is called Optimal Mode, while sorts that must use temp space are called One-pass and Multi-pass. In particular, when Multi-pass occurs, Temp I/O-related events are observed and become a cause of performance degradation.

Join Buffer

This area is used in joins when the join column of the inner (driven) table has no suitable index, so an Index Scan, Index Range Scan, or Full Table Scan must be performed. (This buffer is used when the join type is ALL or index.)

The join buffer is created per join at the session level, and rather than being allocated as needed, the whole join buffer is allocated at once when it is needed. Each join uses one join buffer, so if there are several tables with join type ALL or index, a buffer is allocated for each, and the same row combination is stored multiple times in different buffers.

The size of the join buffer is determined by join_buffer_size. A larger join buffer can reduce the number of lookups needed to perform a join. Because at least the configured size is allocated for every join, it is better to keep the global setting small and change it to a larger value only in sessions that run large joins, or use the SET_VAR optimizer hint to change the setting per query.

One join buffer is allocated when two tables need a full join, and composite joins of multiple tables without indexes may need several join buffers. Allocated buffers are released after the query completes, and only the columns used are stored in the join buffer, not entire rows.

Read Buffer

In general, when a sequential scan (full table) must be performed on a MyISAM table, a read buffer is allocated for each table. Not only the MyISAM engine but other engines also allocate and use a read buffer in the cases mentioned below.

Cases where all storage engines use the read buffer are as follows.

When sorting rows with ORDER BY and caching indexes in a temp file (not a temp table)
When doing bulk inserts into partitions
When caching the results of nested queries

The size of the read buffer can be set with the read_buffer_size system variable, and you can increase this value if you run sequential scans often. Like other local memory, it is allocated per client, so it should not be set to a large value globally; it is better to change it at the session level only in clients that need to run large queries.

📢 After a sort, a read buffer may be temporarily allocated to avoid disk seeks when reading the data back in sorted order. The size of this read buffer is determined by the read_rnd_buffer_size system variable.

Connection Buffer & Result Buffer

The server creates a thread for each client connection request, and each thread needs spaces called the connection buffer and the result buffer.

Both buffers are initially allocated at net_buffer_length and grow up to max_allowed_packet as needed. When a query completes, the result buffer is reset to net_buffer_length, but the connection buffer is not.

Binlog Cache (Binlog=Binary Log)

The binary log is used for replication, point-in-time recovery, and so on, and records events that changed the database, such as table creation or data changes, in binary format. (Something similar to Oracle's Flashback feature can be implemented on top of the binlog.)

The binary log format can be set to one of 3: STATEMENT, ROW, or MIXED.

STATEMENT (default before MySQL 5.7.6) : writes the executed command (SQL) to the binary log as is. It is efficient for SQL that updates many rows and does not take much space. However, because it records only the SQL, inconsistent results can occur when some SQL contains functions.

ROW (default from MySQL 5.7.6) : writes events that show how table rows are affected (based on the data changed by the SQL, not the SQL itself). Because it records the contents of updated rows explicitly, data is never copied incorrectly. It is efficient when running commands that do not affect rows. (For operations such as batch updates, deleting entire tables, or altering tables, the log volume can become very large and cause I/O performance problems.)

MIXED : STATEMENT+ROW. It follows the STATEMENT format by default but records in ROW format when needed.

The MySQL server does not write binary log content straight to disk; it buffers it in a temporary memory space, called the binlog cache.

When a transaction starts, the thread reserves memory up to the size set in the binlog_cache_size system variable and records the transaction in that memory area until the transaction ends. When the transaction ends, it is written to the binary log file.

Among the status variables, binlog_cache_use shows the number of transactions that used the binlog cache, and binlog_cache_disk_use shows the number of transactions that used disk (a temporary file) for the binlog cache. If the ratio of binlog_cache_disk_use to binlog_cache_use is high, many transactions are exceeding binlog_cache_size.

The total size used to cache transactions is limited by the max_binlog_cache_size system variable, so a transaction larger than this value fails with the following error.

Multi-statement transaction required more than 'max_binlog_cache_size'bytes of storage

📢 The cache for non-transactional statements uses a space sized by binlog_stmt_cache_size, and its status is checked through binlog_stmt_cache_use (number of non-transactional statements that used the binary log cache) and binlog_stmt_cache_disk_use (number of non-transactional statements that used the binary log cache but exceeded the binlog_stmt_cache_size setting and used a temporary file).

Related parameters

Parameter	Default Value	Range	Description
key_buffer_size	8388608	0 ~ OS_PER_PROCESS_LIMIT	• Index blocks of MyISAM tables are buffered and shared by all threads; this sets the size of the buffer (key cache) used for these index blocks. • You can increase the value to improve index handling for all reads and multiple writes. • If the value is too large, the system can slow down severely once it starts paging. (MySQL relies on the operating system to perform file system caching when reading data.)
table_open_cache	4000	1 ~ 524288	• Limits the number of open tables for all threads. • Increasing this value increases the number of file descriptors mysqld needs.
max_connections	151	1 ~ 100000	The maximum number of concurrent client connections allowed.
sort_buffer_size	262144(256KB)	32768 ~ [4GB-1](window 4294967295, other 18446744073709551615)	• The size of the buffer allocated to each session that must perform a sort. • Consider increasing this value if ORDER BY/GROUP BY processing cannot be sped up by query optimization or index improvements. • It is better to increase it with a session setting only for sessions that need a lot of space.
join_buffer_size	262144(256KB)	128 ~ [4GB-1](window 4294967295, other 18446744073709551615)	• The size of the buffer used for plain index scans, range index scans, and joins that do full table scans because no index is used. • If you cannot add indexes, increasing this value speeds up full joins. • One join buffer is allocated for a full join between two tables, and complex joins between several tables may need multiple join buffers. • It is better to keep the global setting small and change it to a larger value only with a session setting in sessions that run large joins, or set it per query with an optimizer hint.
read_buffer_size	131072(128KB)	8192 ~ 2147479552	Each thread that does a sequential scan of a MyISAM table allocates a read buffer for each table it scans; this system variable determines the size of that buffer.
read_rnd_buffer_size	262144(256KB)	1~ 2147483647	• Determines the size of the random-read buffer used for MRR (Multi-Range Read) optimization in all storage engines. • Reading rows with a range scan on a secondary index can cause many random disk accesses to the base table if the table is large and not stored in InnoDB's cache.
net_buffer_length	16384	1024 ~ 1048576(1MB)	• Each client thread is associated with a connection buffer and a result buffer of the size set in this value. • They grow up to the size set in max_allowed_packet as needed, and after each SQL statement the result buffer shrinks back to this value. If an SQL statement is longer than this value, the connection buffer grows automatically.
max_allowed_packet	67108864(64MB)	1024 ~ 1073741824(1GB)	• The maximum size of one packet or any generated/intermediate string, or of parameters sent by the mysql_stmt_send_long_data() C API function. • The packet message buffer is initialized to net_buffer_length but can grow up to max_allowed_packet as needed. • You must increase this value if you use large BLOB columns or long strings.
binlog_cache_size	32768	4096 ~ 18446744073709547520(16EiB)	• Specifies the size of the memory buffer that holds changes to the binary log during a transaction. • When the binary log is enabled, a binlog cache is allocated to each client, and when each transaction commits, the binlog cache is reset by clearing the memory buffer and truncating the temporary file.
max_binlog_cache_size	18446744073709547520	4096 ~ 18446744073709547520(16EiB)	• Limits the total size used to cache multi-statement transactions. • If a transaction is larger than the set value, the transaction fails and is rolled back. • The recommended maximum is 4GB.
binlog_stmt_cache_size	32768	4096 ~ 18446744073709547520(16EiB)	• The size of the binary log memory buffer that holds non-transactional statements issued during a transaction. • If you often use large non-transactional statements in transactions, increasing this value can improve performance by reducing or eliminating writes to temporary files.