从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • mysql一些常用的my.conf变量和 show global status

mysql一些常用的my.conf变量和 show global status

发布:蔺要红09-03分类: MYSQL


参考博客  https://blog.csdn.net/loophome/article/details/46549921
 
# ------------------------------------慢查询相关------------------------------------

show variables like '%slow_query_log%';  # 查看慢日志是否开启,以及文件存放位置
show variables like 'long_query_time';   # 查看多少秒的SQL语句会被记录
show global status like '%slow%';  # 查看有多少条慢语句

# ------------------------------------连接数------------------------------------

show variables like 'max_connections';          # 查看服务器最大支持的连接数
mysql> show global status like '%connections';  # 查看总共的连接数/峰值连接数
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Connections          | 2456  |  # 总共连接数
| Max_used_connections | 10    |  # 峰值连接数
+----------------------+-------+
2 rows in set (0.01 sec)

# ------------------------------------ 线程使用情况 ------------------------------------
# 当客户端断开时,服务器处理此客户请求的线程将会缓存起来以响应一下客户而不是销毁(前提是缓存数未达上线)Thread_created表示创建过的线程数
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 7     |   
| Threads_connected | 3     |
| Threads_created   | 10    | # 表示创建过的线程数,如果过大的话,表明MySQL服务器一直在创建线程,这也是比较耗资源,可以适当增加配置文件中thread_cache_size值
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> show variables like 'thread_cache_size'; # 查看大小
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 256   |   # 4-64 8-128 16-256
+-------------------+-------+
1 row in set (0.00 sec)

# ------------------------------------Key_buffer_size------------------------------------

mysql>  show variables like 'key_buffer_size';  #  是对MyISAM表性能影响最大的一个参数
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'key_read%'; #  Key_buffer_size使用情况
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 99    |
| Key_reads         | 22    |
+-------------------+-------+
2 rows in set (0.00 sec)

# 索引命中率 = (1 - (Key_reads / Key_read_requests)) * 100%  

# ------------------------------------ Open Table情况 ------------------------------------

mysql> show variables like '%table_open%';  # 最大不要超过2048M(2-192,4-614,8G-1024,16G-2048,)
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2048  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+ 
| Open_tables   | 1192  |       # 表示已打开的表的数量,若过大,增加table_cache_size,
| Opened_tables | 1505  |       # 表示打开过的表数量
+---------------+-------+
2 rows in set (0.00 sec)
# 推荐值
# open_tables / opened_tables* 100% > = 85%
# open_tables / table_open_cache* 100% < = 95%

# ------------------------------------查询缓存(query cache) ------------------------------------

mysql>  show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 3         | # 缓存中相邻内存块的个数,数目大说明可能有碎片,FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块
| Qcache_free_memory      | 303192176 | # 缓存中的空闲内存
| Qcache_hits             | 462282    | # 每次查询在缓存中命中时就增大
| Qcache_inserts          | 106507    | # 每次插入一个查询时就增大,命中次数除以插入次数就是不中比率
| Qcache_lowmem_prunes    | 0         | # 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数,这个数字最好长时间来看:如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少
| Qcache_not_cached       | 10676     | # 不适合进行缓存的查询的数量,通常是由于这些查询不是SELECT语句或者用了now()之类的函数
| Qcache_queries_in_cache | 77146     | # 当前缓存的查询(和响应)的数量
| Qcache_total_blocks     | 155204    | # 缓存中块的数量
+-------------------------+-----------+
8 rows in set (0.00 sec)
# 查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
# 查询缓存命中率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

mysql> show variables like 'query_cache%';  # query_cache的配置
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   | # 超过此大小的查询将不缓存
| query_cache_min_res_unit     | 4096      | # 缓存块的最小大小,默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。
| query_cache_size             | 402653184 | # 查询缓存大小
| query_cache_type             | ON        | # 缓存类型,决定缓存什么样的查询
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

# ------------------------------------排序使用情况------------------------------------

mysql> show global status like 'sort%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+ 
| Sort_merge_passes | 0      | # 若值过大,增加sort_buffer_size
| Sort_range        | 2591   |
| Sort_rows         | 102622 |
| Sort_scan         | 8911   |
+-------------------+--------+
4 rows in set (0.00 sec)

# 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 并不一定能提高速度


# -----------------------------------文件打开数(open_files)------------------------------------
mysql>  show global status like 'open_files';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 155   | # 比较合适的设置:Open_files / open_files_limit * 100% <= 75%
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'open_files_limit'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

# -----------------------------------表锁情况------------------------------------

mysql> show global status like 'table_locks%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 159677 |  # 表示立即释放表锁数
| Table_locks_waited    | 0      |  # 表示需要等待的表锁数
+-----------------------+--------+
2 rows in set (0.00 sec)

# -----------------------------------表扫描情况-----------------------------------

mysql> show global status like 'handler_read%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Handler_read_first    | 26042    |
| Handler_read_key      | 635308   |
| Handler_read_last     | 444      |
| Handler_read_next     | 739227   |
| Handler_read_prev     | 59983    |
| Handler_read_rnd      | 94241    |
| Handler_read_rnd_next | 53261849 |
+-----------------------+----------+
7 rows in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 130523 |
+---------------+--------+
1 row in set (0.00 sec)

# 表扫描率 = Handler_read_rnd_next / Com_select
# 如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好,增加read_buffer_size值会有一些好处,但最好不要超过8MB
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站