mysql性能优化

一、优化概述

MySQL数据库常见的2个瓶颈是CPU和I/O的瓶颈,CPU的饱和一般发生在数据装入内存或者从磁盘读取数据的时候,I/O的瓶颈一般发生在装入的数据远大于存储容量的时候。如果应用分别在网络上,那么查询量相当大的时候,瓶颈就会出现在网络上。除了以上3种瓶颈服务器性能的瓶颈,对于MySQL本身,我们可以使用工具来优化数据库的性能,通常有3种:使用索引,使用EXPLAIN语句分析查询,以及调整MySQL的内部配置


二:分析查询

在优化MySQL时,常常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN分析查询,profiling分析以及show命令查询系统状态和系统变量

性能瓶颈定位:show命令

show status --显示状态信息(扩展:show status like 'xxx')

show variables --显示系统变量(扩展:show variables like 'xxx')

show innodb status --显示innodb存储引擎状态

show processlist --查看当前sql执行,包括执行状态以及是否锁表等


慢查询日志:

慢查询日志的开启:

一、在配置文件my.ini中的[mysqld]下加入以下3条语句

log_how_queries=/data/mysqldata/slow-query.log

long_query_time=2

log_queries_not_using_indexs

第一条语句设置慢查询记录的保存地址

第二条语句表示超过2S的语句才记录

第三条语句表示记录下没有使用索引的查询

二、在命令行中即时开启

set global slow_query_log=ON

set long_query_time=2


profiling分析查询:得到详细的SQL执行消耗系统资源的信息

profiling默认关闭,可以通过以下语句查看是否开启:select @@profiliing --0表示关闭,1表示开启

打开功能:set profiling=1;执行需要测试的sql语句

show profiles\G:得到被执行sql语句的ID和时间

show profile for query ID:得到对应SQL语句的详细信息,同过得到的信息来优化sql语句

关闭profiling:set profiling=0;


配置优化:

安装MySQL后,配置文件my.cnf在mysql安装目录/share/mysql中,该目录还包含多个配置文件可供参考,有my-large.cnf , my-huge.cnf , my-medium.cnf , my-small.cnf,分别对应大中小型数据库的应用配置。在win环境下即存在与MySQL安装目录中的.ini文件中。

以下列出对性能优化影响较大的主要变量,分为连接请求变量和缓冲区变量:

一、连接请求变量

1、max_connections:mysql的最大连接数,增加该值增加mysqld要求的文件描述符的数量。如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这是在服务器能支撑的条件下,因为如果连接数越多,介于mysql会为每个连接提供连接缓冲区,就会开销越多的内存,所以不能盲目的提高该值。

该值过小会经常出现ERROR 1040:Too many connections错误,可以通过show variables 'conn%'查看当前连接的数量,以定夺该值的大小。

show variables  like 'max_connections'; 最大连接数

show status like 'max_used_connections'; 响应的连接数

响应连接数/最大连接数*100%(理想值为85%),如果max_used_connections和max_connections相同,那么就是max_connections设置过低或者服务器负载上线了,如果低于10%,那么就是max_connections设置过高。

2、back_log:mysql能暂存的连接数量,当主要mysql线程在短时间内得到非常多的连接请求,这就起作用。如果mysql的连接数据达到max_connections上限时,新的请求将会被存放在堆栈中,以等待某一连接释放资源,该堆栈数量即back_log,如果等待数量超过back_log上线,将不会被授予连接资源。

当你在观察主机进程列表时(show full processlist),发现大量的26408|unauthenticated user|XXX.XXX.XXX.XXX|NULL|connect|NULL|login|NULL的待链接进程时,就需要放大back_log的值。

默认值为50,可调优为128,liunx系统设置范围为小于512的整数。

3、interactive_timeout:一个交互连接在被服务器关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE的客户。

默认值为28800,可调优为7200。

二、缓冲区变量

全局缓冲:

key_buffer_size;指定索引缓冲区大小,它决定索引处理的速度,尤其是索引读的速度,通过检查状态值key_read_requests和key_reads(show status like 'key_read%'),可以知道key_buffer_size的设置是否合理,key_reads/key_read_requests的值应尽可能低,至少是1:100,1:1000更好。

key_buffer_size只对MyISAM表起作用。即使你不用MyISAM表,但内部的临时磁盘表是MyISAM表,也要使用该值。可以检查状态值created_tmp_disk_tables得知详情。

默认配置为8388600(8M),主机有4G内存,可调优为268435456(256M)。

query_cache_size:使用查询缓冲,MySQL将查询结果放在缓冲区,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。通过检查状态值Ocache_*,可以知道query_cache_size设置是否合理。如果Qcache_lowman_prunes的值非常大,则说明经常出现缓存不够,如果Qcache_hits的值也非常大,说明查询缓冲使用非常频繁,此时需要增加缓冲大小,如果Qcache_hits的值很低,说明查询缓冲重复率很低,这种情况下使用查询缓冲反而影响效率。此外在SELECT语句中加入SQL_NO_CACHE可以明确表示不用查询缓冲。

mysql> show global status like ‘qcache%‘;

+——————————-+—————–+

| Variable_name              | Value |

+——————————-+—————–+

| Qcache_free_blocks        | 22756 |

| Qcache_free_memory        | 76764704 |

| Qcache_hits            | 213028692 |

| Qcache_inserts          | 208894227|

| Qcache_lowmem_prunes       | 4010916 |

| Qcache_not_cached         | 13385031 |

| Qcache_queries_in_cache    | 43560 |

| Qcache_total_blocks        | 111212 |

+——————————-+—————–+

mysql> show variables like ‘query_cache%‘;

+————————————–+————–+

| Variable_name            | Value     |

+————————————–+———–+

| query_cache_limit        | 2097152    |

| query_cache_min_res_unit    | 4096     |

| query_cache_size         | 203423744  |

| query_cache_type         | ON        |

| query_cache_wlock_invalidate | OFF   |

+————————————–+—————+

查询缓冲碎片:Qcache_free_blocks/Qcache_total_blocks*100%

如果查询缓冲碎片超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。

查询缓存利用率:(query_cache_size-Qcahce_free_memory)/query_cache_size*100%

查询利用率如果在25%一下说明query_cache_size值设置过大,可适当减小,查询缓存利用率如果在80%以上而且Qcache_lowman_prunes>50说明query_cache_size可能有点小,要不就是碎片太多。

查询缓冲命中率:(Qcahce_hits-Qcache_inserts)/Qcache_hits*100%

示例:查询缓冲碎片=20.46%,查询缓冲利用率=62.62%,查询缓冲命中率=1.94.命中率很差,可能写操作比较频繁,而且可能有碎片

每个连接的缓冲:

1、record_buffer_size:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区,如果你做很多顺序扫描,可能需要增大该值。默认131027(128K),可改为16773120(16M)

2、read_rnd_buffer_size:随机读缓冲区大小。当按照任意顺序读取行时(例如按照排序顺序),将分配一个随机读缓冲区。进行排序查询时,MySQL将会先扫描一遍该缓冲,避免磁盘搜索,提高查询速度。一般设置为16M。

3、sort_buffer_size:每个需要排序的线程分配一个该大小的缓冲区,增加该值加速ORDER BY或GROUP BY操作。默认2M,可改为16M

4、join_buffer_size:联合查询操作所能使用的缓冲区大小。

注:record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size为每个线程独占,也就是所如果有100个线程连接,则占用为100*16MB

5.table_cache:表高速缓存的的大小,每当MYSQL访问一个表时,如果表缓冲区还有空间,表就会被打开放入其中,加快表的访问速度,通过检查状态值open_tables和opened_tables,可以决定是否增加table_cache的值,如果open_tables=table_cache,而且opened_tables还在增长,那么就要考虑增加table_cache了。1G内存推荐值为128~256M,4G内存可以设置为256或者384M。

6、max_heap_table_size:用户可以创建的内存表的大小,这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap(堆积)表的大小超过了tmp_table_size,那么mysql会根据需要自动将内存中的heap表变为基于硬盘的MyISAM表

7、tmp_table_size:通过设置tmp_table_size来提高临时表的大小,例如做高级GROUP BY操作生成临时表,提高tmp_table_size的值,也还提高heap表的容积,可达到提高连接查询速度的效果。建议尽量优化查询,要确保在查询过程中生成的临时表在内存中,避免临时表过大生成基于硬盘的MyISAM表。默认为16M,可调到64~256最佳,线程独占,太大可能导致内存不够I/O堵塞。

8、thread_cache_size:默认110,可调优为80

9、thread_concurrency:推荐设置为服务器CPU核数的2倍。默认为8

10、wait_timeout:指定一个请求的最大连接时间。4G内存的服务器可设置为5~10.

配置innodb的几个变量:

1、innodb_buffer_pool_size:类似MyISAM表的key_buffer_size,指定内存来缓冲数据和索引。最大值可以设置为服务器物理内存的80%,但推荐为50%。

2、innodb_flush_log_at_trx_commit:主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0,1,2三个。0:表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次;1:表示每秒钟或者事务每次提交都会引起日志文件写入、flush磁盘操作,确保了事务的ACID;2:事务每次提交都会引起写入日志文件的动作,每秒钟flush磁盘一次。

实际测试发现,该值对插入数据的影响非常大,设置为2时,插入1000条数据需要2S,0时需要1S,而1时需要229S,因此MYSQL手册也建议尽量将插入操作合并为一个事务,可以大幅度提高速度。(PS;设置为0或2可能引起丢失最近部分事务的危险)

3、innodb_buffer_size:log缓存大小,默认为1M,对于较大的事务可设置为4或8M

4、innodb_additional_mem_pool_size:一般不用


书山有路勤为径 学海无涯苦作舟