MySQL巡检.docx

MySQL巡检系统基本信息机型号IPCPU内存磁盘(业务)系统信息操作系统主机名操作系统巡检检查内容说明检查方法结果（异常需详细说明）正常输出结果系统配置检查操作系统版本#uname a正常 异常显示系统版本和核心补丁信息系统I/O检查检查系统I/O负载情况#iostat、top正常 异常Top中CPUS中的%wa与iostat中的%iowait不应同时有值系统CPU检查检查系统CPU负载情况#top、Cat/proc/cpuinfo正常 异常%wa（等待）的时间无或者较小系统内存检查检查系统内存负载情况#vmstat、free正常 异常Swap中的so有值证明内存不足系统网络检查检查系统网络连通性#ping、telnet正常 异常网络可连通MySQL监控类巡检1、 TPS/QPS1.1、 TPStps: Transactions Per Second，每秒事务数；TPS = Com_commit/s + Com_rollback/sCom_commit /s= mysqladmin extended-status -relative -sleep=1|grep -w Com_commitCom_rollback/s = mysqladmin extended-status -relative -sleep=1|grep -w Com_rollback理想状态：参照日常数，当出现峰值时告警1.2、 QPSqps: Queries Per Second每秒查询数；QPS = mysqladmin extended-status -relative -sleep=1|grep -w Questions理想状态：参照日常数，当出现峰值时告警2、 innodb_buffer_pool_statusinnodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数（逻辑读请求数）innodb_buffer_pool_write_requests: 平均每秒向innodb缓冲池的写次数innodb_buffer_pool_pages_dirty: 平均每秒innodb缓存池中脏页的数目innodb_buffer_pool_pages_flushed: 平均每秒innodb缓存池中刷新页请求的数目innodb缓冲池的读命中率innodb_buffer_read_hit_ratio =( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100Innodb缓冲池的利用率Innodb_buffer_usage = ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100理想状态：参照日常数，当出现峰值时告警；当缓冲池的读命中率较低时可以适当的增大innodb_buffer_pool或检查SQL语句3、 slow_queries status执行时间超过long_query_time秒的查询的个数理想状态：查询值波动不大，若出现大量慢查询，需检查近期上线的SQL语句4、Sort statusmysql show global status like %sort% ; Sort_merge_passes Sort_range Sort_rows Sort_scan 理想状态：Sort_merge_passes表示当需要排序时，在排序缓冲中无法将结果完全存放，则将会基于磁盘创建临时文件进行排序。如果该值较高，则应提高sort_buffer_size大小。最好的办法是找到是由哪些排序SQL造成的。5、select status 5.1、select_scan对表进行完全扫描的联接的数量mysql show global status like select_scan ;理想状态：恒定的值，不会随时间增长而有明显的增长。5.2、select_full_join没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能。mysql show global status like select_full_join ;理想状态：出现full join的次数，如果该值不为0，需要检查表上是否有合适的索引6、Lock statusmysql show global status like table_locks% ;table_locks_immediate表示立即释放表锁数;table_locks_waited表示需要等待的表锁数;理想状态：table_locks_waited如果该值很大，而且性能很慢，建议针对业务拆分主表7、Thread statusmysql show global status like thread%;Threads_cached Threads_connected Threads_created Threads_running 理想状态：threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，MySQL参数类巡检1、 Connections settings1.1、max_connections查询服务器最大连接数mysql show variables like max_connections;查询服务器响应的最大连接数mysql show global status like max_used_connections;理想状态：max_used_connections / max_connections * 100% 85%；error_log中未出现MySQL: ERROR 1040: Too many connections该类错误。1.2、Aborted_connects查询服务器尝试已经失败的MySQL服务器的连接的次数；mysql show global status like Aborted_connects;理想状态：固定值，不随时间增长而增长（或增长幅度较小），若该数出现持续增长，需检查连接状况或错误日志，定位异常连接主机（一般为应用账号密码问题）。1.3、Aborted_clients查询服务器异常客户端数（由于客户没有正确关闭连接已经死掉，已经放弃的连接数量）；mysql show global status like Aborted_clients;理想状态：固定值，不随时间增长而增长（或增长幅度较小），若该数出现持续增长，需检查连接状况或错误日志，定位异常连接主机（一般为程序逻辑开启连接后未正常关闭）。2、innodb settings2.1、innodb_buffer_pool_size查询innodb存储引擎数据缓存大小mysql show variables like innodb_buffer_pool_size ;理想状态：建议设置为物理内存的60%80%，或根据业务决定2.2、innodb_io_capacity 配置刷新I/O操作性能，此参数可以限制I/O操作mysql show variables like innodb_io_capacity ;理想状态：根据实际磁盘情况设置innodb_io_capacity磁盘配置200单盘SAS/SATA2000SAS*12 RAID 105000SSD20000FUSION-IO2.3、innodb_flush_neighbors 刷新相同extent脏块mysql show variables like innodb_flush_neighbors ;理想状态：默认为1，建议开启，在SSD环境建议配置为02.4、innodb_print_all_deadlocks记录死锁记录mysql show variables like innodb_print_all_deadlocks ;理想状态：默认关闭，建议开启2.5、innodb_lock_wait_timeoutInnoDB行锁等待时间mysql show variables like innodb_lock_wait_timeout ;理想状态：默认为50秒，建议配置为5秒2.6、innodb_flush_method 配置刷新方法mysql show variables like innodb_flush_method ;理想状态：Linux系统MySQL建议配置为：O_DIRECT3、Log Settings3.1、binlog settings3.1.1、binlog_formatbinlog日志格式mysql show variables like binlog_format ;理想状态：建议配置为row格式，特殊业务情况另考虑3.1.2、binlog_rows_query_log_events写log 事件（例如 行操作日志时间）到binglogmysql show variables like binlog_rows_query_log_events ;理想状态：默认为：0，建议配置为：1注：此参数需要在binlog格式为ROW下配置3.1.3、sync_binlog控制binlog 同步磁盘方式当sync_binlog=1时，所有的事务都在提交前写入binlog。因此即使binlog事件遇到意外重启，一些在prepared状态的binlog会丢失。这导致服务器在恢复数据时自动回滚这些事务。这确保了从binlog不丢失事务，因此是最安全的选项。事实上，这增加了同步到磁盘的总次数。但是从MySQL5.6开始，已经支持组提交和合并同步了，这使得出现性能问题的可能性最小化了。当sync_binlog=0时，mysql-server并不把binlog同步到磁盘，而是依赖操作系统把binlog的内容同步到磁盘。因此，当出现掉电或操作系统崩溃时，很可能出现已经提交的事务没有被同步到磁盘的情况。因此mysql在自动恢复时无法恢复这些事务，他们从binlog中丢失了。所以说，新的默认设置sync_binlog=1更安全。mysql show variables like binlog_rows_query_log_events ;理想状态：设置sync_binlog=1更安全。3.1.4、expire_logs_days自动清理二进制日志的天数mysql show variables like expire_logs_days ;理想状态：根据备份策略与业务要求设置（如备份保留的天数是一周，则expire_logs_days可设置为7，如：8，业务要求随时能提取1年内的数据，则设置365）。3.1.5、master_info_repository slave 记录 master 状态和连接信息是否放在文件（）或者是写表（mysql.slave_master_info）mysql show variables like master_info_repository ;理想状态：默认为：file，建议配置为：table3.1.6、relay_log_info_repositoryslave 上的relay log的位置信息写在文件（）或者是写表（mysql.slave_relay_log_info）mysql show variables like relay_log_info_repository ;理想状态：默认为：file，建议配置为：table3.1.7、slave_parallel_type& slave_parallel_workers并行复制方法（库级别，表级别）mysql show variables like slave_parallel_type ;并行复制线程数mysql show variables like slave_parallel_workers ;理想状态：slave_parallel_type默认为：DATABASE，配置为：LOGICALC_CLOCKSlave_parallel_workers默认为：0，配置为：163.1.8、relay_log_recovery在服务启动时自动执行relay log 恢复操作mysql show variables like relay_log_recovery ;理想状态：默认关闭，建议开启3.2、slowlog settings4.2.1、slow_query_log是否开启慢查询日志mysql show variables like slow_query_log ;理想状态：默认为关闭慢查询日志，建议开启3.2.2、Othersslow_query_log_file慢查询日志记录文件long_query_time设置慢查询日志的时间阀值（单位：秒）min_examined_row_limit设置检查行数小于多少行数的查询不记录到慢查询日志log_queries_not_using_indexes开启后记录没有使用索引查找说有行的查询；也会记录使用索引查询了所有行的查询理想状态：根据业务需求设置3.3、generallog settings记录所有SQL语句，包含查询甚至语法错误的语句都会被记录mysql show variables like generallog settings ;理想状态：不建议开启。在做测试的时候想知道MySQL都执行了什么命令的时候可以开启。4、mysqld settings以下参数建议在配置文件中体现：参数项参考值参数解释socket /tmp/mysql.sock在unix环境下，指定用于本地连接的套接字文件pid_file /opt/mysql/mysql.pidMySQL的进程文件路径名称port 3306MySQL的监听端口log_bin /opt/mysql/data/bin.log开启数据库二进制log_bin_index/opt/mysql/data/bin.index二进制日志文件名的索引文件server-id 2服务器ID，在复制环境中主从服务器的唯一标识datadir /opt/mysql/dataMySQL数据目录路径user mysqlMySQL服务的运行用户interactive_timeout 300关闭一个交互式连接等待的时间wait_timeout 300关闭一个非交付时连接等待的时间read_buffer_size 16777216MyISAM表顺序扫描时分配的读入缓冲区大小read_rnd_buffer_size 33554432MyISAM表随机扫描时分配的读入缓冲区大小sort_buffer_size 33554432每个回话进行排序的缓存大小lower_case_table_names 1表名大小写敏感设置character_set_serverutf8mb4MySQL服务器的默认字符集skip_name_resolve 1客服端连接数据库时，不进行主机名解析max_connections 2000MySQL服务器允许的客服端最大连接数transaction_isolation READ-COMMITTEDMySQL服务器的默认事务隔离级别join_buffer_size 1342177