Mysql性能优化(二)--MySQL监控

adsadadaddadasda 2020-03-27

1、检查监控服务是否正常提供;

   命令:

     mysqladmin -uroot -p密码 -h服务器主机地址 ping

     正常应该显示:mysqld is alive

Mysql性能优化(二)--MySQL监控

2、获取mysql当前的状态值

mysqladmin -uroot -p密码 -h服务器主机地址 status

 Mysql性能优化(二)--MySQL监控

uptime:服务器启动后的运行时间      

Questions: SQL查询数  

Threads: 当前连接数   

Slow queries:慢查询数   

Opens:已经打开的表的数量

Open_table 表示打开过的表数量  

Queries per second avg: 平均查询执行时间

3、获取当前数据库当前的连接信息

mysqladmin -uroot -p密码 -h服务器主机地址 ping  processlist

 Mysql性能优化(二)--MySQL监控

4、检查,修复,分析,优化Mysql_Server中相关的表

   Mysqlcheck -uroot -p密码 -h服务器主机地址 --all-databases

   注意:该命令应该在数据库使用不频繁的时候进行使用;

 Mysql性能优化(二)--MySQL监控

 5、查看mysql是否使用了临时表,临时表过多会使内存转化到磁盘上

   命令:

Show status like ‘Created_ tmp%’;

Mysql性能优化(二)--MySQL监控

created_tmp_tables=0 表示没有使用临时表

查看服务器对临时表的配置:

show variables where Variable_name in (‘tmp_table_size‘,‘max_heap_table_size‘);

    系统会在什么时候使用临时表:服务器在处理语句时会创建内部临时表用户没有办法直接控制临时表的使用

   1、使用union 关键词时

   2评估某些视图,例如使用TEMPTABLE算法 UNION或聚合的视图

   3、派生表的时候

   4为子查询或半联接实现而创建的表

   5包含一个ORDER BY子句和另一个GROUP BY子句的语句

   6DISTINCT结合 ORDER BY可能需要一个临时表

   7INSERT ... SELECT从同一表中选择并插入到该表中的语句,MySQL创建了一个内部临时表来保存中的行 SELECT,然后将这些行插入目标表中

   8多表 UPDATE语句

   9  表达式的

如何判断是否使用了临时表?

  要确定一条语句是否需要一个临时表,可以使用 EXPLAIN并检查该 Extra列是否显示 Using temporary对于派生或具体化的临时表,EXPLAIN 不一定会说Using temporary

 Mysql性能优化(二)--MySQL监控

Select_type值说明

 Mysql性能优化(二)--MySQL监控

7、连接数监控

命令:

show VARIABLES like "max_connections";  //最大连接数

show global status like ‘Threads_connected‘; //当前连接数

show global status like ‘Threads_running‘; //正在处理的连接数

show global status like ‘Thread%‘;   //查看进行情况

Threads的状态有:

 线程高速缓存中的线程数。

当前打开的连接数。

创建用于处理连接的线程数。如果 较大,则可能要增加 值。高速缓存未命中率可以计算为 / 

未休眠的线程数。

命令:

show variables like ‘Thread%‘;

参数说明:

thread_cache_size    服务器应缓存多少线程以供重用如果服务器每秒看到数百个连接,则通常应设置 足够高,以便大多数新连接使用缓存的线程

thread_handling 服务器用于连接线程的线程处理模型

no-threads(服务器使用一个线程来处理一个连接)

one-thread-per-connection(服务器使用一个线程来处理每个客户端连接

thread_stack 每个线程的堆栈大小

命令:show status like ‘Connections‘;

变量说明:

ConnectionsMySQL服务器的连接尝试次数(成功或失败)

监控说明:

连接缓存命中率(Threads_Cache_Hit)=(Connections-Threads_connected)/Connections*100

建议该值在90%左右,甚至更高

8、缓存监控

命令:

show global status like ‘Qcache_%‘;

变量说明:

Qcache_free_blocks 查询缓存中的可用内存块数

Qcache_free_memory 查询缓存的可用内存量

Qcache_hits         查询缓存命中数

Qcache_inserts     添加到查询缓存中的查询数

Qcache_lowmem_prunes 由于内存不足而从查询缓存中删除的查询数

Qcache_not_cached 非缓存查询的数量

Qcache_queries_in_cache 在查询缓存中注册的查询数

Qcache_total_blocks 查询缓存中的块总数

命令:show variables like ‘%Query_cache%‘;

变量说明:

have_query_cache         YES表示mysqld支持查询缓存,NO则不支持。

query_cache_limit         缓存的最大值,默认值是1M

query_cache_min_res_unit 查询缓存分配的块的最小大小

query_cache_size 分配用于缓存查询结果的内存量

query_cache_type 设置查询缓存类型

query_cache_wlock_invalidate 通常,当一个客户端获取表WRITE 上的锁时MyISAM,如果查询高速缓存中存在查询结果,则不会阻止其他客户端发出从该表读取的语句。将此变量设置为1会导致获取WRITE表的 锁,以使查询缓存中引用该表的所有查询无效。这会强制尝试访问该表的其他客户端在锁定生效时等待。

监控说明:

1、Qcache_free_blocks 如果大约等Qcache_total_blocks/2,则说明碎片非常严重;

2、如果Qcache_lowmem_prunes 的值在增加,并具有大量的自由块,意味着碎片导致查询正在被从缓存中永久删除;

3、缓存碎片率 = Qcache_free_blocks/Qcache_total_blocks*100%   如果超过20% 则可以使用FLUSH QUERY CACHE整理缓存碎片;

4、缓存利用率 = (query_cache_size - Qcache_free_memory)/query_cache_size*100%  

   如果缓存利用率在25%以下的话则说明query_cache_size设置过大,如果利用率在80%以上而且Qcache_lowmem_prunes>50的话,则说明query_cache_size可能有点小,或者是碎片太多;

9、索引缓存

命令:

show variables like ‘key_buffer_size%‘;

参数说明:

key_buffer_size    是用于索引块的缓冲区的大小

命令:

show status like ‘key%‘;

参数说明:

Key_blocks_not_flushed   MyISAM索引缓存中已更改但尚未刷新到磁盘 索引块数

Key_blocks_unused   MyISAM 索引高速缓存中未使用的块数

Key_blocks_used       MyISAM 索引高速缓存中使用的块数

Key_read_requests      MyISAM索引高速缓存中读取索引块的请求数

Key_reads         从磁盘到MyISAM索引高速缓存中的索引块的物理读取数

Key_write_requests 索引块写入MyISAM索引高速缓存的请求数 

Key_writes         MyISAM索引高速缓存到磁盘的索引块的物理写入次数 

监控说明:

1、索引缓存未命中率(key_cache_miss_rate)= / 

1%表示每100个索引中有1个在缓存中找不到,要直接从硬盘中读取

2、key_reads 建议<0.1%

3、索引缓存命中率

   Key_buffer_read_hits=(1-Key_reads/Key_read_requests)*100%

   Key_buffer_write_hits = (1-Key_writes/Key_write_requests)*100%

这两个值应该越大越好;

10、表扫描情况

命令:

show global status like ‘handler_read%‘;

变量说明:

Handler_read_first   索引中第一个条目的读取次数。如果此值很高,则表明服务器正在执行很多全索引扫描

Handler_read_key  基于键读取行的请求数。如果此值很高,则表明表已为查询正确索引。

Handler_read_last  读取索引中最后一个键的请求数

Handler_read_next   按键顺序读取下一行的请求数。如果要查询具有范围约束的索引列或进行索引扫描,则此值将增加

Handler_read_prev   按键顺序读取上一行的请求数。这种读取方法主要用于优化ORDER BY ... DESC

Handler_read_rnd   基于固定位置读取行的请求数。如果要执行很多需要对结果进行排序的查询,则此值很高。可能有很多查询需要MySQL扫描整个表,或者联接未正确使用键。

Handler_read_rnd_next   读取数据文件下一行的请求数。如果要进行大量表扫描,则此值较高。通常,这表明您的表未正确建立索引,或者未编写查询以利用拥有的索引。

命令:

show global status like ‘com_select‘;

变量说明:

Com_select 每次查询语句已被执行的次数;

监控说明:

表扫描率 = Handler_read_rnd_next/com_select

如果表扫描率超过4000,说明进行了太多表的扫描,很有可能索引没有建好,增加read_buffer_size 值会好一些,但最好不要超过8M;

 好了,以上就是MySQL性能优化的第二步操作了,针对MySQL的监控,更多技术交流请加QQ群:1085210541

相关推荐