mysql性能優化（二）

                      mysql性能優化（二）

query cache的限制

所有的子查詢中的外部查詢sql語句，不會被cache（緩存）

在procedure，function以及trigger中的query不被cache

包含其他很多每次執行可能得到不一樣的結果函數query不能被cache

在/etc/my.cnf文件，在[mysqld]下面添加query_cache_size=256M   query_cache_type=1(開啟)  重啟后生效

6.max_connect_error是一個mysql中與安全有關的計數器值，負責阻止過多的嘗試失敗的客戶端，以防止暴力破解。修改配置文件/etc/my.cnf [mysqld]下添加max_connect_errors=次數

重啟服務生效

7.sort_buffer_size每個進行線程分配大小緩沖區，增加這值加速order by或group by操作

Sort_Buffer_Size 是一個connection級參數，在每個connection（session）第一次需要使用這個buffer的時候，一次性分配設置的內存。
Sort_Buffer_Size 并不是越大越好，由于是connection級的參數，過大的設置+高并發可能會耗盡系統內存資源。例如：500個連接將會消耗 500*sort_buffer_size(2M)=1G內存

例如設置sort_buffer_size

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

sort_buffer_size = 2M
重啟MySQL Server進入后，查看設置已經生效。

8.max_allowed_packet = 32M

MySQL根據配置文件會限制Server接受的數據包大小。有時候大的插入和更新會受max_allowed_packet參數限制，導致寫入或者更新失敗。最大值是1GB，必須設置1024的倍數。
9．join_buffer_size = 2M   
用于表間關聯緩存的大小，和sort_buffer_size一樣，該參數對應的分配內存也是每個連接獨享。

10.thread_cache_size = 300   
服務器線程緩存，這個值表示可以重新利用保存在緩存中線程的數量,當斷開連接時,那么客戶端的線程將被放到緩存中以響應下一個客戶而不是銷毀(前提是緩存數未達上限),如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創建,如果有很多新的線程，增加這個值可以改善系統性能.通過比較 Connections 和 Threads_created 狀態的變量，可以看到這個變量的作用。

1GB 內存配置為8，2GB配置為16，3GB配置為32，4GB及以上內存，可配置更大。

Threads_cached :代表當前此時此刻線程緩存中有多少空閑線程。
Threads_connected :代表當前已建立連接的數量，因為一個連接就需要一個線程，所以也可以看成當前被使用的線程數。
Threads_created :代表從最近一次服務啟動，已創建線程的數量，如果發現Threads_created值過大的話，表明MySQL服務器一直在創建線程，這也是比較耗資源，可以適當增加配置文件中thread_cache_size值。
Threads_running :代表當前激活的（非睡眠狀態）線程數。并不是代表正在使用的線程數，有時候連接已建立，但是連接處于sleep狀態。

3）配置InnoDB的幾個變量

11.innodb_buffer_pool_size

對于InnoDB表來說，innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。對于單獨的MySQL服務器，最大可以把該值設置成物理內存的80%。根據MySQL手冊，對于2G內存的機器，推薦值是1G（50%）。 如果你的數據量不大，并且不會暴增，那么無需把 innodb_buffer_pool_size 設置的太大了。

mysql> show variables like 'innodb_buffer_pool_size';

設置innodb_buffer_pool_size

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

innodb_buffer_pool_size = 2048M
重啟MySQL Server進入后，查看設置已經生效。

12.innodb_flush_log_at_trx_commit

主要控制了innodb將log buffer中的數據寫入日志文件并flush磁盤的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日志寫入操作，而是每秒鐘將log buffer中的數據寫入日志文件并flush磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush磁盤的操作，確保了事務的ACID；設置為2，每次事務提交引起寫入日志文件的動作，但每秒鐘完成一次flush磁盤操作。

實際測試發現，該值對插入數據的速度影響非常大，設置為2時插入10000條記錄只需要2秒，設置為0時只需要1秒，而設置為1時則需要229秒。因此，MySQL手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。

根據MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。

13.innodb_thread_concurrency = 0   
此參數用來設置innodb線程的并發數量，默認值為0表示不限制，若要設置則與服務器的CPU核數相同或是cpu的核數的2倍，建議用默認設置，一般為8.

14.innodb_log_buffer_size

此參數確定些日志文件所用的內存大小，以M為單位。緩沖區更大能提高性能，對于較大的事務，可以增大緩存大小。

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M   

此參數確定數據日志文件的大小，以M為單位，更大的設置可以提高性能.

16. innodb_log_files_in_group = 3   

為提高性能，MySQL可以以循環方式將日志文件寫到多個文件。推薦設置為3

17.read_buffer_size = 1M  
MySql讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區，MySql會為它分配一段內存緩沖區。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內存緩沖區大小提高其性能。和sort_buffer_size一樣，該參數對應的分配內存也是每個連接獨享。
18.read_rnd_buffer_size = 16M   
MySql的隨機讀（查詢操作）緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區。進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但MySql會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。
注：順序讀是指根據索引的葉節點數據就能順序地讀取所需要的行數據。隨機讀是指一般需要根據輔助索引葉節點中的主鍵尋找實際行數據，而輔助索引和主鍵所在的數據段不同，因此訪問方式是隨機的。
19.bulk_insert_buffer_size = 64M   
批量插入數據緩存大小，可以有效提高插入效率，默認為8M

20.binary log

log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M  //為每個session 分配的內存，在事務過程中用來存儲二進制日志的緩存,提高記錄bin-log的效率。沒有什么大事務，dml也不是很頻繁的情況下可以設置小一點，如果事務大而且多，dml操作也頻繁，則可以適當的調大一點。前者建議是--1M，后者建議是：即 2--4M

max_binlog_cache_size = 8M //表示的是binlog能夠使用的最大cache 內存大小

max_binlog_size = 512M  //指定binlog日志文件的大小，如果當前的日志大小達到max_binlog_size，還會自動創建新的二進制日志。你不能將該變量設置為大于1GB或小于4096字節。默認值是1GB。在導入大容量的sql文件時，建議關閉sql_log_bin，否則硬盤扛不住，而且建議定期做刪除。

expire_logs_days = 7  //定義了mysql清除過期日志的時間。
二進制日志自動刪除的天數。默認值為0,表示“沒有自動刪除”。

mysqladmin flush-logs 也可以重新開始新的binary log

在優化之前執行mysqlslap工具進行測試

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' --engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose

顯示結果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.837 seconds

Minimum number of seconds to run all queries: 13.837 seconds

Maximum number of seconds to run all queries: 13.837 seconds

Number of clients running queries: 10

Average number of queries per client: 200