{"id":6473,"date":"2013-09-03T09:04:12","date_gmt":"2013-09-03T07:04:12","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6473"},"modified":"2013-09-03T20:34:35","modified_gmt":"2013-09-03T18:34:35","slug":"converting-an-olap-database-to-tokudb-part-1","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/converting-an-olap-database-to-tokudb-part-1","title":{"rendered":"Converting an OLAP database to TokuDB, part 1"},"content":{"rendered":"

This is the first in a series of posts describing my impressions of converting a large OLAP server to TokuDB. There’s a lot to tell, and the experiment is not yet complete, so this is an ongoing blogging. In this post I will describe the case at hand and out initial reasons for looking at TokuDB.<\/p>\n

Disclosure: I have no personal interests and no company interests; we did get friendly, useful and free advice from Tokutek engineers. TokuDB is open source and free to use, though commercial license is also available.<\/p>\n

The case at hand<\/h4>\n

We have a large and fast growing DWH MySQL setup. This data warehouse is but one component in a larger data setup, which includes Hadoop, Cassandra and more. For online dashboards and most reports, MySQL is our service. We populate this warehouse mainly via Hive\/Hadoop. Thus, we have an hourly load of data from Hive, as well as a larger daily load.<\/p>\n

There are some updates on the data, but the majority of writes are just mysqlimport<\/strong>s of Hive queries.<\/p>\n

Usage of this database is OLAP: no concurrency issues here; we have some should-be-fast-running queries issued by our dashboards, as well as ok-to-run-longer queries issued for reports.<\/p>\n

Our initial and most burning trouble is with size. Today we use COMPRESSED<\/strong> InnoDB tables (KEY_BLOCK_SIZE<\/strong> is default, i.e. 8<\/strong>). Our data volume sums right now at about 2TB<\/strong>. I happen to know this translates as 4TB<\/strong> of uncompressed data.<\/p>\n

However growth of data is accelerating. A year ago we would capture a dozen GB per month. Today it is a 100GB<\/strong> per month, and by the end of this year it may climb to 150GB<\/strong> per month or more.<\/p>\n

Our data is not sharded. We have a simple replication topology of some 6<\/strong> servers. Machines are quite generous as detailed following. And yet, we will be running out of resources shortly: disk space (total 2.7TB<\/strong>) is now running low and is expected to run out in about six months. One of my first tasks in Outbrain is to find a solution to our DWH growth problem. The solution could be sharding; it could be a commercial DWH product; anything that works.<\/p>\n

The approach we experiment with<\/h4>\n

It was at my initial interview that I suggested TokuDB<\/a> might be a good solution, with the primary reason of being so good with compression. And we decided to experiment with this simple (setup-wise) solution of compression. If we could compress the data even by 50%<\/strong>, that would buy us considerable time. And it’s the simplest approach as we would need to change nothing at the application side, nor add additional frameworks.<\/p>\n

Of course, we were already using InnoDB COMPRESSED<\/strong> tables. How about just improving the compression? And here I thought to myself: we can try KEY_BLOCK_SIZE=4<\/strong>, which I know would generally compress by 50%<\/strong> as compared to KEY_BLOCK_SIZE=8<\/strong> (not always, but in many use cases). We’re already using InnoDB so this isn’t a new beast; it will be “more of the same”. It would work.<\/p>\n

I got myself a dedicated machine: a slave in our production topology I am free to play with. I installed TokuDB 7.0.1<\/strong>, later upgraded to 7.0.3<\/strong>, based on MySQL 5.5.30<\/strong>.<\/p>\n

The machine is a Dell Inc. PowerEdge R510<\/strong> machine, with 16<\/b> CPUs @ 2.1 GHz<\/b> and 126 GiB<\/b> RAM, 16 GiB<\/b> Swap. OS is CentOS 5.7<\/strong>,\u00a0 kernel 2.6.18<\/strong>. We have RAID 10<\/strong> over local 10k<\/strong> RPM SAS disks (10x600GB<\/strong> disks)<\/p>\n

How to compare InnoDB & TokuDB?<\/h4>\n

2TB<\/strong> of compressed data (for absolute measurement I consider it to be a 4TB<\/strong> worth of data) is quite a large setup. How do I do the comparison? I don’t even have too much disk space here…<\/p>\n

We have tables of various size. Our largest is in itself 1TB<\/strong> (2TB<\/strong> uncompressed) – half of the entire volume. The rest ranging 330GB<\/strong>, 140GB<\/strong>, 120GB<\/strong>, 90GB<\/strong>, 50GB<\/strong> and below. We have MONTH<\/strong>ly partitioning schemes on most tables and obviously on our larger tables.<\/p>\n

For our smaller tables, we could just CREATE TABLE test_table LIKE small_table<\/strong>, populating it and comparing compression. However, the really interesting question (and perhaps the only interesting question compression-wise) is how well would our larger (and specifically largest) tables would compress.<\/p>\n

Indeed, for our smaller tables we saw between 20%<\/strong> to 70%<\/strong> reduction in size when using stronger InnoDB compression: KEY_BLOCK_SIZE=4\/2\/1<\/strong>. How well would that work on our larger tables? How much slower would it be?<\/p>\n

We know MySQL partitions are implemented by actual independent<\/em> tables. Our testing approach was: let’s build a test_table from a one month worth of data (== one single partition) of our largest table. We tested:<\/p>\n