Mysql – OOM in thesql-percona, memory leak

configurationMySQLpercona

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?

Best Answer

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.

Related Solutions

MySQL master binlog corruption

Surprisingly, that's not gibberish.

That indeed appears at the top of binlogs whenever you do mysqlbinlog to a binary log generated using MySQL 5.1 and MySQL 5.5. You will not see that gibberish in binary logs for MySQL 5.0 and back.

This is why the start point for replication from an empty binary log is

107 for MySQL 5.5
106 for MySQL 5.1
98 for MySQL 5.0 and back

This is good to remember if you do MySQL Replication where the Master if MySQL 5.1 and the slave is MySQL 5.0. This could present a really big headache.

Replication from Master using 5.0 and Slave using 5.1 works fine, not the other way around.(According to MySQL Documentation, it is generally not supported for 3 reasons: 1) Binary Log Format, 2) Row-based Replication, 3) SQL Incompatibility).

Anyway, do a mysqlbinlog on the offending binary log on the master. If the resulting dump produces gibberish in the middle of the dump (which I have seen a couple of times in my DBA career) you may have to skip to position 98 (MySQL 5.0) or 106 (MySQL 5.1) or 107 (MySQL 5.5) of the master's next binary log and start replicating from there (SOB :( you may need to use MAATKIT tools mk-table-checksum and mk-table-sync to reload master changes not on the slave [if you want to be a hero]; even worse, mysqldump the master and reload the slave and start replication totally over [if you don't want to be a hero])

If the mysqlbinlog of the master is completely readable after the top gibberish you saw, it is possible the master's binary log is fine but the relay log on the slave is corrupt (due to transmission/CRC errors). If that's the case, just reload the relay logs by issuing the CHANGE MASTER TO command as follows:

STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST='< master-host ip or DNS >',
MASTER_PORT=3306,
MASTER_USER='< usernmae >',
MASTER_PASSWORD='< password >',
MASTER_LOG_FILE='< MMMM >',
MASTER_LOG_POS=< PPPP >;
START SLAVE;

Where

MMMM is the last file used from the Master that was last processed on the Slave
PPPP is the last position used from the Master that was last processed on the Slave

You can get MMMM and PPPP by doing SHOW SLAVE STATUS\G and using

Relay_Master_Log_File for MMMM
Exec_Master_Log_Pos for PPPP

Try it out and let me know !!!

BTW running CHANGE MASTER TO command erases the slave's current relay logs and starts fresh.

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.