mysql學習(2)-MySQL伺服器最佳化

標籤：

調優思路：

1.資料庫設計與規劃--以後再修該很麻煩，估計資料量，使用什麼儲存引擎

2.資料的應用--怎樣取資料，sql語句的最佳化

3.mysql服務最佳化--記憶體的使用，磁碟的使用

4.作業系統的最佳化--核心、tcp串連數量

5.升級硬體裝置

 

磁碟io規劃

raid技術：raid0[xfs]

swap分區：最好使用raid0

磁碟分割：一個庫放到一個分區上或一個磁碟上

物理分區

create table t1(id int,name char(20)) data directory=‘/data/‘ index directory =‘/data‘;

mkdir /data

chown mysql.mysql /data

 

mysql> show variables like ‘%part%‘;

 

 

4.作業系統的最佳化

網卡bonding技術，

tcp串連數量限制

最佳化系統開啟檔案的最大限制

關閉作業系統不必要的服務

 

5.mysql服務最佳化

show status  看系統的資源

show variables  看變數，在my.cnf設定檔裡定義的

show warnings   查看最近一個sql語句產生的錯誤警告，看其他的需要看.err日誌

show processlist顯示系統中正在啟動並執行所有進程。

show errors

 

啟用mysql慢查詢：---分析sql語句，找到影響效率的SQL

 

log-slow-queries=/var/lib/mysql/slow.log 這個路徑對mysql使用者具有可寫入權限

long_query_time=2   查詢超過2秒鐘的語句記錄下來

上面的2 是查詢的時間，即當一條SQL 執行時間超過5秒的時候才記錄，/var/lib/mysql/slow.log 是日誌記錄的位置。

然後重新啟動MySQL服務

 

對查詢進行緩衝

query_cache_size 使用多大記憶體來緩衝查詢語句[+8M]

mysql> show variables like‘%query%‘

query_cache_size=8M

[[email protected] mysql]# vim /etc/my.cnf

 

 

mysql> show status like ‘%Qcache%‘;

Qcache_free_blocks：說明緩衝太大了。緩衝中相鄰記憶體的個數。數目大說明可能有片段。FLUSH QUERY CACHE會對緩衝中的片段進行整理，從而得到一個空閑塊。[+8M]

Qcache_free_memory緩衝中的空閑記憶體

Qcache_hits每次查詢在緩衝中命中時就增大

Qcache_inserts每插入一個查詢時就增大。叫用次數除以插入次數就是命中率。

Qcache_lowmen_prunes緩衝出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間看；如果這個數字在不斷增長就表示可能片段非常嚴重，或者記憶體很少

Qcache_hits/Qcache_inserts 命中率

 

關鍵字緩衝區

mysql> show status like ‘%key%‘;

mysql> show variables like‘key_buffer_size‘;

 

key_buffer_size  指定索引緩衝的大小，它決定索引處理的速度，尤其是索引讀的速度。[+8M]

key_read_requests  請求總數

key_reads   代表命中磁碟的請求個數

(key_read_requests-key_read)/key_read_requests：命中率

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

對於1G記憶體的機器，如果不使用MyISAM表，推薦值是16M（8-64M）。

 

暫存資料表空間大小：order by和group by時把資料放到暫存資料表裡。

tmp_table_size   占的是記憶體的大小，如果太小在排序時會出錯

created_tmp_tables  建立暫存資料表的數量

max_tmp_tables=32

tmpdir=/tmp  硬碟上暫存資料表所在的位置

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

innodb表：

建立資料表空間檔案

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend

這個設定配置一個可擴充大小的尺寸為10MB的單獨檔案，名為ibdata1。沒有給出檔案的位置，所以預設的是在MySQL的資料目錄內。

如果你對最後的資料檔案指定autoextend選項。如果資料檔案耗盡了資料表空間中的自由空間，InnoDB就擴充資料檔案。擴充的幅度是每次8MB。

要為一個自動擴充資料檔案指定最大尺寸，請使用max屬性。下列配置允許ibdata1漲到極限的500MB：

[mysqld]

innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB預設地在MySQL資料目錄建立資料表空間檔案。要明確指定一個位置，請使用innodb_data_home_dir選項。比如，要使用兩個名為ibdata1和ibdata2的檔案，但是要把他們建立到/ibdata，像如下一樣配置InnoDB：

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

 

mysql> show variables like ‘innodb_buffer_pool_size‘;

innodb_buffer_pool_size

對於InnoDB表來說，innodb_buffer_pool_size的作用就相當於key_buffer_size對於MyISAM表的作用一樣。InnoDB使用該參數指定大小的記憶體來緩衝資料和索引。對於單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%。

根據MySQL手冊，對於2G記憶體的機器，推薦值是1G（50%）。

 

mysql> show variables like ‘innodb_%per%‘;［建議開啟］

innodb_file_per_table =1 為每一個表單獨建立一個資料表空間檔案。

其他參數

skip-locking

取消檔案系統的外部鎖，減少出錯幾率增強穩定性

skip-name-resolve

關閉mysql的dns反查功能。這樣速度就快了!

選項就能禁用DNS解析，連線速度會快很多。不過，這樣的話就不能在MySQL的授權表中使用主機名稱了而只能用ip格式。

wait_timeout=10 終止空閑時間超過10秒的連結，避免長串連［預設8個小時］

max_connect_errors=10 //10次串連失敗就鎖定，使用flush hosts 解鎖，

或mysqladmin flush-hosts解鎖

 

mysql學習(2)-MySQL伺服器最佳化