day8-mysql調優

標籤：

DBA

資料庫伺服器????響應用戶端的串連請求特別慢

1. 硬體的配置過低——CPU、記憶體、硬碟
2. 網路傳輸速度
3. 用戶端訪問量過多，資料庫伺服器繁忙
4. 軟體問題——資料服務軟體的版本效能不行
5. 程式員編寫的SQL語句有問題

?

硬調優

軟調優——服務運行時的運行參數

?

1、用戶端串連資料庫伺服器？

2、資料庫伺服器響應用戶端的串連請求？

3、串連成功後，執行sql操作（查看 select、寫）

4、若執行的是查詢操作，先在查詢快取裡找資料，找到後直接返回給用戶端，若查詢快取裡沒有，到表對應的檔案裡去尋找——預設會把本次的查詢結果儲存到查詢快取裡，然後在把查詢結果返回給用戶端

?

mysql調優

設定資料庫伺服器運行時的運行參數

查看最多串連並發數——取決於硬體

mysql> show variables like "max_connections";

查看已串連並發數

mysql> show global status like "max_used_connections";

重新統計資料

mysql>flush status；

1、設定資料庫伺服器的並發串連數

臨時設定

mysql>set GLOBAL max_connections =值

永久設定

將max_connectinos=值寫到my.cnf檔案裡[mysqld]下面

?

max_used_connectinos????/ max_connectinos=值 * 100%=85%（理想值85%）

max_used_connectinos????/ max_connectinos=0.85 * 100%=85%

?

2、用戶端連線逾時時間的設定

mysql>sql命令

連線逾時時間

connect_timeout=值????單位為秒

串連後，等待發送指令的逾時時間

wait_timeout=值????單位為秒

預設這兩項一般不修改

?

3、緩衝空間的設定

索引緩衝空間????預設為8M????單位為位元組

mysql> show variables like "key_buffer_size"

mysql> show global status like ‘key_read%‘;

Key_read_requests???? 索引讀取請求總數、

Key_reads 記憶體中沒有找到直接從硬碟讀取索引資料

?

查詢快取空間

每個需要進行排序的線程分配該大小的緩衝區（增加此值加速order by或group by）——當分組和排序較多可把下面調大

mysql> show variables like "sort_buffer_size%";

從資料表順序讀取資料的讀操作保留的緩衝區的長度

mysql> show variables like "read_buffer_size%";

按某種特定順序（如何使用了ORDER BY子句的查詢）輸出的查詢結果

mysql> show variables like "read_rnd_buffer_size%";

可以重複使用的儲存在緩衝中線程的數量——預設值為0

mysql> show variables like "thread_cache_size";

查看進程使用方式

mysql> show global status like ‘thread%‘;

Threads_cached ????Threads_cached_size

Threads_connected????已有的連結

Threads_created????建立過的線程數

Threads_running????正在運作著的連結

所有線程開啟表的數量

mysql> show variables like "table%_cache%";

mysql> show global status like "open%table%";

Open_table_definitions ????

Open_tables ????????開啟表的數量

Opened_table_definitions ????

Opened_tables????????開啟過的表數量

?

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

?

4種類型日誌

binlog日誌——記錄發生改變的SQL語句

慢查詢日誌——記錄超出指定時間顯示查詢結果的SQL語句

查詢日誌——記錄用戶端串連自己後執行的所有SQL語句

????????general-log

????????general-log-file=路徑

錯誤記錄檔——記錄資料庫服務在啟動和運行過程中產生的錯誤——預設開啟——檔案名稱/var/lib/mysql/主機名稱.err——在設定檔log-error=路徑

?

啟用mysql資料庫伺服器的慢查詢日誌

slow-query-log

slow-query-log-file=路徑

long-query-time=逾時時間（單位秒）——當查詢時間超過X秒，才會被記錄

log-queries-not-using-indexes????記錄未使用索引的查詢

?

開啟日誌後重啟mysql服務

查看慢查詢記錄檔記錄的內容

使用mysqldumpslow????慢查詢記錄檔名

?

顯示當前資料庫伺服器關於查詢快取參數的設定

mysql> show variables like "query_cache%";

query_cache_limit????查詢結果超過裡面的值，就不儲存到查詢快取

query_cache_min_res_unit????緩衝塊的大小（相當於磁碟的4K）

query_cache_size????查詢快取的大小

query_cache_type????緩衝類型（0對應OFF，關閉查詢結果放入緩衝；1對應ON，開啟查詢結果放入緩衝；2，開啟查詢結果放入緩衝，但查詢時需加關鍵字）

query_cache_wlock_invalidate????當有其他用戶端下在對myisam表進行寫操作時，如果查詢在query cache 中，是否返回cache結果還是等寫操作完成再讀表擷取結果

?

查詢快取變數的值

mysql> show global status like "qcache%";

Qcache_free_blocks????空閑塊的塊數（值多了，有片段）

Qcache_free_memory ????緩衝中的空閑記憶體

Qcache_hits????記錄查詢快取的次數（每次失去一切都緩衝中命中時就自加1）

Qcache_inserts????????記錄查詢總的次數（每次插入一個查詢時就自加1，叫用次數除以插入次數就是不中比率）

Qcache_lowmem_prunes????當查詢快取空間不足時，記錄刪除的次數（緩衝出現記憶體不足時，並且必須要進程清理以便為更多查詢提供空間的次數）

Qcache_not_cached ????記錄查詢結果超過多大就不允許放入緩衝空間的次數（不適合進行緩衝的查詢數量）

Qcache_queries_in_cache ????當前緩衝的查詢（和響應）的數量

Qcache_total_blocks????緩衝中塊的數量

day8-mysql調優