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