接下来,这里测试下同样的环境InnoDB和TokuDB的性能差异。当然,我没有做压力测试,只是简单的手动执行了几次SQL而已。
(5.6.10-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial))
用来导入的文件大概为35M。
1. INNODB.
对应的参数:
innodb_buffer_pool_size=32M
bulk_insert_buffer_size=20M
query_cache_size = 0
导入性能:(InnoDB在这里慢在CPU一直忙于IO置换。)
mysql> load data infile '/tmp/t3_push.csv' into table t3_push;
Query OK, 955527 rows affected (30 min 44.03 sec)
Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0
读性能:(读的性能还是很好的,这里用到5.6的ICP以及MRR特性。)
mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30;
+----------+
| count(*) |
+----------+
| 49 |
+----------+
1 row in set (0.06 sec)
调大
innodb_buffer_pool=128M
mysql> load data infile '/tmp/t3_push.csv' into table t3_push;
Query OK, 955527 rows affected (38.72 sec)
Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0
调大后,其实导入性能还是不错的。
2. TokuDB.
(5.5.30-tokudb-7.1.0-e-log TokuDB Enterprise Server (GPL) )
对应的参数:
tokudb_cache_size=32M
tokudb_loader_memory_size=20M
query_cache_size = 0
写性能:(这里IO次数很少,所以导入速度很快。)
mysql> load data infile '/tmp/t3_push.csv' into table t3_push;
Query OK, 955527 rows affected (19.73 sec)
Records: 955527 Deleted: 0 Skipped: 0 Warnings: 0
读性能:(读的速度比INNODB稍微慢了些。)
mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30;
+----------+
| count(*) |
+----------+
| 49 |
+----------+
1 row in set (0.54 sec)
mysql> select count(*) from t3_push where rank1 < 200 and rank2 < 300;
+----------+
| count(*) |
+----------+
| 5759 |
+----------+
1 row in set (4.13 sec)
但是TokuDB可以给二级索引变聚簇,所以这点上如果只读的话,还是会比InnoDB快。
给列rank2 加聚簇索引,
mysql> alter table t3_push add clustering index idx_rank2(rank2);
Query OK, 0 rows affected (6.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
现在所有的基于索引idx_rank2 的查询都是瞬间的。
mysql> select count(*) from t3_push where rank1 < 20 and rank2 < 30;
+----------+
| count(*) |
+----------+
| 49 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from t3_push where rank1 < 200 and rank2 < 300;
+----------+
| count(*) |
+----------+
| 5759 |
+----------+
1 row in set (0.01 sec)