{"id":6603,"date":"2013-10-14T12:03:43","date_gmt":"2013-10-14T10:03:43","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6603"},"modified":"2013-10-14T12:03:43","modified_gmt":"2013-10-14T10:03:43","slug":"converting-an-olap-database-to-tokudb-part-3-operational-stuff","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/converting-an-olap-database-to-tokudb-part-3-operational-stuff","title":{"rendered":"Converting an OLAP database to TokuDB, part 3: operational stuff"},"content":{"rendered":"<p>This is the third post in a series of posts describing our experience in migrating a large DWH server to TokuDB (see <a href=\"http:\/\/code.openark.org\/blog\/mysql\/converting-an-olap-database-to-tokudb-part-1\">1st<\/a> and <a href=\"http:\/\/code.openark.org\/blog\/mysql\/converting-an-olap-database-to-tokudb-part-2-the-process-of-migration\">2nd<\/a> parts). This post discusses operations; namely ALTER TABLE operations in TokuDB. We ran into quite a few use cases by this time that we can shed light on.<\/p>\n<p>Quick recap: we&#8217;ve altered one of out DWH slaves to TokuDB, with the goal of migrating most of out servers, including the master, to TokuDB.<\/p>\n<h4>Adding an index<\/h4>\n<p>Shortly after migrating our server to TokuDB we noticed an unreasonably disproportionate slave lag on our TokuDB slave (red line in chart below) as compared to other slaves.<\/p>\n<blockquote><p><a href=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2013\/09\/tokudb-slave-lag.png\"><img loading=\"lazy\" decoding=\"async\" alt=\"tokudb-slave-lag\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2013\/09\/tokudb-slave-lag.png\" width=\"700\" height=\"329\" \/><\/a><\/p><\/blockquote>\n<p>Quick investigation led to the fact that, coincidentally, a manual heavy-duty operation was just taking place, which updated some year&#8217;s worth of data retroactively. OK, but why so slow on TokuDB? Another quick investigation led to an apples vs. oranges problem: as depicted in <a href=\"http:\/\/code.openark.org\/blog\/mysql\/converting-an-olap-database-to-tokudb-part-1\">part 1<\/a>, our original setup included MONTHly partitioning on our larger tables, whereas we could not do the same in TokuDB, where we settled for YEARly partitioning.<\/p>\n<p>The heavy-duty operation included a query that was relying on the MONTHly partitioning to do reasonable pruning: a <strong>WHERE<\/strong> condition on a date column did the right partition pruning; but where on InnoDB that would filter <strong>1<\/strong> month&#8217;s worth of data, on TokuDB it would filter <strong>1<\/strong> <em>year<\/em>.<\/p>\n<p>Wasn&#8217;t it suggested that TokuDB has online table operations? I decided to give it a shot, and add a proper index on our date column (I actually created a compound index, but irrelevant).<\/p>\n<p>It took <strong>13<\/strong> minutes to add an index on a <strong>1GB<\/strong> TokuDB table (approx. <strong>20GB<\/strong> InnoDB uncompressed equivalent):<\/p>\n<ul>\n<li>The <strong>ALTER<\/strong> was non blocking: table was unlocked at that duration<\/li>\n<li>The client issuing the <strong>ALTER<\/strong> <em>was<\/em> blocked (I thought it would happen completely in the background) &#8212; but who cares?<\/li>\n<li>I would say <strong>13<\/strong> minutes is fast<\/li>\n<\/ul>\n<p>Not surprisingly adding the index eliminated the problem altogether.<\/p>\n<h4>Modifying a PRIMARY KEY<\/h4>\n<p>It was suggested by our DBA that there was a long time standing need to modify our <strong>PRIMARY KEY<\/strong>. It was impossible to achieve with our InnoDB setup (not enough disk space for the operation, would take weeks to complete if we did have the disk space). Would it be possible to modify our TokuDB tables? On some of our medium-sized tables we issued an <strong>ALTER<\/strong> of the form:<!--more--><\/p>\n<blockquote>\n<pre>ALTER TABLE my_table DROP PRIMARY KEY, ADD PRIMARY KEY (c1, c2, c3, ...);<\/pre>\n<\/blockquote>\n<p>Time-wise the operation completed in good time. We did note, however, that the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/tokudb_file_map.html\">disk space consumed by the new table<\/a> was <em>doubled<\/em>. Was it due to the fact we added two columns to our PK? Did that account for the bloated space? I did not believe so, and decided to rebuild the table:<\/p>\n<blockquote>\n<pre>OPTIMIZE TABLE my_table<\/pre>\n<\/blockquote>\n<p>Nope. Space not reduced. However we were unconvinced and <a href=\"https:\/\/groups.google.com\/forum\/#!topic\/tokudb-user\/ow47QY2pcgU\">asked<\/a>. As usual, we got quick response from the Tokutek team; this was a bug: while our original table used the TOKUDB_SMALL row format (high compression), the table rebuild reset it to TOKUDB_FAST (normal compression), which makes for roughly twice the file size. The bug was filed as: <a href=\"https:\/\/github.com\/Tokutek\/ft-engine\/issues\/107\">alter table operations that rebuild the table lose the original tokudb compression<\/a>.<\/p>\n<p>Now, we <em>were<\/em> altering the <strong>PRIMARY KEY<\/strong>. We were not expecting an online operation anyhow, and didn&#8217;t mind blocking the table; hence the solution was simple: make sure to spceify the row format:<\/p>\n<blockquote>\n<pre>ALTER TABLE my_table DROP PRIMARY KEY, ADD PRIMARY KEY (c1, c2, c3, ...) ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL;<\/pre>\n<\/blockquote>\n<p>This worked in terms of disk space &#8212; but we only later realized it would still make us trouble.<\/p>\n<h4>Modifying a PRIMARY KEY on our largest table<\/h4>\n<p>We moved on to our largest table: originally <strong>1TB<\/strong> InnoDB <strong>COMPRESSED<\/strong>, worth of <strong>2TB<\/strong> uncompressed. With TokuDB it went down to <strong>100GB<\/strong>. Converting this table to TokuDB took about <strong>40<\/strong> hours, which is just fast. We issued an ALTAR TABLE modifying the PRIMARY KEY as above and waited.<\/p>\n<p>The operation did not complete after <strong>40<\/strong> hours. Nor after <strong>3<\/strong> days. By day <strong>4<\/strong> we thought we might look into this. Fortunately, TokuDB is friendly on <strong>SHOW PROCESSLIST<\/strong> and provides you with useful information, such as &#8220;<strong>Fetched about 1234567890 rows, loading data still remains<\/strong>&#8220;. Yikes! We extrapolated the values to realize it would take <strong>2<\/strong> <em>weeks<\/em> to complete! Weekend went by and we decided to find a better way. Again, posting on the tokudb-user group, we got a definitive answer: a table rebuild does not utilize the <em>bulk loader<\/em> (you really want to be friends with the bulk loader, it&#8217;s the process that loads your data quickly).<\/p>\n<p>And so we chose to <strong>KILL<\/strong> the <strong>ALTER<\/strong> process and go another way; again, <strong>KILL<\/strong>s are very easy with TokuDB <strong>ALTER<\/strong> operations: took <strong>3<\/strong> minutes to abort this week old operation. The alternative operation was:<\/p>\n<blockquote>\n<pre>CREATE TABLE my_table_New LIKE my_table;\r\nALTER TABLE my_table_New DROP PRIMARY KEY, ADD PRIMARY KEY (c1, c2, c3, ...) ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL;\r\nINSERT INTO my_table_New SELECT * FROM my_table;\r\nRENAME TABLE my_table TO my_table_Old, my_table_New TO my_table;\r\nDROP TABLE my_table_Old;<\/pre>\n<\/blockquote>\n<p>The <strong>INSERT INTO &#8230; SELECT<\/strong> operation does use the bulk loader when you do it on an empty table. It completed within merely <strong>30<\/strong> hours. Hurrah!<\/p>\n<h4>DROPping a TABLE<\/h4>\n<p>It was an immediate operation to drop our &#8220;Old&#8221; table &#8212; subsecond. Nothing like your InnoDB DROP.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is the third post in a series of posts describing our experience in migrating a large DWH server to TokuDB (see 1st and 2nd parts). This post discusses operations; namely ALTER TABLE operations in TokuDB. We ran into quite a few use cases by this time that we can shed light on. Quick recap: [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,14,102],"class_list":["post-6603","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-innodb","tag-tokudb"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Iv","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6603","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=6603"}],"version-history":[{"count":13,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6603\/revisions"}],"predecessor-version":[{"id":6620,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6603\/revisions\/6620"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}