MySQL memory usage is too high - OOM

Question

Im using 32vCPU and 120GB VM for Mysql 5.6.

1. it's a dedicated MySQL server.
2. 15000 IOPS SSD disk.
3. Total database size 40G

This server was working fine for a couple of months. Memory usage is never crossed 60GB.

But a few days back the mysql process killed due to OOM. After restart memory usage is fine, but its gradually increasing within 6 to hrs it'll reach the maximum limit and killed by OOM Killer.

After installing the mysql, I used percona configuration wizard to generate the my.cnf file.

Here are the important parameters:

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 5000
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 50
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 90G

The last downtime happened yesterday. I have verified that the InnoDB buffer never crossed 30GB for the whole day.

Buffer Pool usage

enter image description here

Memory used:

The both highlighted points are the maximum limit. enter image description here

InnoDB IO

enter image description here

Mysql client thread activity:

enter image description here

Temo Objects:

enter image description here

Mysql Internal memory

enter image description here

From a shell script:

+------------------------------------------+--------------------+
| key_buffer_size | 32.000 MB |
| query_cache_size | 0.000 MB |
| innodb_buffer_pool_size | 92160.000 MB |
| innodb_additional_mem_pool_size | 8.000 MB |
| innodb_log_buffer_size | 8.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 92208.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 0.250 MB |
| read_buffer_size | 0.125 MB |
| read_rnd_buffer_size | 0.250 MB |
| join_buffer_size | 0.250 MB |
| thread_stack | 0.250 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 32.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 33.156 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 1399 |
| max_connections | 5000 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 138593.594 MB |
| TOTAL (MAX) | 257989.250 MB |
+------------------------------------------+--------------------+

I just wanted to know that, InnoDB never crossed>30, but how mysql used all the memory?

• TheDataGuy: This is dedicated for mysql, other services.
• TheDataGuy: I have verified that global buffers are 96GB
• Kevin Bott: I'm facing something similar, though with a 8GB server. What's RSS/VSZ reported by ps aux? Can you add the message reported by OOM killer? As configured, you could need up to 256GB to support his config. it's probably unlikely you'll have 5000 connections using a 32MB temp table... but it could happen :)
• TheDataGuy: Hi Kevin, I fixed this issue by reducing the max_prepare_statement_count value. Earlier it was the max value.
• Kondybas: Here is my explanation for mysql memory consumption. I hope it will be useful: dba.stackexchange.com/a/256135/7895

Answer 1

5,000 connections is a LOT, there are a lot of different memory buffers allocated on a per connection basis. Just your max_connections * tmp_table_size is enough to overwhelm your server's memory if many of your connections start using a lot of large implicitly created temporary tables.

Here is a reasonably comprehensive equation from my MySQL memory tuning article:

SELECT (
 (
 @@binlog_file_cache_size +
 @@innodb_buffer_pool_size +
 @@innodb_log_buffer_size +
 @@key_buffer_size +
 @@query_cache_size +
 ( @@max_connections *
 ( @@binlog_cache_size +
 @@binlog_stmt_cache_size +
 @@bulk_insert_buffer_size +
 @@join_buffer_size +
 @@max_allowed_packet +
 @@read_buffer_size +
 @@read_rnd_buffer_size +
 @@sort_buffer_size +
 @@thread_stack +
 @@tmp_table_size
 )
 ) +
 ( @@slave_parallel_threads * 
 ( @@slave_parallel_max_queued )
 ) +
 ( @@open_files_limit * 1024)
 ) / 1024 / 1024 / 1024) AS max_memory_GB;

Adjust those, including your max_connections until the value output by this query reasonably fits into your memory.

Disclosure: I wrote the referenced article.