How MySQL uses memory

MySQL allocates global buffers at the server startup and these are shared among all the connections. The majority of MySQL’s memory is consumed by the global buffers e.g. innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size etc.

The InnoDB buffer pool is typically the largest consumer of memory in a MySQL instance. It is configured using innodb_buffer_pool_size parameter. It is used to cache the table data and indexes, change buffer, adaptive hash index, and other internal structures. Cloud SQL for MySQL, Google Cloud’s managed MySQL offering, configures innodb_buffer_pool_size up to 72% of the instance memory depending on the instance size. 

InnoDB reserves additional memory for buffers and associated data structures, the total allocated memory is approximately 10% greater than the specified buffer pool size. You can check the InnoDB buffer pool memory usage in show engine innodb status\G output.

mysql> show engine innodb status\G

…

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 11511349248

…

The InnoDB log buffer is used to hold the changes to be written to the InnoDB redo log files on the disk. It is configured using innodb_log_buffer_size. MySQL community default value is 16 MB and Cloud SQL for MySQL uses the same.

The key buffer is used by MySQL to cache the MyISAM indexes in memory. It is configured using key_buffer_size. MySQL community default value is 8 MB and Cloud SQL for MySQL uses the same. Cloud SQL for MySQL doesn’t support MyISAM tables, therefore, it is OK to leave it to default.

The query_cache_size configuration variable defines the amount of memory allocated for caching query results. It is disabled by default in MySQL Community 5.7 and Cloud SQL for MySQL 5.7. 

Query cache was known for serious scalability issues, therefore, it was deprecated in MySQL 5.7.20 and removed in MySQL 8.0. If you’re still using it for your MySQL 5.7 instance, please check if it is really useful for your workload. Please refer to this blog for more information. 

MySQL allocates global caches that are shared among all the connections, these are allocated dynamically and the configuration variables define the maximum limit for them.

MySQL uses table cache to speed up the opening of tables. MySQL table cache is separated into two parts, a cache of open tables and a cache of table definitions, configured using table_open_cache and table_definition_cache respectively. 

The table_open_cache is a memory cache to store the file descriptor of the open tables by all the connected threads. Increasing this value increases the number of file descriptors that mysqld program, also known as MySQL server, requires. Please make sure that your operating system can handle the number of open file descriptors implied by the table_open_cache setting.

Multiple client sessions can access the given table simultaneously and the table is opened independently by each concurrent client session. This is the reason you might see open tables count being higher than the number of tables in the server. When the table cache is full, the server releases the tables that are currently not in use, starting with the least recently used table.

The table_definition_cache is a memory cache to store the table definitions. It is global and shared among all connections.

CloudSQL for MySQL 5.7 uses 2000 and 1400 as defaults for table_open_cache and table_definition_cache.

CloudSQL for MySQL 8.0 uses 4000 and 2000 as defaults for table_open_cache and table_definition_cache.

For each client connection, MySQL assigns a dedicated thread which executes all the queries and returns the result back to the client until the client disconnects. MySQL caches the threads so that it doesn’t have to create and destroy threads for each connection. The number of threads in the thread cache is configured using thread_cache_size variable.

CloudSQL for MySQL uses 48 as default for thread_cache_size.

InnoDB has its own cache for storing table definitions, this is different from the table open cache and table definition cache. You can check the memory allocated for InnoDB data dictionary in the show engine innodb status\G output.

----------------------

BUFFER POOL AND MEMORY

----------------------

…

Dictionary memory allocated 65816817

The table_definition_cache setting sets a soft limit on the number of table instances in the InnoDB data dictionary cache, if the number of table instances in InnoDB data dictionary cache exceeds the table_definition_cache limit, LRU mechanism begins marking table instances for eviction and eventually removes them from this cache. 

Does it mean that the number of table instances in the InnoDB data dictionary cache will always be lower than the table_definition_cache limit? This is not the case, table instances with foreign key relationships are not placed on the LRU list. They remain cached and cause the table instances to grow beyond the table_definition_cache limit which leads to additional memory usage. The memory consumed by tables with foreign key relationships is released at the MySQL shutdown/restart event only. This issue exists in both MySQL 5.7 and 8.0 and there is a known verified bug.

If your MySQL instance has a large number of tables with foreign key relationships, InnoDB data dictionary cache may consume multiple GBs of memory. It is often overlooked while configuring MySQL buffers/caches and could be one of the reasons for unanticipated high memory usage or out-of-memory (OOM) issues.

A sample output showing a small instance consuming 4.16GB for InnoDB data dictionary cache.

$ mysql -e "show engine innodb status\G" | grep -i memory

BUFFER POOL AND MEMORY

Total large memory allocated 7696023552

Dictionary memory allocated 4465193358

Another feature of MySQL which consumes memory is the session buffers. These buffers are allocated on the per-session basis and in some cases multiple instances of them can be allocated for a single query (join_buffer_size, in particular).

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

These buffers are allocated only when a query needs them (for sorting, joins, index/full table scans, and more.) but when these are needed, they are allocated to their full size even if a very small portion is required. Setting these buffers to a high value may result in wasted memory.

The default values are the same in the MySQL community and Cloud SQL for MySQL.

MySQL uses binary log cache to hold the changes made to binary log while a transaction is running. It is configured using binlog_cache_size. It is allocated for each client if binary logging is enabled (log_bin=ON).

The default value for binlog_cache_size is the same in MySQL Community and Cloud SQL for MySQL.

MySQL creates internal temporary tables to store the intermediate result while processing some types of queries such as GROUP BY, ORDER BY, DISTINCT, and UNION. These internal temporary tables are created in memory first and converted to on-disk tables when the maximum size is reached. The maximum size of internal temporary tables is determined as the minimum of tmp_table_size and max_heap_table_size variables. 

The default values for tmp_table_size and max_heap_table_size are the same in the MySQL community and Cloud SQL for MySQL.

Note: As per-session buffers and in-memory temporary tables allocate memory separately for each connection, the overall memory usage can be very high if a large number of connections need them. It is recommended to not set these values too high, experiment to find the best value for your workload.

Every thread requires little memory to manage the client connection. The following variables control their size.

• thread_stack: The stack size for each thread, default is 256KB.
• net_buffer_length: Each client is associated with a connection buffer and result buffer of net_buffer_length. This can further grow up to max_allowed_packet size.

If performance_schema is enabled, it helps in monitoring the MySQL server execution at low level. Performance_schema allocates memory dynamically and it is freed only at MySQL shutdown/restart.

Cloud SQL for MySQL allows enabling performance_schema on the instances having RAM size 15 GB or above, it is enabled by default starting with the MySQL 8.0.26 version. Performance_schema is disabled by default for MySQL 5.6, 5.7 and 8.0.18 versions, it can be enabled using database flags.

Cloud SQL for MySQL automatically configures memory related parameters for good out-of-the-box performance. Your instance can still run into OOM issues if the workload requires larger caches to support a large number of tables and/or connections. Many threads allocating session buffers simultaneously can also lead to memory related problems. The more memory is allocated for global buffers/caches, the less memory is available for connections, session buffers and vice versa, finding a good balance is the key.

If your workload requires bigger caches/session buffers, you can reduce the size of the global buffer with innodb_buffer_pool_size. You can change the configuration parameters of your Cloud SQL for MySQL instances using database flags. If you still run into OOM or performance issues, you can upgrade the instance size to increase the memory.