Innodb – question about using tokuDB or InnoDB for financial data

innodbtokudb

we want to use Percona xtraDBcluster for clustering 4 nodes (Master-Master) and we have a finance project which should have a rather large database. The number of tables is not more than 40, but we have about 2 tables which store financial transaction data and the estimated amount of data in these tables is going to grow at 350,000 records per day, and we should keep them at least 10 years to be able to do various reports.

Most of the operation (98%) in these tables is Insert/read but we can separate updatable fields in a separate table.

My questions are the following:

  • Should I use a tokuDB storage engine for such large amounts of data or InnoDB?
  • What is the best solution for such large and sensitive database?

Best Regards

Ali

Best Answer

A few things to note:

  • XtraDB Cluster doesn't scale writes (horizontally), i.e. you can't increase number of writes by adding more nodes. However, 350,000 records per day is approx. 4 record per second, which is no problem at all for XtraDB Cluster (or any other replication topology), at least as long as the nodes are on the same network.
  • XtraDB Cluster only supports the XtraDB / InnoDB table storage engine, so TokuDB isn't an option if you want to use that particular cluster technology.
  • 4 nodes isn't a good number. You should be using an odd number (3, 5, ...) of nodes, or an even number of nodes + one node with garbd, the Galera arbitrator.

XtraDB (which is InnoDB with a few patches from Percona) is a tried and tested technology, and XtraDB / InnoDB is the default storage engine in MySQL, Percona and MariaDB. InnoDB is being developed and supported by MySQL (owned by Oracle).

TokuDB is newer, it doesn't have as many years of usage and development. If I understand correctly, TokuDB is developed by Tokutek, now owned by Percona.

With these considerations, in a choice between these two technologies for a system for financial transaction data, I would probably go with XtraDB.