안녕하세요,
MySQL이 시스템의 모든 메모리를 사용하기 시작했을 때 문제에 직면했습니다. MySQL을 다시 시작한 후 메모리는 무료가 되지만 일정 시간이 지나면 다시 채워집니다.
메모리 사용량 그래프: http://netmaster.com.ua/files/sys_mem_stat.png
MySQL이나 시스템 설정에는 변경 사항이 없습니다. 이러한 상황은 아마도 Wordpress에 데이터베이스를 추가한 이후에 나타날 것입니다. 서버에는 28개의 데이터베이스만 포함되어 있으며 Wordpress용 데이터베이스를 제외하고 거의 모두 InnoDB를 사용합니다.
자세한 설명은 다음과 같습니다.
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 --버전
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1
내.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)
mysql 보고서
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
답변1
흥미로운 질문이고 관련 배경을 제시하는 훌륭한 작업이었습니다.
mysqltuner가 보고하고 있는 것
[확인] 가능한 최대 메모리 사용량: 16.6G(설치된 RAM의 52%)
이는 사실이 아니며 매우 이상한 일이 발생하거나 mysql이 아닌 다른 곳에서 메모리 사용량이 발생하고 있음을 의미합니다.
웹서버/PHP가 별도의 시스템에 있다고 가정합니까?
mysql db를 다시 시작한 후 메모리가 가득 차면 ps -ef 스냅샷을 찍고 추가 메모리가 어디로 가는지 식별하려고 합니다(mysql이 아닐 수도 있음).
테이블 수는 약 4000개! 정말?
서버에는 28개의 데이터베이스만 포함되어 있으며 Wordpress용 데이터베이스를 제외하고 거의 모두 InnoDB를 사용합니다.
그렇다면 Wordpress에는 3500개의 테이블이 필요합니까?
메모리를 많이 차지하는 것으로 유명하지만 이것이 그렇게 큰 데이터베이스 공간을 차지한다는 것을 깨닫지 못했습니다.