在aws ubuntu服务器上mysql对服务器的高cpu使用率

eivgtgni  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(427)

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使用率

hsgswve4

hsgswve41#

没有任何慢查询
没有任何慢速查询,或者日志中没有显示任何慢速查询?我认为您的第一个prt调用将是检查查询日志记录是否如您所期望的那样工作。这可以很容易地使用任意锁来完成,而无需在系统上添加额外的负载。
假设真的没有超过1秒的查询,那么负载将由许多不到1秒的查询产生。您需要捕获它们,以确定优化工作的优先级。但是您可以先启用查询缓存(由于某些原因禁用了该缓存)。

相关问题