MySQL filled all RAM on the system

Question

Good Day,

I've faced a problem when MySQL began to use all memory in system. After MySQL restart memory goes free but after some time filles again.

graph of memory usage: http://netmaster.com.ua/files/sys_mem_stat.png

There was no changes either in MySQL or system settings. Such situation appear presumably after adding database for Wordpress. Server only contain 28 databases, almost all of them using InnoDB, except database for Wordpress.

Here is detailed description:

uname -a

Linux Server 2.6.32-31-server #61-Ubuntu SMP Fri Apr 8 19:44:42 UTC 2011 x86_64 GNU/Linux

mysql --version

mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1

my.cnf

[mysqld]
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
max_allowed_packet      = 16M

log_slave_updates = 1
relay_log = mysql-relay-bin
relay-log-purge=1
skip-slave-start
character_set_server = utf8
character_set_client = utf8
bind-address =  0.0.0.0
log_error   = /var/log/mysql/error.log
skip-name-resolve
skip-locking
max_connections = 150
open-files-limit = 10240
tmpdir = /dev/shm
query_cache_size = 128M
table_cache = 2048
tmp_table_size = 64M
max_heap_table_size = 64M
thread_stack = 192K
thread_cache_size = 60
join_buffer_size = 64M
query_cache_limit=2M
key_buffer = 50M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_concurrency = 8
long_query_time = 10
log-slow-queries = /var/log/mysql/slow.log
binlog-format = ROW
log-bin         = /home/backup/data/mysql-updates/
expire_logs_days = 14
max_binlog_size = 1024M
innodb_file_per_table
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method=O_DIRECT
innodb_doublewrite=0
innodb_lock_wait_timeout = 50
innodb_support_xa=0
transaction-isolation = READ-COMMITTED

mysqltuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 148M (Tables: 308)
[--] Data in InnoDB tables: 33G (Tables: 3514)
[!!] Total fragmented tables: 423

-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 54m 41s (14M q [202.695 qps], 3K conn, TX: 81B, RX: 28B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 6.3G global + 70.2M per thread (150 max threads)
[OK] Maximum possible memory usage: 16.6G (52% of installed RAM)
[OK] Slow queries: 0% (348/14M)
[OK] Highest usage of available connections: 50% (76/150)
[OK] Key buffer size / total MyISAM indexes: 50.0M/71.9M
[OK] Key buffer hit rate: 100.0% (234M cached / 1K reads)
[OK] Query cache efficiency: 64.4% (7M cached / 11M selects)
[!!] Query cache prunes per day: 2399978
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 304K sorts)
[!!] Joins performed without indexes: 136460
[OK] Temporary tables created on disk: 10% (68K on disk / 637K total)
[OK] Thread cache hit rate: 98% (76 created / 3K connections)
[!!] Table cache hit rate: 1% (2K open / 148K opened)
[OK] Open file limit used: 0% (79/10K)
[OK] Table locks acquired immediately: 100% (468M immediate / 468M locks)
[!!] Connections aborted: 12%
[!!] InnoDB data size / buffer pool: 33.6G/6.0G

-------- Recommendations ---------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_cache (> 2048)
    innodb_buffer_pool_size (>= 33G)

mysqlreport

Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL 5.1.41-3ubuntu12.  uptime 0 19:59:11      Thu Nov 17 09:01:48 2011

__ Key _________________________________________________________________
Buffer used     1.91M of  50.00M  %Used:   3.82
  Current       9.35M            %Usage:  18.71
Write hit      96.73%
Read hit      100.00%

__ Questions ___________________________________________________________
Total          14.60M   202.9/s
  QC Hits       7.58M   105.4/s  %Total:  51.94
  DMS           5.10M    70.9/s           34.95
  Com_          1.91M    26.6/s           13.09
  COM_QUIT      3.99k     0.1/s            0.03
  -Unknown        557     0.0/s            0.00
Slow 10 s         360     0.0/s            0.00  %DMS:   0.01  Log:  ON
DMS             5.10M    70.9/s           34.95
  SELECT        4.21M    58.5/s           28.82         82.46
  UPDATE      390.79k     5.4/s            2.68          7.66
  INSERT      281.62k     3.9/s            1.93          5.52
  DELETE      222.67k     3.1/s            1.52          4.36
  REPLACE           0       0/s            0.00          0.00
Com_            1.91M    26.6/s           13.09
  set_option  941.16k    13.1/s            6.45
  commit      859.21k    11.9/s            5.88
  rollback     62.69k     0.9/s            0.43

__ SELECT and Sort _____________________________________________________
Scan            1.00M    14.0/s %SELECT:  23.87
Range         171.60k     2.4/s            4.08
Full join     137.44k     1.9/s            3.27
Range check         1     0.0/s            0.00
Full rng join       0       0/s            0.00
Sort scan     209.76k     2.9/s
Sort range     95.98k     1.3/s
Sort mrg pass       3     0.0/s

__ Query Cache _________________________________________________________
Memory usage   87.00M of 128.00M  %Used:  67.97
Block Fragmnt   7.45%
Hits            7.58M   105.4/s
Inserts         3.40M    47.3/s
Insrt:Prune    1.70:1    19.4/s
Hit:Insert     2.23:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate     477.60M    6.6k/s

__ Tables ______________________________________________________________
Open             2048 of 2048    %Cache: 100.00
Opened        160.71k     2.2/s

__ Connections _________________________________________________________
Max used           76 of  150      %Max:  50.67
Total           4.00k     0.1/s

__ Created Temp ________________________________________________________
Disk table     68.62k     1.0/s
Table         572.51k     8.0/s    Size:  64.0M
File               22     0.0/s

__ Threads _____________________________________________________________
Running             6 of   59
Cached             17 of   60      %Hit:  98.10
Created            76     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients            14     0.0/s
Connects          486     0.0/s

__ Bytes _______________________________________________________________
Sent           81.80G    1.1M/s
Received       28.58G  397.2k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           6.00G of   6.00G  %Used: 100.00
Read hit       99.99%
Pages
  Free              0            %Total:   0.00
  Data        383.39k                     97.50 %Drty:   0.00
  Misc           9830                      2.50
  Latched                                  0.00
Reads           4.36G   60.6k/s
  From file   527.77k     7.3/s            0.01
  Ahead Rnd     20752     0.3/s
  Ahead Sql     16100     0.2/s
Writes          3.45M    47.9/s
Flushes       465.81k     6.5/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits              16     0.0/s
Current             0
Time acquiring
  Total          2137 ms
  Average         133 ms
  Max             311 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads       604.30k     8.4/s
  Writes      465.56k     6.5/s
  fsync       252.99k     3.5/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created       3.95k     0.1/s
  Read          2.51M    34.9/s
  Written     465.81k     6.5/s

Rows
  Deleted     136.69k     1.9/s
  Inserted    270.04k     3.8/s
  Read         11.13G  154.7k/s
  Updated     240.46k     3.3/s

28 databases located on this server 25 of them are using InnoDB and 3 of them - MyISAM — Maxim, Nov 17 '11 at 13:28
Is the memory usage an issue? Databases like to use available RAM as cache. Unless you're seeing issues aside from high memory usage, then this isn't a problem at all. — Shane Madden, Nov 17 '11 at 16:50
issue is why mysql take more than 32GB if according to settings and mysqltunning only 16 GB dedicated for it - **** — Maxim, Nov 24 '11 at 10:30

score 1 · Answer 1 · answered Nov 17 '11 at 13:45

1

Interesting question and nice job of presenting relevant background.

That mysqltuner is reporting

[OK] Maximum possible memory usage: 16.6G (52% of installed RAM)

implies that either this is not the case and somethnig very weird is happening or that the memory usage is occurring somewhere other than in mysql.

I assume that the webserver/PHP is on a separate machine?

I'd take a ps -ef snapshot after restarting the mysql db and when the memory is full and try to identify where the additional memory is going (it might not be mysql).

Nearly 4000 tables! Really?

Server only contain 28 databases, almost all of them using InnoDB, except database for Wordpress.

So Wordpress needs 3500 tables?

It's famous as a memory hog but I didn't realise it created such a big database footprint.

answered Nov 17 '11 at 13:45

symcbean

21,009
1
31
52

**> I assume that the webserver/PHP is on a separate machine?** - webserver is on a separate machine. – Maxim Nov 17 '11 at 15:03
**> I'd take a ps -ef snapshot after restarting the mysql db and when the memory is full and try to identify where the additional memory is going (it might not be mysql).** I see that MySQL uses most of memory and this size is growing #ps axu | grep mysql -> _mysql 18973 60.0 70.4 24227304 23252144 ? Ssl Nov16 950:47 /usr/sbin/mysqld_ – Maxim Nov 17 '11 at 15:04
**> So Wordpress needs 3500 tables?** We use only one database for Wordpress and it contains 28 tables – Maxim Nov 17 '11 at 15:05

MySQL filled all RAM on the system

uname -a

mysql --version

1 Answers1