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

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

命令:

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

正常应该显示:mysqld is alive

2、获取mysql当前的状态值

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

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

Questions: SQL查询数

Threads: 当前连接数

Slow queries:慢查询数

Opens:已经打开的表的数量

Open_table 表示打开过的表数量

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

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

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

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

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

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

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

命令:

Show status like ‘Created_ tmp%’;

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子句的语句

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

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

8、多表 UPDATE语句

9、GROUP_CONCAT() 或COUNT(DISTINCT) 表达式的

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

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

Select_type值说明

7、连接数监控

命令:

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

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

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

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

Threads的状态有:

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

Threads_connected:当前打开的连接数。

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

Threads_running:未休眠的线程数。

命令:

show variables like ‘Thread%‘;

参数说明:

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

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

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

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

thread_stack 每个线程的堆栈大小

命令:show status like ‘Connections‘;

变量说明:

Connections:与MySQL服务器的连接尝试次数(成功或失败)

监控说明:

连接缓存命中率(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)= Key_reads/ Key_read_requests

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

原文地址:https://www.cnblogs.com/luoman/p/12579419.html

时间: 03-26

Mysql性能优化(二)--MySQL监控的相关文章

?二十种实战调优MySQL性能优化的经验

?http://www.searchdatabase.com.cn/showcontent_58391.htm [为查询缓存优化你的查询] 像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的.所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存. [EXPLAIN 你的 SELECT 查询] 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的. [当只要一行数据时使用 LIMIT 1] 当

mysql 性能优化方案 (转)

网 上有不少MySQL 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦与复杂,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来配置mysql是行不通的,最好能使用 status信息对mysql进行具体的优化. mysql> show global status; 可以列出mysql服务器运行各种状态值,另外,查询mysql服务器配置信息语句: mysql> show variables; 一

MySQL 性能优化---索引及优化

博主QQ:819594300 博客地址:http://zpf666.blog.51cto.com/ 有什么疑问的朋友可以联系博主,博主会帮你们解答,谢谢支持! 一.MySQL性能优化之-影响性能的因素 1.商业需求的影响 Myisam存储引擎内置一个计数器,count(*)时直接从计数器读取:而通过innodb存储引擎查找某个数据时,是必须扫描全表的,所以当执行对表的统计(即使用count(*)函数)时,myisam要比innodb要快的很多.所以一般在innodb上执行count(*)时一般要

MySQL配置文件mysql.ini参数详解、MySQL性能优化

MySQL配置文件mysql.ini参数详解.MySQL性能优化 my.ini(Linux系统下是my.cnf),当mysql服务器启动时它会读取这个文件,设置相关的运行环境参数. my.ini分为两块:Client Section和Server Section.   Client Section用来配置MySQL客户端参数.   要查看配置参数可以用下面的命令: show variables like '%innodb%'; # 查看innodb相关配置参数 show status like

MySQL 性能优化的最佳20多条经验分享

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的                    事,而这更是我们程序员需要去关注的事情. 当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语                句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的M

MySQL性能优化的最佳20+条经验

http://www.pythonclub.org/mysql/optimize-20-tips 今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存

20多条MySQL 性能优化经验分享

当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的.当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了. 这里最主要

MySQL性能优化的21个最佳实践

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库.希望下面的这些优化技巧对你有用. 1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存.这是提高性最有效的方法之一,而且这是被My

mysql 性能优化常用工具

mysql 性能优化常用工具: 常用性能检测工具 show engine innodb  status show full processlist information_schema.processlist explain create table innodb_table_monitor(a int) engine=innodb; innodb_locks innodb_lock_wait innodb_trx zabbix监控 pt-tools 工具 性能优化的两种方式: 1.执行计划的解

mysql性能优化学习笔记(1)优化目的、方向及数据库准备

前言: 最近参加面试,问到了很多关于mysql的优化方面的问题,回答的不是很好,也是因为原先做的项目流量不是很大,所以对mysql优化不是太了解,所以趁着周末,恶补一下. 本文来源于慕课网sqlercn老师所讲的<性能优化之mysql优化>,根据老师所讲的内容整理所得. 一.为什么需要优化mysql数据库  1.避免出现页面访问错误          由于数据库连接超时产生的5xx错误          由于慢查询造成页面无法加载          由于阻塞造成数据无法提交       2.增