MySQL調優腳本tuning-primer.sh使用說明

運作 tuning-primer.sh顯示說明

SLOW QUERIES

慢查詢檢查

SLOW QUERIESThe slow query log is enabled.

說明我已經啟用了慢查詢記錄功能。也就是參數

slow_query_log = 1

Current long_query_time = 5.000000 sec.

慢查詢的閥值時間。也就是參數

long_query_time = 5

You have 17 out of 638844 that take longer than 5.000000 sec. to complete

說明慢查詢日志中記錄了17條查詢時間超過5秒的語句。

slow_query_log_file=/data/ats_db/mysql-slow.log設定慢查詢日志路徑。使用

mysqldumpslow指令查詢慢日志

Your long_query_time seems to be fine

慢查詢閥值時間設定得在推薦的範圍内

BINARY UPDATE LOG

更新二進制日志檔案

The binary update log is enabled

這項說明啟用了bin-log日志功能。參數

log-bin = /data/ats_db/mysql-bin

Binlog sync is not enabled, you could loose binlog records during a server crash

沒有啟用 sync_binlog 選項。也即是将二進制日志實時寫入到磁盤通過 sync_binlog=1來指定

WORKER THREADS

工作線程

Current thread_cache_size = 8

目前線程緩存大小。

thread_concurrency = 8

Current threads_cached = 7

Show status like ‘threads_cached’

Current threads_per_sec = 0

腳本先執行Show status like ‘Threads_cached’檢視目前的線程建立情況，然後sleep 1後在執行相同的指令，最終後者減去前者的數就是每秒線程建立數。

Historic threads_per_sec = 0

該值是使用Threads_cached /uptime獲得的。

Your thread_cache_size is fine

MAX CONNECTIONS

最大連接配接數

Current max_connections = 1024

目前配置檔案中設定的并發連接配接數

Current threads_connected = 2

目前線程連接配接诶數。

show status like ‘Threads_connected’

Historic max_used_connections = 4

show status like ‘Max_used_connections’;

The number of used connections is 0% of the configured maximum.

這個值使用 Max_used_connections*100/ max_connections得出。

You are using less than 10% of your configured max_connections. Lowering max_connections could help to avoid an over-allocation of memory

See “MEMORY USAGE” section to make sure you are not over-allocating

Max_used_connections的值不足max_connections值的10%。設定合适的max_connections值有助于節省記憶體。

MEMORY USAGE

記憶體使用

Max Memory Ever Allocated : 841 M

Max Memory Ever Allocated = max_memory

Configured Max Per-thread Buffers : 28.40 G

Configured Max Per-thread Buffers = per_thread_buffers

Configured Max Global Buffers : 586 M

Configured Max Global Buffers = global_buffers

Configured Max Memory Limit : 28.97 G

Configured Max Memory Limit = total_memory

這一項很重要，他是将各個緩存的大小累加，然後同max_connections相乘，進而得出當達到max_connections後需要配置設定的記憶體有多少。我這裡由于max_connections寫得很大，造成了最大記憶體限制超過了真實記憶體很多，是以建議不要随意增大max_connections的值。減小 max_connections的值，最終保證最大記憶體限制在真實記憶體的90%以下。

Physical Memory : 7.79 G

實際實體記憶體

Max memory limit exceeds 90% of physical memory

per_thread_buffers

(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack+

join_buffer_size+binlog_cache_size)*max_connections

per_thread_max_buffers

(read_buffer_size+read_rnd_buffer_size +sort_buffer_size+thread_stack

+join_buffer_size+binlog_cache_size)*max_used_connections

max_used_connections=$(mysql -Bse "show status like 'Max_used_connections'" | awk '{ print $2 }')

global_buffers

innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+

key_buffer_size+query_cache_size

max_memory=global_buffers+per_thread_max_buffers

total_memory=global_buffers+per_thread_buffers

KEY BUFFER

Key 緩沖

Current MyISAM index space = 222 K

目前資料庫MyISAM表中索引占用磁盤空間

Current key_buffer_size = 512 M

MySQL配置檔案中key_buffer_size 設定的大小

Key cache miss rate is 1 : 3316

Key_read_requests/ Key_reads 這裡說明3316次讀取請求中有1次丢失(也就是說1次讀取磁盤)

Key buffer free ratio = 81 %

key_blocks_unused * key_cache_block_size / key_buffer_size * 100

Your key_buffer_size seems to be fine

QUERY CACHE

Query 緩存

Query cache is enabled

該項說明 我們指定了query_cache_size 的值。如果query_cache_size=0的話這裡給出的提示是：

Query cache is supported but not enabled

Perhaps you should set the query_cache_size

Current query_cache_size = 64 M

目前系統query_cache_size 值大小 [F]

Current query_cache_used = 1 M

query_cache_used =query_cache_size-qcache_free_memory

Current query_cache_limit = 128 M

變量 query_cache_limit 大小

Current Query cache Memory fill ratio = 1.79 %

query_cache_used/query_cache_size *100%

Current query_cache_min_res_unit = 4 K

show variables like ‘query_cache_min_res_unit’;

Your query_cache_size seems to be too high.

Perhaps you can use these resources elsewhere

這項給出的結論是query_cache_size的值設定的有些過高。其比對标準是 “Query cache Memory fill ratio”的值如果小于<25%就會給出這個提示。可以将這些資源應用到其他的地方

MySQL won’t cache query results that are larger than query_cache_limit in size

MySQL不會将大于query_cache_limit的查詢結果進行緩存

show status like ‘Qcache%’;Qcache_free_blocks        10

Qcache_free_memory        65891984

Qcache_hits            14437

Qcache_inserts            707

Qcache_lowmem_prunes    0

Qcache_not_cached        216

Qcache_queries_in_cache    540

Qcache_total_blocks        1191

SORT OPERATIONS

SORT 選項

Current sort_buffer_size = 6 M

show variables like ’sort_buffer%’;

Current read_rnd_buffer_size = 16 M

show variables like ‘read_rnd_buffer_size%’;

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 132.00 K

show variables like ‘join_buffer_size%’;join_buffer_size= join_buffer_size+4kb

You have had 6 queries where a join could not use an index properly

這裡的6是通過 show status like ‘Select_full_join’; 獲得的

You should enable “log-queries-not-using-indexes”

Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your

join_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size when

ANY joins not using indexes are found.

你需要啟用 “log-queries-not-using-indexes” 然後在慢查詢日志中看是否有取消索引的joins語句。如果不優化查詢語句的話，則需要增大join_buffer_size。

OPEN FILES LIMIT

檔案打開數限制

Current open_files_limit = 1234 files

show variables like ‘open_files_limit%’;

The open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.

如果系統中有很多的MyISAM類型的表，則建議将open_files_limit 設定為2X~3X的table_open_cache

show status like ‘Open_files’;open_files_ratio= open_files*100/open_files_limit

如果open_files_ratio 超過75% 則需要加大open_files_limit

Your open_files_limit value seems to be fine

TABLE CACHE

TABLE 緩存

Current table_open_cache = 512 tables

show variables like ‘table_open_cache’;

Current table_definition_cache = 256 tables

show variables like ‘ table_definition_cache ‘;

You have a total of 368 tables

SELECTCOUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’

You have 371 open tables.

show status like ‘Open_tables’;

The table_cache value seems to be fine

Open_tables /table_open_cache*100% < 95%

You should probably increase your table_definition_cache value.

table_cache_hit_rate =open_tables*100/opened_tables

TEMP TABLES

臨時表

Current max_heap_table_size = 16 M

show variables like ‘max_heap_table_size’;

Current tmp_table_size = 16 M

show variables like ‘tmp_table_size’;

Of 285 temp tables, 11% were created on disk

Created_tmp_tables=285created_tmp_disk_tables*100/

(created_tmp_tables+created_tmp_disk_tables)=11%

Created disk tmp tables ratio seems fine

TABLE SCANS

掃描表

Current read_buffer_size = 6 M

show variables like ‘read_buffer_size’;

Current table scan ratio = 9 : 1

read_rnd_next =show global status like ‘Handler_read_rnd_next’;

com_select= show global status like ‘Com_select’;

full_table_scans=read_rnd_next/com_select

Current table scan ratio = full_table_scans : 1″

如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 0 : 5617

show global status like’Table_locks_waited’;

show global status like

‘Questions’;

如果 Table_locks_waited=0

Current Lock Wait ratio = 0: Questions

Your table locking seems to be fine

本文轉自 liang3391 51CTO部落格，原文連結:http://blog.51cto.com/liang3391/748795