Why is MySQL consuming so much memory?

Question

I have mysql 5.6.36 database where the size is ~35G running on CentOS 7.3 with 48G of RAM.

[UPDATE 17-08-06] I will update relevant information here.

I am seeing that my server runs out of memory and crashes even with ~48G of RAM. I could not keep it running on 24G, for example. A DB this size should be able to run on much less. Clearly, I a missing something fundamental.

[UPDATE: 17-08-05] By crashes, I mean mysqld stops and restarts with no useful information in the log, other than restarting from a crash. Also, with all this memory, I got this error during recovery:

[ERROR] InnoDB: space header page consists of zero bytes in tablespace ./ca_uim/t_qos_snapshot.ibd (table ca_uim/t_qos_snapshot)

The relevant portion of my config file looks like this [EDITED 17-08-05 to add missing lines]:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
lower_case_table_names = 1
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
max_allowed_packet = 32M
max_connections = 300
table_definition_cache=2000
innodb_buffer_pool_size = 18G
innodb_buffer_pool_instances = 9
innodb_log_file_size = 1G
innodb_file_per_table=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

It was an oversight to use file per table, and I need to change that (I have 6000 tables, and most of those are partitioned).

After running for a short while (one hour), mytop shows this:

MySQL on 10.238.40.209 (5.6.36)    load 0.95 1.08 1.01 1/1003 8525 up 0+01:31:01 [17:44:39]
 Queries: 1.5M     qps:  283 Slow:    22.0         Se/In/Up/De(%):    50/07/09/01
 Sorts:     27 qps now:  706 Slow qps: 0.0  Threads:  118 (   3/   2) 43/28/01/00 
 Key Efficiency: 100.0%  Bps in/out: 76.7k/176.8k   Now in/out: 144.3k/292.1k

And free shows this:

# free -h
              total        used        free      shared  buff/cache   available
Mem:            47G         40G        1.5G        8.1M        5.1G        6.1G
Swap:          3.9G        508K        3.9G

Top shows this:

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                             
2010 mysql     20   0 45.624g 0.039t   9008 S  95.0 84.4  62:31.93 mysqld                                                              

How can this be? Is this related file per table? The entire DB could fit in memory. What am I doing wrong?

Answer 1

In your my.cnf (MySQL configuration) file:

Add a setting in [mysqld] block

[mysqld]

performance_schema = 0

---

For MySQL 5.7.8 onwards, you will have to add extra settings as below:

[mysqld]

performance_schema = 0

show_compatibility_56 = 1

NOTE: This will cut your Memory usage to more than 50%-60%. "show_compatibility_56" is optional, for some cases it works, better to check it once added to the config file.

Answer 2

Well, I resolved the issue. I appreciate all the insight from those who responded. The solution is very strange, and I cannot explain why this solves the problem, but it does. What I did was add the following line to my.cnf:

log_bin

You may, in addition, need to add the following:

expire_logs_days = <some number>

We have seen at least one instance where the logs accumulated and filled up a disk. The default is 0 (no auto removal). https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_expire_logs_days