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

成都創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設、高性價比中寧網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式中寧網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設找我們，業(yè)務覆蓋中寧地區(qū)。費用合理售后完善，10多年實體公司更值得信賴。

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是一個mysql中與安全有關(guān)的計數(shù)器值，負責阻止過多的嘗試失敗的客戶端，以防止暴力破解。修改配置文件/etc/my.cnf [mysqld]下添加max_connect_errors=次數(shù)

重啟服務生效

 

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

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

例如設置sort_buffer_size

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

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

8.max_allowed_packet = 32M

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

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

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

 

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

3）配置InnoDB的幾個變量

11.innodb_buffer_pool_size

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

mysql> show variables like 'innodb_buffer_pool_size';

設置innodb_buffer_pool_size

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

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

12.innodb_flush_log_at_trx_commit

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

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

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

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

14.innodb_log_buffer_size

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

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M   

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

16. innodb_log_files_in_group = 3   

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

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

20.binary log

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

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

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

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

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

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

在優(yōu)化之前執(zhí)行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

顯示結(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

文章標題：mysql性能優(yōu)化（二）
分享網(wǎng)址：http://www.rwnh.cn/article32/jdghsc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、手機網(wǎng)站建設、網(wǎng)站收錄、網(wǎng)站策劃、移動網(wǎng)站建設、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)