mysql数据库调优篇章1--日志篇

SHOW VARIABLES LIKE ‘innodb_read_io_threads’;
SHOW VARIABLES LIKE ‘innodb_write_io_threads’;
SHOW VARIABLES LIKE ‘thread_cache_size’;
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
show global status like ‘Thread%’;

1.认识数据库中日志的作用

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令， 对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的 状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故 障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。 从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。

2.增加mysql数据库中my.ini 基本配置

查询这些变量
show VARIABLES like ‘%slow%’
show variables like ‘%log_bin%’;

# 配置mysql数据库存储数据的位置
datadir=E:/MySQL/Data
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

log-output=FILE
#开启通用查询日志记录 会记录SELECT、INSERT、UPDATE、DELETE 等操作
general-log=1
general_log_file="C:\ProgramData\MySQL\MySQL Server 8.0\Mytest-generaltest.log"
#开启慢查询日志  但是内存储  建议关闭  
slow-query-log=1
# 执行时间超过了这个10s阈值 
long_query_time=10
#指定慢查询日志中时间戳的格式。
log_timestamps = system
#指定慢查询日志文件路径
slow_query_log_file="C:\ProgramData\MySQL\MySQL Server 8.0\Mytest-slow.log"
# 开启记录错误日志
log-error="C:\ProgramData\MySQL\MySQL Server 8.0\Mytest-Err.err"
# Binary Logging.
#默认开启二进制日志文件  数据库默认开启的当数据库中的数据发生变化时，MySQL 会将这些变更操作记录到二进制日志中，以便于数据恢复、主从复制等操作。
log-bin="xxxxx-xxxx-bin"    

3.增加my.ini中参数配置

观察字段QUERY_SAMPLE_TEXT 具体sql执行语句
thread_cache_size 参数用于设置线程缓存的大小，它指定了服务器为处理客户端连接请求而预先分配的线程数目。
查询 show global status like ‘Threads_connected%’; 结果是525 峰值达到了500多，但是thread_cache_size才设置300，所以需要增加 数量，减少Threads_created 创建的数量
查询SHOW STATUS LIKE ‘Threads_connected’;结果的2倍或3倍

thread_cache_size=300    

指定在 MySQL 服务器接受新连接之前，允许在内核连接队列中等待的连接数目。它控制着 MySQL 服务器能够处理的最大连接数。

back_log=600

查询最大连接数，my.ini 中没有 max_connections参数，默认是151 ，可以手动配置
SHOW VARIABLES LIKE ‘max_connections’;

max_connections=1000

InnoDB 存储引擎的参数，用于配置数据库系统中读取和写入数据时所使用的 I/O 线程数量。
SHOW VARIABLES LIKE ‘innodb_read_io_threads’;
SHOW VARIABLES LIKE ‘innodb_write_io_threads’;
一般是当前服务器CPU核心数量的一半

innodb_read_io_threads = 8
innodb_write_io_threads = 8

查询 show global status like ‘Thread%’;

发现 Threads_created 记录的是数据库开启后一共创建的线程较多，说明 thread_cache_size设置的小了

thread_cache_size = 500

4.查看已经执行过的sql语句过去执行时间

SHOW VARIABLES LIKE ‘performance_schema’; 看是否开启
SELECT * FROM performance_schema.events_statements_summary_by_digest;
观察字段SCHEMA sql执行具体数据库名称
观察字段QUERY_SAMPLE_SEEN sql执行起始时间
观察字段 DIGEST_TEXT sql执行信息摘要
观察字段QUERY_SAMPLE_TEXT 具体sql执行语句
TIMER_WAIT：表示该 SQL 语句摘要的总执行时间（秒）。
LOCK_TIME：表示该 SQL 语句摘要在等待表锁上的时间（秒）。
QUERY_TIME：表示该 SQL 语句摘要的总查询时间（秒），包括等待时间。

5.找出慢查询的sql

找出执行时间较长的sql，设置sql

SHOW VARIABLES LIKE 'slow_query_log';

6.常用参数查询命令

SHOW VARIABLES;
show variables like 'profiling';

7.认识慢查询日志记录

# Time: 2024-05-09T15:10:58.145015+08:00
# User@Host: root[root] @ WIN-TLGUR8CSAR [192.168.1.2]  Id: 647518
# Query_time: 12.184095  Lock_time: 0.000198 Rows_sent: 213727  Rows_examined: 213727
use testbase;
SET timestamp=1715238717;
SELECT *  FROM `user`;

8.认识通用日志记录（记录增删改查操作）

2024-05-09T15:40:57.489468+08:00 7 Connect
2024-05-09T15:40:57.489592+08:00 7 Query SELECT @@skip_networking, @@skip_name_resolve, @@have_ssl=‘YES’, @@ssl_key, @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_crl, @@ssl_crlpath, @@tls_version
2024-05-09T15:40:57.490856+08:00 7 Quit
2024-05-09T15:41:34.690494+08:00 8 Connect root@localhost on using TCP/IP
2024-05-09T15:41:34.690780+08:00 8 Query SET NAMES utf8mb4
2024-05-09T15:41:34.691133+08:00 8 Init DB packing_test
2024-05-09T15:41:34.691212+08:00 8 Query SET PROFILING = 1
2024-05-09T15:41:34.691420+08:00 8 Query SHOW STATUS
2024-05-09T15:53:46.319141+08:00 12 Query SELECT COUNT(*) FROM performance_schema.threads

9.认识二进制文件binlog主要应用场景

10.总结

根据慢查询sql排查出查询时间较长的sql，导致长时间占用cpu
分析sql是否全查、是否索引失效、是否mysql参数配置是否合理，是否硬件资源不够用