Azure Database for MySQL - Memory keeps increasing then crashes

Question

I know there are some similar posts as mine but none of them helped me out.

I have an Azure Database for MySQL. The average memory usage is above 90% and sometimes the server crashes and restarts. After the restart the memory usage keeps increasing and slowly it reaches the 90% usage. See this behavior on the following image.

On an average basis we have around 300 active connections to the DB. At the time of crash there were not any spikes in case of connections.

Our workload is running on multiple cloud platforms, and we do not have this kind of issue on the other managed database services. Based on this I can say our queries, functions, etc. are well written. So the problem comes from elsewhere.

Server parameters:

• MySQL version: 5.7
• general purpose v1 storage 400GB and 4vCPU -> 20GB RAM (this is coming from the Azure pricing tier model)
• storage engine: InnoDB

I have collected some configs and metrics about my server. These are the values I have checked.

InnoDB
The innodb_buffer_pool_size is ~ 8GB that leaves 20-8=12GB memory to use.

• innodb_buffer_pool_chunk_size 67108864
• innodb_buffer_pool_instances 8
• innodb_buffer_pool_size 8053063680
• innodb_dirty_page_fflush 10
• innodb_flush_log_at_trx_commit 1
• innodb_flush_method async_unbuffered
• innodb_log_buffer_size 16777216
• innodb_log_file_size 268435456

Logbin

• log_bin OFF

Read,sort,join buffers

• read_buffer_size 262144
• join_buffer_size 262144
• sort_buffer_size 524288

Query Cache

• have_query_cache YES
• query_cache_limit 1048576
• query_cache_size 0
• query_cache_type OFF

Other useful configs

• max_connections 1260
• thread_cache_size 20
• thread_handling one-thread-per-connection
• thread_stack 262144
• table_open_cache 2500
• table_open_cache_instances 1

Performance schema memory usage:

select event_name, current_alloc, high_alloc
from sys.memory_global_by_current_bytes 
where current_count > 0;

Results. (records smaller than 1MiB not shown)
memory/performance_schema/table_handles 27.19 MiB 27.19 MiB
memory/performance_schema/events_statements_summary_by_thread_by_event_name 17.61 MiB 17.61 MiB
memory/performance_schema/events_statements_history_long 13.66 MiB 13.66 MiB
memory/performance_schema/memory_summary_by_thread_by_event_name 11.25 MiB 11.25 MiB
memory/performance_schema/events_statements_summary_by_digest.tokens 10.27 MiB 10.27 MiB
memory/performance_schema/events_statements_history_long.tokens 9.77 MiB 9.77 MiB
memory/performance_schema/events_statements_history_long.sqltext 9.77 MiB 9.77 MiB
memory/performance_schema/events_statements_history 6.99 MiB 6.99 MiB
memory/performance_schema/events_statements_current 6.99 MiB 6.99 MiB
memory/performance_schema/events_waits_summary_by_thread_by_event_name 6.47 MiB 6.47 MiB
memory/performance_schema/file_instances 5.50 MiB 5.50 MiB
memory/performance_schema/table_io_waits_summary_by_index_usage 5.50 MiB 5.50 MiB
memory/performance_schema/events_statements_summary_by_digest 5.20 MiB 5.20 MiB
memory/performance_schema/events_statements_history.tokens 5.00 MiB 5.00 MiB
memory/performance_schema/events_statements_history.sqltext 5.00 MiB 5.00 MiB
memory/performance_schema/events_statements_current.tokens 5.00 MiB 5.00 MiB
memory/performance_schema/events_statements_current.sqltext 5.00 MiB 5.00 MiB
memory/performance_schema/events_statements_summary_by_account_by_event_name 4.40 MiB 4.40 MiB
memory/performance_schema/events_statements_summary_by_host_by_event_name 4.40 MiB 4.40 MiB
memory/performance_schema/events_statements_summary_by_user_by_event_name 4.40 MiB 4.40 MiB
memory/performance_schema/table_shares 4.00 MiB 4.00 MiB
memory/performance_schema/events_transactions_history_long 3.13 MiB 3.13 MiB
memory/performance_schema/memory_summary_by_account_by_event_name 2.81 MiB 2.81 MiB
memory/performance_schema/memory_summary_by_host_by_event_name 2.81 MiB 2.81 MiB
memory/performance_schema/memory_summary_by_user_by_event_name 2.81 MiB 2.81 MiB
memory/performance_schema/events_stages_summary_by_thread_by_event_name 2.34 MiB 2.34 MiB
memory/performance_schema/mutex_instances 2.12 MiB 2.12 MiB
memory/performance_schema/rwlock_instances 2.12 MiB 2.12 MiB
memory/performance_schema/threads 1.78 MiB 1.78 MiB
memory/performance_schema/events_waits_summary_by_user_by_event_name 1.62 MiB 1.62 MiB
memory/performance_schema/events_waits_summary_by_account_by_event_name 1.62 MiB 1.62 MiB
memory/performance_schema/events_waits_summary_by_host_by_event_name 1.62 MiB 1.62 MiB
memory/performance_schema/events_waits_history_long 1.60 MiB 1.60 MiB
memory/performance_schema/events_transactions_history 1.60 MiB 1.60 MiB
memory/performance_schema/events_statements_summary_by_digest.sqltext 1.50 MiB 1.50 MiB
memory/performance_schema/table_lock_waits_summary_by_table 1.34 MiB 1.34 MiB

Relevant output from the SHOW ENGINE INNODB STATUS command:

BUFFER POOL AND MEMORY

Total large memory allocated 8237875200
Dictionary memory allocated 28248522
Buffer pool size 491520
Free buffers 8195
Database pages 478536
Old database pages 176484
Modified db pages 1637
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 531503, not young 78203347
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2821560, created 701494, written 3187860
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 478536, unzip_LRU len: 0
I/O sum[2280]:cur[0], unzip sum[0]:cur[0]

Any ideas are welcomed, and I am happy to provide more information if needed.

=====================EDIT========================
The whole output of SHOW VARIABLES:
https://pastebin.com/VHpyJ44j

The whole output of SHOW GLOBAL STATUS:
https://pastebin.com/cVMyF9EJ

Answer 1

The quick fix is to lower innodb_buffer_pool_size to, say, 7G.

But with 20G of RAM, you should not be running out of RAM. Do you have other applications on the same server? And they are the ones causing the crash?

For further analysis of MySQL, please provide all the VARIABLES and STATUS: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

Not much showed up in my analysis of the Variables and Status:

Analysis of GLOBAL STATUS and VARIABLES:

Observations:

• Version: 5.7.32
• 20 GB of RAM
• Uptime = 1d 10:43:41
• You are running on Windows.
• 847 QPS

The More Important Issues:

Max_used_connections = 453 -- This is quite high. If that many processes are actually running (not just Sleeping), then the connections may be stumbling over each other.

max_connections = 1260 -- Drop that to 500 to avoid risking running ouf of RAM. (However, you may then run out of "connections", which should then be handled.)

innodb_io_capacity_max = 100,000 -- Much too high; drop it to 2000. Ditto for similar settings.

There are some indications of slow queries that might be optimizable. Find the busy queries and let's discuss a couple of them.

170 "set option" per second -- What's going on?

Details and other observations:

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 7680M / 0.70)) / 20480M = 54.0% -- Most of available ram should be made available for caching. -- http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 2,500 -- Number of table descriptors to cache -- Several hundred is usually good.

( binlog_cache_size * max_connections ) = (1M * 1260) / 20480M = 6.2% -- RAM used for caching transactions on the way to the binlog. -- Decrease binlog_cache_size (now 1048576) and/or max_connections (now 1260)

( innodb_buffer_pool_size ) = 7,680 / 20480M = 37.5% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 100,000 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( innodb_io_capacity_max ) = 100,000 -- When urgently flushing, use this many IOPs. -- Reads could be slugghish or spiky.

( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash. -- OFF is OK for FusionIO, Galera, Replicas, ZFS.

( Innodb_os_log_written ) = 38,144,144,384 / 125021 = 305101 /sec -- This is an indicator of how busy InnoDB is. -- Very busy InnoDB.

( Innodb_log_writes ) = 5,626,176 / 125021 = 45 /sec

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 125,021 / 60 * 256M / 38144144384 = 14.7 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 268435456). (Cannot change in AWS.)

( innodb_file_per_table ) = innodb_file_per_table = OFF -- Put each file in its own tablespace -- (Mildly recommended, especially for large tables)

( Innodb_row_lock_time_max ) = 23,963 -- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( Innodb_row_lock_waits ) = 344,886 / 125021 = 2.8 /sec -- How often there is a delay in getting a row lock. -- May be caused by complex queries that could be optimized.

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_allowed_packet ) = 1,024 / 20480M = 5.0% -- If you do not have large blobs (etc) to load, then decrease the value. Else decrease innodb_buffer_pool_size (now 8053063680) to make room. Swapping is terrible for performance.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( Created_tmp_disk_tables ) = 721,828 / 125021 = 5.8 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216). Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 721,828 / 1400391 = 51.5% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size (now 16777216) and max_heap_table_size (now 16777216); improve indexes; avoid blobs, etc.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (22984286 + 12815127 + 1192460 + 0 + 9893254 + 32725) / 125021 = 375 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of HDDs

( relay_log_space_limit ) = 1024M -- The max total size for relay logs on a Replica. (0=unlimited) -- Let's discuss the rationale for having a limit.

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

Abnormally small:

(Com_select) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.555
Innodb_dblwr_pages_written = 0
Innodb_num_open_files = 21
innodb_buffer_pool_chunk_size = 64MB
innodb_lru_scan_depth / innodb_io_capacity = 0.0102
net_buffer_length / max_allowed_packet = 0.00%

Abnormally large:

Com_alter_table + Com_flush = 0.037 /sec
Com_commit = 96 /sec
Com_create_db = 0.12 /HR
Com_create_function = 3.5 /HR
Com_create_index = 23 /HR
Com_create_procedure = 0.23 /HR
Com_create_table = 76 /HR
Com_create_user = 0.12 /HR
Com_create_view = 1.3 /HR
Com_delete = 102 /sec
Com_delete_multi = 9.5 /sec
Com_drop_db = 0.26 /HR
Com_drop_table = 90 /HR
Com_drop_user = 0.26 /HR
Com_empty_query = 1.6 /HR
Com_flush = 0.033 /sec
Com_grant = 0.12 /HR
Com_insert = 183 /sec
Com_insert_select = 1.7 /sec
Com_insert_select + Com_replace_select = 1.7 /sec
Com_release_savepoint = 1.4 /HR
Com_rollback_to_savepoint = 0.05 /sec
Com_savepoint = 1.6 /HR
Com_set_option = 170 /sec
Com_show_create_func = 42 /HR
Com_show_create_proc = 7.9 /HR
Com_show_function_status = 1.9 /HR
Com_show_keys = 0.045 /sec
Com_show_master_status = 0.033 /sec
Com_show_plugins = 36 /HR
Com_show_procedure_status = 7.3 /HR
Com_show_slave_status = 0.033 /sec
Com_show_triggers = 0.056 /sec
Com_show_warnings = 0.073 /sec
Com_truncate = 60 /HR
Com_update_multi = 0.26 /sec
Handler_read_last = 2 /sec
Handler_savepoint = 1.6 /HR
Handler_savepoint_rollback = 0.05 /sec
Open_table_definitions = 2,499
Performance_schema_file_instances_lost = 1,242
binlog_cache_size = 1.05e+6
binlog_group_commit_sync_delay = 1,000
net_read_timeout = 120
net_write_timeout = 240
optimizer_trace_offset = --1
port = 20,019
report_port = 20,019

Abnormal strings:

binlog_row_image = MINIMAL
delay_key_write = OFF
flush = ON
have_crypt = NO
innodb_fast_shutdown = 1
innodb_temp_data_file_path = C:  emp\ibtmp1:12M:autoextend
innodb_tmpdir = C:\temp
log_bin_trust_function_creators = ON
lower_case_file_system = ON
lower_case_table_names = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_recovery = ON
slave_parallel_type = LOGICAL_CLOCK
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
sql_safe_updates = ON