Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/sql/docs/mysql/optimize-high-memory-usage below:

Optimize high memory usage in instances | Cloud SQL for MySQL

Stay organized with collections Save and categorize content based on your preferences.

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

Some MySQL memory blocks are used globally. This means that all query workloads share memory locations, are occupied all the time, and are released only when the MySQL process stops. Some memory blocks are session based, which means that as soon as the session closes, memory used by that session is also released back to the system.

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

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

Each user session consumes memory depending on the queries running, buffers, or cache 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 query consumes memory according to these parameters during the session.

This part of memory is common for all the queries and is controlled by parameters such as Innodb_buffer_pool_size, Innodb_log_buffer_size, and key_buffer_size.

Cache memory includes a query cache, which is used to save the queries and their results for faster data retrieval of the same subsequent queries. It also includes the binlog cache to hold the changes made to the binary log while the transaction is running, and is controlled by binlog_cache_size.

Memory is also used by join and sort operations. If your queries use join or sort operations, those queries use memory on the basis of join_buffer_size and sort_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 *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

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

The MyISAM-related parameter for bulk data insertion is bulk_insert_buffer_size.

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

The following sections offer some recommendations for optimal memory usage.

You can review the memory usage of an instance with the database/memory/components.usage metric in Metrics Explorer.

If you have less than 5% memory in database/memory/components.cache and database/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 an alerting policy with a metric threshold condition of 95% or more in database/memory/components.usage.

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

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) x max_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.

• Run SHOW PROCESSLIST to see the ongoing queries that are consuming memory. It displays all connected threads and their running SQL statements and tries to optimize them. Pay attention to the state and duration columns.

  mysql> SHOW [FULL] PROCESSLIST;
  
  

• Check SHOW ENGINE INNODB STATUS in the section BUFFER POOL AND MEMORY to see the current buffer pool and memory usage, which can help you set your buffer pool size.

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• Use MySQL's SHOW variables command to check the counter values, which give you information such as number of temporary tables, number of threads, number of table caches, dirty pages, open tables, and buffer pool usage.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

After you analyze the memory usage by different components, set the appropriate flag in your MySQL database. To change the flag in Cloud SQL for MySQL instance, you can use Google Cloud console or gcloud CLI. To change the flag value using the Google Cloud console, edit the Flags 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.

• Google Cloud recommenders

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-14 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-14 UTC."],[],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4