纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

MySQL参数优化 MYSQL配置参数优化详解

清酒故人   2021-03-31 我要评论

MySQL参数优化对于不同的网站及其在线量访问量帖子数量网络情况以及机器硬件配置都有关系优化不可能一次性完成需要不断的观察以及调试才有可能得到最佳的效果。

1)连接请求的变量

1、max_connections

MySQL的最大连接数如果服务器的并发连接请求量较大建议调高此值以增加并行连接数量当然这建立在机器能支撑的情况下因为如果连接数越多MySQL回味每个连接提供连接缓冲区就会开销越多的内存所以要适当调整该值不能盲目提高设值。

    数值过小经常会出现ERROR 1040:Too mant connetcions错误可以通过mysql>show status like ‘connections';通配符来查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)以定夺该值的大小。
    show variadles like ‘max_connections'最大连接数
    show variables like ‘max_used_connection'相应连接数
    max_used_connection/max_connections*100%(理想值约等于85%)
    如果max_used_connections和max_connections相同那么就是max_connections值设置过低或者超过服务器的负载上限了低于10%则设置过大了。

2、back_log

    MySQL能够暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求他就会起作用。如果MySQL的连接数据达到max_connections时新的请求将会被存在堆栈中以等待某一连接释放资源该堆栈数量即back_log如果等待连接的数量超过back_log将不被接受连接资源。

3、wait_timeout和interative_timeout

    wait_timeout:指的是MySQL再关闭一个非交互的连接之前所需要等待的秒数。
    interative_timeout:指的是关闭一个交互的连接之前所需要等待的秒数。

    对性能的影响
        wait_timeout
        (1)如果设置太小那么连接关闭的很快从而使一些持久的连接不起作用
        (2)如果设置太大容易造成连接打开时间过长在show processlist时能够看到太多的sleep状态的连接从而造成too many connections错误。
        (3)一般希望wait_timeuot尽可能的低
        interative_timeout的设置将对你的web application没有多大的影响

2)缓冲区变量

全局缓冲

4、key_buffer_size

key_buffer_size指定索引缓冲区的大小他决定索引的处理速度尤其是索引读的速度。通过检查状态值 key_read_requests和key_reads可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低至少是1:1001:1000更好(上述状态值可以使用show status like ‘key_read%'获得)

    未命中缓存的概率:
    key_cache_miss_rate = key_reads/key_read_requests*100%
    key_buffer_size只对MAISAM表起作用。

    如何调整key_buffer_size的值
    默认的配置数时8388608(8M)主机有4G内存可以调优值为268435456(256M)

5、query_cache_size(查询缓存简称QC)

    使用查询缓存MySQL将查询结果存放在缓冲区中今后对同样的select语句(区分大小写)将直接从缓冲区中读取结果。
    一个SQL查询如果以select开头那么MySQL服务器将尝试对其使用查询缓存。

    注:两个SQL语句只要相差哪怕是一个字符(例如 大小写不一样:多一个空格等)那么两个SQL将使用不同的cache

    通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理

    Qcache_free_blocks:缓存中相邻内存块的个数。如果该值显示过大则说明Query Cache中的内存碎片较多了。
    注:当一个表被更新后和他相关的cache block将被free。但是这个block依然可能存在队列中除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。

    Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够是需要增多还是过多了。

    Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。

    Qcache_inserts:表示多少次未命中而插入意思是新来的SQL请求在缓存中未找到不得不执行查询处理执行查询处理后把结果insert带查询缓存中。这样的情况次数越多表示查询缓存 应用到的比较少效果也就不理想。

    Qcache_lowmen_prunes:多少条Query因为内存不足而被清除出Query Cache通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看如果这个数字在不断增长就表示可能碎片化非常严重或者内存很少。

    Qcache_queries_in_cache:当前Query Cache 中cache的Query数量
    Qcache_total_blocks:当前Query Cache中block的数量

    查询服务器关于query_cache的配置
    各字段的解释:
    query_cache_limit:超出此大小的查询将不被缓存
    query_cache_min_res_unit:缓存块的最小大小query_cache_min_res_unit的配置是一柄双刃剑默认是 4KB 设置值大对大数据查询有好处但是如果你查询的都是小数据查询就容易造成内存碎片和浪费。
    query_cache_size:查询缓存大小(注:QC存储的单位最小是1024byte所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
    query_cache_type:缓存类型决定缓存什么样子的查询注意这个值不能随便设置必须设置为数字可选值以及说明如下:
        0:OFF 相当于禁用了
        1:ON 将缓存所有结果除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
        2:DENAND  则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
    query_cache_wlock_invalidate:当有其他客户端正在对MyISAM表进行写操作时如果查询在query cache中是否返回cache结果还是等写操作完成在读表获取结果。

    查询缓存碎片率:Qcache_free_block/Qcache_total_block*100%
    如果查询缓存碎片率超过20%可以用flush query cache整理缓存碎片或者试试减小query_cache_min_res_unit如果你的查询都是小数据量的话。

    查询缓存利用率:(query_cache_size-Qcache_free_memory)/query_cache_size*100%
    查询缓存利用率在25%以下的话说明query_cache_size设置过大可以适当减小:查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50
    的话说明query_cache_size可能有点小要不就是碎片太多

    查询缓存命中率:Qcache_hits/(Qcache_hits+Qcache_inserts)*100%

    Query Cache的限制
    a)所有子查询中的外部查询SQL 不能被Cache:
    b)在p'rocedurefunction以及trigger中的Query不能被Cache
    c)包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache

6、max_connect_errors:

是一个MySQL中与安全有关的计数器值他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况当超过指定次数MySQL服务器将禁止host的连接请求直到mysql服务器重启或通过flush hotos命令清空此host的相关信息。(与性能并无太大的关系)

7、sort_buffer_size:

每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作
    sort_buffer_size是一个connection级的参数在每个connection(session)第一次需要使用这个buffer的时候一次性分配设置的内存。
    sort_buffer_size:并不是越大越好由于是connection级的参数过大的设置+高并发可能会耗尽系统的内存资源。例如:500个连接将会消耗500*sort_buffer_size(2M)=1G

8、max_allowed_packet=32M

    根据配置文件限制server接受的数据包大小。

9、join_buffer_size=2M

    用于表示关联缓存的大小和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。

10、thread_cache_size=300

    服务器线程缓存这个值表示可以重新利用保存在缓存中的线程数量当断开连接时那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提时缓存数未达上限)如果线程重新被请求那么请求将从缓存中读取如果缓存中是空的或者是新的请求这个线程将被重新请求那么这个线程将被重新创建如果有很多新的线程增加这个值可以改善系统性能通过比较Connections和Threads_created状态的变量可以看到这个变量的作用。

    设置规则如下:1G内存配置为82G内存为16.服务器处理此客户的线程将会缓存起来以响应下一个客户而不是被销毁(前提是缓存数未到达上限)

    Threads_cached:代表当前此时此刻线程缓存中有多少空闲线程。
    Threads_connected:代表当前已建立连接的数量因为一个连接就需要一个线程所以也可以看成当前被使用的线程数。
    Threads_created:代表最近一次服务启动已创建线程的数量如果发现Threads_created值过大的话说明MySQL服务器一直在创建线程这也比较消耗资源可以适当增加配置文件中thread_cache_size值
    Threads_running:代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数有时候连接已建立但是连接处于sleep状态。

3)配置Innodb的几个变量

11、innodb_buffer_pool_size

对于innodb表来说innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。

12、innodb_flush_log_at_trx_commit

    主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点取值分别为012.

    实际测试发现该值对插入数据的速度影响非常大设置为2时插入10000条记录只需要两秒设置为0时只需要一秒设置为1时则需要229秒。因此MySQL手册也建议尽量将插入操作合并成一个事务这样可以大幅度提高速度。

13、innodb_thread_concurrency=0

    此参数用来设置innodb线程的并发数默认值为0表示不被限制若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。

14、innodb_log_buffer_size

    此参数确定日志文件所用的内存大小以M为单位。缓冲区更大能提高性能对于较大的事务可以增大缓存大小。

15、innodb_log_file_size=50M

    此参数确定数据日志文件的大小以M为单位更大的设置可以提高性能。

16、innodb_log_files_in_group=3

    为提高性能MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

17、read_buffer_size=1M

    MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配到一个读入缓冲区MySQL会为他分配一段内存缓冲区

18、read_rnd_buffer_size=16M

    MySQL 的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)将分配到一个随机都缓冲区。进行排序查询时MySQL会首先扫描一遍该缓冲区以避免磁盘搜索提高查询速度如果需要排序大量数据可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存消耗过大。
    注:顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据而辅助索引和主键所在的数据端不同因此访问方式是随机的。

19、bulk_insert_buffer_size=64M

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

20、binary log

    binlog_cache_size=2M   //为每个session分配的内存在事务过程中用来存储二进制日志的缓存提高记录bin-log的效率。

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

    max_binlog_size=512M  //指定binlog日志文件的大小。不能将变量设置为大于1G或小于4096字节。默认值为1G.在导入大容量的sql文件时建议关闭sql_log_bin否则硬盘扛不住而且建议定期做删除。

    expire_logs_days=7  //定义了mysql清除过期日志的时间

    参数汇总:

  [mysqld]
  basedir = /usr/local/mysql
  datadir = /usr/local/mysql/data
  server_id = 1
  socket = /usr/local/mysql/mysql.sock
  log-error = /usr/local/mysql/data/mysqld.err
  slow_query_log = 1
  slow_query_log_file=/usr/local/mysql/data/slow-query.log
  long_query_time = 1
  log-queries-not-using-indexes
  max_connections = 1024
  back_log = 128
  wait_timeout = 60
  interactive_timeout = 7200
  key_buffer_size = 256M
  query_cache_size = 256M
  query_cache_type = 1
  query_cache_limit = 50M 
  max_connect_errors = 20
  sort_buffer_size = 2M
  max_allowed_packet = 32M
  join_buffer_size = 2M
  thread_cache_size = 200
  innodb_buffer_pool_size = 2048M
  innodb_flush_log_at_trx_commit = 1
  innodb_log_buffer_size = 32M
  innodb_log_file_size = 128M
  innodb_log_files_in_group = 3
  log-bin=/usr/local/mysql/data/mysqlbin
  binlog_cache_size = 2M 
  max_binlog_cache_size = 8M
  max_binlog_size = 512M
  expire_logs_days = 7
  read_buffer_size = 1M
  read_rnd_buffer_size = 16M
  bulk_insert_buffer_size = 64M
  # Remove leading # and set to the amount of RAM for the most important data
  # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
  # innodb_buffer_pool_size = 128M

  # Remove leading # to turn on a very important data integrity option: logging
  # changes to the binary log between backups.
  # log_bin

  # These are commonly set, remove the # and set as required.
  # basedir = .....
  # datadir = .....
  # port = .....
  # server_id = .....
  # socket = .....

  # Remove leading # to set options mainly useful for reporting servers.
  # The server defaults are faster for transactions and fast SELECTs.
  # Adjust sizes as needed, experiment to find the optimal values.
  # join_buffer_size = 128M
  # sort_buffer_size = 2M
  # read_rnd_buffer_size = 2M 

  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

相关文章

猜您喜欢

网友评论

Copyright 2020 www.Shellfishsoft.com 【贝软下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式