Greetings, I’ve just replaced an MySQL 5.7 with Percona 5.7.19-17 looking to replace InnoDB tables with TokuDB ones.
The server is a humble on (tight budget): 6 Cores - Centos 6.9, 8GB RAM with HDD 500GB for a database of about 70GB (data+index in InnoDB).
I wanted to try tokudb for the compression (to add an SSD of 128GB for the database), for the insertion speed and the concurrency. I was very pleased with the time it took to convert the biggest tables from InnoDB to TokuDB and the verbosity of the process, but after the conversion, the daily loading process run in the TokuDB tables tooooo slow. With InnoDB obtained in the order of 120/250 insertions per second, and with TokuDB went to 20/25 insertions per second. There is something very wrong with the configuration, but can’t figure what.
The my.cnf is very simple:
[mysqld]
lower_case_tablenames=1
skip-name-resolve
query_cache_size = 0
key_buffer_size=268435456
innodb_ft_sort_pll_degree = 3
innodb_log_file_size = 3072M
innodb_buffer_pool_size = 3072M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
I noticed when inserting into InnoDB tables, the disk reached 50% usage, but with TokuDB insertions got the disk at 100%.
SELECT querys run a tad faster, I’ve yet to change the index schema to exploit TokuDB advantages, but first I need to fix the loading time. Where should I begin tweaking?
Any advice is valued (sorry for the english)… Thanx.