mysql的cpu高定位

原创

游戏开发司机 2022-11-01 14:10:23 ©著作权

文章标签 mysql sql 数据 文章分类 运维

©著作权归作者所有：来自51CTO博客作者游戏开发司机的原创作品，请联系作者获取转载授权，否则将追究法律责任

导致数据库CPU很高的原因有很多种，一般和慢SQL也有关（因为每条SQL要么占CPU高，要么占IO高，大体是这样）。

（1）、如果服务器有多个mysql实例，需要通过top命令看看是哪个mysql实例导致的cpu高（如果不是mysql导致的cpu高，需要优化其他导致cpu的程序）：

（2）、定位到占用cpu高的线程

通过top命令发现mysql占用CPU高，再看mysql进程下有多少线程占用CPU高：top -H -p [pid]

可以看到有一个mysql的线程占用的cpu较高

（3）、可以通过performance_schema.threads和information_schema.processlist表定位到该线程执行的sql（仅支持mysql5.7及以上，如果是mysql5.7以下的版本只能优化information_schema.processlist中执行时间较长的sql）

SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info FROM performance_schema.threads a,information_schema.processlist b WHERE b.id = a.processlist_id and a.THREAD_OS_ID=5909;

其中info字段就是该线程正在执行的sql

（4）、找到占用cpu较高的sql后，需要优化该sql，可以通过explain查看这个sql的执行计划，看看索引情况，也可以通过show profile和information_schema.OPTIMIZER_TRACE查看更详细

的执行计划。

（5）、如果有大量的慢sql，导致服务器cpu很高，mysql hang住，可以通过kill id（id在SHOW PROCESSLIST中显示），关掉疑似占CPU高的线程，以确认是否能让CPU降下来。

之后再优化相关的慢sql。

这里我提供一份优化mysql的配置参数：

[client]
#password       = [your_password]
port            = 3306
socket          = /tmp/mysql.sock
#socket          = /var/run/mysqld/mysqld.sock
#设置客户端字符集
#default-character-set = utf8


[mysqld]


# generic configuration options
port            = 3306
#socket          = /var/run/mysqld/mysqld.sock
socket          = /tmp/mysql.sock
basedir         = /usr/local/mysql
datadir         = /data/mysql_data
user            = mysql


#设置服务器失去，建议每次都显示指定
default-time-zone='+8:00'
#设置服务器端字符集，注意和客户端字符集一样，建议都设置utf8,防止中文乱码
character-set-server=utf8


skip-name-resolve
#是否支持federated 分布式引擎默认不支持
#federated
#设置sql校验模式，该设置影响到数据库对字段数据的校验严格程度
#sql_mode = STRICT_TRANS_TABLES
expire_logs_days = 7
#在同步配置中这个要额外注意否则容易导致主键冲突
#modify
auto-increment-increment = 2
auto-increment-offset = 1
back_log = 50
max_connections = 2500
max_connect_errors = 2000
table_open_cache = 1024
#external-locking
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 64
#这个变量是针对Solaris系统的，如果设置这个变量的话，mysqld就会调用thr_setconcurrency()。#这个函数使应用程序给同一时间运行的线程系统提供期望的线程数目。
thread_concurrency = 8
#无论是否有misam表建议都16-128M，如果misam表多那么建议设置为128M最大不要超#过256M
query_cache_type=0
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
#memlock
#5.5以下版本建议显示设置。5.5默认是innodb
default-storage-engine = innodb
thread_stack = 192K
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
#防止夸库更新混乱，同步里推荐的设置
#replicate_wild_do_table=tang_monitor_report.%
replicate_wild_do_table=%.%
#建议显示指定binlog和relaylog的名字，防止因修改hostname而导致出错
log-bin=mysql-bin
relay-log=mysql-relay-bin
#binlog记录格式，建议设置row，防止statement格式导致的数据不一直无法检测的问题
binlog_format=mixed
#设置是否将主库的更新写入binlog ，一般情况关闭，建议关闭
#log_slave_updates
#log_warnings
slow_query_log
slow_query_log_file=/var/log/mysql/myslow.log
long_query_time = 2


innodb_buffer_pool_instances=2
innodb_change_buffering =changes
innodb_old_blocks_time=1000
innodb_autoextend_increment=50
sync_binlog=100
innodb_open_files=1024
innodb_file_per_table =1


replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=mysql


open-files-limit = 8192
log-error = /var/log/mysql/error.log
#modify
server-id = 45140
#半同步配置参数，很简单吧，简单的让人震惊
#rpl_semi_sync_slave_enabled=1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000


#                                                                                                                                                    
#CHANGE MASTER TO MASTER_HOST='172.31.96.66',MASTER_LOG_FILE='mysql-bin.000011', #MASTER_LOG_POS=8717 ,MASTER_USER='slave',MASTER_PASSWORD='jajx' 


#slave库的时候有用，设置是否slave库为只读，建议开启
#read_only
#misam index 缓存如果misam表多建议适当的增大
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
#当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分#配的缓冲区。
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
#设置misam表修复的线程数
myisam_repair_threads = 1
#myisam表自动崩溃恢复级别
myisam_recover=  BACKUP,FORCE 
#设置innodb数据字典的缓存，一般16-20M基本可以，如果innodb表特别多可适当增大到#32M
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 8G
innodb_data_file_path = ibdata1:512M:autoextend
#5.1版本为（innodb_file_io_threads）及其之前的版本都是硬编码为4，默认是4即使修改也无用 ,5.5之后可以修改最大#不超过64
innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
#设置innodb内部线程并发数
innodb_thread_concurrency = 16
#设置日志刷新的方式
innodb_flush_log_at_trx_commit = 2
#设置binlog刷新方式，1表示提交前写入了二进制，但事务commit失败。二进制日志无法回#滚解决：设置innodb_support_xa=1（默认开启）,能够确保二进制日志和数据文件的同步
#sync_binlog = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 4M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#配置数据脏叶的比例
innodb_max_dirty_pages_pct = 75


#设置mysql 数据刷新的方式默认是fdatasync
# doublewrite flush logic. The default value is "fdatasync", another
# option is "O_DSYNC".
innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 15
#控制普通用户show database的权限
#skip-show-database
#控制是否支持UDF
log_bin_trust_function_creators = 1
# 控制是否slave线程随mysql server的重启而重启，建议开启
#skip_slave_start 
##之前是回滚当前query，现在是回滚整个事物，这样可以更快的释放资源
innodb_rollback_on_timeout = ON 
#设置连接的超时时间，如果不希望长连接可以设置相对较小的值，有个高手老王建议设置为10-15
interactive_timeout = 259200
#wait_timeout只作用于TCP/IP和Socket链接的线程，一般设置值和interactive_timeout一样
wait_timeout = 259200
##新连接时候用到，在高并发的系统里建议10-15默认10
connect_timeout=15
####主从复制里面slave检查主库是否正常并试图从新连接之前的等待时间的时间，默认1小时建议30秒
slave_net_timeout = 30


[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick


max_allowed_packet = 16M


[mysql]
no-auto-rehash


# Only allow UPDATEs and DELETEs that use keys.
#safe-updates


[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M


[mysqlhotcopy]
interactive-timeout


[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
#open-files-limit = 8192
#log-error = /var/log/mysql/error.log