MySQL的配置优化

共计 7600 个字符，预计需要花费 19 分钟才能阅读完成。

需求：MySQL 的参数优化对于不同的网站，极其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，优化不可能一次万次，需要在工作当中不断的监控观察和调试，才能得到最佳的效果。性能优化影响最大的变量分为连接请求变量和缓冲区变量。

理论总结：

        修改 vim/my.cnf

        max_connections = 1024    设置最大连接数为 1024

        back_log = 100      暂存的连接数量 

        wait_timeout = 100

        interactive_timeout = 100 修改 interactive 的值

        key_bugger_size =268435456 和 key_buffer_size=256M   调整

        query_cache_size = 256M    缓存大小

        query_cache_type = 1    缓冲类型

        max_connect_errors = 20    开启安全有关的计数器，host 连接请求多余 20 禁止连接请求

        sort_buffer_size = 2M     为进行排序的线程分配该大小的一个缓冲区

连接请求变量：

    max_connections。如果服务器的并发连接请求量大，此值应相应调高。增加并行连接量是建立服务器能支撑的情况下，连接数越多，则 mysql 为内个连接提供连接缓冲区开销的内存越大，所以应适当调整该值。数值过小会经常出现错误 ERROR 1040：Too many connections 错误。

            show status like ‘connections’; 通配符查看当前连接数量，无论是否成功都会记录。

            show variables like ‘%max_connections%’    最大连接数

            show status like ‘%max_used_connections’    响应连接数

            show variables like’%wait_timeout%’查看 wait_timeout

            show variables like ‘%interactive_timeout%’   查看 interactive

            show status like ‘qcache%’;    可以指定 query_cache_size 设置是否合理

            show variables like ‘query_cache%’;    验证 query_cache 配置是否开启

            max_used_connections 跟 max_connections 相同那么就是 max_connections 设置过低或超过服务器负载上限，地狱 10% 则设置过大。

    暂存的连接数据：back_log。主要 mysql 线程在一个短时间内得到很多请求就会起作用。连接数值达到 max_connections 时，新来的请求将被存在堆栈中，等待某一连接释放资源。等待连接数量超过 back_log 则不被授予连接资源。back_log 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。如果希望在短时间很多连接，需要增加 back_log 值。

                查看主机进程表：show variables processlist

                查看 back_log 的值：show variables like ‘back_log’

                如果主机进程表中大量的待连接进程时，要加大 max_connections 的值。

     mysql 关闭一个非交互的连接之前索要等待的秒数wait_timeout 和 interactive_timeout

            wait_timeout 指 mysql 在关闭一个非交互的连接之前所要等待的秒数

            interactive_timeout 指的是 mysql 在关闭一个交互的连接之前索要等待的秒数，就比如我们在终端上进入 mysql 管理，使用的即是交互式连接，如果操作时间超过了 interactive_timeout 设置的值，就会自动断开。

         对性能的影响 

            如果设置大小，连接关闭很快，使一些持久的连接不起作用

            如果设置太大，造成连接打开时间过长，如果执行 show processlist 能看到太多的 sleep 状态的连接，造成 too many connections 错误

            一般希望 wait_timeout 的值尽可能的低。interactive 的设置对 web application 没有多大的影响

缓冲区变量

    全局缓冲

    指定索引缓冲区的大小，决定索引处理的速度，尤其是索引读的速度。

        状态值：key_read_requests 和 key_reads

                create_tmp_disk_tables

        key_reads/key_read_requests 应该尽可能的低，至少是 1：100，1：1000 会更好

            show status like ‘key_read%’

           show status like ‘key_read%’;

                 variables_name    value

                 key_read_requests  6

                 key_reads          3

                一共有 6 个索引读取请求，有 3 个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

                key_cache_miss_rate = key_reads/key_read_requests * 100% = 50%

                key_buffer_size 只对 MyLSAM 表起作用。即使不使用 MyLSAM 表，内部的临时磁盘表是 MyLSAM 表，也要使用该值

    查询缓冲简 QC：query_cache_size

        使用查询缓冲，mysql 将查询结果存放在缓冲区中，今后对于同样的 select 语句将直接从缓冲区中读取结果。

        一个 sql 语句，只要相差哪怕是一个字符，那么这两个 sql 将使用不同的一个 CACHE

            show status like ‘qcache%’

                variables_name        value

               qcache_free_blocks        1        缓冲中相邻内存块个数，该值较大，说明内存碎片多，FLUSH QUERY CACHE 会对缓存中的碎片进行整理。

               qcache_free_memory        1031832    剩余的内存大小，可以较准确的观察当前系统中的 quuery cache 内存大小是否足够。

               qcache_hits               0    有多少命中缓存。来验证我们查询缓冲的效果，数字越大，缓冲效果明显

               qcache_inserts            0    多少此未命中然后插入，新来的 sql 请求的缓冲中未找到，不得不执行查询处理，值越大，表示查询缓存应用到的比较少，效果不理想。

               qcache_lowment_prunes     0    多少条 query 因为内存不足而被清理出 query cache。通过 qcache_lowmen_prunes 和 qcache_free_memory 相互结合，更清楚了解系统中 query cache 的内存大小是否真足够，是否非常频繁的出现因为内存不足而又 query 被换出

               qcache_not_cached         1    不适合进行缓存的查询数量，这些查询不是 select 之类的函数。

               qcache_queries_in_cache   0    当前 query cache 中 cache 得到 query 数量

               qcache_total_blocks       1    当前 query cache 中 block 数量

        查询关于 query_cache 的配置：show variables like ‘query_cache%’;

                variables_name                value

                query_cache_limit            104876    超过此大小的查询将不缓存

                query_cache_min_res_unit    4096      缓冲快大小。默认是 4kB，设置值大对大数据查询有好处，但如果查询都是小数据，就造成内存碎片和浪费

                query_cache_size             209715209    查询缓存大小

                query_cache_type            OFF    缓冲类型，决定缓冲什么样的查询，不能随便设置，必须设置为数字，可选项目

                    0    off，不缓冲或重新得到结果

                    1    on   缓存所有的结果，除了 select sql_no_cache … 查询

                    2    dnmand 仅缓存 select sql_cache… 查询

                query_cache_wlock_invalidata   当有客户端正对 MyLSAM 表进行写操作，如果查询在 query cache 中，是否返回 cache 结果还是等写操作完成在读表获取结果

                    查询缓冲碎片率 = qcache_free_blocks/qcache_total_blocks * 100%

                       碎片率低于 25% 说明 query_cache_size 设置过大，可适当减小，查询缓冲利用率在 80* 以上而且 qcache_lowmen_prunes>50 说明 query_cache_size 可能有点小，要不碎片太多

                查询缓冲命中率 =qcache_hits/(qcache_hits+qcache_inserts)*100%

              query cache 的限制

                所有子查询中的外部查询 sql 不能被 cache

                在 procedure，function 以及 trigger 中的 query 不能被 cache

                包含其他很多每次执行可能得到不一样的结果的函数的 query 不能被 cache

    mysql 中与安全有关的计数器值：max_connect_errors，负责阻止过多尝试失败的客户端以防暴力破解密码，超过指定次数，将禁止 host 连接请求。直到 mysl 服务器重启或通过 flush hosts 命令清空此 host 的相关信息。

    需要进行排序的线程分配该大小的一个缓冲区：sort_buffer_size。加速 order by 或 group by 操作。sort_buffer_size 是一个 connections 级参数，每个 connection 第一次需要使用这个 buffer 的时候，一次性分配设置的内存。这个值不是越大越好，过大高并发可能会耗尽传统内存资源

    限制 server 接收数据包大小，大的插入和更新会收 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是 1GB，必须设置 1024 的倍数。

    表间关联缓存的大小，与 sort_buffer_size 一样，对应的分配内存也是每个连接独享

    服务器线程缓存，表示可以重新利用保持在缓冲中线程的数量，端口连接时，客户端的线程放到缓存中以响应下一个客户而不是销毁，如果线程重新被请求，请求将从缓存中读取，缓存中是空的或者是新的请求，这个线程将被重新创建，如果有很多新线程，增加这个值可以改善系统性能，通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用

        试图连接 mysql 的连接数:show status like ‘conections’;

        试图连接 mysql 的连接数（不管连接是否成功）：show status like ‘threads_%’;

配置 InnoDB 的几个变量

        修改 /etc/my.cnf 文件，在 [mysqld] 下面添加

            innoDB_buffer_pool_size = 2048M

    相当于 key_buffer_size 对于 MyLSAM 表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引，单独的 mysql 数据库，最大可以把该值设成物理内存的 80&innodb_buffer_pool_size

    控制 innodb 将 log buffer 中的数据写入日志文件并 flush 磁盘的时间点，取值分别是 0，1，2.0 表示事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入日志文件并 flush 磁盘一次；1 在每秒钟或每次事务的提交会引起日志文件写入 flush 磁盘的操作，确保了事务的 ACID；2 每次事务提交引起写入日志文件的动作，但每秒钟完成一次 flush 磁盘操作。

                实际操作发现，该值对插入数据的速度影响大，设置为 2 是插入 10000 条记录只需要 2 秒，设置为 0 是只需 1 秒，设置为 1 是需要 229 秒。因此 mysql 手册建议尽量将插入操作合并和一个事务，可以提高速度。

    设置 innodb 线程的并发数量，默认值为 0 表示不限制，若要设置则与服务器 cpu 核数相同或是 cpu 的核数的 2 倍, 建议使用默认设置，一般为 8.：innodb_thread_concurrency = 0

    确定此日志文件所用内存大小，以 M 为单位，缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小 innodb_log_bugger)size = 32M

    确定日志文件的大小，以 M 为单位，更大的设置可以提供性能：innodb_log_file_size = 50M

    提高性能，mysql 可以以循环方式将日志文件写到多个文件，推荐设置为 3：innodb_log_iles_in_group = 3

    mysql 读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，mysql 会为它分配一端内存缓冲区，如果对表的顺序扫描请求非常频繁，并且认为频繁扫描进行太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size 一样，该参数对应的分配内存也是每个连接独享。read_buffer_size = 1M

    随机读缓冲区大小，当按任意顺序读取行时，将分配一个随机读缓冲区，进行排序查询是，mysql 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 mysql 会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，避免内存开销过大。read_rnd_buffer_size = 16M

    bulk_insert_buffer_size = 64M 批量插入数据缓存大小，可以有效提高插入效率，默认为8M

    binary log

    log-bin=/usr/local/mysql/data/mysql-bin

    binlog_cache_size = 2M  //为每个 session 分配的内存，在事务过程中用来存储二进制日志的 缓存 , 提高记录 bin-log 的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是–1M，后者建议是：即 2–4M

max_binlog_cache_size = 8M //表示的是 binlog 能够使用的最大 cache 内存大小

max_binlog_size= 512M  //指定 binlog 日志文件的大小，如果当前的日志大小达到 max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB 或小于 4096 字节。默认值是 1GB。在导入大容量的sql 文件时，建议关闭 sql_log_bin，否则 硬盘 扛不住，而且建议定期做删除。

expire_logs_days = 7  //定义了 mysql 清除过期日志的时间。
二进制日志自动删除的天数。默认值为 0, 表示 “ 没有自动删除”。

mysqladmin flush-logs 也可以重新开始新的binarylog

在优化之前执行 mysqlslap 工具进行测试

[root@localhost ~]#mysqlslap–defaults-file=/etc/my.cnf –concurrency=10 –iterations=1–create-schema=’test1′ –query=’select * from test1.tb1′ –engine=innodb–number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

Benchmark

        Runningfor engine innodb

        Averagenumber of seconds to run all queries: 13.837 seconds

        Minimumnumber of seconds to run all queries: 13.837 seconds

        Maximumnumber of seconds to run all queries: 13.837 seconds

        Numberof clients running queries: 10

        Averagenumber of queries per client: 200

优化之后执行 mysqlslap 工具进行测试

[root@localhost ~]#mysqlslap –defaults-file=/etc/my.cnf–concurrency=10 –iterations=1 –create-schema=’test1′ –query=’select * fromtest1.tb1′ –engine=innodb –number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

Benchmark

        Runningfor engine innodb

        Averagenumber of seconds to run all queries: 4.199 seconds

        Minimumnumber of seconds to run all queries: 4.199 seconds

        Maximumnumber of seconds to run all queries: 4.199 seconds

        Numberof clients running queries: 10

        Averagenumber of queries per client: 200

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141899.htm