2014年5月3日星期六

MySQL基準與mysqlslap

Original post: http://anothermysqldba.blogspot.com/2014/05/mysql-benchmark-with-mysqlslap.html

因此,基準不同的MySQL查詢在你的數據庫是一個明智的做法。 這應該不用說。 雖然我們優化查詢,我們可以利用解釋(和擴展)的最佳服用時間為基準他們應該證明是有益的。 

這是執行mysqlslap語句的一個簡單的例子。 

對於這個例子,我從MySQL載入世界資料庫。 ( http://dev.mysql.com/doc/index-other.html 

我創建了一個連接的所有三個表,並把它放到/ tmp目錄/ tests.sql查詢。 解釋計劃如下。 

root@localhost [world]> EXPLAIN EXTENDED SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: C
type: range
possible_keys: CountryCode,name_key
key: name_key
key_len: 5
ref: NULL
rows: 127
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Y
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: world.C.CountryCode
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: L
type: ref
possible_keys: PRIMARY,CountryCode
key: CountryCode
key_len: 3
ref: world.C.CountryCode
rows: 2
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `world`.`C`.`Name` AS `City`,`world`.`Y`.`Name` AS `Country`,`world`.`L`.`Language` AS `Language`,`world`.`Y`.`Population` AS `Population` from `world`.`City` `C` join `world`.`Country` `Y` join `world`.`CountryLanguage` `L` where ((`world`.`Y`.`Code` = `world`.`C`.`CountryCode`) and (`world`.`L`.`CountryCode` = `world`.`C`.`CountryCode`) and (`world`.`Y`.`Continent` = 'Europe') and (`world`.`C`.`Name` like 'D%'))


現在mysqlslap工具,此後一直圍繞的MySQL 5.1.4 
下面是一些其他有用的鏈接。 
現在,我有我的疑問,我可以基準是對數據庫使用以下命令。 

mysqlslap - 並發= 150 - 迭代= 50 - 查詢= / tmp目錄/的test.sql - 創建型模式=世界 

一注: 
查詢具有作為工具方便地做錯誤是非常乾淨的。 
例如下面扔這個錯誤: 

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population
FROM City C
INNER JOIN Country Y ON C.CountryCode = Y.Code
INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode
WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' 

雖然這個查詢工作就好了。 

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' 


該工具將輸出的基準測試結果為你 


Benchmark
Average number of seconds to run all queries: 0.104 seconds
Minimum number of seconds to run all queries: 0.096 seconds
Maximum number of seconds to run all queries: 0.141 seconds
Number of clients running queries: 150
Average number of queries per client: 1 


mysqlslap - 幫助會給你無數的選擇與測試您的查詢。 

您可以自動做的一切 

# mysqlslap --auto-generate-sql
Benchmark
Average number of seconds to run all queries: 0.243 seconds
Minimum number of seconds to run all queries: 0.243 seconds
Maximum number of seconds to run all queries: 0.243 seconds
Number of clients running queries: 1
Average number of queries per client: 0 


你可以測試插件也是如此。 比如我創建這個表: 

CREATE TABLE `foobar_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ; 


所以後來用下面的測試。 

# mysqlslap --concurrency=1150 --iterations=530 --query="use test;insert into foobar_table (id) values (null)" --delimiter=";"
mysqlslap: Could not create thread 


確定錯誤是不是非常有幫助......但希望你注意到這個錯誤。 這是很難有1150個並發交易,如果你只有530次迭代。 


# mysqlslap --concurrency=150 --iterations=30 --query=/tmp/test1.sql --create-schema=test --verbose
Benchmark
Average number of seconds to run all queries: 0.260 seconds
Minimum number of seconds to run all queries: 0.192 seconds
Maximum number of seconds to run all queries: 0.476 seconds
Number of clients running queries: 150
Average number of queries per client: 1


例如下面的工作要好得多。 

# mysqlslap --concurrency=200 --iterations=1000 --query=" insert into foobar_table (id) values (null)" --verbose --create-schema=test
Benchmark
Average number of seconds to run all queries: 0.282 seconds
Minimum number of seconds to run all queries: 0.217 seconds
Maximum number of seconds to run all queries: 0.726 seconds
Number of clients running queries: 200
Average number of queries per client: 1 


只是為了證明我們在做真正的插入.. 

root@localhost [test]> select count(id) from foobar_table;
+-----------+
| count(id) |
+-----------+
| 206091 |
+-----------+
1 row in set (0.13 sec) 

現在,我還應該說,這只是我使用博客文章一個測試數據庫,所以不評價對這些結果的生產數據庫。 

我猜點之後這一切......找到一個麻煩的查詢,優化它最好就可以了,它的基準。 這是更好地了解自己的極限,而不是只是一個猜測。