It's a common problem to have instances consuming lots of memory or running intoout-of-memory (OOM) events. A database instance running with high memoryutilization often causes performance issues, stalls, or even database downtime.

Some MySQL memory blocks are used globally. This means that all query workloadsshare memory locations, are occupied all the time, and are released only whenthe MySQL process stops. Some memory blocks are session based, which means thatas soon as the session closes, memory used by that session is also released backto the system.

Whenever there is high memory usage by a Cloud SQL for MySQL instance, Cloud SQLrecommends that you identify the query or process that's using a lot of memoryand release it. MySQL memory consumption is divided into three major parts:

• Threads and process memory consumption
• Buffer memory consumption
• Cache memory consumption

Threads and process memory consumption

Each user session consumes memory depending on the queries running, buffers, orcache used by that session and is controlled by the session parameters of MySQL.The major parameters include:

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

If there are N number of queries running at a particular time, then each queryconsumes memory according to these parameters during the session.

Buffer memory consumption

This part of memory is common for all the queries and is controlled by parameterssuch asinnodb_buffer_pool_size,innodb_log_buffer_size, andkey_buffer_size.

The InnoDB buffer pool, which is configured by theinnodb_buffer_pool_size flag,occupies a significant amount of memory on yourCloud SQL for MySQL instance and serves as acache for improving performance. To reduce the risk ofout-of-memory (OOM) events, you canenable managed buffer pool(Preview).

Cache memory consumption

Cache memory includes a query cache, which is used to save the queries and theirresults for faster data retrieval of the same subsequent queries. It alsoincludes thebinlog cache to hold the changes made to the binary log while thetransaction is running, and is controlled bybinlog_cache_size.

Other memory consumption

Memory is also used by join and sort operations. If your queries use join or sortoperations, those queries use memory on the basis ofjoin_buffer_size andsort_buffer_size.

Apart from this, if you enable the performance schema, it consumes memory.To check the memory usage by the performance schema, use the following query:

SELECT*FROMperformance_schema.memory_summary_global_by_event_nameWHEREEVENT_NAMELIKE'memory/performance_schema/%';

There are many instruments available in MySQL that you can set up to monitor thememory usage through the performance schema. To learn more, see theMySQL documentation.

The MyISAM-related parameter for bulk data insertion isbulk_insert_buffer_size.

To learn about how MySQL uses memory, see theMySQL documentation.

Recommendations

The following sections offer some recommendations for optimal memory usage.

Enable managed buffer pool

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

If your memory usage is high, then your instance canexperience out-of-memory (OOM) events. To help prevent OOM events,enable managed buffer pool to reduce the value ofinnodb_buffer_pool_size to free up memory.When memory usage stabilizes at a lower value,MySQL increases the value ofinnodb_buffer_pool_size incrementallyto its original value.

You can't enable managed buffer pool for shared core instances, orfor MySQL 5.6 or MySQL 5.7.

To enable managed buffer pool for your instance, set theinnodb_cloudsql_managed_buffer_pool flag toon. For more informationabout setting database flags, seeSet a database flag.

Changing the value of theinnodb_cloudsql_managed_buffer_pool flag doesn'trequire a restart of the Cloud SQL instance.

By default, if your MySQL database exceeds 95% of itsallocated memory, then Cloud SQL begins toreduce the size of itsinnodb_buffer_pool_size. To modify the 95%threshold, set theinnodb_cloudsql_managed_buffer_pool_threshold_pct flagto a different percentage value. For example, to adjust the threshold to 97%,use the following command:

gcloud sql instances patchINSTANCE_NAME   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

You can set theinnodb_cloudsql_managed_buffer_pool_threshold_pct flagto an integer value between 50 and 99. Changing the value of the memory usagethreshold doesn't require a restart of the Cloud SQL instance.

When managed buffer pool makes adjustments to value ofinnodb_buffer_pool_size, thechanges aren't reflected in the flag value displayed in Google Cloud console. In order to view thecurrent value ofinnodb_buffer_pool_size when managed buffer pool is enabled, youcan query the flag value by using the MySQL client:

mysql>SHOWGLOBALVARIABLESLIKE'innodb_buffer_pool_size';

Reducing the buffer pool size can't prevent OOMs inall cases. For example, some workloads might consume memory unsustainably or increase at a sudden rate,some Cloud SQL instances might be under provisioned, or the buffer poolmight not be warmed up. Cloud SQL might not beable to free up memory quickly enough to accommodatesudden changes in memory workload. In addition,Cloud SQL can't accommodatemisconfigured values of other memory flags.

You can review the memory usage of an instance with thedatabase/memory/components.usage metric inMetrics Explorer.

In general, if you have less than 10% memory indatabase/memory/components.cache anddatabase/memory/components.free combined, the risk of an OOM event is high.To monitor the memory usage and to prevent OOM events,we recommend that you set up analerting policywith a metric threshold condition indatabase/memory/components.usage.

The following table shows the relationship between your instance memory and therecommended alerting threshold:

Calculate memory consumption

Calculate the maximum memory usage by your MySQL database to select the appropriate instance type for your MySQL database. Use the following formula:

Maximum MySQL memory usage =innodb_buffer_pool_size +innodb_additional_mem_pool_size +innodb_log_buffer_size +tmp_table_size +key_buffer_size + ((read_buffer_size +read_rnd_buffer_size +sort_buffer_size +join_buffer_size) xmax_connections)

Here are the parameters used in the formula:

• innodb_buffer_pool_size: The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.
• innodb_additional_mem_pool_size: The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
• innodb_log_buffer_size: The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
• tmp_table_size: The maximum size of internal in-memory temporary tables created by the MEMORY storage engine and, as of MySQL 8.0.28, the TempTable storage engine.
• key_buffer_size: The size of the buffer used for index blocks. Index blocks for MyISAM tables are buffered and are shared by all threads.
• read_buffer_size: Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.
• read_rnd_buffer_size: This variable is used for reads from MyISAM tables, for any storage engine, and for Multi-Range Read optimization.
• sort_buffer_size: Each session that must perform a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.
• join_buffer_size: The minimum size of the buffer that's used for plain index scans, range index scans, and joins that don't use indexes and thus perform full table scans.
• max_connections: The maximum permitted number of simultaneous client connections.

After you analyze the memory usage by different components, set the appropriateflag in your MySQL database. To change the flag in Cloud SQL for MySQL instance, you can use Google Cloud console orgcloud CLI. To change the flag value using the Google Cloud console, edit theFlags section, select the flag, and enter the new value.

Lastly, if the memory usage is still high and you feel running queries and flag values are optimized, then consider increasing the instance size to avoid OOM.

What's next

• Google Cloud recommenders