Mysql 功能装备ITeye - 超凡娱乐

Mysql 功能装备ITeye

2019-01-10 20:23:04 | 作者: 运乾 | 标签: 装备,查询,缓存 | 浏览: 1396

依照从大到小,从首要到非必须的方式,剖析 mysql 功能优化点,到达终究优化的作用。

运用 mindmanger 收拾了思路,构成如下图,每个点在网上都能找到阐明,并记载下。构成了优化的思路:

 

1 衔接 Connections

经常会遇见”mysql: error 1040: too many connections”的状况,一种是访问量的确很高,mysql效劳器抗不住,这个时分就要考虑添加从效劳器涣散读压力,别的一种状况是mysql装备文件中max_connections值过小: 

mysql show variables like ‘max_connections‘;

+-----------------+-------+

| variable_name | value   |

+-----------------+-------+

| max_connections | 256 |

+-----------------+-------+ 

这台mysql效劳器最大衔接数是256,然后查询一下效劳器呼应的最大衔接数:

 mysql show global status like ‘max_used_connections‘; 

mysql效劳器曩昔的最大衔接数是245,没有到达效劳器衔接数上限256,应该没有呈现1040过错,比较抱负的设置是

 max_used_connections / max_connections * 100% ≈ 85% 

最大衔接数占上限衔接数的85%左右,假如发现份额在10%以下,mysql效劳器衔接数上限设置的过高了。

 

2  线程 Thread

mysql show global status like ‘thread%‘;

+-------------------+-------+

| variable_name |   value |

+-------------------+-------+

| threads_cached |    46 |

| threads_connected | 2 |

| threads_created | 570  |

| threads_running  | 1   |

+-------------------+-------+ 

假如咱们在mysql效劳器装备文件中设置了thread_cache_size,当客户端断开之后,效劳器处理此客户的线程将会缓存起来以呼应下一个客户而不是毁掉(条件是缓存数未达上限)。

threads_created标明创立过的线程数,假如发现threads_created值过大的话,标明mysql效劳器一直在创立线程,这也是比较耗资源,能够恰当添加装备文件中thread_cache_size值,

查询效劳器 thread_cache_size 装备: 

mysql show variables like ‘thread_cache_size‘;

+-------------------+-------+

| variable_name | value   |

+-------------------+-------+

| thread_cache_size | 64 |

+-------------------+-------+ 

示例中的效劳器仍是挺健康的。

 

3  缓存 cache

3.1 文件翻开数

mysql show global status like ‘open_files‘;

+---------------+-------+

| variable_name | value |

+---------------+-------+

| open_files | 1410 |

+---------------+-------+

 mysql show variables like ‘open_files_limit‘;

+------------------+-------+

| variable_name | value |

+------------------+-------+

| open_files_limit    | 4590 |

+------------------+-------+ 

比较适宜的设置:open_files / open_files_limit * 100% = 75%

3.2 数据表

3.2.1 翻开数 open_tables

mysql show global status like ‘open%tables%‘;

+---------------+-------+

| variable_name | value   |

+---------------+-------+

| open_tables | 919 |

| opened_tables | 1951 |

+---------------+-------+ 

open_tables: 翻开表的数量

opened_tables: 翻开过的表数量

假如 opened_tables 数量过大,阐明装备中 table_cache(5.1.3之后这个值叫做table_open_cache)值或许太小,咱们查询一下效劳器table_cache值: 

mysql show variables like ‘table_cache‘;

+---------------+-------+

| variable_name | value |

+---------------+-------+

| table_cache | 2048 |

+---------------+-------+ 

比较适宜的值为: 

open_tables / opened_tables * 100% = 85% 

open_tables / table_cache * 100% = 95%

 3.2.2 暂时表 tmp_table

mysql show global status like ‘created_tmp%‘;

+-------------------------+---------+

| variable_name | value |

+-------------------------+---------+

| created_tmp_disk_tables | 21197 |

| created_tmp_files | 58 |

| created_tmp_tables   | 1771587 |

+-------------------------+---------+ 

每次创立暂时表,created_tmp_tables 添加,假如是在磁盘上创立暂时表,created_tmp_disk_tables也添加,created_tmp_files标明mysql效劳创立的暂时文件文件数,比较抱负的装备是: 

created_tmp_disk_tables / created_tmp_tables * 100% = 25% 

比方上面的效劳器 created_tmp_disk_tables / created_tmp_tables * 100% = 1.20%,应该恰当好了。咱们再看一下mysql效劳器对暂时表的装备: 

mysql show variables where variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);

+---------------------+-----------+

| variable_name | value |

+---------------------+-----------+

| max_heap_table_size | 268435456 |

| tmp_table_size | 536870912 |

+---------------------+-----------+ 

只要 256mb 以下的暂时表才干悉数放内存,超越的就会用到硬盘暂时表。 

3.2.3 表锁状况

mysql show global status like ‘table_locks%‘;

+-----------------------+-----------+

| variable_name | value |

+-----------------------+-----------+

| table_locks_immediate | 490206328 |

| table_locks_waited | 2084912 |

+-----------------------+-----------+

 table_locks_immediate 标明当即开释表锁数, 

table_locks_waited 标明需求等候的表锁数, 

假如 table_locks_immediate / table_locks_waited 5000,最好选用innodb引擎,因为innodb是行锁而myisam是表锁,关于高并发写入的运用innodb作用会好些。 

示例中的效劳器 table_locks_immediate / table_locks_waited = 235,myisam就足够了。

3.2.4 表扫描状况

mysql show global status like ‘handler_read%‘;

+-----------------------+-------------+

| variable_name | value |

+-----------------------+-------------+

| handler_read_first | 5803750   |

| handler_read_key | 6049319850  |

| handler_read_next  | 94440908210 |

| handler_read_prev  | 34822001724 |

| handler_read_rnd | 405482605 |

| handler_read_rnd_next | 18912877839 |

+-----------------------+-------------+

 各字段解说拜见  ,调出效劳器完结的查询恳求次数: 

mysql show global status like ‘com_select‘;

+---------------+-----------+

| variable_name | value |

+---------------+-----------+

| com_select | 222693559 |

+---------------+-----------+ 

核算表扫描率: 

表扫描率 = handler_read_rnd_next / com_select 

假如表扫描率超越 4000,阐明进行了太多表扫描,很有或许索引没有建好,添加 read_buffer_size 值会有一些优点,但最好不要超越8mb。 

3.3 key_buffer_size

key_buffer_size是对myisam表功能影响最大的一个参数,下面一台以myisam为首要存储引擎效劳器的装备: 

mysql show variables like ‘key_buffer_size‘; 

+-----------------+------------+

| variable_name | value |

+-----------------+------------+

| key_buffer_size | 536870912 |

+-----------------+------------+ 

分配了 512mb 内存给 key_buffer_size ,咱们再看一下 key_buffer_size 的运用状况: 

mysql show global status like ‘key_read%‘;

+------------------------+-------------+

| variable_name | value |

+------------------------+-------------+

| key_read_requests | 27813678764 |

| key_reads | 6798830 |

+------------------------+-------------+

 一共有 27813678764个 索引读取恳求,有 6798830个 恳求在内存中没有找到直接从硬盘读取索引,核算索引未射中缓存的概率: 

key_cache_miss_rate = key_reads / key_read_requests * 100% 

比方上面的数据,key_cache_miss_rate为0.0244%,4000个索引读取恳求才有一个直接读硬盘,现已很bt了,key_cache_miss_rate在0.1%以下都很好(每1000个恳求有一个直接读硬盘),假如key_cache_miss_rate在0.01%以下的话,key_buffer_size分配的过多,能够恰当削减。 

【留意】key_read_buffer 默许值为 8M 。在专有的数据库效劳器上,该值可设置为 RAM * 1/4

mysql效劳器还供给了key_blocks_*参数: 

mysql show global status like ‘key_blocks_u%‘;

+------------------------+-------------+

| variable_name | value |

+------------------------+-------------+

| key_blocks_unused | 0 |

| key_blocks_used | 413543   |

+------------------------+-------------+ 

key_blocks_unused 标明未运用的缓存簇(blocks)数

key_blocks_used 标明从前用到的最大的blocks数

比方这台效劳器,一切的缓存都用到了,要么添加 key_buffer_size,要么便是过渡索引了,把缓存占满了。比较抱负的设置: 

key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%

3.4 排序运用状况 sort_buffer

mysql show global status like ‘sort%‘;

+-------------------+------------+

| variable_name | value |

+-------------------+------------+

| sort_merge_passes | 29 |

| sort_range | 37432840 |

| sort_rows | 9178691532 |

| sort_scan | 1860569 |

+-------------------+------------+ 

sort_merge_passes 包含两步。mysql 首先会测验在内存中做排序,运用的内存巨细由体系变量 sort_buffer_size 决议,假如它的巨细不行把一切的记载都读到内存中,mysql 就会把每次在内存中排序的成果存到暂时文件中,等 mysql 找到一切记载之后,再把暂时文件中的记载做一次排序。这再次排序就会添加 sort_merge_passes。实际上,mysql 会用另一个暂时文件来存再次排序的成果,所以通常会看到 sort_merge_passes 添加的数值是建暂时文件数的两倍。因为用到了暂时文件,所以速度或许会比较慢,添加 sort_buffer_size 会削减 sort_merge_passes 和 创立暂时文件的次数。但盲目的添加 sort_buffer_size 并不一定能进步速度,见 how fast can you sort data with mysql?(引自 ,形似被墙) 

别的,添加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的优点,拜见:

3.5 查询缓存

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 查询缓存变量解说: 

qcache_free_blocks:缓存中相邻内存块的个数。数目大阐明或许有碎片。flush query cache会对缓存中的碎片进行收拾,然后得到一个闲暇块。 

qcache_free_memory:缓存中的闲暇内存。 

qcache_hits:每次查询在缓存中射中时就增大 

qcache_inserts:每次刺进一个查询时就增大。射中次数除以刺进次数便是射中比率。 

qcache_lowmem_prunes:缓存呈现内存不足并且必需求进行收拾以便为更多查询供给空间的次数。这个数字最好长期来看;假如这个数字在不断增加,就标明或许碎片十分严峻,或许内存很少。(上面的 free_blocks和free_memory能够告诉您归于哪种状况) 

qcache_not_cached:不适合进行缓存的查询的数量,通常是因为这些查询不是 select 句子或许用了now()之类的函数。 

qcache_queries_in_cache:当时缓存的查询(和呼应)的数量。 

qcache_total_blocks:缓存中块的数量。 

 

咱们再查询一下效劳器关于query_cache的装备: 

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   |

+------------------------------+----------+ 

各字段的解说: 

query_cache_limit:超越此巨细的查询将不缓存 

query_cache_min_res_unit:缓存块的最小巨细 

query_cache_size:查询缓存巨细 

query_cache_type:缓存类型,决议缓存什么样的查询,示例中标明不缓存 select sql_no_cache 查询

query_cache_wlock_invalidate:当有其他客户规矩在对myisam表进行写操作时,假如查询在query cache中,是否回来cache成果仍是等写操作完结再读表获取成果。

query_cache_min_res_unit的装备是一柄”双刃剑”,默许是4kb,设置值大对大数据查询有优点,但假如你的查询都是小数据查询,就简单形成内存碎片和糟蹋。

查询缓存碎片率 = qcache_free_blocks / qcache_total_blocks * 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_inserts) / qcache_hits * 100% 

示例效劳器 查询缓存碎片率 = 20.46%,查询缓存运用率 = 62.26%,查询缓存射中率 = 1.94%,射中率很差,或许写操作比较频频吧,并且或许有些碎片。

4 其他

4.1 read_buffer_size

4.2 慢查询

mysql show variables like ‘%slow%‘;

+------------------+-------+

| variable_name   | value  |

+------------------+-------+

| log_slow_queries | on |

| slow_launch_time | 2 |

+------------------+-------+

mysql show global status like ‘%slow%‘;

+---------------------+-------+

| variable_name | value   |

+---------------------+-------+

| slow_launch_threads | 0   |

| slow_queries | 4148  |

+---------------------+-------+ 

装备中翻开了记载慢查询,执行时刻超越2秒的即为慢查询,体系显现有4148个慢查询,你能够剖析慢查询日志,找出有问题的sql句子,慢查询时刻不宜设置过长,不然含义不大,最好在5秒以内,假如你需求微秒等级的慢查询,能够考虑给mysql打补丁:,记住找对应的版别。 

翻开慢查询日志或许会对体系功能有一点点影响,假如你的mysql是主-从结构,能够考虑翻开其间一台从效劳器的慢查询日志,这样既能够监控慢查询,对体系功能影响又小。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表超凡娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    Linux翻开端口办法ITeye

    端口,翻开,保存
  • 3
  • 4

    Oracle的根本数据字典ITeye

    一切,用户,信息
  • 5
  • 6
  • 7
  • 8

    Oracle排序(转)ITeye

    排序,空间,暂时
  • 9

    数据导入导出ITeye

    数据,导入,导出
  • 10

    sysbench test oracleITeye

    测验,数据,生成