sysbench-tpcc再調査
なんとなくこれを見ていたらsysbench-tpccの構造は前々回あたりの認識では間違っているかもしれないので、実際に簡単なベンチマークを取ってみる。
https://www.s-style.co.jp/blog/2018/03/1666/
mysql> create database tpcc;
$./tpcc.lua --mysql-user=tombo --mysql-db=tpcc --mysql-password=passwd --db-driver=mysql --tables=1 --scale=1 prepare
mysql> set global slow_query_log=ON;
mysql> set global long_query_time=0;
$./tpcc.lua --mysql-user=tombo --mysql-db=tpcc --mysql-password=passwd --db-driver=mysql --tables=1 --scale=1 run
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 8019
write: 8226
other: 1248
total: 17493
transactions: 623 (62.26 per sec.)
queries: 17493 (1748.06 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0017s
total number of events: 623
Latency (ms):
min: 0.41
avg: 16.04
max: 62.13
95th percentile: 30.26
sum: 9995.07
Threads fairness:
events (avg/stddev): 623.0000/0.00
execution time (avg/stddev): 9.9951/0.00
pt-query-digestからクエリ見てみる
% pt-query-digest slow.log
# 1.4s user time, 10ms system time, 36.71M rss, 100.88M vsz
# Current date: Wed Jun 27 23:10:37 2018
# Hostname: tombo-ThinkPad-X260
# Files: slow.log
# Overall: 17.50k total, 40 unique, 51.62 QPS, 0.03x concurrency _________
# Time range: 2018-06-27T14:03:15 to 2018-06-27T14:08:54
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 9s 4us 40ms 490us 445us 2ms 194us
# Lock time 1s 0 545us 68us 119us 34us 69us
# Rows sent 8.46k 0 14 0.50 0.99 0.70 0
# Rows examine 29.10k 0 1.02k 1.70 0.99 18.08 0.99
# Query size 2.76M 5 720 165.51 258.32 82.11 174.84
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0x813031B8BBC3B329 4.9692 57.9% 623 0.0080 0.00 COMMIT
# 2 0x08043A0F95830A03 0.5797 6.8% 2821 0.0002 0.00 UPDATE stock?
# 3 0x78147F9532B94609 0.5542 6.5% 2821 0.0002 0.00 INSERT order_line?
# 4 0xF8F75EDE9DD44246 0.5394 6.3% 2821 0.0002 0.00 SELECT stock?
# 5 0xA42FAA7C8575BC56 0.4364 5.1% 2821 0.0002 0.00 SELECT item?
# 6 0x2AFC2598CA878FCE 0.1569 1.8% 276 0.0006 0.01 INSERT new_orders?
# 7 0x9A49E40A9056A7BB 0.1071 1.2% 276 0.0004 0.00 SELECT customer? warehouse?
# 8 0x17154DCE7E0AC776 0.0964 1.1% 269 0.0004 0.00 INSERT history?
# 9 0x816F682E53C0CF03 0.0929 1.1% 30 0.0031 0.00 SELECT order_line? stock?
# 10 0xA630D4C717D9DC96 0.0870 1.0% 170 0.0005 0.00 UPDATE order_line?
# 11 0xD8722F4270561E50 0.0824 1.0% 269 0.0003 0.00 SELECT customer?
# 12 0x88D9C47F6B95AA12 0.0718 0.8% 246 0.0003 0.00 UPDATE customer?
# 13 0x6038CAF4293E6E82 0.0700 0.8% 269 0.0003 0.00 UPDATE district?
# 14 0xC0E4F49350F491A8 0.0681 0.8% 269 0.0003 0.00 UPDATE warehouse?
# 15 0x579C28C03D797339 0.0632 0.7% 174 0.0004 0.00 SELECT customer?
# 16 0x507F33348C82A66C 0.0632 0.7% 276 0.0002 0.00 INSERT orders?
# 17 0xE2A5C4334B7249BF 0.0621 0.7% 276 0.0002 0.00 UPDATE district?
# 18 0x6B61C34631EEC274 0.0542 0.6% 276 0.0002 0.00 SELECT district?
# MISC 0xMISC 0.4252 5.0% 2515 0.0002 0.0 <22 ITEMS>
# Query 1: 62.30 QPS, 0.50x concurrency, ID 0x813031B8BBC3B329 at byte 2936273
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-06-27T14:03:34 to 2018-06-27T14:03:44
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 623
# Exec time 57 5s 7us 22ms 8ms 12ms 4ms 7ms
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 3.65k 6 6 6 6 0 6
# String:
# Databases tpcc
# Hosts localhost
# Users tombo
# Query_time distribution
# 1us #
# 10us #############
# 100us
# 1ms ##############################################################
# 10ms ################################################################
# 100ms
# 1s
# 10s+
COMMIT\G
# Query 2: 282.10 QPS, 0.06x concurrency, ID 0x08043A0F95830A03 at byte 5936900
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-06-27T14:03:34 to 2018-06-27T14:03:44
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 16 2821
# Exec time 6 580ms 39us 897us 205us 301us 78us 214us
# Lock time 14 176ms 11us 146us 62us 98us 23us 66us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 9 2.75k 1 1 1 1 0 1
# Query size 10 305.31k 107 111 110.83 107.34 0.10 107.34
# String:
# Databases tpcc
# Hosts localhost
# Users tombo
# Query_time distribution
# 1us
# 10us ########
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `tpcc` LIKE 'stock1'\G
# SHOW CREATE TABLE `tpcc`.`stock1`\G
UPDATE stock1
SET s_quantity = 44
WHERE s_i_id = 54555
AND s_w_id= 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select s_quantity = 44 from stock1 where s_i_id = 54555
AND s_w_id= 1\G
# Query 3: 282.10 QPS, 0.06x concurrency, ID 0x78147F9532B94609 at byte 82827
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-06-27T14:03:34 to 2018-06-27T14:03:44
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 16 2821
# Exec time 6 554ms 32us 40ms 196us 287us 746us 185us
# Lock time 17 207ms 14us 173us 73us 108us 27us 80us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 23 655.55k 233 241 237.96 234.30 0.21 234.30
# String:
# Databases tpcc
# Hosts localhost
# Users tombo
# Query_time distribution
# 1us
# 10us ###########
# 100us ################################################################
# 1ms
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `tpcc` LIKE 'order_line1'\G
# SHOW CREATE TABLE `tpcc`.`order_line1`\G
INSERT INTO order_line1
(ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info)
VALUES (3001,4,1,2,16590,1,8,405,'qqqqqqqqqqqqqqqqqqqqqqqq')\G
省略。。。
sys.statement_analysisから見てみる。
mysql> update setup_consumers set ENABLED = 'YES' where NAME like '%events_statements_history_long%';
# bench 実行
mysql> SELECT distinct query FROM statement_analysis
-> ;
+-------------------------------------------------------------------+
| query |
+-------------------------------------------------------------------+
| SELECT @@`version_comment` LIMIT ? |
| SELECT SCHEMA ( ) |
| SHOW SCHEMAS |
| SHOW TABLES |
| SELECT * FROM `statement_analysis` LIMIT ? |
| SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` ( |
| SHOW VARIABLES LIKE ? |
| SET SESSION `transaction_isolation` = ? |
| SET `autocommit` = ? |
| BEGIN |
| UPDATE `warehouse1` SET `w_ytd` = `w_ytd` + ? WHERE `w_id` = ? |
| SELECT `w_street_1` , `w_stree ... `warehouse1` WHERE `w_id` = ? |
| UPDATE `district1` SET `d_ytd` ... E `d_w_id` = ? AND `d_id` = ? |
| SELECT `d_street_1` , `d_stree ... E `d_w_id` = ? AND `d_id` = ? |
| SELECT COUNT ( `c_id` ) `namec ... `c_d_id` = ? AND `c_last` = ? |
| SELECT `c_id` FROM `customer1` ... _last` = ? ORDER BY `c_first` |
| SELECT `c_first` , `c_middle` ... = ? AND `c_id` = ? FOR UPDATE |
| UPDATE `customer1` SET `c_bala ... D `c_d_id` = ? AND `c_id` = ? |
| INSERT INTO `history1` ( `h_c_ ... ( ?, ... , NOW ( ) , ?, ... ) |
| COMMIT |
| SELECT `c_balance` , `c_first` ... D `c_d_id` = ? AND `c_id` = ? |
| SELECT `o_id` , `o_carrier_id` ... _id` = ? ORDER BY `o_id` DESC |
| SELECT `ol_i_id` , `ol_supply_ ... l_d_id` = ? AND `ol_o_id` = ? |
| SELECT `c_discount` , `c_last` ... D `c_d_id` = ? AND `c_id` = ? |
| SELECT `d_next_o_id` , `d_tax` ... = ? AND `d_id` = ? FOR UPDATE |
| UPDATE `district1` SET `d_next ... E `d_id` = ? AND `d_w_id` = ? |
| INSERT INTO `orders1` ( `o_id` ... ( ?, ... , NOW ( ) , ?, ... ) |
| INSERT INTO `new_orders1` ( `n ... d` , `no_w_id` ) VALUES (...) |
| SELECT `i_price` , `i_name` , ... FROM `item1` WHERE `i_id` = ? |
| SELECT `s_quantity` , `s_data` ... ? AND `s_w_id` = ? FOR UPDATE |
| UPDATE `stock1` SET `s_quantit ... `s_i_id` = ? AND `s_w_id` = ? |
| INSERT INTO `order_line1` ( `o ... `ol_dist_info` ) VALUES (...) |
| SELECT `no_o_id` FROM `new_ord ... _o_id` ASC LIMIT ? FOR UPDATE |
| DELETE FROM `new_orders1` WHER ... o_d_id` = ? AND `no_w_id` = ? |
| SELECT `o_c_id` FROM `orders1` ... `o_d_id` = ? AND `o_w_id` = ? |
| UPDATE `orders1` SET `o_carrie ... `o_d_id` = ? AND `o_w_id` = ? |
| UPDATE `order_line1` SET `ol_d ... l_d_id` = ? AND `ol_w_id` = ? |
| SELECT SUM ( `ol_amount` ) `sm ... l_d_id` = ? AND `ol_w_id` = ? |
| UPDATE `customer1` SET `c_bala ... `c_d_id` = ? AND `c_w_id` = ? |
| SELECT `d_next_o_id` FROM `dis ... E `d_id` = ? AND `d_w_id` = ? |
| SELECT COUNT ( DISTINCTROW ( ` ... ol_i_id` AND `s_quantity` < ? |
| SELECT `c_data` FROM `customer ... D `c_d_id` = ? AND `c_id` = ? |
| SELECT `i_price` , `i_name` , ... OM `item1` WHERE `i_id` = - ? |
| ROLLBACK |
| SELECT `c_balance` , `c_first` ... _last` = ? ORDER BY `c_first` |
+-------------------------------------------------------------------+
45 rows in set (0.01 sec)
うーん。。。 とり方が良くなくて、実際CRUDがどれくらいの割合で実行されてるかこれではわからない。。。
次回方法を考えて、参考にした生地にあるように↓が埋められるようにする。
| 種別 | 割合 |
|---|---|
| SELECt | |
| UPDATE | |
| INSERT | |
| DELETE |
