TokuDB在MariaDB中的性能

Question

我在TokuDB引擎上做了一些性能测试(我使用了来自戴尔PowerEdge R720上Tokutek网站的MariaDB5.5.30-Tokudb-7.0.4，内存为128 up，TokuDB的默认内存分配为64 up tokudb_cache_size)，并出现了一些意想不到的事情。

在测试场景中，我在一个空表 (33列、1个主auto_increment键、5个索引)上插入了大约9000万行，虽然我注意到与MyISAM引擎相比，速度性能和数据压缩非常显著，即从15K到20K记录/秒和30G数据到带有TokuDB的7G，查询性能急剧下降(没有索引是群集的)。

特别是简单的查询：从test_table中选择count(*)；MyISAM花费了0.0001秒，而TokuDB则上升到20秒。还查询扫描所有90M行表(，其中column_not_indexed = something)，并为MyISAM接受2 5min，而TokuDB的持续时间超过5分钟！所有其他查询的性能也都有所下降。他们没有一个比MyISAM更好的相同大小的桌子。

因此，虽然我的理解是，TokuDB使用的分形树索引在查询速度上会有很好的性能，但这是不可能的。是否有任何测试过TokuDB的人遇到了同样的问题，或者能够给出如何提高查询性能的提示？

create语句是：

CREATE TABLE `table` (
`Event` bigint(20) NOT NULL AUTO_INCREMENT,
`TimeStamp` bigint(20) NOT NULL DEFAULT '0',
`Field_1` smallint(4) NOT NULL DEFAULT '0',
`Field_2` bigint(12) NOT NULL DEFAULT '0',
`Field_3` varchar(40) NOT NULL DEFAULT '',
`Field_4` bigint(15) DEFAULT NULL,
(...)
PRIMARY KEY (`Event`) USING BTREE,
KEY `Index_ts` (`Timestamp`),
KEY `Index_1` (`Field_1`),
KEY `Index_2` (`Field_2`),
KEY `Index_3` (`Field_3`)
)ENGINE=TokuDB DEFAULT CHARSET=latin1

其中一些问题是：

SELECT count(*) FROM table  
**MyISAM:0.00981429   TokuDB:21.40218998**
SELECT Field_2, Timestamp FROM table IGNORE KEY (Index_3, Index_1) WHERE (Field_3 LIKE "%asweb.be") AND (Field_1 < 42) ORDER BY Timestamp LIMIT 2000
**MyISAM:125.9707183  TokuDB:356.6146628**
SELECT Field_2, Timestamp FROM table IGNORE KEY (Index_1) WHERE (Field_4 = "206012216849912") AND (Field_1 < 42) ORDER BY Timestamp LIMIT 2000
**MyISAM:120.0966643  TokuDB:293.2259202**
SELECT Field_2, Timestamp FROM table IGNORE KEY (Index_1) WHERE (Field_2 = "32475731333") AND (Field_1 < 42) ORDER BY Timestamp LIMIT 100000
**MyISAM:0.00552937   TokuDB:0.18659729**

另外：

Query 1: SHOW PROFILE CPU FOR QUERY 1;          
Status  Duration    CPU_user    CPU_system
Queried about 88450000 rows 0.001905    0   0
Queried about 88460000 rows 0.001902    0.004   0
Queried about 88470000 rows 0.001906    0   0
Queried about 88480000 rows 0.001905    0.004   0
Queried about 88490000 rows 0.001664    0   0
Queried about 88500000 rows 0.001907    0.004   0
Queried about 88510000 rows 0.001898    0   0
Queried about 88520000 rows 0.001903    0.004001    0
Queried about 88530000 rows 0.001902    0   0
Queried about 88540000 rows 0.001903    0.004   0
Queried about 88550000 rows 0.001661    0   0
Queried about 88560000 rows 0.001905    0   0
Queried about 88570000 rows 0.001901    0.004   0
Queried about 88580000 rows 0.001912    0   0
Queried about 88590000 rows 0.001902    0.004   0
Queried about 88600000 rows 0.001908    0   0
Queried about 88610000 rows 0.001664    0.004001    0
Queried about 88620000 rows 0.001899    0   0
Queried about 88630000 rows 0.001905    0.004   0
Queried about 88640000 rows 0.001901    0   0
Queried about 88650000 rows 0.0019  0.004   0
Queried about 88660000 rows 0.001661    0   0
Queried about 88670000 rows 0.001906    0.004   0
Queried about 88680000 rows 0.001895    0   0
Queried about 88690000 rows 0.001907    0   0
Queried about 88700000 rows 0.001907    0.004001    0
Queried about 88710000 rows 0.001905    0   0
Queried about 88720000 rows 0.001663    0.004   0
Queried about 88730000 rows 0.001905    0   0
Queried about 88740000 rows 0.001903    0.004   0
Queried about 88750000 rows 0.001899    0   0
Queried about 88760000 rows 0.001898    0.004   0
Queried about 88770000 rows 0.001898    0   0
Queried about 88780000 rows 0.001665    0.004001    0
Queried about 88790000 rows 0.0019  0   0
Queried about 88800000 rows 0.001903    0.004   0
Queried about 88810000 rows 0.001909    0   0
Queried about 88820000 rows 0.001903    0.004   0
Queried about 88830000 rows 0.001663    0   0
Queried about 88840000 rows 0.001902    0   0
Queried about 88850000 rows 0.001901    0.004   0
Queried about 88860000 rows 0.001903    0   0
Queried about 88870000 rows 0.0019  0.004001    0
Queried about 88880000 rows 0.001904    0   0
Queried about 88890000 rows 0.001662    0.004   0
Queried about 88900000 rows 0.001903    0   0
Queried about 88910000 rows 0.001901    0.004   0
Queried about 88920000 rows 0.001905    0   0
Queried about 88930000 rows 0.001904    0.004   0
Queried about 88940000 rows 0.001898    0   0
Queried about 88950000 rows 0.001666    0.004001    0
Queried about 88960000 rows 0.001901    0   0
Queried about 88970000 rows 0.001905    0.004   0
Queried about 88980000 rows 0.001899    0   0
Queried about 88990000 rows 0.001907    0   0
Queried about 89000000 rows 0.00166 0.004   0
Queried about 89010000 rows 0.001903    0   0
Queried about 89020000 rows 0.001899    0.004   0
Queried about 89030000 rows 0.001905    0   0
Queried about 89040000 rows 0.0019  0.004001    0
Queried about 89050000 rows 0.0019  0   0
Queried about 89060000 rows 0.001662    0.004   0
Queried about 89070000 rows 0.001897    0   0
Queried about 89080000 rows 0.001901    0.004   0
Queried about 89090000 rows 0.001894    0   0
Queried about 89100000 rows 0.001897    0.004   0
Queried about 89110000 rows 0.001891    0   0
Queried about 89120000 rows 0.001667    0   0
Queried about 89130000 rows 0.001899    0.004001    0
Queried about 89140000 rows 0.001901    0   0
Queried about 89150000 rows 0.001903    0.004   0
Queried about 89160000 rows 0.00191 0   0
Queried about 89170000 rows 0.001662    0.004   0
Queried about 89180000 rows 0.001906    0   0
Queried about 89190000 rows 0.001903    0.004   0
Queried about 89200000 rows 0.0019  0   0
Queried about 89210000 rows 0.001904    0.004001    0
Queried about 89220000 rows 0.001897    0   0
Queried about 89230000 rows 0.001663    0.004   0
Queried about 89240000 rows 0.001902    0   0
Queried about 89250000 rows 0.001906    0   0
Queried about 89260000 rows 0.001905    0.004   0
Queried about 89270000 rows 0.001903    0   0
Queried about 89280000 rows 0.001894    0.004   0
Queried about 89290000 rows 0.00166 0   0
Queried about 89300000 rows 0.001903    0.004001    0
Queried about 89310000 rows 0.001901    0   0
Queried about 89320000 rows 0.0019  0.004   0
Queried about 89330000 rows 0.001904    0   0
Queried about 89340000 rows 0.001661    0.004   0
Queried about 89350000 rows 0.001892    0   0
Queried about 89360000 rows 0.001808    0.004   0
Queried about 89370000 rows 0.000027    0   0
end 0.000008    0   0
query end   0.000008    0   0
closing tables  0.000008    0   0
freeing items   0.000007    0   0
updating status 0.000029    0   0
logging slow query  0.000005    0   0
cleaning up 0.000005    0   0

闻起来像虫子？

Answer 1

MyISAM在全表扫描中击败了InnoDB和TokuDB，因为它基本上只是按顺序扫描一个文件。