当前位置:Gxlcms > mysql > PerformanceTuningMySQL

PerformanceTuningMySQL

时间:2021-07-01 10:21:17 帮助过:4人阅读

通常来说,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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} (dyy) >

请确定你真的需要使用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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} ((none)) >

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)mysql [localhost] {msandbox} (dyy) >

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)mysql [localhost] {msandbox} (dyy) >

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)root@localhost : (none) 01:34:21>

当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

人气教程排行