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?
thread_handling = pool-of-threads
, can you limit the number of thread or using a one thread for connection? Maybe it is possible that threads are not releasing memory.thread_cache_size
. 256 looks too much high.