title: TokuDB performance in MariaDB
body: <p>I was doing some performance tests on TokuDB engine (i used mariadb-5.5.30-tokudb-7.0.4 from Tokutek website on Dell PowerEdge R720 with 128GB of memory and with the default memory allocation for TokuDB, which is 64GB - tokudb_cache_size) and something really unexpected came up. </p>
  
  <p>In the testing scenario, i inserted about <strong>90M rows</strong> on an <strong>empty table</strong> (33 columns, 1 primary auto_increment key, 5 indexes) and while i noticed significant speed performance and data compession compared to MyISAM engine, that is from <strong>15K to 20K records/sec</strong> and <strong>30G data to 7G</strong> with TokuDB, query performance drop dramatically (no index was clustering). </p>
  
  <p>Especially, the simple query: <strong>select count(*) from test_table</strong>; took 0.0001 sec for MyISAM while it went up to <strong>20 sec</strong> for TokuDB. Also queries that scan all the 90M row table (<strong>where column_not_indexed = something</strong>) and took about <strong>2min</strong> for MyISAM, with TokuDB their duration was above <strong>5min</strong>! All the other queries had also performance deterioration. None of them was better than MyISAM with the same-size table.</p>
  
  <p>So, while it came to my understanding that the fractal tree indexing that TokuDB uses would have excellent performance on query speed, that does not happen. Does anybody who have tested TokuDB had the same problems or can give hints on how to make query performance better?</p>
  
  <p>The create table statement is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Some of the queries were:</p>
  
  <pre><code>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 &lt; 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 &lt; 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 &lt; 42) ORDER BY Timestamp LIMIT 100000
  **MyISAM:0.00552937   TokuDB:0.18659729**
  </code></pre>
  
  <p>Also:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Smells like bug?</p>