title: Why TokuDB and InnoDB insert is so slow compared to MyISAM
body: <p>I have prepared the following SQL statements to compare the performance behavior of MyISAM, InnoDB, and TokuDB (INSERT is executed for 100000 times):</p>
  
  <p>MyISAM:</p>
  
  <pre><code>CREATE TABLE `testtable_myisam` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `value1` INT DEFAULT NULL, `value2` INT DEFAULT NULL, PRIMARY KEY (`id`), KEY `index1` (`value1`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  INSERT INTO `testtable_myisam` (`value1`, `value2`) VALUES (FLOOR(RAND() * 1000), FLOOR(RAND() * 1000)); 
  </code></pre>
  
  <p>InnoDB:</p>
  
  <pre><code>CREATE TABLE `testtable_innodb` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `value1` INT DEFAULT NULL, `value2` INT DEFAULT NULL, PRIMARY KEY (`id`), KEY `index1` (`value1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO `testtable_innodb` (`value1`, `value2`) VALUES (FLOOR(RAND() * 1000), FLOOR(RAND() * 1000));
  </code></pre>
  
  <p>TokuDB:</p>
  
  <pre><code>CREATE TABLE `testtable_tokudb` (`id` bigint(20) NOT NULL AUTO_INCREMENT, `value1` INT DEFAULT NULL, `value2` INT DEFAULT NULL, PRIMARY KEY (`id`), KEY `index1` (`value1`)) ENGINE=TokuDB DEFAULT CHARSET=utf8;
  
  INSERT INTO `testtable_tokudb` (`value1`, `value2`) VALUES (FLOOR(RAND() * 1000), FLOOR(RAND() * 1000));
  </code></pre>
  
  <p>At the beginning, the INSERT performance of InnoDB is almost 50 times slower than MyISAM, and TokuDB is 40 times slower than MyISAM. </p>
  
  <p>Then I figure out the setting of "innodb-flush-log-at-trx-commit=2" on InnoDB, to make its INSERT behavior similar with MyISAM. </p>
  
  <p>The question is, what should I do on the TokuDB? I bet the poor INSERT performance of TokuDB is also caused by some inproper setting, but I cannot figure out the reason. </p>
  
  <p>--------- UPDATE ---------</p>
  
  <p>Thanks to tmcallaghan's comments, I have modified my setting into "tokudb_commit_sync=OFF", now the insert rate of TokuDB on small dataset seems to be meaningful (I will execute them on large dataset once I figure out the following problem):</p>
  
  <p>However, the select performance of TokuDB is still wired compared to MyISAM and InnoDB with following SQL (wherein the ? is replaced by a different Int by my simulator):</p>
  
  <pre><code>SELECT id, value1, value2 FROM testtable_myisam WHERE value1=?; 
  SELECT id, value1, value2 FROM testtable_innodb WHERE value1=?; 
  SELECT id, value1, value2 FROM testtable_tokudb WHERE value1=?; 
  </code></pre>
  
  <p>Upon a million dataset, each 10k SELECT statments cost 10 and 15 seconds by MyISAM and InnoDB individually, but TokuDB requires about 40 seconds. </p>
  
  <p>Did I miss some other settings?</p>
  
  <p>Thanks in advance!</p>