1

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?

  • Does this happen at a particular time of day? Do you have cron jobs running? When did this start happening? Do you have other similar servers - is it happening with them? CPU, RAM, disk config (+networking info. if relevant). – Vérace Mar 23 '16 at 3:26
  • no,it is random,wo have some slave is the same question, wo guess maybe some select cause it. but we do not know why? may be Tmp table cause? – guo hardy Mar 23 '16 at 3:45
  • Maybe turn on your general log? This will place a load on your system - but maybe worth it to find out the underlying cause? – Vérace Mar 23 '16 at 3:50
  • I see the 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. – Giovanni Mar 23 '16 at 7:51
  • You can try to lower the value of thread_cache_size. 256 looks too much high. – Giovanni Mar 23 '16 at 8:00
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.

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.