mysql - 数据库服务器占用 CPU 超过 100%

Question

我的数据库服务器占用 CPU 超过 100%

当我运行 mysqltuner 时，给出以下推荐

]# sudo ./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.0.92-community
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 9347)
[--] Data in InnoDB tables: 477M (Tables: 1803)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 98

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 8h 25m 8s (87M q [430.053 qps], 534K conn, TX: 2B, RX: 2B)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 670.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 938.7M (30% of installed RAM)
[OK] Slow queries: 0% (1K/87M)
[!!] Highest connection usage: 100%  (101/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/610.0M
[OK] Key buffer hit rate: 96.9% (10B cached / 331M reads)
[OK] Query cache efficiency: 89.9% (73M cached / 81M selects)
[!!] Query cache prunes per day: 148657
[OK] Sorts requiring temporary tables: 0% (467 temp sorts / 560K sorts)
[!!] Joins performed without indexes: 11968
[OK] Temporary tables created on disk: 23% (334K on disk / 1M total)
[OK] Thread cache hit rate: 86% (71K created / 534K connections)
[!!] Table cache hit rate: 0% (80 open / 918K opened)
[OK] Open file limit used: 11% (117/1K)
[OK] Table locks acquired immediately: 96% (11M immediate / 11M locks)
[OK] InnoDB data size / buffer pool: 477.2M/500.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 80)


    Already my.cnf value is

[mysqld]
innodb_buffer_pool_size = 500M
max_heap_table_size = 64M
query_cache_limit = 1M
query_cache_size = 96M
query_cache_type = 1
table_cache = 128
thread_cache_size = 4
tmp_table_size = 96M

现在我可以在 my.cnf 中做什么请帮助我谢了

Answer 1

MySQL 吃得更多 100% 因为它可以这样做。想一想，您有超线程\多核\多处理器服务器(或某种组合)。

在双核 + typerthreading 系统上，您有 4 个虚拟 CPU。每个满载的CPU都会在监控程序中造成“100%负载”，例如top。因此，在该示例中，某些可以利用多个处理器的单个程序(mysql 服务器就是这样做的)可以消耗高达 400% 的资源。

超过 100% 的负载本身并不是一个错误。它只是某件事(好或坏)的症状。什么是真正的问题，迫使您查看 CPU 负载？

">100% 负载"可能是由高查询率或查询复杂性引起的。

有多种方法可以帮助服务器更快地执行查询

首先让我们尝试一下您的脚本建议的内容:

Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 96M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 80)

这可以解决查询和索引缓存子系统的一些问题。它还允许服务器消耗更多 RAM 并并行服务更多查询并提高 CPU 负载。

还可以考虑这样做:

• 检查查询以优化表结构，重新排列查询。但我认为，你们的指数还不错。

• 降低查询率(开玩笑;-))