mysql性能優(yōu)化（二）

                      mysql性能優(yōu)化（二）

query cache的限制

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

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

包含其他很多每次執(zhí)行可能得到不一樣的結(jié)果函數(shù)query不能被cache

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

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

重啟服務(wù)生效

7.sort_buffer_size每個(gè)進(jìn)行線程分配大小緩沖區(qū)，增加這值加速order by或group by操作

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

例如設(shè)置sort_buffer_size

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

sort_buffer_size = 2M
重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

8.max_allowed_packet = 32M

MySQL根據(jù)配置文件會限制Server接受的數(shù)據(jù)包大小。有時(shí)候大的插入和更新會受max_allowed_packet參數(shù)限制，導(dǎo)致寫入或者更新失敗。最大值是1GB，必須設(shè)置1024的倍數(shù)。
9．join_buffer_size = 2M   
用于表間關(guān)聯(lián)緩存的大小，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。

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

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

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

3）配置InnoDB的幾個(gè)變量

11.innodb_buffer_pool_size

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

mysql> show variables like 'innodb_buffer_pool_size';

設(shè)置innodb_buffer_pool_size

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

innodb_buffer_pool_size = 2048M
重啟MySQL Server進(jìn)入后，查看設(shè)置已經(jīng)生效。

12.innodb_flush_log_at_trx_commit

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

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

根據(jù)MySQL手冊，在允許丟失最近部分事務(wù)的危險(xiǎn)的前提下，可以把該值設(shè)為0或2。

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

14.innodb_log_buffer_size

此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，對于較大的事務(wù)，可以增大緩存大小。

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M   

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

16. innodb_log_files_in_group = 3   

為提高性能，MySQL可以以循環(huán)方式將日志文件寫到多個(gè)文件。推薦設(shè)置為3

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

20.binary log

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

binlog_cache_size = 2M  //為每個(gè)session 分配的內(nèi)存，在事務(wù)過程中用來存儲二進(jìn)制日志的緩存,提高記錄bin-log的效率。沒有什么大事務(wù)，dml也不是很頻繁的情況下可以設(shè)置小一點(diǎn)，如果事務(wù)大而且多，dml操作也頻繁，則可以適當(dāng)?shù)恼{(diào)大一點(diǎn)。前者建議是--1M，后者建議是：即 2--4M

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

max_binlog_size = 512M  //指定binlog日志文件的大小，如果當(dāng)前的日志大小達(dá)到max_binlog_size，還會自動(dòng)創(chuàng)建新的二進(jìn)制日志。你不能將該變量設(shè)置為大于1GB或小于4096字節(jié)。默認(rèn)值是1GB。在導(dǎo)入大容量的sql文件時(shí)，建議關(guān)閉sql_log_bin，否則硬盤扛不住，而且建議定期做刪除。

expire_logs_days = 7  //定義了mysql清除過期日志的時(shí)間。
二進(jìn)制日志自動(dòng)刪除的天數(shù)。默認(rèn)值為0,表示“沒有自動(dòng)刪除”。

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

在優(yōu)化之前執(zhí)行mysqlslap工具進(jìn)行測試

[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

顯示結(jié)果：

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