Julio Garcia Julio Garcia - 6 months ago 90
MySQL Question

Why is MySQL consuming so much memory?

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]:

lower_case_table_names = 1
max_allowed_packet = 32M
max_connections = 300
innodb_buffer_pool_size = 18G
innodb_buffer_pool_instances = 9
innodb_log_file_size = 1G


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 (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:

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 Source

Well, I resolve 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:


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download