15、單機運行環境搭建之 －－Centos6.4下對mysql進行壓力測試

 ===================================================================================================

方案一：Mysql內建了壓力測試工具mysqlslap，所以我們可以不用自己編寫程式來測試Mysql讀取的壓力。壓力測試shell指令碼如下：

 建立表的指令碼：

CREATE TABLE `test` (  `c1` int(11) DEFAULT NULL,  `c2` int(11) DEFAULT NULL,  `c3` int(11) DEFAULT NULL,  `c4` varchar(256) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 vi /usr/local/test_insert.sh

#!/bin/sh  while true  do      mysqlslap --concurrency=100 --iterations=10 --create-schema='test' --query="insert into test(c1,c2,c3,c4) values(1,1,1,'a')" --number-of-queries=200 --debug-info -uroot -pdsideal    usleep 100  done  

運行：

chmod +x /usr/local/test_insert.sh /usr/local/test_insert.sh 

 上面指令碼的意思是每隔100ms迴圈做這樣的事：類比100個mysql用戶端，對資料庫test的表test執行200次插入（number-of-queries = concurrency * 每個mysql用戶端的查詢次數，所以這裡的每個mysql用戶端的查詢次數是2次），迭代10次。--debug-info是列印記憶體和CPU的相關資訊。

接著我們可以編寫shell指令碼來輸出指定時間間隔（比如1秒）內的mysql操作次數，shell指令碼如下：

vi /usr/local/view_test.sh

#!/bin/sh    lastTimes="0"    while true  do      currentTimes=$(mysql -uroot -p'dsideal' -e "show global status like 'Com_insert'" | sed '1d' | awk '{print $2}')      times=$(expr ${currentTimes} - ${lastTimes})      lastTimes="${currentTimes}"      echo "${times}"      sleep 1  done  

運行：

chmod +x /usr/local/view_test.sh

/usr/local/view_test.sh

 

查看mysql各種操作的次數，可以通過查看global status裡的'Com_'開頭的變數，它們就是mysql的操作命令，比如Com_insert就是插入命令、Com_update就是更新命令，等等，具體可以查看文檔說明。將相鄰兩次的次數相減，就得到這個時間間隔內執行的次數。

 

PS：除了iostat等命令外，也可以通過top命令來查看io的負載（看wait的百分比，如果大於等於 1 / cpu核心數，則說明硬碟IO有問題）。請參考：Understanding Disk I/O - when should you be worried?

#########################################################################################################

方案二：使用sysbench

 

sysbench是一個模組化的、跨平台、多線程基準測試載入器，主要用於評估測試各種不同系統參數下的資料庫負載情況。關於這個項目的詳細介紹請看：http://sysbench.sourceforge.net。

它主要包括以下幾種方式的測試：

1、cpu效能
2、磁碟io效能
3、發送器效能
4、記憶體配置及傳輸速度
5、POSIX線程效能
6、資料庫效能(OLTP基準測試)
目前sysbench主要支援 MySQL,pgsql,oracle 這3種資料庫。

一、安裝

首先，在 http://sourceforge.net/projects/sysbench 下載源碼包。接下來，按照以下步驟安裝：

yum -y install libtool

cd /usr/local

wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz

tar zxf sysbench-0.4.12.tar.gz
cd sysbench-0.4.12
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib 

cp /usr/bin/libtool libtool

make 

make install

 二、開始測試

編譯成功之後，就要開始測試各種效能了，測試的方法官網網站上也提到一些，但涉及到 OLTP 測試的部分卻不夠準確。在這裡我大致提一下：
1、cpu效能測試

export LD_LIBRARY_PATH=/usr/local/mysql/libsysbench --test=cpu --cpu-max-prime=20000 run

cpu測試主要是進行素數的加法運算，在上面的例子中，指定了最大的素數為 20000，自己可以根據機器cpu的效能來適當調整數值。

2、線程測試

sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run

3、磁碟IO效能測試

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw preparesysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw runsysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

上述參數指定了最大建立16個線程，建立的檔案總大小為3G，檔案讀寫入模式為隨機讀。

4、記憶體測試

sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run

上述參數指定了本次測試整個過程是在記憶體中傳輸 4G 的資料量，每個 block 大小為 8K。

5、OLTP測試

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-host=10.10.3.154 \--mysql-password=dsideal --mysql-db=test prepare 

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-host=10.10.3.154 \--mysql-password=dsideal --mysql-db=test run 

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-host=10.10.3.154 --mysql-password=dsideal --mysql-db=test cleanup

上述參數指定了本次測試的表格儲存體引擎類型為 myisam，這裡需要注意的是，官方網站上的參數有一處有誤，即 --mysql-table-engine，官方網站上寫的是 --mysql-table-type，這個應該是沒有及時更新導致的。另外，指定了表最大記錄數為 1000000，其他參數就很好理解了，主要是指定登入方式。測試 OLTP 時，可以自己先建立資料庫 sbtest，或者自己用參數 --mysql-db 來指定其他資料庫。--mysql-table-engine 還可以指定為 innodb 等 MySQL 支援的表格儲存體引擎類型。

好了，主要的就是這些了，想要瞭解更多資訊就訪問 sysbench 項目的首頁吧。

sync_binlog=1


sync_binlog：這個參數是對於MySQL系統來說是至關重要的，他不僅影響到Binlog對MySQL所帶來的效能損耗，而且還影響到MySQL中資料的完整性。對於“sync_binlog”參數的各種設定的說明如下：

sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。
sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。
在MySQL中系統預設的設定是sync_binlog=0，也就是不做任何強制性的磁碟排清指令，這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失。而當設定為“1”的時候，是最安全但是效能損耗最大的設定。因為當設定為1的時候，即使系統Crash，也最多丟失binlog_cache中未完成的一個事務，對實際資料沒有任何實質性影響。從以往經驗和相關測試來看，對於高並發事務的系統來說，“sync_binlog”設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。

transaction-isolation=READ-COMMITTEDinnodb_buffer_pool_size = 2500M

innodb_buffer_pool_size 定義了 InnoDB 儲存引擎的表資料和索引資料的最大記憶體緩衝區大小。和 MyISAM 儲存引擎不同， MyISAM 的 key_buffer_size 只能緩衝索引鍵，而 innodb_buffer_pool_size 卻可以快取資料塊和索引鍵。適當的增加這個參數的大小，可以有效減少 InnoDB 類型的表的磁碟 I/O 。在一個以 InnoDB 為主的專用資料庫伺服器上，可以考慮把該參數設定為實體記憶體大小的 60%-80% 。
innodb_thread_concurrency = 9innodb_flush_log_at_trx_commit = 1innodb_io_capacity = 400

 

假如你有一台伺服器,專用於典型的Web項目，需要存放海量的資料，並使用MySQL InnoDB引擎記憶體:16G問題來了:你會怎樣配置這個伺服器？以下的InnoDB 效能最佳化概要普遍的適用於大多數的項目硬體記憶體  對於使用InnoDB引擎儲存海量資料，記憶體是最重要的。16G－32G 是目前較為低成本高效率的。CPU 在CPU方面2個雙核的CPU也可以勝任，但如果有4核的CPU當然可以擔任更大的工作量IO系統  帶有後備電池的RAID是一個很好的選擇硬碟    6到8個硬碟會比較符合標準要求，而2.5英寸 SAS硬碟，速度會更快。RAID10 對於資料存放區和大多數為讀操作的應用的支援很好，如果你喜歡冗餘磁碟陣列RAID5，要當心它的隨機寫作業系統使用64位的作業系統。在Database Backup方面，在大多數情況下，EXT3檔案系統會比LVM更高效。MySQL InnoDB 設定innodb_buffer_pool_size 記憶體的70-80% 是一個安全的選擇. 如果有16G記憶體，可以設定為12Ginnodb_log_file_size 取決於你的還原速度需要，而對於合理的還原時間和高效的效能，可以設定為256Minnodb_log_buffer_size=4M 對於大多數情況，4M比較合理，除非你傳送一些很大的位元據塊到InnoDB,可以稍微調大點innodb_flush_log_at_trx_commit=2  如果不太在意ACID屬性和容許丟失最後時刻的少量事務innodb_thread_concurrency=8  這個數字要根據實際的情況來設定，但對於大多數的情況，是一個比較合適的設定innodb_flush_method=O_DIRECT  避免重複緩衝和減少linux交換分區的壓力，在大多數情況下這個設定可以改善效能，但如果你沒有後備電源備份RAID緩衝，某些緩衝會被洗掉innodb_file_per_table – 如果你沒有大量的表，可以使用這個選項，但使用了這個選項後，你將不能控制innodb的主資料表空間，自MySQL 4.1以後加入並可以穩定使用如果項目可以運行 READ-COMMITED 唯一性模式，可以設定 transaction-isolation=READ-COMMITTED，在MySQL 5.0 這個選項有利於行級鎖的效能提高，5.1以後，也有利於行級複製使用InnoDB的應用程式調優對於習慣使用MyISAM的開發人員，在InnoDB需要瞭解部分改變。當你進行update操作的時候，要確保使用事務， 既為一致起見，也為獲得更好的效能假如你的項目有任何的寫操作，就要準備好可能發生的死結。檢查表結構並且思考如何利用InnoDB的特性，例如主鍵叢集，在索引裡麵包含主鍵(但要保證主鍵的長度)，通過主鍵快速尋找，打包大的索引。

 

修改最大串連數

  vi /etc/my.cnf

max_connections=1000

 mysql -uroot -p

 show variables like '%connection%';