MySQL显示出非常高的CPU使用率。有时会超过200%。我登录了慢速查询。并且慢速查询时间设置为1s。不存在任何慢速查询。我使用的是代码点火器PHP。
我的my.cnf文件:
[mysqld]
innodb_io_capacity=2000
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_log_buffer_size=250M
innodb_thread_concurrency=0
innodb_buffer_pool_size=1000M
user = mysql
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
##
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
innodb_buffer_pool_size = 12G
key_buffer_size = 1G
max_allowed_packet = 256M
thread_stack = 192K
thread_cache_size = 100
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
max_connections = 300
#innodb_buffer_pool_instances=8
#innodb_read_io_threads=8
#innodb_write_io_threads=8
#open_files_limit = 1024
#table_open_cache = 400
server_id = 2
bind-address = 0.0.0.0
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
replicate-ignore-db=phpmyadmin
replicate-ignore-db=mysql
skip-name-resolve
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit=0
query_cache_size=0
sort_buffer_size = 2M
join_buffer_size = 128K
read_buffer_size = 128K
read_rnd_buffer_size=256K
我能做些什么来减少CPU使用量?有什么方法可以找到哪些查询导致的高cpu使用率吗
没有任何慢速查询
没有任何慢速查询,或者日志中没有显示任何慢速查询?我认为您的第一个调用是检查查询日志是否如您所期望的那样工作。这可以使用任意锁定轻松完成,而无需在系统上添加额外负载。
假设真的没有超过1秒的查询,那么负载就来自于许多不到1秒的查询。您需要将它们全部捕获,以确定调整工作的优先级。但是,您可以从启用查询缓存开始(由于某种原因,您已经禁用了查询缓存(。