Linux运维基础知识之Mysql性能优化

今天小编要跟大家分享的文章是关于Linux运维基础知识之Mysql性能优化的介绍。想要学习Linux运维相关知识的小伙伴们来和小编一起看一看本篇文章吧，希望能够对小伙伴们有所帮助。

1、 查看MySQL服务器配置信息

mysql> show variables;

2、 查看MySQL服务器运行的各种状态值

mysql> show global status;

3、 慢查询

mysql> show variables like '%slow%';

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

| Variable_name | Value |

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

| log_slow_queries | OFF |

| slow_launch_time | 2 |

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

mysql> show global status like '%slow%';

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

| Variable_name | Value |

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

| Slow_launch_threads | 0 |

| Slow_queries | 279 |

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

配置中关闭了记录慢查询(最好是打开，方便优化)，超过2秒即为慢查询，一共有279条慢查询

4、 连接数

mysql> show variables like 'max_connections';

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

| Variable_name | Value |

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

| max_connections | 500 |

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

mysql> show global status like 'max_used_connections';

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

| Variable_name | Value |

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

| Max_used_connections | 498 |

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

设置的最大连接数是500，而响应的连接数是498

max_used_connections / max_connections * 100% = 99.6% (理想值 ≈ 85%)

5、 key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb

mysql> show variables like 'key_buffer_size';

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

| Variable_name | Value |

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

| key_buffer_size | 67108864 |

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

mysql> show global status like 'key_read%';

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

| Variable_name | Value |

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

| Key_read_requests | 25629497 |

| Key_reads | 66071 |

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

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

key_cache_miss_rate = Key_reads / Key_read_requests * 100% =0.27%

需要适当加大key_buffer_size

mysql> show global status like 'key_blocks_u%';

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

| Variable_name | Value |

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

| Key_blocks_unused | 10285 |

| Key_blocks_used | 47705 |

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

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (理想值 ≈ 80%)

6、临时表

mysql> show global status like 'created_tmp%';

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

| Variable_name | Value |

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

| Created_tmp_disk_tables | 4184337 |

| Created_tmp_files | 4124 |

| Created_tmp_tables | 4215028 |

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

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数：

Created_tmp_disk_tables / Created_tmp_tables * 100% = 99% (理想值<= 25%)

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');

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

| Variable_name | Value |

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

| max_heap_table_size | 134217728 |

| tmp_table_size | 134217728 |

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

需要增加tmp_table_size

7、open table 的情况

mysql> show global status like 'open%tables%';

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

| Variable_name | Value |

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

| Open_tables | 1024 |

| Opened_tables | 1465 |

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

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 | 1024 |

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

Open_tables / Opened_tables * 100% =69% 理想值 (>= 85%)

Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)

8、 进程使用情况

mysql> show global status like 'Thread%';

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

| Variable_name | Value |

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

| Threads_cached | 31 |

| Threads_connected | 239 |

| Threads_created | 2914 |

| Threads_running | 4 |

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

如果我们在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 | 32 |

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

9、查询缓存(query cache)

mysql> show global status like 'qcache%';

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

| Variable_name | Value |

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

| Qcache_free_blocks | 2226 |

| Qcache_free_memory | 10794944 |

| Qcache_hits | 5385458 |

| Qcache_inserts | 1806301 |

| Qcache_lowmem_prunes | 433101 |

| Qcache_not_cached | 4429464 |

| Qcache_queries_in_cache | 7168 |

| Qcache_total_blocks | 16820 |

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

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 | 33554432 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 33554432 |

| 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%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

以上就是小编今天为大家分享的关于Linux运维基础知识之Mysql性能优化的文章，希望本篇文章能够对正在从事Linux运维工作的小伙伴们有所帮助，想要了解更多Linux相关知识记得关注达内Linux培训官网。最后祝愿小伙伴们工作顺利。

【免责声明：本文图片及文字信息均由小编转载自网络，旨在分享提供阅读，版权归原作者所有，如有侵权请联系我们进行删除。】