TokuDB not much faster than MySQL

Question

I have converted a MySQL database with 80.000.000 rows to TokuDB.

Now when I run:

 select count(id) from xxx where active=1

it takes 90% of the time of the normal MySQL request.

What do I have to further optimize so that it runs faster?

---

The table definition:

CREATE TABLE `adsDelivered` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` varchar(40) NOT NULL,
  `_adsDelivered` bigint(20) NOT NULL DEFAULT '0',
  `_campaign` bigint(20) NOT NULL DEFAULT '0',
  `_ad` bigint(20) NOT NULL DEFAULT '0',
  `session` varchar(44) NOT NULL,
  `referer` text NOT NULL,
  `refererDomain` varchar(256) NOT NULL,
  `pageTime` int(11) NOT NULL DEFAULT '0',
  `pageVisibleTime` int(11) NOT NULL DEFAULT '0',
  `browser` varchar(256) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `clicks` int(11) NOT NULL DEFAULT '0',
  `clickTimeLast` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tag` varchar(256) NOT NULL,
  `countryShort` varchar(2) NOT NULL,
  `timeCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timeUpdated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`),
  KEY `_campaign` (`_campaign`),
  KEY `_ad` (`_ad`),
  KEY `_adsDelivered` (`_adsDelivered`),
  KEY `session` (`session`),
  KEY `tag` (`tag`),
  KEY `ip` (`ip`),
  KEY `countryShort` (`countryShort`),
  KEY `refererDomain` (`refererDomain`)
) ENGINE=TokuDB AUTO_INCREMENT=7420143 DEFAULT CHARSET=utf8;

Answer 1

I work for Tokutek. The answers here are mostly good. As Justin mentions, you need the right index, and your schema probably does not have the right index. I am happy to hear TokuDB was a little faster than InnoDB, but for table scans, assuming the table is not aged, it can go either way.

Here is a talk I gave on indexing that you may find helpful: http://www.youtube.com/watch?v=vaGAoK66ctM.

The first half is on indexing, the second half is a bit of a technical dive into fractal trees on a whiteboard. Hopefully this helps you with index design. I highly recommend understanding clustering secondary indexes, which TokuDB provides.

On other points. RolandoMySQLDBA is mostly correct on how InnoDB and TokuDB perform. Here is how to think about the performance of TokuDB. While the dataset fits in memory, TokuDB's fractal trees do not have any inherent advantage over InnoDB or other B-Tree based storage engines. The bottleneck, or rather the cliff, hits when data is big and does not fit in main memory. Where InnoDB's write performance, and write performance of other B-tree based storage engines, fall off a cliff, TokuDB's performance stays level. THAT is the indicator that you are getting something out of TokuDB. TokuDB will not take some well running existing system and supercharge its performance. TokuDB will take systems work well in memory but start to break down when falling out of memory, and ensures those systems perform well as data grows. That is what is going on with benchmarks that Percona shows, that is what is going on with iiBench (http://www.tokutek.com/resources/benchmarks/#iiBench).

Combine this write performance with TokuDB's compression, and suddenly clustering indexes, as explained in the indexing talk, become relatively cheaper. Maintaining more better indexes becomes cheaper. Use better indexes, a lot of I/O from queries may dissappear, and query throughput improves. This is how one can benefit from TokuDB.

Answer 2

You don't have active indexed. The only reason tokudb would be faster than InnoDB or MyISAM for this query would be if the table had exceptional compression which would reduce the total IO, because you are examining the whole table.

If a small fraction of the rows in the table (less then 30% give-or-take) have the value of active=1, then adding an index will help.

If most of the rows in the table are active=1, and this query is important, then you might consider maintaining a summary table instead. You could also consider partitioning the table and accessing the partitions in parallel using Shard-Query.

http://code.google.com/p/shard-query

TokuDB should be faster at INSERTIONS compared with InnoDB for large tables with many non-unique indexes, but not necessarily faster at SELECT queries. Mark Callaghan from Facebook saw a 3x speedup in query performance and a 50% reduction in storage footprint compared with InnoDB on the Facebook graph benchmark.

If the data is append only, you could also consider Infobright Community Edition, which is a column store, or if you are more academic, Fastbit.

http://infobright.org

https://sdm.lbl.gov/fastbit/

Answer 3

it takes 90% of the time of the normal mysql request.

If it takes less time, then it is faster?

TokuDB is all about efficient use of SSDs, particularly for write performance and longevity - if most of your data fits in memory, the MyISAM and InnoDB will be much faster at fetching data. And it will be no faster for single thread benchmarks. You appear to have taken no steps to recreate the situations in which TokuDB significantly outperforms other engines and ask for an explanation of a scenario where it should be slower.

Answer 4

In all fairness, TokuDB has strengths and weaknesses against InnoDB.

InnoDB has better transactional throughput than TokuDB until you hit a bottleneck that puts both storage engines the same level playing field: Data Compression.

TokuDB is always compressing data and saves space by a factor of 3 over InnoDB. What verified this was a Percona benchmark between the two storage engines. The test could not be completed because InnoDB ran out of diskspace.

In the test, InnoDB was starting to degrade although it had the better throughput. Over time and with enough diskspace, a true evaluation can be reached. IMHO, TokuDB would probably do better over the long haul with Throughput/Diskspace considered as a single metric.

Given the indexes you have, things can get sluggish with diskspace usage since secondary indexes include rowid entries back to the Clustered Index. I cannot make this same assertion on TokuDB since I have not learned of its internals at this time.

Answer 5

I don't think a count query is ever going to be representative of Toku's strengths. Inserting data in 1 shot is also a problem for a test on a dataset with indices that will be updated constantly. Regardless of whether it fits in memory, you're not aging the index so the BTREE will be nice and neat. Wait a few weeks and the BTREE will get more fragmented. A fractal tree is not going to get fragmented. So that's 1 flaw in the test. You're also not SELECTing anything. Once you do, you might want more than 1 clustered index, which InnoDB will not do. The way secondary indices work on InnoDB is what I hate most about it.

In InnoDB you're going to take a hit the second you use a non-primary index because it's essentially dereferencing (a quasi-self-join) a pointer in a random position with every row an index refers to. It uses a rather large 6 byte key to do this. I'm pretty sure that does horrible things to hardware cache since you're bouncing all over the place.

Depending on what you're SELECTing, the advantages of a clustered index could be huge since it would obviate that quasi-join.

But what do I know? I get away with using MyISAM + concurrent inserts on many things =)

@symcbean I believe that was the original intent of Toku (SSD), but it eventually led to grander aspirations. I vaguely remember a press release about it.

I also expect Toku to be competitive for reads except in read-heavy low-contention environments where MVCC is mostly just more complexity. Also, if the queries are very ad-hoc referring to various columns without much of a pattern, multiple clustered indexes will do less for you. Do not underestimate the utility of multiple clustered indexes when you have say ~5 types of queries that refer to ~5 different tuples each requiring a bunch of stuff to be SELECTed. Esp. without SSD, it's huge. Think about the principle of locality. InnoDB secondary indexes aren't friendly to spinning disks. I guess you can hack it with a covering index so you don't have to do that ugly quasi-join to get the other columns, but that won't scale and you're indexing lots of stuff for nothing (said: lots of GBs), esp. without compression. You're only referring to a prefix of the index. So unless it's just like 1-2 extra columns, it's a horrible abuse of space. In a clustered index, the row is just a payload on the leaves.

I'm still not sure why InnoDB can't support more than 1 clustering index regardless. It would be useful even in MyISAM in moderation. People abuse covering indexes to mimic clustered indexes all the time, and it would be nice.

@Zardosht Even if it does fit in memory, doesn't Toku keep the trees more balanced than BTREEs over time, offering better performance even in that case?

Also, why can't Toku do a partial clustering index. What if you don't need the whole row?