Mysql上线后优化项

小柒博客 MySQL1 78.9K3字数 6532阅读21分46秒阅读模式

查询Mysql服务器运行的各种状态值,命令如下:

mysql> show global status;

1、慢查询

查询慢查询日志相关命令如下:

mysql> show variables like '%slow%';

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

| Variable_name | Value |

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

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | slow.log |

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

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

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

| Variable_name | Value |

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

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

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

2、连接数

mysql> show variables like 'max_connections';

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

| Variable_name | Value |

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

| max_connections | 4096 |

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

服务器响应的最大连接数

mysql> show global status like "Max_used_connections";

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

| Variable_name | Value |

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

| Max_used_connections | 20 |

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

3、Key_buffer_size

key_buffer_size是设置MyISAM表索引擎缓存空间的大小,此参数对MyISAM表性能影响很大。

mysql> show variables like 'key_buffer_size';

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

| Variable_name | Value |

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

| key_buffer_size | 8388608 |

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

查询key_buffer_size使用情况

mysql> show global status like 'key_read%';

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

| Variable_name | Value |

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

| Key_read_requests | 102 |

| Key_reads | 5 |

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

一共有102个索引读请求,有5个请求在内存中没有找到,直接从硬盘读取索引,计算索引未命中缓存的概率:key_cache_miss_rate=Key_reads/Key_read_requests * 100

4、临时表

临时表数量查询命令如下:

mysql> show global status like 'created_tmp%';

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

| Variable_name | Value |

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

| Created_tmp_disk_tables | 4387 |

| Created_tmp_files | 5 |

| Created_tmp_tables | 188846 |

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

每次创建临时表时,Created_tmp_tables都会增加,如果是在磁盘上创建临时表,Created_tmp_disk_tables也会增加,Created_tmp_files表示MYSQL服务创建的临时文件数,比较理想的配置是:Created_tmp_disk_tables/Created_tmp_tables * 100 <= 25%

MYSQL服务器对临时表的配置:

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 |

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

5、Open Table的情况

Open_tables表示打开表的数量

Opened_tables表示打开过的表数量

查询命令如下:

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

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

| Variable_name | Value |

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

| Open_tables | 3118 |

| Opened_tables | 3183 |

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

比较合适的值为:

Open_tables / Opened_tables * 100% >= 85%

Open_tables / table_cache * 100% <= 95%

6、进程使用情况

Threads_created表示创建过的线程数,可以用如下命令查看:

mysql> show global status like 'Thread%';

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

| Variable_name | Value |

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

| Threads_cached | 1 |

| Threads_connected | 56 |

| Threads_created | 120 |

| Threads_running | 1 |

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

如果发现Threads_created的值过大,标明MYSQL服务器一直在创建线程,这也是比较耗资源的,可以适当增大thread_cache_size的值,查询服务器thread_cache_size配置,如下:

mysql> show variables like 'thread_cache_size';

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

| Variable_name | Value |

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

| thread_cache_size | 16 |

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

7、查询缓存

它涉及到两个参数,qurey_cache_size和qurey_cache_type,其中qurey_cache_size设置MYSQL的Query Cache大小,qurey_cache_type设置使用查询缓存的类型,查询命令如下:

mysql> show global status like 'qcache%';

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

| Variable_name | Value |

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

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 134200384 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 3577291 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

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

MYSQL查询缓存变量的相关解释如下所示:

Qcache_free_blocks:缓存中相邻内存块的个数,数目大说明可能有碎片,flush query cache会对缓存中的碎片进行整理,从而得到一个空闲快。

Qcache_free_memory:缓存中的空闲内存。

Qcache_hits:表示有多少次命中。

Qcache_inserts:每插入一个查询时就会增大。

Qcache_lowmem_prunes:表示有多少条Query因为内存不足而被清除出Query Cache。

Qcache_not_cached:不适合进行缓存的查询数量。

Qcache_queries_in_cache:当前缓存的查询(和响应)数量。

Qcache_total_blocks:缓存中块的数量。

查询服务器query_cache的配置:

mysql> show variables like 'query_cache%';

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

| Variable_name | Value |

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

| query_cache_limit | 4194304 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 134217728 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

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

各字段解释如下:

query_cache_limit:超过此大小的查询不缓存。

query_cache_min_res_unit:缓存块的最小值。

query_cache_size:查询缓存大小。

query_cache_type:缓存类型。

query_cache_wlock_invalidate:表示当有其他客户端正在进行MyISAN表进行写操作时,读请求是要等WRITE LOCK释放资源后在查询还是允许直接从Query Cache中读取结果,默认为FALSE。

8、排序使用情况

表示系统中对数据进行排序时使用的Buffer,我们可以用如下命令查看:

mysql> show global status like 'sort%';

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

| Variable_name | Value |

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

| Sort_merge_passes | 0 |

| Sort_range | 239040 |

| Sort_rows | 6264803 |

| Sort_scan | 641147 |

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

9、文件打开数

我们在处理MYSQL故障时,当open_files大于open_files_limit时,MYSQL数据库就会出现卡住的现象,导致WEB服务器打不开相应页面,查询命令如下:

mysql> show global status like 'open_files';

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

| Variable_name | Value |

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

| Open_files | 23 |

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

mysql> show variables like 'open_files_limit';
+------------------+-------+| Variable_name | Value |
+------------------+-------+
| open_files_limit | 15000 |
+------------------+-------+

比较合适的设置是:Open_files / Open_files_limit * 100% <= 75%

10、innodb_buffer_pool_size的合理设置

InnoDB存储引擎的缓存机制和MyISAM的最大区别在于,InnoDB不仅仅缓存索引,同时还会缓存实际的数据,此参数用于设置InnoDB最主要的buffer大小,也就是用户表及索引数据的最主要的缓存空间,对InnoDB的整体性能影响很大。

mysql> show status like 'Innodb_buffer_pool_%';

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

| Variable_name | Value |

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

| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 181130 14:25:04 |

| Innodb_buffer_pool_resize_status | |

| Innodb_buffer_pool_pages_data | 6897 |

| Innodb_buffer_pool_bytes_data | 113000448 |

| Innodb_buffer_pool_pages_dirty | 2507 |

| Innodb_buffer_pool_bytes_dirty | 41074688 |

| Innodb_buffer_pool_pages_flushed | 1399155 |

| Innodb_buffer_pool_pages_free | 1046 |

| Innodb_buffer_pool_pages_misc | 249 |

| Innodb_buffer_pool_pages_total | 8192 |

| Innodb_buffer_pool_read_ahead_rnd | 0 |

| Innodb_buffer_pool_read_ahead | 5888 |

| Innodb_buffer_pool_read_ahead_evicted | 131 |

| Innodb_buffer_pool_read_requests | 458760679 |

| Innodb_buffer_pool_reads | 340879 |

| Innodb_buffer_pool_wait_free | 147 |

| Innodb_buffer_pool_write_requests | 57994757 |

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

通过此命令得出的结果可以计算出InnoDB buffer pool的read命中率和write命中率

read命中率大约为:(458760679-340879) / 458760679 = 99.92%

write命中率大约为:6897 / 8192 = 84.2%

若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如本文“对您有用”,欢迎随意打赏,谢谢!

继续阅读
Wechat
微信扫一扫,加我!
weinxin
微信公众号
微信扫一扫,关注我!
weinxin
MySQL最后更新:2024-1-24
小柒博客
  • 本文由 小柒博客 发表于 2018年12月5日16:42:12
  • 声明:本站所有文章,如无特殊说明或标注,本站文章均为原创。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。转载请务必保留本文链接:https://www.yangxingzhen.com/1709.html
    • 运维老司机
      运维老司机 6

      亲测,写的不错,感谢博主 :razz:

    匿名

    发表评论

    匿名网友
    :?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

    拖动滑块以完成验证