search

huhongbo / devops

6 stars 0 forks source link

TOKUDB和mysql比对测试 #24

Open huhongbo opened 9 years ago

aricy commented 9 years ago

以下结果为tpcc-mysql工具得出 Onesql测试结果： 1、LOAD ---tpcc load begin ---2015/01/30 12:44:24

* ###easy### TPC-C Data Loader *

[server]: localhost [port]: 3306 [DBname]: tpcc1000 [user]: root [pass]: root TPCC Data Load Started... Loading Item .................................................. 5000 .................................................. 10000 .................................................. 15000 .................................................. 20000 .................................................. 25000 .................................................. 30000 .................................................. 35000 .................................................. 40000 .................................................. 45000 .................................................. 50000 .................................................. 55000 .................................................. 60000 .................................................. 65000 .................................................. 70000 .................................................. 75000 .................................................. 80000 .................................................. 85000 .................................................. 90000 .................................................. 95000 .................................................. 100000 Item Done. Loading Warehouse Loading Stock Wid=1 ............................................... ............................................... Loading Orders for D=6, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. Loading Orders for D=7, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. Loading Orders for D=8, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. Loading Orders for D=9, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. Loading Orders for D=10, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. ...DATA LOADING COMPLETED SUCCESSFULLY. ---tpcc load end ---2015/01/30 14:28:13 LOAD开始时间： 2015/01/30 12:44:24 LOAD结束时间： 2015/01/30 14:28:13 总计时长：1H 43Min 49S
aricy commented 9 years ago

2、执行时间 ---tpcc start begin ---2015/01/30 14:34:23

* ###easy### TPC-C Load Generator *

option h with value 'localhost' option P with value '3306' option d with value 'tpcc1000' option u with value 'root' option p with value 'root' option w with value '100' option c with value '32' option r with value '60' option l with value '3600' option f with value 'onesql_tpcc_load_100warehouse.txt'

[server]: localhost [port]: 3306 [DBname]: tpcc1000 [user]: root [pass]: root ``` [rampup]: 60 (sec.) [measure]: 3600 (sec.) ``` RAMP-UP TIME.(60 sec.) MEASURING START. 10, 538(1):4.451|5.219, 545(0):0.967|1.594, 54(0):0.613|0.773, 54(0):5.512|6.408, 58(0):14.153|14.971 20, 673(1):4.571|5.035, 670(0):1.013|1.210, 67(0):0.691|0.881, 65(0):5.282|5.286, 67(0):14.406|14.518 30, 973(0):4.436|4.901, 971(0):0.965|1.295, 97(0):0.571|0.586, 98(0):5.229|5.332, 95(0):13.502|13.791 40, 673(1):4.714|5.001, 671(0):1.105|1.255, 66(0):0.631|0.637, 67(0):5.650|5.714, 67(0):13.996|14.416 50, 528(1):4.682|5.023, 542(0):1.015|1.080, 55(0):0.707|0.779, 52(0):5.543|5.570, 61(0):14.649|14.659 60, 947(0):4.464|4.736, 939(0):0.983|1.410, 95(0):0.665|1.304, 97(0):5.471|5.724, 87(0):14.358|14.503 70, 947(2):4.498|5.591, 944(0):0.986|1.100, 95(0):0.562|0.578, 92(0):5.285|5.359, 96(0):14.397|15.180 80, 579(3):4.708|5.347, 577(0):1.021|1.098, 57(0):0.551|0.554, 58(0):5.559|5.637, 58(0):14.318|15.289 90, 773(2):4.680|5.772, 774(0):0.985|1.055, 78(0):0.542|0.580, 77(0):5.538|5.692, 80(0):13.824|16.628 100, 871(2):4.806|5.063, 876(0):0.980|1.160, 87(0):0.659|0.670, 85(0):5.661|5.909, 89(0):14.223|14.267 ....................................................... ....................................................... 3540, 599(13):5.127|5.588, 607(0):1.113|1.251, 60(0):0.627|0.735, 58(0):6.237|6.304, 60(0):15.370|15.505 3550, 862(25):5.125|5.466, 857(0):1.071|1.133, 85(0):0.592|0.607, 88(0):6.021|6.025, 86(0):14.953|15.146 3560, 483(8):5.051|5.921, 488(0):1.035|1.136, 50(0):0.595|0.599, 50(0):5.983|6.552, 46(0):13.828|13.877 3570, 738(3):4.734|5.067, 736(0):1.013|1.145, 72(0):0.595|0.788, 72(0):5.761|6.285, 76(0):14.030|14.281 3580, 628(0):4.735|4.871, 626(0):1.008|1.535, 64(0):0.584|0.589, 64(0):5.525|6.065, 60(0):13.665|14.763 3590, 1151(0):4.451|4.826, 1154(0):0.918|1.095, 115(0):0.557|0.646, 115(0):5.133|5.365, 118(0):13.548|14.440 3600, 798(1):4.766|5.005, 799(0):0.978|1.304, 80(0):0.575|0.600, 81(0):5.804|5.931, 78(0):14.376|15.029 STOPPING THREADS................................ [0] sc:273691 lt:2441 rt:0 fl:0 [1] sc:276140 lt:0 rt:0 fl:0 [2] sc:27614 lt:0 rt:0 fl:0 [3] sc:27610 lt:0 rt:0 fl:0 [4] sc:27619 lt:0 rt:0 fl:0 in 3600 sec. [0] sc:273691 lt:2441 rt:0 fl:0 [1] sc:276140 lt:0 rt:0 fl:0 [2] sc:27614 lt:0 rt:0 fl:0 [3] sc:27610 lt:0 rt:0 fl:0 [4] sc:27619 lt:0 rt:0 fl:0 (all must be [OK]) [transaction percentage] Payment: 43.48% (>=43.0%) [OK] Order-Status: 4.35% (>= 4.0%) [OK] Delivery: 4.35% (>= 4.0%) [OK] Stock-Level: 4.35% (>= 4.0%) [OK] [response time (at least 90% passed)] New-Order: 99.12% [OK] Payment: 100.00% [OK] Order-Status: 100.00% [OK] Delivery: 100.00% [OK] Stock-Level: 100.00% [OK] 4602.200 TpmC ---tpcc start end ---2015/01/30 15:35:45 START开始时间： 2015/01/30 14:34:23 START结束时间： 2015/01/30 15:35:45 TPmC： 4602.200
aricy commented 9 years ago

下面为使用gnuplot画图： onesql

huhongbo commented 9 years ago

TOKUDB的优势在于压缩率和插入索引速度

aricy commented 9 years ago

好的，两者的TPCC测试完成后我再测试下压缩率和索引后的插入速度。

aricy commented 9 years ago

Onesql测试结果(TPCC)： ---tpcc load begin ---2015/01/30 16:28:36

* ###easy### TPC-C Data Loader *

[server]: localhost [port]: 3306 [DBname]: tpcc1000 [user]: root [pass]: root TPCC Data Load Started... Loading Item .................................................. 5000 .................................................. 10000 .................................................. 15000 .................................................. 20000 .................................................. 25000 .................................................. 30000 .................................................. 35000 .................................................. 40000 .................................................. 45000 .................................................. 50000 .................................................. 55000 .................................................. 60000 .................................................. 65000 .................................................. 70000 .................................................. 75000 .................................................. 80000 .................................................. 85000 .................................................. 90000 .................................................. 95000 .................................................. 100000 Item Done. Loading Warehouse ........................................... ........................................... Orders Done. Loading Orders for D=9, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. Loading Orders for D=10, W= 100 .......... 1000 .......... 2000 .......... 3000 Orders Done. ...DATA LOADING COMPLETED SUCCESSFULLY. ---tpcc load end ---2015/01/30 18:14:13 LOAD开始时间： 2015/01/30 16:28:36 LOAD结束时间： 2015/01/30 18:14:13 总计时长：1H 45Min 37S
aricy commented 9 years ago

两种引擎的对比： mysql> show table status \G;
*** 1. row *** Name: onesql_size Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000433 Avg_row_length: 54 Data_length: 546308096 Max_data_length: 0 Index_length: 0 Data_free: 555745280 Auto_increment: NULL Create_time: 2015-02-02 09:47:04 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *** 2. row *** Name: tokudb_size Engine: TokuDB Version: 10 Row_format: tokudb_zlib Rows: 10000000 Avg_row_length: 26 Data_length: 260000000 Max_data_length: 9223372036854775807 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-02-02 09:39:45 Update_time: 2015-02-02 09:39:45 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.00 sec)

ERROR: No query specified

mysql> select count(1) from tokudb_size; +----------+ | count(1) | +----------+ | 10000000 | +----------+ 1 row in set (6.59 sec)

mysql> select count(1) from onesql_size; +----------+ | count(1) | +----------+ | 10000000 | +----------+ 1 row in set (7.44 sec)

aricy commented 9 years ago

两张同样表的不同引擎，tokudb占用空间为innodb的1/2 ?? （未开启inndb_file_per_table） 事实上打开innodb_file_per_table，观察空间占用情况为： mysql> show table status \G; *** 1. row *** Name: innodb_size_default Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000433 Avg_row_length: 54 Data_length: 546308096 Max_data_length: 0 Index_length: 0 Data_free: 5242880 Auto_increment: NULL Create_time: 2015-02-02 12:35:09 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *** 2. row *** Name: split_insert Engine: InnoDB Version: 10 Row_format: Compact Rows: 10000433 Avg_row_length: 54 Data_length: 546308096 Max_data_length: 0 Index_length: 0 Data_free: 5242880 Auto_increment: NULL Create_time: 2015-02-02 11:13:50 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: *** 3. row *** Name: tokudb_size_default Engine: TokuDB Version: 10 Row_format: tokudb_zlib Rows: 10000000 Avg_row_length: 26 Data_length: 260000000 Max_data_length: 9223372036854775807 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-02-02 12:37:50 Update_time: 2015-02-02 12:37:50 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 3 rows in set (0.01 sec)

--默认压缩方式： tokudb :tokudbzlib innodb:Compact root@pc-mon03:/usr/local/tokudb/data# ls -l .tokudb test/innodb_sizedefault. -rw-rw---- 1 mysql mysql 8646 Feb 2 12:35 test/innodb_size_default.frm -rw-rw---- 1 mysql mysql 562036736 Feb 2 12:36 test/innodb_size_default.ibd -rw-rw---- 1 mysql mysql 64367616 Feb 2 12:38 _test_tokudb_size_default_main_2a_1_1b_B_0.tokudb -rw-rw---- 1 mysql mysql 16384 Feb 2 12:37 _test_tokudb_size_default_status_28_1_1b.tokudb tokudb引擎约为innodb引擎的1/10，空间使用大大减少 而使用了tokudb_lzma格式后，为之前tokudbzlib格式的1/3 root@pc-mon03:/usr/local/tokudb/data# ls -l .tokudb test/innodb_sizedefault. -rw-rw---- 1 mysql mysql 8646 Feb 2 12:35 test/innodb_size_default.frm -rw-rw---- 1 mysql mysql 562036736 Feb 2 12:36 test/innodb_size_default.ibd -rw-rw---- 1 mysql mysql 26188800 Feb 2 12:46 _test_innodb_size_lzma_main_30_1_1b_B_0.tokudb -rw-rw---- 1 mysql mysql 16384 Feb 2 12:45 _test_innodb_size_lzma_status_2e_1_1b.tokudb -rw-rw---- 1 mysql mysql 64367616 Feb 2 12:38 _test_tokudb_size_default_main_2a_1_1b_B_0.tokudb -rw-rw---- 1 mysql mysql 16384 Feb 2 12:37 _test_tokudb_size_default_status_28_1_1b.tokudb 下面采用innodb-plugin的Innodb_file_format = barracuda ;使用compressed压缩方式 root@pc-mon03:/usr/local/tokudb/data# ls -l .tokudb test/innodbsize.ibd
-rw-rw---- 1 mysql mysql 285212672 Feb 2 12:59 test/innodb_size_compressed.ibd -rw-rw---- 1 mysql mysql 562036736 Feb 2 12:36 test/innodb_size_default.ibd -rw-rw---- 1 mysql mysql 26188800 Feb 2 12:46 _test_innodb_size_lzma_main_30_1_1b_B_0.tokudb -rw-rw---- 1 mysql mysql 16384 Feb 2 12:45 _test_innodb_size_lzma_status_2e_1_1b.tokudb -rw-rw---- 1 mysql mysql 64367616 Feb 2 12:38 _test_tokudb_size_default_main_2a_1_1b_B_0.tokudb -rw-rw---- 1 mysql mysql 16384 Feb 2 12:37 _test_tokudb_size_default_status_28_1_1b.tokudb 总结： tokudb的lzma格式（空间为26188800）为innodb的compressed格式(285212672)的近1/10。压缩比非常明显。

aricy commented 9 years ago

mysql> alter table onesql_size add index(rowid); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table tokudb_size add index(rowid);
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into onesql_size select * from base_table; Query OK, 10000000 rows affected (1 min 29.01 sec) Records: 10000000 Duplicates: 0 Warnings: 0

mysql> commit; Query OK, 0 rows affected (0.00 sec)

mysql> insert into tokudb_size select * from base_table;
Query OK, 10000000 rows affected (1 min 35.09 sec) Records: 10000000 Duplicates: 0 Warnings: 0

aricy commented 9 years ago

两张表创建相同索引后，插入效率相差不大。