Performance Tuning MySQL

  通常来说,MySQL性能调优是非常复杂的一件事,不是简单的修改参数就可以完成的。需要综合考虑。而且找出性能瓶颈也非易事。但是通常我们有以下的几种方法找到蛛丝马迹。通过下面的几种方法发现瓶颈以后,我们才能确定下一步应该怎么做?

其他的可以参考我前面写的文章:MySQL常用SQL优化Linux上跑MySQL优化

(1)查看系统状态,比如top,vmstat,sar,iostat,dstat等
(2)进入MySQL里查看MySQL的连接数及相应的SQL(show processlist)
(3)如果使用的innodb表还需要把show engine innodb status取出来分析
(4)取两次show global status,间隔5到10秒用于分析
(5)查看慢日志及相应慢日志内容分析

当发现性能瓶颈以后,我们如何解决呢?无非也就是下面的几种方法(当然或许还有更多)

(1)升级硬件(Scale Out/Scale Up)
(2)更改MySQL的配置
(3)改善索引,优化查询
(4)升级MySQL版本(在官方版本里面随着连接数的增加性能急剧下降,企业版提供thread_pool插件,Percona和MariaDB都是开源的。)

 通常我们不会轻易的升级硬件或者改变MySQL的配置,我们首先要做的是通过show global status输出的状态来分析。

1.Temporary Tables on Disk

mysql [localhost] {msandbox} (dyy) > show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 92    |
+-------------------------+-------+
3 rows in set (0.00 sec)

Created_tmp_disk_tables

服务器执行语句时在硬盘上自动创建的临时表的数量

Created_tmp_files

mysqld已经创建的临时文件的数量

Created_tmp_tables

服务器执行语句时自动创建的内存临时表的数量。如果Created_tmp_disk_tables值较大。需要增加tmp_table_size和max_heap_table_size的值。内部临时表最初创建为一个内存中的表,但变得太大时,MySQL会自动将其转换为磁盘上的表。在内存中的临时表的最大尺寸是最小的tmp_table_size和max_heap_table_size的值控制。如果Created_tmp_disk_tables较大,可能要增加tmp_table_size和max_heap_table_size值。以减少在内存临时表被转换为磁盘上的表。

出现临时表的原因

(1)如果有一个ORDER BY子句和一个不同的GROUP BY子句,或如果ORDER BY或GROUP BY包含第一个表中的其他列,创建一个临时表

(2)DISTINCT加上ORDER BY可能需要一个临时表

(3)如果使用SQL_SMALL_RESULT选项,MySQL使用内存中的临时表

(4)表中有BLOB或TEXT列的存在

(5)在GROUP BY或DISTINCT子句大于512字节的任意列的存在

(6)在查询中,如果使用UNION或UNION ALL的任何列大于512字节

(7)GROUP BY和ORDER BY 无法使用索引时

关于是否使用临时表,需要使用EXPLAIN命令查看,请参考我前面的文章,EXPLAIN命令详解

2.Binary Log cache

在事务提交以后,binlog是先写入缓存,然后由操作系统决定何时刷新到磁盘上。如果事务大小超过定义的缓存,则在磁盘上创建一个临时文件。

mysql [localhost] {msandbox} (dyy) > show global status like 'binlog_ca%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 3     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Binlog_cache_use

使用临时二进制日志缓存的事务数量

Binlog_cache_disk_use

使用临时二进制日志缓存但是超过binlog_cache_size的值并使用临时文件来保存事务中的语句的事务数量。如果该值很大,需要加大binlog_cache_size的值。

3.Sorting Data

mysql [localhost] {msandbox} (dyy) > show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

Sort_merge_passes

排序算法已经执行的合并的数量。如果这个变量值较大,可以考虑增加sort_buffer_size变量的值。

原因:

ORDER BY(不能够使用索引进行排序)

GROUP BY(使用了GROUP BY COLUMN没有使用ORDER BY NULL).

ORDER BY优化可以参考:http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

4.Query Cache

mysql [localhost] {msandbox} (dyy) > show global status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031352 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 28      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

请确定你真的需要使用Query Cache,否则将不是你想象的那么美好。
MySQL的Query Cache实现原理实际上并不是特别的复杂,简单的来说就是将客户端请求的 Query语句(当然仅限于SELECT类型的Query)通过一定的hash算法进行一个计算而得到一个hash值,存放在一个hash桶中。同时将该Query的结果集(Result Set)也存放在一个内存Cache中的。存放Query hash值的链表中的每一个hash值所在的节点中同时还存放了该Query所对应的Result Set 的 Cache 所在的内存地址,以及该Query所涉及到的所有Table的标识等其他一些相关信息。系统接受到任何一个SELECT类型的Query的时候,首先计算出其hash值,然后通过该hash值到Query Cache中去匹配,如果找到了完全相同的Query,则直接将之前所Cache的Result Set返回给客户端而完全不需要进行后面的任何步骤即可完成这次请求。而后端的任何一个表的任何一条数据发生变化之后,也会通知 Query Cache,需要将所有与该Table有关的Query的Cache 全部失效,并释放出之前占用的内存地址,以便后面其他的Query能够使用。

select a,b from t1;

Select a,b FROM t1;

第一条语句可以使用查询缓存,而第二条则无法使用。因为上面提到过是基于hash算法的。

何况现在我们使用innodb存储引擎比较多,而且innodb有自己的缓冲池(undo page,insert buffer page,adaptive hash index,lock info,data dictionary,index page)。所以我们通常不需要使用查询,可以使用参数query_cache_type = 0 禁用查询缓存。

5.Table Locks/Row locks

某些存储引擎(MyISAM,Memory)有表级锁。并发大的情况下性能下降也很厉害

mysql [localhost] {msandbox} (dyy) > show global status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 77    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (dyy) > show global status like '%row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

Table_locks_immediate
产生表级锁定的次数
Table_locks_waited
出现表级锁定争用而发生等待的次数
两个状态值都是从系统启动后开始记录,每出现一次对应的事件则数量加1,如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用。

Innodb 的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长,每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。
Innodb_row_lock_current_waits
当前正在等待锁定的数量
Innodb_row_lock_time
从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg
每次等待所花平均时间
Innodb_row_lock_time_max
从系统启动到现在等待最常的一次所花的时间;
Innodb_row_lock_waits
系统启动后到现在总共等待的次数

当Table_locks_waited与Table_locks_immediate 的比值较大,则说明我们的表锁造成的阻塞比较严重,可能需要优化SQL语句,或者更改存储引擎,亦或者需要调整业务逻辑。当然,具体改善方式必须根据实际场景来判断。而 Innodb_row_lock_waits 较大,则说明Innodb的行锁也比较严重,且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是 Query 语句所利用的索引不够合理(Innodb行锁是基于索引来锁定的),造成间隙锁过大。也可能是系统本身处理能力有限,则需要从其他方面(如硬件设备)来考虑解决。

6.Table Cache

mysql [localhost] {msandbox} (dyy) > show global status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 72    |
| Opened_tables | 79    |
+---------------+-------+
2 rows in set (0.00 sec)

Opened_tables

已经打开的表的数量。如果Opend_tables较大,则需要考虑加大table_open_cache的值。

7.Thread Cache

在MySQL中每个连接即一个线程。通过thread_cache可以减少操作系统的线程创建/销毁,提高性能。

mysql [localhost] {msandbox} ((none)) > show global status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 4     |
| Threads_created   | 4     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached

线程缓存内的线程的数量

Threads_connected

当前打开连接的数量

Threads_created

创建用来处理连接的线程数。如果Threads_created较大,需要增加thread_cache_size的值。thread cache命中率计算方法:

Thread_cache_hits = (1 - Threads_created / Connections) * 100%

8.Max Connections

观察max_used_connections是否等于max_connections,在某个时刻连接可能被拒绝

mysql [localhost] {msandbox} (dyy) > show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (dyy) > show global status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 4     |
+----------------------+-------+
1 row in set (0.01 sec)

9.Cartesian Products?
连接两个表的条件没有使用索引往往将导致笛卡尔乘积。可以看见Select_full_join>0

mysql [localhost] {msandbox} (dyy) > show global status like 'Select_full_join';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Select_full_join | 0     |
+------------------+-------+
1 row in set (0.01 sec)

10.InnoDB Log Buffer Size

root@localhost : (none) 01:34:16> show global status like 'innodb_log_waits';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+
1 row in set (0.00 sec)

当Innodb_log_waits值较大时,说明可用log buffer不足,需等待释放次数,数量较大时需要加大innodb_log_buffer_size的值。

 

总结:

  目前就写这么多吧,还有很多很多的状态变量。上面有些是从mysqld启动以来就存在的,所以假如我们需要计算每秒的SELECT,需要知道时间差内产生的变化,例如

每秒的Select 执行量: (t2.Com_select -t1.Com_select)/(t2.Uptime - t1.Uptime)    参考资料: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html http://pan.baidu.com/s/1bnjaxkj 链接:https://www.cnblogs.com/gomysql/p/3833526.html
上一篇:performance_schema初相识


下一篇:面向切面的Spring