mysql 调优相关

越小越好,key_read_requests是从cache中读索引的请求，而key_reads是从物理硬盘上读索引的次数 。我的理解是，每次需要使用索引的时候就会先向cache中请求，cache中没有该索引的时候就再从硬盘上读！ Key_read_requests      | 15500573217 | | Key_reads              | 838756      | 这样才会有所谓的索引失效率 The cache miss rate can be calculated as Key_reads/Key_read_requests.

直以来，多数人在使用MyISAM时都是按照增大Key_read_requests / Key_reads的原则来设置key_buffer_size的，没想到这竟然是错误的！这次给大家醍醐灌顶的仍然是MySQL Performance Blog，详细描述参考：Why you should ignore MySQL’s key cache hit ratio。 Key_read_requests和Key_reads就是两个计数器，它们的含义如下： Key_read_requests：从缓存读取索引的请求次数。 Key_reads：从磁盘读取索引的请求次数。 通常人们认为Key_read_requests / Key_reads越大越好，否则就应该增大key_buffer_size的设置，但通过计数器的比例来调优有两个问题： 问题一：比例并不显示数量的绝对值大小 问题二：计数器并没有考虑时间因素 虽说Key_read_requests大比小好，但是对于系统调优而言，更有意义的应该是单位时间内的Key_reads： Key_reads / Uptime 你可以通过命令行得到一个实时的数据结果，比如： # mysqladmin ext -ri10 | grep Key_reads | Key_reads                         | 83777189     | | Key_reads                         | 211          | | Key_reads                         | 177          | | Key_reads                         | 202          | 提示：命令里的mysqladmin ext其实就是mysqladmin extended-status，你甚至可以简写成mysqladmin e。 其中第一行表示的是汇总数值，所以这里不必考虑，下面的每行数值都表示10秒内的数据变化，从这份数据可以看出每10秒系统大约会出现200次Key_reads访问，折合到每1秒就是20次左右，至于这个数值到底合理与否，就由服务器的磁盘能力而定了。 顺便说一句，为啥数据按10秒取样，而不是直接按1秒取样？这里看看按1秒的结果： # mysqladmin ext -ri1 | grep Key_reads | Key_reads                         | 83776743     | | Key_reads                         | 7            | | Key_reads                         | 7            | | Key_reads                         | 38           | 可以看到，由于时间段过小，数据变化比较剧烈，不容易直观估计大小，所以通常数据按照10秒或者60秒之类的时间段来取样是更好的。 忘记：Key_read_requests / Key_reads 牢记：Key_reads / Uptime

一般数据库优化分sql语句优化和数据库服务器参数优化，数据库服务器参数优化是DBA可以独立完成的，但是sql语句优化就必须和开发人员协同完成。 现在我们先不谈优化的实施，我们先研究下如何优化，优化哪里，我们要找准数据库性能的瓶颈，有的放矢，这样才能让优化立竿见影。 如何找到mysql数据库得瓶颈呢？如何提前发现mysql数据库可能出现的瓶颈呢？这里又分两大方面。         一方面是架构的设计和业务的类型，另一方面是通过观察mysql数据库的状态，好似中医的望闻问切。         关于第一个大的方面，需要注意的是。在业务前期，一定要确定业务的类型，是OLAP系统还是OLTP系统，数据量到底有多大，并发量到底有多大，查询居多，还是修改插入居多，需不需要支持事务和外键约束等信息。          因为这些信息可以帮助我们在设计数据库架构，选取数据库引擎的时候有很大帮助。还有就是硬件层面的需求，到底多大业务，需要多少的硬件资源，硬件支援才压力承受范围内，可以得到很好的性能，如果超过了压力承受范围，那么性能会下降的很厉害，并且硬件的各个组成部分要匹配，不要出现某个部分太差，原因大家都应该知道的。   下面举两个简单的案例 案例一、一个公司的数据库出现这样一个问题，一个myisam引擎的表，只有几百行的数据，但是table.myd文件却占了数十个G的空间，每次操作这个表的时候，速度就奇慢。 通过explain查看，又是正常的，这个问题在很多数据库上都存在，用oracle的话来说，就是高水位和块回收的问题。如果找到原因 optimize table tablename，优化一下表就可以了。 由于那个表操作很频繁，需要经常优化，这样就增加了DBA或者运维的工作量，这种问题其实在当初设计的时候，选用memcache引擎就比较合适，但是web应用的话还是比较适合memcached。   案例二、一个考试系统，当考试完成的时候，大家一交卷，服务器垮掉了，这是为什么呢？ 因为大家都想要同时往一个表里写数据，但是等待表锁很严重，最后服务器挂掉了，所有数据未保存，这种情况在业务设计之初，就应该考虑到，使用innodb引擎，就可以解决这个问题。 应为innodb引擎室行锁对这种大并发的写入操作承受力很强。现在的考试系统已经比较完善，大部分是边做题，边提交数据库，就算这样，innodb引擎在这种应用还是很有优势的。           这些情况都还好，能够修复，不会对业务造成的影响一般，如果一个架构前期没有设计好，等到开发完成，投入使用了，发现有问题，再返回修改，修改量和测试的工作量是相当庞大的，结果就是浪费资金，浪费时间。     本文出自 “fenghao.cn's Soft..” 博客，请务必保留此出处http://linuxguest.blog.51cto.com/195664/455251  今天得重点是第二方面，如何从数据库得状态，发现数据库得瓶颈，或者可能出现瓶颈的地方。主要针对myisam存储引擎 1、慢查询日志 打开、并观察是否有慢查询日志，具体花费多少时间算慢查询，可以根据数据量，和并发量来综合衡量，如果要做sql语句优化的话，一般1s以上的都算慢查询，通过满查询日志找出1s以上的查询，使用explain和prifile分析，在这里提一点，mysql的优化工具没有oracle这种大型数据库好用，没有现成统计某条语句执行的次数的工具，所以只有我们自己开发脚本，通过bin-log或者查询日志来提取某条语句的执行频率，然后从执行频率最高的语句开始逐条分析，看看是否可以优化，比如一条语句，每天执行100W次，你优化后，就算每次提升0.1秒，但是对于总的性能提升效果还是很明显的。   2、最重要的状态参数key_read_requests和key_reads. key_read_requests表示从缓存读索引的请求数，key_reads表示没有在缓存中找到索引，从硬盘上读取索引的次数，这里就是为什么使用索引作为where子句的关键字，速度快的原因，如果key_reads过高，就需要调大Key_buffer_size。   3、关于索引使用的情况 Handler_read_first \\索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。 Handler_read_key   \\根据索引读第一行的请求数，该值高，说明索引设置正确 Handler_read_rnd    \\根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键 Handler_read_rnd_next \\在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引，这个值 要注意一下，不是做一次全表扫描纪录一次，比如做一次全表扫描有多少行，这里就会增加多少数字，表连接和子查询有多条结果，也会累计，所以不一定是全表扫描，具体问题具体分析。   4、创建临时表和临时文件的次数 Created_tmp_disk_tables  \\如果此值较大，证明tmp_table_size较少，需要调大。  Created_tmp_files Created_tmp_tables   5、opened table的数量 open_table_definitions \\缓存的.frm文件的数量 open_tables \\当前打开了多少表 opened_tables \\一共打开了多少表 opened_table_definitions \\总共缓存过多少.frm文件 如果opened_tables和opened_table_definitions的数量过大，那么证明需要优化table_open_cache table_open_cache \\该值设置缓存.frm表定义的数量   6、创建的连接线程数 threads_cached \\已经缓存的连接线程数 threads_connected \\当前正处于连接的线程数，和 show processlist数量相等 threads_created  \\一共创建过多少个连接线程，如果远远大于threads_connected，并且threads_cached=thread_cache_size的值，那么需要调大thread_cache_size的值   7、表锁，确定是否有锁资源的争抢 Table_locks_immediate \\立即获得表锁 Table_locks_waited \\等待表锁，如果太高，就要考虑优化程序结构，语句，或者替换存储引擎   8、排序的状态 Sort_merge_passes \\排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。 Sort_range \\在范围内执行的排序的数量。 Sort_rows \\已经排序的行数。 Sort_scan  \\通过扫描表完成的排序的数量   9、查询缓存 这个值对性能影响很重要，但是比较费内存，所以放在最后 query_cache_type是查询缓存的开关，此参数有三个值，分别的含义如下：  0也就是off //此值设定服务器不支持查询缓存，select不会从缓存中检索结果也不会把结果写到缓存中。  1也就是on //如果要启用，通常设置这个值，select语句默认会从缓存中检索结果页会把查询的结果放到缓存中，除非select语句包含select sql_no_cache  2 //该值告诉服务器只对使用了 select sql_cache的语句使用查询缓存功能。 以下这些是查询缓存的相关设置值。 mysql> show global variables like 'query%';          +------------------------------+-----------+ | Variable_name                | Value     | +------------------------------+-----------+ | query_alloc_block_size       | 8192      | | query_cache_limit            | 1048576   |  \\查过这个值不缓存 | query_cache_min_res_unit     | 4096      |    | query_cache_size             | 102400000 |  \\查询缓存可以使用的内存大小 | query_cache_type             | ON        |  \\是否启用查询缓存 | query_cache_wlock_invalidate | OFF       | | query_prealloc_size          | 8192      | +------------------------------+-----------+ 以下这些是查询缓存的状态值。 mysql> show global status like 'qcache%';          +-------------------------+-----------+ | Variable_name           | Value     | +-------------------------+-----------+ | Qcache_free_blocks      | 1153      |  \\空闲的query cache块 | Qcache_free_memory      | 100094608 |  \\空闲的query cache内存 | Qcache_hits             | 259503    |  \\查询缓存的命中数量--重要 | Qcache_inserts          | 357422    |  \\从缓存查询结果没命中的次数 | Qcache_lowmem_prunes    | 0         |  \\由于cache_size过小，结果从内存中被删                 除的次数 | Qcache_not_cached       | 5187      | \\没有被缓存的次数 | Qcache_queries_in_cache | 1474      | \\缓存中包含的结果数量，如果此值和Qcache_lowmem_prunes值较小，qcache_inserts很大，则可能是结果变坏太快，被cache的结果一旦变化就会从此值中删除 | Qcache_total_blocks     | 4129      | +-------------------------+-----------+ 缓存的使用率=Qcache_hits/（Qcache_hits+com_select）

mysql性能监控指标 1.系统mysql的进程数 ps -ef | grep "mysql" | grep -v "grep" | wc –l 2.Slave_running mysql > show status like 'Slave_running'; 如果系统有一个从复制服务器，这个值指明了从服务器的健康度 3.Threads_connected mysql > show status like 'Threads_connected'; 当前客户端已连接的数量。这个值会少于预设的值，但你也能监视到这个值较大，这可保证客户端是处在活跃状态。 4.Threads_running mysql > show status like 'Threads_running'; 如果数据库超负荷了，你将会得到一个正在（查询的语句持续）增长的数值。这个值也可以少于预先设定的值。这个值在很短的时间内超过限定值是没问题的。当Threads_running值超过预设值时并且该值在5秒内没有回落时， 要同时监视其他的一些值。   5.Aborted_clients mysql > show status like 'Aborted_clients'; 客户端被异常中断的数值，即连接到mysql服务器的客户端没有正常地断开或关闭。对于一些应用程序是没有影响的，但对于另一些应用程序可能你要跟踪该值，因为异常中断连接可能表明了一些应用程序有问题。 6.Questions mysql> show status like 'Questions'; 每秒钟获得的查询数量，也可以是全部查询的数量，根据你输入不同的命令会得到你想要的不同的值。 7.Handler_* mysql> show status like 'Handler_%'; 如果你想监视底层（low-level）数据库负载，这些值是值得去跟踪的。 如果Handler_read_rnd_next值相对于你认为是正常值相差悬殊，可能会告诉你需要优化或索引出问题了。Handler_rollback表明事务被回滚的查询数量。你可能想调查一下原因。 8.Opened_tables mysql> show status like 'Opened_tables'; 表缓存没有命中的数量。如果该值很大，你可能需要增加table_cache的数值。典型地，你可能想要这个值每秒打开的表数量少于1或2。 9.Select_full_join mysql> show status like 'Select_full_join'; 没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能。 10.Select_scan mysql> show status like 'Select_scan'; 执行全表搜索查询的数量。在某些情况下是没问题的，但占总查询数量该比值应该是常量（即Select_scan/总查询数量商应该是常数）。如果你发现该值持续增长，说明需要优化，缺乏必要的索引或其他问题。 11.Slow_queries mysql> show status like 'Slow_queries'; 超过该值（--long-query-time）的查询数量，或没有使用索引查询数量。对于全部查询会有小的冲突。如果该值增长，表明系统有性能问题。 12.Threads_created mysql> show status like 'Threads_created'; 该值应该是低的。较高的值可能意味着你需要增加thread_cache的数值，或你遇到了持续增加的连接，表明了潜在的问题。 13.客户端连接进程数 shell> mysqladmin processlist mysql> show processlist; 你可以通过使用其他的统计信息得到已连接线程数量和正在运行线程的数量，检查正在运行的查询花了多长时间是一个好主意。如果有一些长时间的查询，管理员可以被通知。你可能也想了解多少个查询是在"Locked"的状态—---该值作为正在运行的查询不被计算在内而是作为非活跃的。一个用户正在等待一个数据库响应。 14.innodb状态 mysql> show innodb status; 该语句产生很多信息，从中你可以得到你感兴趣的。首先你要检查的就是“从最近的XX秒计算出来的每秒的平均负载”。 (1)Pending normal aio reads: 该值是innodb io请求查询的大小（size）。如果该值大到超过了10—20，你可能有一些瓶颈。 (2)reads/s, avg bytes/read, writes/s, fsyncs/s:这些值是io统计。对于reads/writes大值意味着io子系统正在被装载。适当的值取决于你系统的配置。 (3)Buffer pool hit rate:这个命中率非常依赖于你的应用程序。当你觉得有问题时请检查你的命中率 (4)inserts/s, updates/s, deletes/s, reads/s:有一些Innodb的底层操作。你可以用这些值检查你的负载情况查看是否是期待的数值范围。 15.主机性能状态 shell> uptime 16.CPU使用率 shell> top shell> vmstat 17.磁盘IO shell> vmstat shell> iostat 18.swap进出量(内存) shell> free 19.MySQL错误日志 在服务器正常完成初始化后，什么都不会写到错误日志中，因此任何在该日志中的信息都要引起管理员的注意。 20.InnoDB表空间信息 InnoDB仅有的危险情况就是表空间填满----日志不会填满。检查的最好方式就是：show table status;你可以用任何InnoDB表来监视InnoDB表的剩余空间。 21.QPS每秒Query量 QPS = Questions(or Queries) / seconds mysql > show /* global */ status like 'Question'; 22.TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql > show status like 'Com_commit'; mysql > show status like 'Com_rollback'; 23.key Buffer 命中率 key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% mysql> show status like 'Key%'; 24.InnoDB Buffer命中率 Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% mysql> show status like 'innodb_buffer_pool_read%'; 25.Query Cache命中率 Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; mysql> show status like 'Qcache%'; 26.Table Cache状态量 mysql> show status like 'open%'; 27.Thread Cache 命中率 Thread_cache_hits = (1 - Threads_created / connections ) * 100% mysql> show status like 'Thread%'; mysql> show status like 'Connections'; 28.锁定状态 mysql> show status like '%lock%'; 29.复制延时量 mysql > show slave status 30.Tmp Table状况(临时表状况) mysql > show status like 'Create_tmp%'; 31.Binlog Cache使用状况 mysql > show status like 'Binlog_cache%'; 32.Innodb_log_waits量 mysql > show status like 'innodb_log_waits';