☰

CPU 利用率 LAMP 堆栈

tag-icon • tag-icon mysql php centos amazon-web-services httpd
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 <[email protected]>
 >>  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)

答案1

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 搜索找到它），但还有其他的。

希望这些能有所帮助。我不像这里的一些用户那样是天才，但希望这些技巧能有所帮助。

答案2

您可以使用 Nginx 来前置 Apache。这将代表客户端向 Apache 发出 Nginx 代理请求，您也可以将压缩任务卸载到 Apache。如果您这样做，请尝试将最大 Apache 进程数减少到不会减慢服务器速度的程度。

您还可以配置 Nginx 来代替 Apache 提供静态内容。

此链接可以帮助您入门。

http://tumblr.intranation.com/post/766288369/using-nginx-reverse-proxy

相关内容