下面一起來了解下MySQL中使用show variables like查詢,相信大家看完肯定會(huì)受益匪淺,文字在精不在多,希望MySQL中使用show variables like查詢這篇短內(nèi)容是你想要的。
我們提供的服務(wù)有:網(wǎng)站設(shè)計(jì)制作、成都做網(wǎng)站、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、根河ssl等。為上1000家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù),是有科學(xué)管理、有技術(shù)的根河網(wǎng)站制作公司
1, 查看MySQL云服務(wù)器配置信息
mysql> show variables;
2, 查看MySQL云服務(wù)器運(yùn)行的各種狀態(tài)值
mysql> show global status;
3, 慢查詢
1. mysql> show variables like '%slow%';
2. +------------------+-------+
3. | Variable_name | Value |
4. +------------------+-------+
5. | log_slow_queries | OFF |
6. | slow_launch_time | 2 |
7. +------------------+-------+
8. mysql> show global status like '%slow%';
9. +---------------------+-------+
10. | Variable_name | Value |
11. +---------------------+-------+
12. | Slow_launch_threads | 0 |
13. | Slow_queries | 279 |
14. +---------------------+-------+
配置中關(guān)閉了記錄慢查詢(最好是打開,方便優(yōu)化),超過2秒即為慢查詢,一共有279條慢查詢
4, 連接數(shù)
1. mysql> show variables like 'max_connections';
2. +-----------------+-------+
3. | Variable_name | Value |
4. +-----------------+-------+
5. | max_connections | 500 |
6. +-----------------+-------+
7.
8. mysql> show global status like 'max_used_connections';
9. +----------------------+-------+
10. | Variable_name | Value |
11. +----------------------+-------+
12. | Max_used_connections | 498 |
13. +----------------------+-------+
設(shè)置的最大連接數(shù)是500,而響應(yīng)的連接數(shù)是498
max_used_connections / max_connections * 100% = 99.6% (理想值 ≈ 85%)
5, key_buffer_size
key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù), 不過數(shù)據(jù)庫中多為Innodb
1. mysql> show variables like 'key_buffer_size';
2. +-----------------+----------+
3. | Variable_name | Value |
4. +-----------------+----------+
5. | key_buffer_size | 67108864 |
6. +-----------------+----------+
7.
8. mysql> show global status like 'key_read%';
9. +-------------------+----------+
10. | Variable_name | Value |
11. +-------------------+----------+
12. | Key_read_requests | 25629497 |
13. | Key_reads | 66071 |
14. +-------------------+----------+
一共有25629497個(gè)索引讀取請(qǐng)求,有66071個(gè)請(qǐng)求在內(nèi)存中沒有找到直接從硬盤讀取索引,計(jì)算索引未命中緩存的概率:
key_cache_miss_rate = Key_reads / Key_read_requests * 100% =0.27%
需要適當(dāng)加大key_buffer_size
1. mysql> show global status like 'key_blocks_u%';
2. +-------------------+-------+
3. | Variable_name | Value |
4. +-------------------+-------+
5. | Key_blocks_unused | 10285 |
6. | Key_blocks_used | 47705 |
7. +-------------------+-------+
Key_blocks_unused表示未使用的緩存簇(blocks)數(shù),Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (理想值 ≈ 80%)
6, 臨時(shí)表
1. mysql> show global status like 'created_tmp%';
2. +-------------------------+---------+
3. | Variable_name | Value |
4. +-------------------------+---------+
5. | Created_tmp_disk_tables | 4184337 |
6. | Created_tmp_files | 4124 |
7. | Created_tmp_tables | 4215028 |
8. +-------------------------+---------+
每次創(chuàng)建臨時(shí)表,Created_tmp_tables增加,如果是在磁盤上創(chuàng)建臨時(shí)表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務(wù)創(chuàng)建的臨時(shí)文件文件數(shù):
Created_tmp_disk_tables / Created_tmp_tables * 100% = 99% (理想值<= 25%)
1. mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
2. +---------------------+-----------+
3. | Variable_name | Value |
4. +---------------------+-----------+
5. | max_heap_table_size | 134217728 |
6. | tmp_table_size | 134217728 |
7. +---------------------+-----------+
需要增加tmp_table_size
7,open table 的情況
1. mysql> show global status like 'open%tables%';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | Open_tables | 1024 |
6. | Opened_tables | 1465 |
7. +---------------+-------+
Open_tables 表示打開表的數(shù)量,Opened_tables表示打開過的表數(shù)量,如果Opened_tables數(shù)量過大,說明配置中 table_cache(5.1.3之后這個(gè)值叫做table_open_cache)值可能太小,我們查詢一下云服務(wù)器table_cache值
1. mysql> show variables like 'table_cache';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | table_cache | 1024 |
6. +---------------+-------+
Open_tables / Opened_tables 100% =69% 理想值 (>= 85%)
Open_tables / table_cache 100% = 100% 理想值 (<= 95%)
8, 進(jìn)程使用情況
1. mysql> show global status like 'Thread%';
2. +-------------------+-------+
3. | Variable_name | Value |
4. +-------------------+-------+
5. | Threads_cached | 31 |
6. | Threads_connected | 239 |
7. | Threads_created | 2914 |
8. | Threads_running | 4 |
9. +-------------------+-------+
如果我們?cè)贛ySQL云服務(wù)器配置文件中設(shè)置了thread_cache_size,當(dāng)客戶端斷開之后,云服務(wù)器處理此客戶的線程將會(huì)緩存起來以響應(yīng)下一個(gè)客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限)。Threads_created表示創(chuàng)建過的線程數(shù),如果發(fā)現(xiàn)Threads_created值過大的話,表明 MySQL云服務(wù)器一直在創(chuàng)建線程,這也是比較耗資源,可以適當(dāng)增加配置文件中thread_cache_size值,查詢?cè)品?wù)器 thread_cache_size配置:
1. mysql> show variables like 'thread_cache_size';
2. +-------------------+-------+
3. | Variable_name | Value |
4. +-------------------+-------+
5. | thread_cache_size | 32 |
6. +-------------------+-------+
9, 查詢緩存(query cache)
1. mysql> show global status like 'qcache%';
2. +-------------------------+----------+
3. | Variable_name | Value |
4. +-------------------------+----------+
5. | Qcache_free_blocks | 2226 |
6. | Qcache_free_memory | 10794944 |
7. | Qcache_hits | 5385458 |
8. | Qcache_inserts | 1806301 |
9. | Qcache_lowmem_prunes | 433101 |
10. | Qcache_not_cached | 4429464 |
11. | Qcache_queries_in_cache | 7168 |
12. | Qcache_total_blocks | 16820 |
13. +-------------------------+----------+
Qcache_free_blocks:緩存中相鄰內(nèi)存塊的個(gè)數(shù)。數(shù)目大說明可能有碎片。FLUSH QUERY CACHE會(huì)對(duì)緩存中的碎片進(jìn)行整理,從而得到一個(gè)空閑塊。
Qcache_free_memory:緩存中的空閑內(nèi)存。
Qcache_hits:每次查詢?cè)诰彺嬷忻袝r(shí)就增大
Qcache_inserts:每次插入一個(gè)查詢時(shí)就增大。命中次數(shù)除以插入次數(shù)就是不中比率。
Qcache_lowmem_prunes:緩存出現(xiàn)內(nèi)存不足并且必須要進(jìn)行清理以便為更多查詢提供空間的次數(shù)。這個(gè)數(shù)字最好長時(shí)間來看;如果這個(gè)數(shù)字在不斷增長,就表示可能碎片非常嚴(yán)重,或者內(nèi)存很少。(上面的 free_blocks和free_memory可以告訴您屬于哪種情況)
Qcache_not_cached:不適合進(jìn)行緩存的查詢的數(shù)量,通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。
Qcache_queries_in_cache:當(dāng)前緩存的查詢(和響應(yīng))的數(shù)量。
Qcache_total_blocks:緩存中塊的數(shù)量。
我們?cè)俨樵円幌略品?wù)器關(guān)于query_cache的配置:
1. mysql> show variables like 'query_cache%';
2. +------------------------------+----------+
3. | Variable_name | Value |
4. +------------------------------+----------+
5. | query_cache_limit | 33554432 |
6. | query_cache_min_res_unit | 4096 |
7. | query_cache_size | 33554432 |
8. | query_cache_type | ON |
9. | query_cache_wlock_invalidate | OFF |
10. +------------------------------+----------+
各字段的解釋:
query_cache_limit:超過此大小的查詢將不緩存
query_cache_min_res_unit:緩存塊的最小大小
query_cache_size:查詢緩存大小
query_cache_type:緩存類型,決定緩存什么樣的查詢,示例中表示不緩存 select sql_no_cache 查詢
query_cache_wlock_invalidate:當(dāng)有其他客戶端正在對(duì)MyISAM表進(jìn)行寫操作時(shí),如果查詢?cè)趒uery cache中,是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。
query_cache_min_res_unit的配置是一柄”雙刃劍”,默認(rèn)是4KB,設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處,但如果你的查詢都是小數(shù)據(jù)查詢,就容易造成內(nèi)存碎片和浪費(fèi)。
查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢緩存碎片率超過20%,可以用FLUSH QUERY CACHE整理緩存碎片,或者試試減小query_cache_min_res_unit,如果你的查詢都是小數(shù)據(jù)量的話。
查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大,可適當(dāng)減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點(diǎn)小,要不就是碎片太多。
查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例云服務(wù)器 查詢緩存碎片率 = 20.46%,查詢緩存利用率 = 62.26%,查詢緩存命中率 = 1.94%,命中率很差,可能寫操作比較頻繁吧,而且可能有些碎片。
10,排序使用情況
1. mysql> show global status like 'sort%';
2. +-------------------+----------+
3. | Variable_name | Value |
4. +-------------------+----------+
5. | Sort_merge_passes | 2136 |
6. | Sort_range | 81888 |
7. | Sort_rows | 35918141 |
8. | Sort_scan | 55269 |
9. +-------------------+----------+
Sort_merge_passes 包括兩步。MySQL 首先會(huì)嘗試在內(nèi)存中做排序,使用的內(nèi)存大小由系統(tǒng)變量 Sort_buffer_size 決定,如果它的大小不夠把所有的記錄都讀到內(nèi)存中,MySQL 就會(huì)把每次在內(nèi)存中排序的結(jié)果存到臨時(shí)文件中,等 MySQL 找到所有記錄之后,再把臨時(shí)文件中的記錄做一次排序。這再次排序就會(huì)增加 Sort_merge_passes。實(shí)際上,MySQL 會(huì)用另一個(gè)臨時(shí)文件來存再次排序的結(jié)果,所以通常會(huì)看到 Sort_merge_passes 增加的數(shù)值是建臨時(shí)文件數(shù)的兩倍。因?yàn)橛玫搅伺R時(shí)文件,所以速度可能會(huì)比較慢,增加 Sort_buffer_size 會(huì)減少 Sort_merge_passes 和 創(chuàng)建臨時(shí)文件的次數(shù)。但盲目的增加 Sort_buffer_size 并不一定能提高速度,見 How fast can you sort data with MySQL?(引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html)
另外,增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對(duì)排序的操作也有一點(diǎn)的好處,參見:http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is- read_rnd_buffer_size/
11.文件打開數(shù)(open_files)
1. mysql> show global status like 'open_files';
2. +---------------+-------+
3. | Variable_name | Value |
4. +---------------+-------+
5. | Open_files | 821 |
6. +---------------+-------+
7.
8. mysql> show variables like 'open_files_limit';
9. +------------------+-------+
10. | Variable_name | Value |
11. +------------------+-------+
12. | open_files_limit | 65535 |
13. +------------------+-------+
比較合適的設(shè)置:Open_files / open_files_limit * 100% <= 75%
正常
12。 表鎖情況
1. mysql> show global status like 'table_locks%';
2. +-----------------------+---------+
3. | Variable_name | Value |
4. +-----------------------+---------+
5. | Table_locks_immediate | 4257944 |
6. | Table_locks_waited | 25182 |
7. +-----------------------+---------+
Table_locks_immediate 表示立即釋放表鎖數(shù),Table_locks_waited表示需要等待的表鎖數(shù),如果 Table_locks_immediate / Table_locks_waited > 5000,最好采用InnoDB引擎,因?yàn)镮nnoDB是行鎖而MyISAM是表鎖,對(duì)于高并發(fā)寫入的應(yīng)用InnoDB效果會(huì)好些.
1. mysql> show global status like 'handler_read%';
2. +-----------------------+-----------+
3. | Variable_name | Value |
4. +-----------------------+-----------+
5. | Handler_read_first | 108763 |
6. | Handler_read_key | 92813521 |
7. | Handler_read_next | 486650793 |
8. | Handler_read_prev | 688726 |
9. | Handler_read_rnd | 9321362 |
10. | Handler_read_rnd_next | 153086384 |
11. +-----------------------+-----------+
調(diào)出云服務(wù)器完成的查詢請(qǐng)求次數(shù):
計(jì)算表掃描率:
表掃描率 = Handler_read_rnd_next / Com_select
如果表掃描率超過4000,說明進(jìn)行了太多表掃描,很有可能索引沒有建好,增加read_buffer_size值會(huì)有一些好處,但最好不要超過8MB。
看完MySQL中使用show variables like查詢這篇文章后,很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容,如需獲取更多的行業(yè)信息,可以關(guān)注我們的行業(yè)資訊欄目。