MySQL съедает всю память в системе
Posted by: Max Vasilets
Date: November 24, 2011 06:34AM
Date: November 24, 2011 06:34AM
Добрый день,
Я столкнулся с проблемой когда mysql вдруг начал съедать всю доступную память в системе, хотя ему чётко заданы настройки по управлению памятью такие как innodb_buffer_pool_size.. (полный список параметров ниже)
Сразу упомяну что на сервере 32Gb оперативной памяти и как только mysql ее полностью забивает система убивает процесс mysql, память освобождается и после этого mysql_safe опять запускает mysqld и память опять постепенно заполняется, и так повторяется все время.
Вот график загрузки памяти http://netmaster.com.ua/files/sys_mem_stat.png
Я точно вижу что всю память съедает MySQL.
В момент когда это началось ни какие настройки не менялись, только добавлялись новый базы и новые пользователи.
Всего на сервере около 28 баз и в каждой около 150 таблиц.
Все эти базы с таблицами что используют InnoDB за исключением нескольких баз под Wordpress, они используют MyISAM.
Опять же сразу подчеркну что mysqltuner показывает -
Maximum possible memory usage: 16.6G (52% of installed RAM)
но mysql забирает под себя больше чем 32Gb
Вот дополнительная информация:
#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
Буду всем очень благодарен за любой совет или помощь!
Я столкнулся с проблемой когда mysql вдруг начал съедать всю доступную память в системе, хотя ему чётко заданы настройки по управлению памятью такие как innodb_buffer_pool_size.. (полный список параметров ниже)
Сразу упомяну что на сервере 32Gb оперативной памяти и как только mysql ее полностью забивает система убивает процесс mysql, память освобождается и после этого mysql_safe опять запускает mysqld и память опять постепенно заполняется, и так повторяется все время.
Вот график загрузки памяти http://netmaster.com.ua/files/sys_mem_stat.png
Я точно вижу что всю память съедает MySQL.
В момент когда это началось ни какие настройки не менялись, только добавлялись новый базы и новые пользователи.
Всего на сервере около 28 баз и в каждой около 150 таблиц.
Все эти базы с таблицами что используют InnoDB за исключением нескольких баз под Wordpress, они используют MyISAM.
Опять же сразу подчеркну что mysqltuner показывает -
Maximum possible memory usage: 16.6G (52% of installed RAM)
но mysql забирает под себя больше чем 32Gb
Вот дополнительная информация:
#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
Буду всем очень благодарен за любой совет или помощь!
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.