MySQL配置优化

2019-02-16 09:21:12   MySQL

MySQL优化总结 - Create by Vien 2016.11.27

自己不断尝试,总结的。有错误请大家指出,谢谢~ 注: 下文提到的所有状态值均可以通过SHOW STAUTS LIKE ''查询 变量值(配置项值)可以通过SHOW VARIABLES LIKE ''查询

key_buffer_size 只针对MyISAM表起作用,指定索引缓冲区大小,决定索引处理尤其是索引读的速度。通过Key_read_requests和Key_reads两个状态值,合理设置该项。应该使Key_reads/Key_read_requests尽可能小 其实也可以通过索引文件总共占有空间来设置这个值,但注意要是MyISAM表的索引文件。如果都是用的MyISAM引擎。可以用root去指定的schema数据库(存放了其他数据库的信息),也就是use information_schema;然后查询指定数据库索引文件大小:SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024),2),'MB') AS 'INDEX_TOTAL_SIZE' FROM TABLES WHERE table_schema = '数据库名字';

query_cache_size 查询缓存。query_cache_type要设置1.使用查询缓存效率可以提高到238%。可以从以下三个方面判断调整: 1.Qcache_lowmem_prunes很大,并且一直在增长,说明缓存经常不够 2.Qcache_hits很大,说明查询重复查询很多,若命中率Qcache_hits/(Qc*ache_hits+Qcache_inserts)100%很大,通常高于80%,就需要增加查询缓存大小 3.内存使用率(query_cache_size-Qcache_free_memory)/query_cache_size*100%,通常高于80%就需要增加查询缓存大小 总结:综合以上三种情况:查询缓存不足次数、命中率、内存使用率,在系统内存允许的情况下,适当调整查询缓存大小。

query_cache_limit 说到query_cache_size不得不提一下这个参数。试想如果一个草鸡炒鸡大的查询记录占了大量内存,而往往小查询记录是最有效的记录集,那么设置该项就很有必要了。默认为1M,尽量设置不要超过默认。

query_cache_min_res_unit 这也是与query_cache_size配套使用的。查询缓存最小结果集单元。设置得当会减少内存块申请分配次数,但是过大又会导致内存碎片数值上升。默认4K,建议1-16K。 系统稳定后,可以根据设置最小内存块为(query_cache_size-Qcache_free_memeory)/Qcache_queries_in_cache 很好理解,就是当前在缓存中的查询结果的平均占用内存大小,设置为最小块的大小。 建议定期进行碎片整理:FLUSH QUERY CACHE

table_open_cache 所有线程能打开的表的数量。通过检查状态值Open_tables和Opened_tables设定该项大小。如果当前打开表数量Open_tables与打开过表的数量Opened_tables接近,并且Opened_tables还在增长,是时候增加该项了!通常可以设置为以下状态: Open_tables/Opened_tables>=0.85 Open_tables/table_open_cache<=0.95 但也要视系统能力而定,因为对于Linux,通过文件描述符维护一个打开的文件,如果设置过大,超过系统处理文件描述符的能力,就会造成文件描述符不足,从而造成性能不稳定尤其是连接失败的情况。

thread_cache_size 可重用线程数。默认值是8+(max_connections/100)。可以根据线程缓存命中率(1-Thread_created/Connections)*100%优化调整此参数。 登陆root,use mysql,show status like ''查看,其中Thread_created表示创建过的线程数,Connections表示试图连接MySQL的次数。创建过的少,而试图连接的多,命中率自然就高。

max_connections "Too many connections"错误,说明max_connections值太小。常常是因为应用程序设计不合理导致交互时间过长,没有正确关闭数据库连接或者并发量较高。如果是前两者的问题可以通过应用程序中的连接池或者MySQL中的线程池有助于解决此问题。使用连接池通常将wait_timeout设置为一个较大的值,系统默认是8h。刚才提到MySQL的线程池设置是thread_cache_size,我认为如果有较好的数据库连接池模型使用thread_cache的意义不大。

back_log MySQL服务连接请求队列所能处理的最大连接请求数,如果队列满了,后续连接才会拒绝。如果短时间内有大量连接,可以适当增大此参数。其实也就是ServerSocket的accept()方法TCP连接监听队列大小问题,如果队列满了,就会阻塞。不过这个大小也跟OS有关。

slow-query-log 记录查询慢的情况,便于找到性能差的查询,进行调优。开启慢查询日志通常搭配以下配置使用: log_queries_not_using_indexs对没使用索引的进行检查 slow_query_log_file=文件路径,设置慢查询日志路径 long_query_time设置慢查询时间,也就是说超过多少秒就被写入日志

sort_buffer_size 可以根据Sort_merge_passes大小设定该项。那么问题来了:BUT WHY?经过老衲一番研究,官方文档也只是说了一句Sort_merge_passes大的时候需要增加sort_buffer_size,并没有说这个状态到底是个鸟东西。其实是这样的:MySQL的外部排序算法用了归并,然后这个值大就说明归并次数多,也就是说缓存小了呗。WHAT?归并是什么?我就想说一句:晚安!

join_buffer_size 通常增加索引是获取最快连接的方法。但是不能增加索引,使连接变快的方式就是增大这个参数。对于没有索引的多表复杂连接,需要多块连接内存。其实全局这个值可以设定较小,然后在会话级别个别调整。默认256K。

read_rnd_buffer_size 随机读缓冲大小。这个参数设置一个较大的值可以提升ORDER BY的性能,等等,那跟sort_buffer_size什么关系?其实MySQL从sort_buffer里取出大量指针,并使用行指针排序,当按照排序好的顺序执行读到read_rnd_buffer的时候,幸运的话,它是非常连续的。 还有一点需要注意的是,他跟read_buffer_size(为每个线程对MyISAm表执行顺序读所分配的内存)使用范围不一样,read_buffer_size限定于MyISAM使用,而该项所有引擎都可以用。相同的是这个也是会话级别的,所以全局不应该太大,在大查询会话中增加此参数就行了

max_heap_table_size 用户创建的memeory表允许增长的最大容量。通常与下面的tmp_table_size共同限定内存表大小,如果内存表使用频繁,可以增大这个参数值。

tmp_table_size 内存临时表的最大内存。超出部分会转化为磁盘临时表。过多使用GROUP BY,ORDER BY查询,而且系统内存充足可以考虑增大此参数。 别忘记提升ORDER BY性能还有上面说过的sort_buffer_size


华丽丽的分割线 下面是比较有(cao)趣(dan)的配置


thread_concurrency 网上好多人说要把这个调成系统核心数乘以2的值,以便于更好的利用CPU。其实官方手册也确实有提到这个,还有一些人说这个只是针对与另一个UNIX-LIKE系统Solaris,对于Linux来说不用理会该项,但有人亲测过在Linux下设置0会报错让调整为1,但是设置1在并发情况下必然阻塞,影响性能。 就我看来,whatever,老子用的5.7,早就废除这个参数了,不行你试试看,设置完了,让你MySQL服务起不来信不信。

skip_name_resolve 说好的"默认关闭,MySQL检查客户端连接会解析主机名,开启后MySQL服务只使用IP,提高性能,这种情况下,授权表中的Host字段必须为IP或者localhost。",然而亲测是只要打开此项就崩盘,无论用本机IP还是127.0.0.1或者localhost都连不上,最坑的是反复尝试了各种情况之后,应用程序就是连不上mysql服务,给我家Tomcat都搞出小情绪了,直接装死,不改回去不肯起来。 而且我不小心手贱写了个程序测试了一下开相同数量的connection,域名、IP地址速度几乎是没有差别的。

vien.tech版权所有,允许转载,但转载请注明出处和原文链接: https://vien.tech/article/66
欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

猜你喜欢


评论

There are no comments yet.
未登录

登录后即可发表评论

登录或注册