title: Why is MySQL consuming so much memory?
body: <p>I have mysql 5.6.36 database where the size is ~35G running on CentOS 7.3 with 48G of RAM.</p>
  
  <p>[<strong>UPDATE</strong> 17-08-06] I will update relevant information <a href="https://github.com/julio-garcia-fc/mysql-problems" rel="noreferrer">here</a>.</p>
  
  <p>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.</p>
  
  <p>[<strong>UPDATE</strong>: 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:</p>
  
  <pre><code>[ERROR] InnoDB: space header page consists of zero bytes in tablespace ./ca_uim/t_qos_snapshot.ibd (table ca_uim/t_qos_snapshot)
  </code></pre>
  
  <p>The relevant portion of my config file looks like this [<strong>EDITED</strong> 17-08-05 to add missing lines]:</p>
  
  <pre><code>[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
  </code></pre>
  
  <p>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).</p>
  
  <p>After running for a short while (one hour), mytop shows this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And free shows this:</p>
  
  <pre><code># 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
  </code></pre>
  
  <p>Top shows this:</p>
  
  <pre><code>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                                                              
  </code></pre>
  
  <p>How can this be? Is this related file per table? The entire DB could fit in memory. What am I doing wrong?</p>