TokuDB Performance Questions

[vinoth kumar ramachandran]

Hi there,

 

I have few questions on tokudb. Please help to clarify.

 

TokuDB : version                 | 5.5.41-tokudb-7.5.5         

 

 

1.       I have table with 2 B records and two clustering Indexes on this table – One is with 6 dimensions and another one on date column and no PK

2.       Daily ETL should delete 17M records and Insert 18M records. Currently this step takes more than 40 Minutes – is there any other ways to tune this script to reduce the Loading time? (attached the code)

3.       During this loading, my select query fails with “Error Code: 1205. Lock wait timeout exceeded; try restarting transaction” error. –How to avoid this issue?

4.       When we do complete data restatement – it taking more than 48 hours to restate the data. Loading data from flat file to staging table(using parallel loads) -5 hours and loading data to target table from staging tables – How to reduce the loading time?

5.       To load the data in parallel in target table – created yearly table like table_2010,table_2011,..,table_2015 and loaded the table into it and create view on top of these five tables. But when we access data view – it is not using the Clustering index. Copying the data to buffer from all the tables and then applying filter. This is causing the delay ( more than 10 minutes). Is there any better plan?

6.       Do you suggest partition this large table for easy maintenance?  If yes than which type?  Partition with clustering Index will provide better performance?

 

Please let me know if you need any other data to provide suggestion for above questions.

 

Thanks,

Vinoth

[Rhys Campbell]

A few things I might try...

- Partitioning might enable you to take advantage of EXCHANGE PARTITION https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

- Drop index before load then recreate after.

- You load does everything in a single transaction. I would break this up into smaller bits. So something like...

WHILE (ROW_COUNT <> 0)

START TRAN

DELETE FROM t1 WHERE condition = true LIMIT 5000;

SET ROW_COUNT = ROW_COUNT()

COMMIT

Similar approach with the inserts too. Your lock timeout will probably disappear.

- It might actually be quicker to SELECT the data you want to keep into a new table then drop TGT_TABLE_SD before renaming your staging table to this.

[vinoth kumar ramachandran]

Hi Rhys Campbell,

Thanks for suggestions.

TokuDB version is 5.5.41-tokudb-7.5.5. we using mysql 5.5, so it is not possible to leverage the advantage of EXCHANGE PARTITION.

Our table size is 4.5 billion records, so dropping and recreating index will take hours to complete

Sure. I will try to break up the delete and insert process. Will keep posted on this.

 

Thanks,
Vinoth

[Gordon Raebiger]

interesting - is there a plan to update Toku to 5.6?

[Abdelhak Errami]

We have the binaries available for download for  Percona Server 5.6:

https://www.percona.com/downloads/Percona-Server-5.6/LATEST/

--
You received this message because you are subscribed to the Google Groups "tokudb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tokudb-user...@googlegroups.com.
To post to this group, send email to tokud...@googlegroups.com.
Visit this group at http://groups.google.com/group/tokudb-user.
For more options, visit https://groups.google.com/d/optout.

--

Abdelhak Errami

Support Engineer

Tokutek

[vinoth kumar ramachandran]

Hi Abdelhak Errami,

 

 

Thanks for the update.

 

Percona Server 5.6 has tokudb-7.5.4 but currently we are using 5.5.41-tokudb-7.5.5.  

 

Does this cause any big problems?  

 

My another question – Is the option available like using TokuDB without Percona Server?

 

Thanks,

Vinoth

[Abdelhak Errami]

not sure where you get the binaries from but the ones I have mentioned have tokudb 7.5.8

mysql> select @@tokudb_version\G select @@version\G

*************************** 1. row ***************************

@@tokudb_version: tokudb-7.5.8

1 row in set (0.00 sec)

*************************** 1. row ***************************

@@version: 5.6.25-73.1-log

1 row in set (0.00 sec)

[Abdelhak Errami]

For this question:

My another question – Is the option available like using TokuDB without Percona Server?

It's not supported,  you will have to compile from source, and it's going to be very hard to maintain going forward.

On Wed, Sep 2, 2015 at 6:12 PM, vinoth kumar ramachandran <dbvin...@gmail.com> wrote:

[vinoth kumar ramachandran]

Thanks for the update Abdelhak Errami.

Yeah, you are right, I used the old one. I got it now.

 

Thanks,
Vinoth

[Phil]

You can also get TokuDB as part of MariaDB (also based on MySQL).

Phil

[Big_G]

But toku itself is no longer distributing new releases, right?

[Abdelhak Errami]

it has a new name PerconaFT instead of Toku-ft:

https://github.com/percona/PerconaFT

[Phil]

The official distributions of MariaDB contain TokuDB.

5.5 contains some basic support (but without some of the advanced features ie hot index creation etc, from memory)
10.0 contains pretty much the full capability I think.

In each case, the syntax for some of the capabilities such as clustered keys is a bit different.

Also, the .frm files aren't directly compatible, so you may need to dump/reload tables to migrate.  They work, but it complains about the format and I think maybe the compression type isn't carried over properly.

I migrated from Tokutek's MariaDB 5.5 to Official MariaDB 10.0 recently.

Phil

[joe]

Gordon: 

Just to clarify, Tokutek was acquired by Percona so the releases from Percona are in effect, being distributed by Tokutek. 

One advantage is that the new releases are now based on PerconaServer and MySQL 5.6 which provides all the benefits of the newer MySQL branch as well as any fractal tree enhancements.

Joe

On Thursday, September 3, 2015 at 8:34:33 AM UTC-4, Gordon Raebiger wrote:

But toku itself is no longer distributing new releases, right?

Am 03.09.2015 12:33 schrieb "Phil" 

You can also get TokuDB as part of MariaDB (also based on MySQL).