O MySQL preencheu toda a RAM no sistema

Question

Bom dia

Eu enfrentei um problema quando o MySQL começou a usar toda a memória no sistema. Após o MySQL, a memória de reinicialização é liberada, mas após algum tempo, ela é preenchida novamente.

gráfico de uso de memória: link

Não houve alterações nem no MySQL nem nas configurações do sistema. Tal situação aparece presumivelmente depois de adicionar banco de dados para o Wordpress. O servidor contém apenas 28 bancos de dados, quase todos usando o InnoDB, exceto o banco de dados do Wordpress.

Aqui está uma descrição detalhada:

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

    

Answer 1

Pergunta interessante e bom trabalho ao apresentar um histórico relevante.

Esse mysqltuner está relatando

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

implica que este não é o caso e algo muito estranho está acontecendo ou que o uso da memória está ocorrendo em algum lugar diferente do mysql.

Eu assumo que o servidor web / PHP está em uma máquina separada?

Eu pegaria um instantâneo ps -ef depois de reiniciar o banco de dados mysql e quando a memória estiver cheia e tentaria identificar onde a memória adicional está indo (pode não ser o mysql).

Quase 4000 mesas! Realmente?

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

Então o Wordpress precisa de 3500 tabelas?

Ele é famoso como um hog de memória, mas eu não sabia que ele criava uma pegada de banco de dados tão grande.