OOM in mysql-percona, memory leak?

Question

Every second day, I run into an OOM problem.

Mar 21 17:31:33 iZ23h7fvujwZ kernel: Out of memory: Kill process 60346 (mysqld) score 943 or sacrifice child
Mar 21 17:31:33 iZ23h7fvujwZ kernel: Killed process 60346, UID 498, (mysqld) total-vm:406039136kB, anon-rss:64936040kB, file-rss:5180kB

Below is my.cnf:

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h \\D \\R:\\m:\\s [\\d]> 
#pager="less -i -n -S"
no-auto-rehash

#[mysqld_multi]
#mysqld = /usr/local/mysql/bin/mysqld_safe
#mysqladmin = /usr/local/mysql/bin/mysqladmin
#log = /opt/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /opt/mysql_3306
datadir = /data/mysql_3306
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 1
max_allowed_packet = 1073741824
group_concat_max_len = 102400
#thread pool
thread_handling = pool-of-threads

#timeout
interactive_timeout = 3600
wait_timeout = 3600

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 40000
max_connect_errors = 10000

skip-name-resolve = 1
#logs
innodb_print_all_deadlocks = 1
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql_3306/log/slow.log
log-error = /data/mysql_3306/error.log
log_warnings = 2
pid-file = /data/mysql_3306/mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog-do-db=db_use
#binlog-ignore-db = mysql
replicate_wild_do_table=db_use.%
replicate_wild_do_table=BBS.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
slave-skip-errors = all
#binlog
binlog_format = mixed
server-id = 1
log-bin = mybinlog
binlog_cache_size = 3072M
max_binlog_size = 2048M
max_binlog_cache_size = 3072M
sync_binlog = 0
expire_logs_days = 10

#relay log
#skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

#buffers & cache
table_open_cache = 4096
table_definition_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 1073741824
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_repair_threads = 1
log_bin_trust_function_creators = 1

#innodb
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size=20M

Server details

• CPU : 16
• MEM : 62G

Server version: Ver 14.14 Distrib 5.6.20-68.0, for Linux (x86_64) using EditLine wrapper

Buffer pool and memory

• Total memory allocated 38461767680; in additional pool allocated 0
• Total memory allocated by read views 5120

We find memory allocated is 38461767680, but MySQL use memory is more and more ,never free. and when MySQL OOMs it uses total-vm:406039136kB, anon-rss:64936040kB, is more than 38461767680. Is there a memory leak? We can use flush tables free 6 g.

Any idea to fix this? Is my buffer_pool_size is too big in my.cnf?

Answer 1

OOM is usually a result of bad my.cnf config and actually your my.cnf overcommits memory big time. Run mysqltuner.pl to get a sense of what parameters needs tweaking. Many of your configuration values are way high and some of them doesn't even make sense.

For example:

1) binlog_cache_size 3Gb? default value is 32768 bytes(!)

According to dev.mysql.org:

The size of the cache to hold changes to the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large transactions, you can increase this cache size to get better performance.

Do you have 3GB big transactions?

2) Per connection buffers:

read_buffer_size = 2M
read_rnd_buffer_size = 16M

Why are you optimizing for random read? These values are way too high even for a very heavily used databases.

You can easily drop these under 1MB. Maybe 256K as a start and increase if necessary.

Same goes for sort_buffer_size and join_buffer_size. 1Mb for the former and 128kb for the latter should be sufficient if your tables are well indexed for the common queries.

Especially with max_connections = 40000 these values can easily run your out of memory.

+) Innodb

innodb_log_buffer_size = 64M

Do you actually need this? You should be able to drop this to a much lower level and like 8MB or 16Mb and monitor the Innodb_log_wait status variable.

innodb_log_files_in_group = 3

You don't get any benefit of having 3. Keep it to 2.

Answer 2

binlog_cache_size = 3072M - Your setting is really excessive. The default (32K) is usually sufficient, but 1M would not be bad.

max_connections = 40000 - This is really asking for OOM. A few hundred, maybe a few thousand is usually sufficient. What is the value of Max_used_connections?

thread_cache_size = 256 - That is a cache. Most servers are happy with a mere 10.

innodb_log_buffer_size = 64M - High, but not a big deal since there is only one of them and you have 62GB of RAM.

tmp_table_size = 1073741824 - Since tmp tables use up to min( tmp_table_size, max_heap_table_size ), there is no need to have this more than 96M (the other setting). In general, it should be no more than about 1% of RAM. In an extreme situation, multiple tmp tables can be created by every connection.

innodb_buffer_pool_size = 35G - This should be fine since you have 62GB of RAM. But it might be good to increase innodb_buffer_pool_instances to 16.

For further analysis, please post SHOW VARIABLES and SHOW GLOBAL STATUS (after at least 24 hours of up time).