MySQL CPU 使用率 200%

MySQL CPU 使用率 200%

在我的服务器中,MySQL 占用了超过 200% 的 CPU 使用率。我在使用 hibernate JPA 执行 MySQL 操作的 tomcat 服务器中运行 java war 文件,我也执行了 slowSQLQuerylogs,但没有花费超过 2 秒的时间,但由于 spring-boot JPA,执行的查询数量很高。

OS:  Ubuntu 18.04.1 LTS

H/W specification like: 
memory: 16GB 
Architecture: x86_64 
CPU op-mode(s): 32-bit, 64-bit 
Byte Order: Little Endian 
CPU(s): 4 
On-line CPU(s) list: 0-3 
Thread(s) per core: 1 
Core(s) per socket: 4 
Socket(s): 1 
NUMA node(s): 1 
Vendor ID: GenuineIntel 
CPU family: 6 
Model: 79 
Model name: Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz 
CPU MHz: 2300.033

mysqld.cnf 文件

key_buffer_size     = 128M  

max_allowed_pa​​cket = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
max_connections = 500
query_cache_limit = 8M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
innodb_lru_scan_depth=100

当我在 ubuntu 中运行 top 命令时,它有时显示 MySQL 的 CPU 使用率超过 200%。

> top

23:19:47 up 4:42, 1 user, load average: 0.86, 0.86, 0.83 Tasks: 167 total, 1 running, 116 sleeping, 0 stopped, 0 zombie %Cpu(s): 45.9 us, 4.8 sy, 0.0 ni, 47.5 id, 0.6 wa, 0.0 hi, 0.8 si, 0.4 st KiB Mem : 16424600 total, 4822956 free, 4830684 used, 6770960 buff/cache KiB Swap: 0 total, 0 free, 0 used. 11290580 avail Mem

PID   USER   PR  NI  VIRT     RES     SHR   S  %CPU   %MEM  TIME+     COMMAND
8675  mysql  20  0   3262732  856304  16244 S  162.8  5.2   74:18.57  mysqld

MySQL 性能调优给了我这个结果

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: perimetrix
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.27-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /database/mysql/mypm-aws.err(0B)
[!!] Log file /database/mysql/mypm-aws.err doesn't exist
[!!] Log file /database/mysql/mypm-aws.err isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 8.0G (Tables: 1130)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (IF(plugin='mysql_native_password', authentication_string, password) = '' OR IF(plugin='mysql_native_password', authentication_string, password) IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 2h 21m 0s (103M q [1K qps], 10K conn, TX: 959G, RX: 285G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 8.7G
[--] Other process memory: 0B
[--] Total buffers: 8.2G global + 1.1M per thread (500 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 8.4G (53.51% of installed RAM)
[OK] Maximum possible memory usage: 8.7G (55.48% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/103M)
[OK] Highest usage of available connections: 40% (202/500)
[OK] Aborted connections: 0.06%  (7/10980)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 101M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 187K sorts)
[!!] Joins performed without indexes: 53751600
[OK] Temporary tables created on disk: 0% (108 on disk / 70K total)
[OK] Thread cache hit rate: 98% (202 created / 10K connections)
[!!] Table cache hit rate: 2% (2K open / 83K opened)
[OK] Open file limit used: 0% (0/5K)
[OK] Table locks acquired immediately: 100% (237 immediate / 237 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/43.0K
[OK] Read Key buffer hit rate: 96.5% (258 cached / 9 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 8.0G/8.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.171875 %): 48.0M * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (13946932499 hits/ 13947178305 total)
[!!] InnoDB Write Log efficiency: 30.94% (117771 hits/ 380652 total)
[OK] InnoDB log waits: 0.00% (0 waits / 262881 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64:
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2000)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 8M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    table_open_cache (> 2000)
    innodb_buffer_pool_size (>= 8.0G) if possible.
    innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

我找不到导致问题的原因。

答案1

当我们遇到此类问题时,一个损坏的进程仍然处于活动状态。重新启动数据库实例后,高 CPU 使用率就消失了。

答案2

我找不到问题的原因

mysqltuner.pl 已经为您提供了问题列表。

反向 DNS 查找非常糟糕,并且您的 innodb 缓冲池很小(但池大小不会导致您的 CPU 使用率过高)。Hibernate 和性能从不出现在同一个句子中(除非伴随着“糟糕”) - 但 JDBC 池管理可能仍有改进的空间。但您没有告诉我们如何配置它。

我怀疑数据库缺少很多必需的索引。获取此数据的最佳方法是将 slow_query 阈值设置为 0 - 但这会产生大量磁盘 I/O(因此最好暂时这样做),然后查看 DBMS 最耗时的查询。使用 mysqldumpslow 来处理日志。

答案3

针对您的 AWS 参数组 (my.cnf [mysqld] 部分) 需要考虑的建议

innodb_buffer_pool_size=8G  # from 128M to use half of your RAM for data/index storage
innodb_buffer_pool_instances=8  # from 1 to reduce mutex contention
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function

免责声明:我是我的个人资料、网络个人资料中提到的网站的内容作者,我们可以在其中下载免费的实用脚本来提高性能和其他服务。

相关内容