mysql监控和优化(2)

时间:2024-09-18 09:05:26

mysql主从复制

3个线程完成复制:
主库1个线程负责记录数据库变更日志
从库1个线程负责拉取主库的变更日志
从库1个线程负责执行主库的变更日志
实现了获取事件和重放事件的解耦,允许异步进行。
复制的瓶颈:主库并行(多线程)写入和从库串行(单线程)写入,会造成主从延迟。

MySQL主从延时延时问题如何处理?
1.偶发性延时:
控制写入速度,削峰填谷。
2.频发性延时:
拆分数据库实现多点写入
最后一招:从库磁盘硬件升级为ssd。

mysql慢查询日志

慢查询日志中记录的是执行时间较长的query,也就是我们常说的slowquery,通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名。

配置mysql慢查询:Linux:在mysql配置文件my.cnf中增加:log-slow-queries=/opt/data/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)long_query_time=2 (记录超过的时间,默认为10s)log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启);Windows:在my.ini的[mysqld]添加如下语句:log-slow-queries = E:\web\mysql\log\mysqlslowquery.loglong_query_time = 2(其他参数如上)

配置mysql慢查询的命令:

set global slow_query_log=on;
set global long_query_time=1;#设置记录查询超过多长时间的sql
set global slow_query_log_file=‘/opt/data/slow_query.log’;#设置mysql慢查询日志路径,此路径需要有写权限。这种方式不用重启mysql服务。

查询mysql慢查询状态:

SHOW VARIABLES LIKE '%query%';使用这个语句可以看到当前mysql慢查询是否开启,以及mysql的慢查询日志文件在哪。
slow_query_log #是否开启慢查询
slow_query_log_file #日志的存放位置
long_query_time #超过多少秒的查询就写入日志。

mysql监控和优化(2)

注意:mysqldumpslow是liunx命令。 使用mysqldumpslow命令可以解析mysql慢查询日志。

Mysqldumpslow命令参数如下:
-s,是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
-t,是top n的意思,即为返回前面多少条的数据;
-g,后边可以写一个正则匹配模式,大小写不敏感的;

比如我本地输入的命令:按照sql执行时间最长的前20条sql:mysqldumpslow -s -t -t 20 -g 'select' /opt/data/slowquery_2016050921.log

mysql监控和优化(2)

针对占用资源最严重的sql进一步的分析是什么?
explain:查看sql的执行效率,帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们的查询。

mysql profile

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中,可以通过配置profiling参数来启用SQL剖析。该参数开启后,后续执行的SQL语句都将记录其资源开销,诸如IO,上下文切换,CPU,Memory等等。

开启 profile:
set @@profiling=1;
查看profile是否开启
select @@profiling;
1为开启,0为关闭
开启后只对当前session有效。

开启profile后,执行的sql都会被profile记录
show profiles;可以看到当前已经被记录的sql

show profile for query n;#n为show profile中的query_id

mysql监控和优化(2)

查看profile结果:

show profile可以看到sql执行计划中每步的执行时间,以及cpu、内存、io的消耗;show profile cpu for query 1;#查看cpu消耗;show profile block io for query 1;#查看io消耗;show profile memory for query 1;#查看cpu;也可以一起写;show profile cpu ,block io for query 1;

mysql监控和优化(2)