王家林老师提供的课程mysql10,server性能优化

1、1选择合适1. 版版（RPM 等包装好的特定二进制版本AB AB a) 安装版本是经过比较完善的功能和性能测试的编所使用的编译参数更具通用性的，且d) 了的服务，将能最大 第进。还有些由各OS 厂商所提供版本，则可能是在有些代码方面针对自己的OS 做了一些相应的底OS 的析一1选择合适1. 版版（RPM 等包装好的特定二进制版本AB AB a) 安装版本是经过比较完善的功能和性能测试的编所使用的编译参数更具通用性的，且d) 了的服务，将能最大 第进。还有些由各OS 厂商所提供版本，则可能是在有些代码方面针对自己的OS 做了一些相应的底OS 的析一版本可能存在哪些问题其次，由版本者并一定都是一个

2、足够让人信任的公司（或者个人）， 在其生出版本之前，是否有做过足够全面的功能和性能不得而知使用的时候是否的了版本中并不存在的 的相关服务，而又使用了最后，如的时候，恐的提供支持。的系统出现问题的支持工 师的支持工作会大打折扣，甚至可能如果大家可以完全抛开以上这些可能存在隐患的顾虑，完全可以尝试使用进制版本，而选用特性或者更高性版本了Percona Patch 当然，Percona Patch Percona Patch Percona 对于二进制分发版本的安装，对于安装本身来说基本上没有太多可以优化的地方，唯一可做的就是决定了选择分发版本之后，可以根据自身环境和应用特点来选择适环境优版本来安装

3、。2. 源码与二所做的调整版本相比，如选择了通过源代码进行安装，那么在安装过 能够更灵活一些。因为通过源代码编可以针对自己的根据不同选用合2. 源码与二所做的调整版本相比，如选择了通过源代码进行安装，那么在安装过 能够更灵活一些。因为通过源代码编可以针对自己的根据不同选用合适的编译器来优化编译后的二进制环境调整相关的编译参数针根特定应用场景选择需要什么组件不需要的所需的数据内容选需要的字符集；e) ；f) 等等其他一些可以根据特定应用场景所作的各种调整在源码安装带来更大灵活性的同时，同样带来了可能引入的隐患：a) 对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定；b)

4、 对自己的应用环境而使用的优化参数可能反而使系统性能更差；还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过 更为复杂，所花费时间更长通过源码安装的最大特点就是可以自行调整编译参数，最大 度的定制安装结果。下面我自己在通过源码编译安装中的一些优化心得做一个简单的介绍，希望能够对大家有所帮助 configureconfiguresthispackagetoadapttomanykindsof . -. Forbettercontrol,usetheoptionsFinetuningoftheinstallation-.Program-. prependPREFIXtoinstalle

5、dprogram-. configureforbuildingonBUILD . -with-.-without-.DonotincludetheInnoDBtable Cd.CCASFLAGS assemblercompilerflags(defaultsto.上面的输出内容中很多都已经省略了，大家完全可以通过自针对几个比较重要的编译参数做一个简单的介绍：“prefix”：设定安装路径，默认为试得到更为丰富的内容输出。下-with-.-without-.DonotincludetheInnoDBtable Cd.CCASFLAGS assemblercompilerflags(default

6、sto.上面的输出内容中很多都已经省略了，大家完全可以通过自针对几个比较重要的编译参数做一个简单的介绍：“prefix”：设定安装路径，默认为试得到更为丰富的内容输出。下“with-charset”：设定系统的默认字符集“with-collation”：系统默认的校验规则“with-extra-charsets”：出了默认字符集之外需要编译安装的字符集“ix-socket-path”socket “with-tcp-port”：指定特端口，默认“d os ；“without-query-cache”QueryCache “-with-partition”5.1 partition “-enab

7、le-thread-safe-c nt”：以线 方式编译客户端“with-pthread”pthread 线 库编译；“with-named-thread-libs”：指定使用某个特定的线 “without-debug”debug “d link “with-c nt-ldflags”：c nt link 以上这些参数是在源码安装中比较常用的一些编译参数，其中前面几个编译参数主要是为了方便们在安装的时候可以定制自己的系统，让系统更适自己应用环境的相关规范，做到环，按照实际需求生成相应的二进制代码。而后面的一些参数主要是用来优化编译结果的。符集带来尽可能高的的系统能够尽可能的简单，因为这也会此外

8、，对于一些特环境上，可能会有多种线 库的选择的，如果你对各个线 库较为了化的一面使用最合适的线 在Debug Debug Debug 而“d-ldflags”和“with-c nt-ldflags”两个编译参数如果设“-s ic”的话，可以告诉编译器以静态方式编译来使编译Debug Debug Debug 而“d-ldflags”和“with-c nt-ldflags”两个编译参数如果设“-s ic”的话，可以告诉编译器以静态方式编译来使编译结果代码得到最高的性能。使用静态编译和态方式编译的代码相比，性能差距可能会达到 5%到 10%就我个人来说最常使用的编译配置参数如下，各位可以参照自行增删

9、相关内容 -enable-thread-safe-c nt -d-ldflags=-all-s ic -with-c nt-ldflags=-all-s ic -with-charset=latin1 -with-extra-charset=utf8,gbk -with-csv-storage-engine -with-federated-storage-engine -d-without-embedded-server -munity ix-socket-2但不管怎样，尽可能充足的硬件资源的性总是有帮助的。在这一主要分析一下的日志（主Binlog）对系统性能的影响，并根据日志的相关特性得出相

10、应的优化思路带来的直接性能损耗就是数据库系统中最为昂资源，所以对于日志 在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减损耗提高系统SQL 置）SQL Query IO 自己的日志）Binlog Binlog Binlog Binlog 也显示出了innodb_locks_unsafe_for_binlog”这Binlog showvariableslike+|+| | | |SQL 置）SQL Query IO 自己的日志）Binlog Binlog Binlog Binlog 也显示出了innodb_locks_unsafe_for_binlog”这Binlog sh

11、owvariableslike+|+| | | |1073741824|+“binlog_cache_sizeSQL 存，注意，是每个nt 都可以分配设置大小的binlogcache 空间。如果读者朋友的系统中经常会出多语句事务的华，可以尝试增加该值的大小，以获得更有的性能。当可以通的以下 “max_binlog_cache_size”：和binlog_cache_size相对应，但是所代表的能够使用最大cache 内存大小。 Multi-s ementstorage”的错误ionrequirednmax_binlog_cache_sizebytes“max_binlog_size”：Binl

12、og 512M 1G1GBinlog Binlog 统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL Oracle 对 Oracle 来说并不关键。而Redo Oracle Redo Redo Undo Binlog DML Query 下Binlog 所fsync binlog_cache Filesystem cache sync_binlog=nn binlog_cache fsync 在Crashbinlog_cache binlog 信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统 Crash，也最多丢

13、失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”0 1 5 。的（Replication），Master Binlog IO 到 SQL Binlog 下Binlog 所fsync binlog_cache Filesystem cache sync_binlog=nn binlog_cache fsync 在Crashbinlog_cache binlog 信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统 Crash，也最多丢

14、失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”0 1 5 。的（Replication），Master Binlog IO 到 SQL Binlog 以，Binlog IO 线 以及 Msater Slave Binlog Query Query Binlog 8 个参数可以让者 Table 的，分别为：Binlog_Do_DB：设定哪些数据库（Schema）需Binlog_Ignore_DB：设定哪些数据库（Schema）不DB Replicate_Do_DB：设定需的数据库（Sc

15、hema）用逗号（“,”）分Replicate_Ignore_DB：设定可以忽略的数据库Replicate_Do_Table：设定需的Replicate_Ignore_Table：设定可以忽略Replicate_Wild_Do_Table：功能Replicate_Do_Table，但可以带通配符来进行设置Replicate_Wild_Ignore_Table：功能Replicate_Ignore_Table，可带通配Master Slave Master Slave IO SQL Slave IO SQL 线 Master Slave 的Master Master IO Master IO 线

16、 Binlog Slave IO 线 IOSlave IO IO SQL 线 的工作量，从而最大幅度的优化Master Event Event Query DBQuery SchemaDB 行“USEDATABASE”DBDB DB 线 Slave IO DB DB DB Table Event Slave Slave Master 果在默认Schema 下更改了不需的Schema 中的数据，则会Slave Slave Schema Slave Master Event IO Slave IO Slave IO 线 增加了 RelayLog SQL Slave DBQuery SchemaDB

17、 行“USEDATABASE”DBDB DB 线 Slave IO DB DB DB Table Event Slave Slave Master 果在默认Schema 下更改了不需的Schema 中的数据，则会Slave Slave Schema Slave Master Event IO Slave IO Slave IO 线 增加了 RelayLog SQL Slave Slave 。Slave Master SlowQueryLog SlowQueryLog 的相关参数配置。有些时候，语句，则需要打开慢查询日志，也就是 Slow Query Log。showvariableslike+

18、|Value+|+|log_slow_queries|+1rowinset(0.00showvariableslike+|Value+|+|long_query_time|+1rowinset(0.01“log_slow_queries ” SlowQueryLog 的SlowQueryLog 1 Percona SQLSlowQuery mslpatch 给介 绍 （installation-walk-/2008/04/20/updated-msl-打开 SlowQueryLog Binlog 那么大，毕竟 Slow QueryLog 的数据IO Query CPU CPU CPU 可以在大

19、部分时候关闭该功能，而只需要间断性的打Slow Query 功能来定位可能存在的慢查打开 SlowQueryLog Binlog 那么大，毕竟 Slow QueryLog 的数据IO Query CPU CPU CPU 可以在大部分时候关闭该功能，而只需要间断性的打Slow Query 功能来定位可能存在的慢查询。 3优QueryCache QueryCache 真的是“尚方宝剑”的 Query Cache 实现原理实际上并不是特别的复杂，简单的来说就是将客户端请求的 Query 语句（SELECT Query）hash hash 值，存放在一个 hash 桶中。同时将该 Query 的结果集

20、（Result Set）也存放在一个内存 Cache 中的。存放 Query hash 值的链表中的每一个 hash 值所在的节点中同时还存放了该 Query 所对应的 Result Set 的 Cache 所在的内存地址，以及该 Query 所涉及到的所有 Table 的标识等其他一些相关信息。系统接受到任何一个SELECT Query hash hash QueryCache 中去匹配，如果找到了完全相同的 Query，则直接将之前所 Cache 的 Result Set 返回给客户端而完全不需要进行后面CacheTable Query Cache Query QueryCache a)

21、Query 语句的hash 运算以及hash 查找资源消耗。使用QueryCache 之后，每条Query hash Query Cachehash 运算的算法可能已经非常高效了，hash 查找的过 也已经足够的优化Query Query CPU b) QueryCache QueryCache 高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说QueryCache Cache 数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同 Query 进来之后无法使用到之前的 Cache。c) QueryCache ResultSet Cache Cach

22、e QueryCache 的大小，但是这样，QueryCache 容易造成因为内而被换出，造QueryCache QueryCache QueryQuery Cache Query Cache Query Cache QueryCache QueryCache 发发挥其优势，并有效的避开其劣势QueryCache Query Cache Query QueryCache QueryCache QueryQuery Cache Query Cache Query Cache QueryCache QueryCache 发发挥其优势，并有效的避开其劣势QueryCache Query Cache

23、Query Table Query Result Set QueryCache Table Query Table Query QueryCache 用的SQL （提示）：SQL_NO_CACHE 和SQL_CACHE，分别代表强制不使用Query Cache 和强制使Query Cache完全可以利用这两个SQL SQL Query Cache SQL Table Query QueryCache QueryCache 可以添加SQL_CACHE 的SQLQueryCacheSQL ResultSet QueryCache Cache Cache SQL_NO_CACHE 强QueryCac

24、he “query_cache_limit”参数值来控制 Query Cache 中所 Cache 的最大 Result （1048576）Query ResultSet 大于“query_cache_limit”Cache Cache Query QueryCache 的系统变量，可以通过执行如下命令获得showvariableslikeQueryCache +|+|268435456| |+Query“query_cache_limit”：QueryCache Query ResultSet “query_cache_min_res_unit”：QueryCache ResultSet ：

25、针对于 OCK Table Cache FALSE（QueryCache 中取得结果）以上参数的设置 |+Query“query_cache_limit”：QueryCache Query ResultSet “query_cache_min_res_unit”：QueryCache ResultSet ：针对于 OCK Table Cache FALSE（QueryCache 中取得结果）以上参数的设置主要是“query_cache_limit”和“query_cache_min_res_unit”两个参数的Cache ResultSet 一般都很小（4k）query_cache_min_r

26、es_unit ” 需要Cache 的Result Set 大部分都大于4k 的话，则最好将“query_cache_min_res_unit”调整到和 Result Set 大小差不多，“query_cache_limit”的ResultSet ResultSet ResultSet Result 差不多大，是每个结果集的一半或者四分之一大小都可以，要想非常完美的完全内存确实也是不可能做到如要了解QueryCache 的使用情况，则可以通过QueryCache 相关的状态变量来获取，如通showuslike+|+|190662000|1888430018|1014096388|1060718

27、85|7951123988|+| |+“Qcache_free_blocks”：Query Cache 中目前还有多少剩blocks。如果该值则说明 Query 中的内存碎片较多了，可能需要寻找合适的机会进行整理（） QueryCache QueryCache “Qcache_inserts”：多少次未命中然。通过“Qcache_hits”和“Qcache_inserts”就可以算出 Query 个参中率了Query=Qcache_hits/(Qcache_hits+Qcache_inserts“Qcache_lowmem_prunes”Query QueryCache“a_ompne”和“c

28、_mo”相互结合，能够更清楚的了解到统中Query=Qcache_hits/(Qcache_hits+Qcache_inserts“Qcache_lowmem_prunes”Query QueryCache“a_ompne”和“c_mo”相互结合，能够更清楚的了解到统中ueryace出系Query “Qcache_not_cached”query_cache_type cache Query “Qcache_queries_in_cache”QueryCache cache Query “Qcache_total_blocks”QueryCache block QueryCache 5.1.1

29、7 Cache Query5.1.17 版本开始，QueryCache 已经开始支持帮定变量的 Query 了；SQL Procedure，Function Trigger Query Query CacheQueryCache 的过 中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入 Query Cache，仅仅让某些 Query 的查询结果被 Cache。4除了安装，日志，Query Cache 之外，可能影 络连接，线 管理管理等。这一性能的其他可优化分析除了前面几节内容之外的可能影网络连接与连接max_conecctions 500 800 max_user_connect

30、ions：每个用户允许的最大max_user_connections 数一样。这个参数的设置完全依赖于应用 序的连接用户数，对于普通的应用来说，完全没有做太多的限制，可以尽量放开一些。 8KBmax_allowed_packet：在网络传输中，一次传消息输量的net_buffer_length netbuffer net_buffer_length netbuffer max_allowed_packet 1MB1GB1024 数为字节back_log：的连接请求等待队列中允许存放的最大连接主线说，完全没有做太多的限制，可以尽量放开一些。 8KBmax_allowed_packet：在网络传输

31、中，一次传消息输量的net_buffer_length netbuffer net_buffer_length netbuffer max_allowed_packet 1MB1GB1024 数为字节back_log：的连接请求等待队列中允许存放的最大连接主线 办法及时给每一个新的连接请求分配（或者创建）连接线 的时候，还没有分配到连接线 的back_log 5065535ack_log 的设置的时候，同时还需要主义OS 级别对网OS 置是没有意义的back_log 加上面介绍了网络连接交互相关的主要优化设置再来看看与每一个客户端连接想对应的连接在ThreadCache 检查ThreadCac

32、he 池中是否存在空闲连接在thread_cache_size：Thread 池中应该存放的连接线 当系统最初启动的时候，并不会马上就创所设置数目的连接线 存放ThreadCache 存放的连接线 达到 thread_cache_size 就不会再续保存用完的连接线 了。的应用 序使用的短连接，ThreadCache ThreadCache 数的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请而如使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，也并不是完全没有价值。因为应用 序即使是使用了长连接，也很难保证他们所管理的所有连接都能处于很稳定的状

33、态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应用服务器数量较大的系统中，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作会。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大帮助的。只而如使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，也并不是完全没有价值。因为应用 序即使是使用了长连接，也很难保证他们所管理的所有连接都能处于很稳定的状态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应用服务器数量较大的系统中

34、，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作会。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大帮助的。只不过在长连接的环境50 100 不需要将 thread_cache_size 参数设置太大，一般来thread_stack：每个连接线 被创建的时候给他分配的内当创建一个新的连接线 的时候，是需要给他分配一定大小的内存堆栈空间，以便存放 可以所有的普通应用环境。如果该值设置太小，会影连接线 能够处理Query Procedures Functions 上面介绍

35、的这些都可以怎样配置网络连接交互以及连接的性能相关参数，下再看该怎样检验上面所做的设置是否合理，是否有需要调整的地方个命令来获得相关的状态信息来帮助大家检验设置的合理性：可以通过在系统中执行如下的现看看连接线 相关的系showvariableslike+|Value|+|thread_cache_size|196608+再来看一下系统被连接的次数以及当前系统中连shows uslike的状态值+|Variable_name|Value+|+|+showuslike+|Value+ |+可以看出，系统设置了ThreadCache 池最多将缓存32 个连接线 创建之初，系统分配 192KB 127

36、 11 7 个连接线 7 个连接状态的线 active ThreadCache Cache 4 |+可以看出，系统设置了ThreadCache 池最多将缓存32 个连接线 创建之初，系统分配 192KB 127 11 7 个连接线 7 个连接状态的线 active ThreadCache Cache 4 thread_cache_size 8 16Connections Threads_created 下ThreadCache =(127-12)/127 *100%=ThreadThreadCache TableCache Innodb 么于表数据的数据文件较多，则同样会打开很多的表文件描述符

37、。除了数据库的实际表或者索引打开以外，临时文件同样也需要使用文件描述符，同样会占用系统的设置限TableCache ，ThreadCache Cache 打开的所有表文件的描述符，当有新的请求的时 在中table_cache（5.1.3 table_open_cache）Cache table_cache table_cache=max_connections*N Query Table 准确，分析如下首先，max_connections 是系统同时可以接受的最大连接数，但是这些连接并不一定都是 active 状Sleep Sleep 状态的连接是不可能打开任何 Table 的。N Query Table Query Table 为Query N table_cache table_cache Cache table_cache=max_connections*N Query Table 准确，分析如下首先