在oltp測試中,它基本上分為三個階段:
一是prepare階段,在此階段,sysbench會在被測試資料庫中建立一張表,名為sbtest。這張表預設是在sbtest庫中。你可以随意指定其它自己手動建立的庫,即使你指定為sbtest庫,庫仍然需要你手動建立, sysbench并不會為你建立庫。
sbtest表的結構如下:
CREATE TABLE `sbtest` (
`id` int ( 10 ) unsigned NOT NULL AUTO_INCREMENT,
`k` int ( 10 ) unsigned NOT NULL DEFAULT ' 0 ' ,
`c` char ( 120 ) NOT NULL DEFAULT '' ,
`pad` char ( 60 ) NOT NULL DEFAULT '' ,
PRIMARY KEY (`id`),
KEY `k` (`k`));
在表結建構立完成後,它會被填充入你指定的行數(--oltp-table-size)
二是run階段,這個階段中有三個mode可以使用:
1.simple mode,如果指定為此mode,那麼在運作過程中, sysbench的每個thread将隻會運作如下的語句:
SELECT c FROM sbtest WHERE id = N
N值是個随機值,它的範圍将會從1...<table size>
2.advanced transactional mode
如果啟用此模式,那麼sysbench的每個thread将會在測試表上(sbtest)上執行事務。不過要注意的是,對于表(sbtest使用的引擎)是否支援事務,其執行的過程是不一樣的。如果是innodb engine,那麼它将會使用BEGIN/COMMIT 語句來start/stop一個事務;如果是MyISAM engine,sysbench将會使用LOCK TABLES/UNLOCK TABLES語句。在一個事務中若是有一些記錄被删除了,不用擔心,在同一個事務中,會有相同的記錄被補回。是以同一張sbtest測試表可以多次用來進行測試。
這個mode中包含執行的語句如下:
Point queries:
SELECT c FROM sbtest WHERE id = N
Range queries:
SELECT c FROM sbtest WHERE id BETWEEN N AND M
Range SUM () queries:
SELECT SUM (K) FROM sbtest WHERE id BETWEEN N and M
Range ORDER BY queries:
SELECT c FROM sbtest WHERE id between N and M ORDER BY c
Range DISTINCT queries:
SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
UPDATEs on index column :
UPDATE sbtest SET k = k + 1 WHERE id = N
UPDATEs on non - index column :
UPDATE sbtest SET c = N WHERE id = M
DELETE queries:
DELETE FROM sbtest WHERE id = N
INSERT queries:
INSERT INTO sbtest VALUES (...)
3.Non-transcational mode
這個mode與simple mode相似,不同的地方是可以自主選擇想要運作的query,這裡要注意的是,它與Advanced transcational mode的差別,它在查詢請求間不會保護測試表中的資料,是以如果有多次測試的話,需要進行cleanup/prepare操作。
它包含執行的語句如下:
Point queries:
SELECT pad FROM sbtest WHERE id = N
UPDATEs on index column :
UPDATE sbtest SET k = k + 1 WHERE id = N
UPDATEs on non - index column :
UPDATE sbtest SET c = N WHERE id = M
DELETE queries:
DELETE FROM sbtest WHERE id = N
The generated row IDs are unique over each test run, so no row is deleted twice.
INSERT queries:
INSERT INTO sbtest (k, c, pad) VALUES (N, M, S)
最後一個階段就是cleanup,這個沒什麼好講的。就是清除測試資料。
上面的内容來自手冊,但似乎手冊的内容已經很長時間不更新了,是以在實際應用中應該檢視sysbench的help:
以下是sysbench的oltp相關測試選項:
--oltp-test-mode = STRING test type to use {simple , complex , nontrx , sp} [ complex ]
--oltp-reconnect-mode = STRING reconnect mode {session , transaction , query , random} [ session ]
--oltp-sp-name = STRING name of store procedure to call in SP test mode []
--oltp-read-only = [ on|off ] generate only 'read' queries (do not modify database) [ off ]
--oltp-skip-trx = [ on|off ] skip BEGIN/COMMIT statements [ off ]
--oltp-range-size = N range size for range queries [ 100 ]
--oltp-point-selects = N number of point selects [ 10 ]
--oltp-simple-ranges = N number of simple ranges [ 1 ]
--oltp-sum-ranges = N number of sum ranges [ 1 ]
--oltp-order-ranges = N number of ordered ranges [ 1 ]
--oltp-distinct-ranges = N number of distinct ranges [ 1 ]
--oltp-index-updates = N number of index update [ 1 ]
--oltp-non-index-updates = N number of non-index updates [ 1 ]
--oltp-nontrx-mode = STRING mode for non-transactional test {select , update_key , update_nokey , insert , delete} [ select ]
--oltp-auto-inc = [ on|off ] whether AUTO_INCREMENT (or equivalent) should be used on id column [ on ]
--oltp-connect-delay = N time in microseconds to sleep after connection to database [ 10000 ]
--oltp-user-delay-min = N minimum time in microseconds to sleep after each request [ 0 ]
--oltp-user-delay-max = N maximum time in microseconds to sleep after each request [ 0 ]
--oltp-table-name = STRING name of test table [ sbtest ]
--oltp-table-size = N number of records in test table [ 10000 ]
--oltp-dist-type = STRING random numbers distribution {uniform , gaussian , special} [ special ]
--oltp-dist-iter = N number of iterations used for numbers generation [ 12 ]
--oltp-dist-pct = N percentage of values to be treated as 'special' (for special distribution) [ 1 ]
--oltp-dist-res = N percentage of 'special' values to use (for special distribution) [ 75 ]
安裝sysbench(補充):
sysbench安裝有時會很麻煩,如果用rpm包或在ubuntu上用apt-get線上安裝會容易許多。不過我傾向與使用tar包安裝,特别是使用的資料庫大多不是系統提供的版本時。
1.去官站下載下傳tar包(略)
2.安裝:
# tar -zxvf sysbench- 0.4.12 .tar.gz
# cd sysbench- 0.4.12
# ./autogen.sh
# ./configure --with-mysql-includes = /usr/local/mysql/include --with-mysql-libs = /usr/local/mysql/lib
# make && make install
在安裝過程中可能會遇到三個問題:
一是在編譯時會報libtools錯誤,解決的方法是在configure之前運作autogen.sh腳本;
二是在運作sysbench時報如下錯誤:
sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
遇到此問題需要将libmysqlclient.so.xx檔案所在的目錄append至/etc/ld.so.conf檔案
如果還是不行,可執行export LD_LIBRARY_PATH=/usr/local/mysql/lib
三是報如下錯誤:
# sysbench --test=oltp --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-table-engine=innodb --oltp-table-size=100000000 prepare sysbench 0.4.12: multi-threaded system evaluation benchmark FATAL: unable to connect to MySQL server, aborting... FATAL: error 1049: Unknown database 'sbtest' FATAL: failed to connect to database server!
解決方法是加--mysql-db option,告訴sysbench往哪個資料庫中裝填資料
參考:
http://sysbench.sourceforge.net/docs/
http://www.penglixun.com/tech/system/install_sysbench.html
轉載于:https://www.cnblogs.com/minglog/archive/2011/05/10/2042143.html