CPU 使用率 LAMP 堆棧

我們有一個執行 Magento 的 ec2 m2.4xlarge（centos 5.6、httpd 2.2、php 5.2.17 和 eaccelerator 0.9.5.3、mysql 5.1.52）。現在我們的流量激增，我們的頂部看起來像這樣：

top - 09:41:29 up 31 days,  1:12,  1 user,  load average: 120.01, 129.03, 113.23
Tasks: 1190 total,  18 running, 1172 sleeping,   0 stopped,   0 zombie
Cpu(s): 97.3%us,  1.8%sy,  0.0%ni,  0.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.4%st
Mem:  71687720k total, 36898928k used, 34788792k free,    49692k buffers
Swap: 880737784k total,        0k used, 880737784k free,  1586524k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
2433 mysql     15   0 23.6g 4.5g 7112 S 564.7  6.6  33607:34 mysqld
24046 apache    16   0  411m  65m  28m S 26.4  0.1   0:09.05 httpd
24360 apache    15   0  410m  60m  25m S 26.4  0.1   0:03.65 httpd
24993 apache    16   0  410m  57m  21m S 26.1  0.1   0:01.41 httpd
24838 apache    16   0  428m  74m  20m S 24.8  0.1   0:02.37 httpd
24359 apache    16   0  411m  62m  26m R 22.3  0.1   0:08.12 httpd
23850 apache    15   0  411m  64m  27m S 16.8  0.1   0:14.54 httpd
25229 apache    16   0  404m  46m  17m R 10.2  0.1   0:00.71 httpd
14594 apache    15   0  404m  63m  34m S  8.4  0.1   1:10.26 httpd
24955 apache    16   0  404m  50m  21m R  8.4  0.1   0:01.66 httpd
24313 apache    16   0  399m  46m  22m R  8.1  0.1   0:02.30 httpd
25119 apache    16   0  411m  59m  23m S  6.8  0.1   0:01.45 httpd

問題：

• 給 msyqld 更多的記憶體會幫助它記憶體查詢並更快地做出反應嗎？
• 如果是這樣，怎麼做？
• 除了將 mysql 和 php 拆分為單獨的伺服器（我們將要做的）之外，我們還可以/應該做些什麼？

謝謝！

更新：

這是我們的 my.cnf 以及 mysqltuner 的輸出。它看起來像一個記憶體問題。再次感謝！

# cat /etc/my.cnf
[client]
port            = ****
socket          = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/mnt/persistent/mysql
port=****
socket=/var/lib/mysql/mysql.sock
key_buffer = 512M
max_allowed_packet = 64M
table_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 128M
tmp_table_size = 128M
join_buffer_size = 1M
query_cache_limit = 2M
query_cache_size= 64M
query_cache_type = 1
max_connections = 1000
thread_stack = 128K
thread_concurrency = 48
log-bin=mysql-bin
server-id       = 1
wait_timeout = 300
innodb_data_home_dir = /mnt/persistent/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 20G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 48
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

# ./mysqltuner.pl

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 2G (Tables: 26)
[--] Data in InnoDB tables: 749M (Tables: 250)
[!!] Total fragmented tables: 262

-------- Security Recommendations  -------------------------------------------

-------- Performance Metrics -------------------------------------------------
[--] Up for: 31d 2h 30m 38s (680M q [253.371 qps], 2M conn, TX: 4825B, RX: 236B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 20.6G global + 15.1M per thread (1000 max threads)
[OK] Maximum possible memory usage: 35.4G (51% of installed RAM)
[OK] Slow queries: 0% (35K/680M)
[OK] Highest usage of available connections: 53% (537/1000)
[OK] Key buffer size / total MyISAM indexes: 512.0M/457.2M
[OK] Key buffer hit rate: 100.0% (9B cached / 264K reads)
[OK] Query cache efficiency: 42.3% (260M cached / 615M selects)
[!!] Query cache prunes per day: 4384652
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 38M sorts)
[!!] Joins performed without indexes: 100404
[OK] Temporary tables created on disk: 17% (7M on disk / 45M total)
[OK] Thread cache hit rate: 99% (537 created / 2M connections)
[!!] Table cache hit rate: 0% (1K open / 946K opened)
[OK] Open file limit used: 9% (453/5K)
[OK] Table locks acquired immediately: 99% (758M immediate / 758M locks)
[OK] InnoDB data size / buffer pool: 749.3M/20.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   Enable the slow query log to troubleshoot bad queries
   Adjust your join queries to always utilize indexes
   Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
   query_cache_size (> 64M)
   join_buffer_size (> 1.0M, or always use indexes with joins)
   table_cache (> 1024)

Would giving msyqld more memory help it cache queries and react faster?

你可以給 MySQL 更多的表記憶體，這會有所幫助，但我認為這對你來說不是太大的問題。由於 MySQL 程序的 CPU 使用率非常高，我相信這只是所有的連接都會減慢 MySQL 的速度。

我喜歡用來調整 MySQL 的一個不錯的網站是這個 MySQL 記憶體計算器： http ://www.omh.cc/mycnf/

我還建議您查看 GitHub 上的以下 Perl 腳本，因為它會檢查您的整個 MySQL 配置是否有任何錯誤。 http://mysqltuner.com

最後，我認為對您的情況有所幫助的東西是一些 HTTP 記憶體。如果您的站點非常動態（您的站點似乎是因為數據庫 CPU 使用率和記憶體非常高），那麼擁有一些 HTTP 記憶體將減輕 MySQL 的一些負擔。我個人使用 Varnish（因為我現在不能發布兩個以上的超連結，您可以通過簡單的 Google 搜尋將其拉出來），但也有其他的。

希望有幫助。我不像這裡的一些使用者那樣是天才，但希望這些提示會有所幫助。

引用自：https://serverfault.com/questions/335201