☰

MySQL 占用过多 CPU

tag-icon • tag-icon mysql performance-tuning optimization
MySQL 占用过多 CPU

下面是我的 .cnf 文件，如果我看到我的 CPU 消耗了 99.99%，则我的 mysqld 命令。MySQL 服务器与远程计算机连接，这些计算机经常更新其中的数据，但我确保远程服务器打开连接、读取/写入/更新，然后关闭它。此外，远程服务器读取了很多数据。

我该怎么做才能减少 CPU 消耗。仅供参考，我使用的是 2 核 CPU 和 4GB RAM。

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice          = 0

[mysqld]
user          = mysqluser
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port          = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

还粘贴来自 mysqltuner 的输出。

 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)

我根本没有遇到这个问题，但是当我开始将 10 台远程服务器连接到这台 MySQL 服务器，并且当这十台服务器开始在这台 MySQL 服务器上执行SELECT查询时UPDATE，我发现 CPU 消耗有所上升。

答案1

我怀疑这里有几个问题正在堆积并导致问题。

  1. 磁盘 i/o 碎片化，可能效率低下。对每个表执行 OPTIMIZE TABLE 应该可以解决此问题并有所帮助。
  2. 内存调整非常糟糕，很可能会导致需要频繁交换的问题。
  3. 查询速度慢 - 这是 CPU 使用率高的主要原因。我会检查您的慢速日志，看看您可以通过添加索引/重新组织查询等来优化哪些操作。可能有一个相当常见的查询正在执行数万或数十万次比较，这会降低您的性能。

如果这些都不能将性能提升到您想要的水平，那么可能是时候扩展您的数据库服务器了。2 个核心并不能告诉我有关机器的处理能力的任何信息，而且 4 GB 的 RAM 在当今来说是相当小的。

如需更具体的建议，请在您的描述中添加更多细节。SSD 还是旋转磁盘？什么 CPU？什么级别的 RAM？每分钟有多少个查询？有多少个客户端，等等？

答案2

如果您能够稍微调整一下设置，我会在 my.cnf 中尝试这样做：

innodb_buffer_pool_size = 3000M

然后重新启动 mysql 并检查性能。这将允许您将更多数据库保留在内存中，从而减少您可能看到的磁盘/内存抖动。假设您有 4Gb 内存，并假设此服务器仅有的数据库服务器，您可以将缓冲池大小增加到系统内存的约 80％。

答案3

发生了什么：你必须尝试将一头大象放入仓鼠球中。

做什么：减少 mysql 的内存占用

. . . . .
. . . . .
[mysqld]
user                    = mysqluser
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 10
log-queries-not-using-indexes
expire_logs_days        = 10
max_binlog_size         = 100M

port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
wait_timeout            = 20
interactive_timeout     = 60

bind-address            = <IP-ADDRESS>
myisam-recover          = BACKUP

## REASONABLE MEMORY PARAMETERS
max_connections         = 64
thread_cache_size       = 2
thread_concurrency      = 4
key_buffer_size         = 64M
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 16M
query_cache_limit       = 2M
query_cache_size        = 32M
table_open_cache        = 512
max_allowed_packet      = 1M
. . . . .
. . . . .

答案4

您应该使用命令分析最频繁的查询EXPLAIN。输出将告诉您查询如何使用表索引，这直接影响性能。

相关内容