search
HomeDatabaseMysql TutorialPerformance 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)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

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools