CentOS 6.5, MariaDB 10.0.12 with latest TokuDB engine

hmm, what's the feature request aspect here ?

there is a db schema called mysql.sql in zabbix sources, so need to create new one tokudb.sql with different indexes than mysql, also this schema must be supported by zabbix server, when it auto updates with new versions.

TokuDB is another DB engine like Mysql, Oracle, Postgres, so I ask to support it.

according to our experience, tokudb doesn't perform that great for zabbix workload, thus supporting it might not be that useful... leaving this open for now, though

we have done all possible optimization to InnoDB and haven't got performance like TokuDB,
Our workload 30K items, 500 nps, we use large buffers, so we work 50 updates and 20 inserts.
Our managers like to use screens with a lot of graphs, we have 600 selects per seconds and more

I highly doubt query cache as it is done in Mysql / Mariadb is usable with zabbix (except there's someone pressing Ctrl + F5 constantly generating the same query).

Vadim, can you please attach schema generated? I'm a bit curious about the solutions like "and this index includes all fields of each row".

(full disclosure, I work at Tokutek, creators of TokuDB)

@richlv, Can you share your past TokuDB experiences further? We continue to make improvements to the performance of TokuDB and I'd be curious to understand workloads that aren't performant.

@everyone, We'd be happy to see broader usage of TokuDB with products like Zabbix, and I'm happy to assist in this effort. I assume a large part of the Zabbix workload is time based insertion on tables with secondary indexes, so our ability to ingest at high rates of speed along with our built-in compression should be a great fit.

tim, there was some communication between tokutek/zabbix may 2012 - october 2013, maybe that allows to dig up more records.
admittedly, that's 2 years ago, maybe situation has changed significantly by now

Yes, I remember the early portion of that timeline well. I'm confident that the current version of TokuDB will perform much better than back then, as is evidenced by the creator of this ticket. Please let me know if there is any way we can help.

Does Zabbix by chance have any benchmarking code to test their workload's performance? That would be a great place to start.

Lets begin deeply into TokuDB
@Tim, thank you for joinning us in this disscusion, I would like to get your recomendations and comparison of original mysql schema and my tokudb.

Here is:

CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) `clustering`=yes,
KEY `clustering_idx_itemid_clock` (`itemid`,`clock`) `clustering`=yes
) ENGINE=TokuDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=48 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_log`
–

DROP TABLE IF EXISTS `history_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) USING BTREE
) ENGINE=TokuDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=462 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_str`
–

DROP TABLE IF EXISTS `history_str`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_str` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` varchar(255) NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `clustering_itemid_clock` (`itemid`,`clock`) USING BTREE,
KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) `clustering`=yes
) ENGINE=TokuDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=93 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_str_sync`
–

DROP TABLE IF EXISTS `history_str_sync`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_str_sync` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`nodeid` int(11) NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` varchar(255) NOT NULL DEFAULT '',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `history_str_sync_1` (`nodeid`,`id`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_sync`
–

DROP TABLE IF EXISTS `history_sync`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_sync` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`nodeid` int(11) NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `history_sync_1` (`nodeid`,`id`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_text`
–

DROP TABLE IF EXISTS `history_text`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_text_1` (`itemid`,`clock`),
KEY `history_text_2` (`itemid`,`id`),
KEY `id` (`id`),
KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) `clustering`=yes
) ENGINE=TokuDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=125 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

–
-- Table structure for table `history_uint`
–

DROP TABLE IF EXISTS `history_uint`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `clustering_itemid_clock` (`itemid`,`clock`) `CLUSTERING`=YES,
KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) `clustering`=yes
) ENGINE=TokuDB DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=48 `COMPRESSION`=TOKUDB_LZMA;
/*!40101 SET character_set_client = @saved_cs_client */;

Such indexes KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) `clustering`=yes are wrong from my side, but we work now with it ok.

Cause deleting indexes in TokuDB locks tables, I kept them, beacause it almost recreation of table.
But creating of additional indexes goes online without locking.

As you see at history table, I deleted primary index on item_id,
and now KEY `clustering_idx_itemid_clock` (`itemid`,`clock`) `clustering`=yes is main index, and all history data must be indexed that way.

@Andrejs Čirkovs, let look at db files:

rwxrwx-x 1 mysql mysql 3.8G Jul 1 22:45 _zabbix_tokunew_history_key_clustering_idx_clock_itemid_11004aa6_3_1a_P_0.tokudb
rwxrwx-x 1 mysql mysql 3.2G Jul 1 22:45 _zabbix_tokunew_history_key_clustering_idx_itemid_clock_111663ae_3_1a_P_0.tokudb
rwxrwx-x 1 mysql mysql 4.1G Jul 1 22:45 _zabbix_tokunew_history_main_760_1_19_B_1.tokudb
rwxrwx-x 1 mysql mysql 64K May 22 04:22 _zabbix_tokunew_history_status_1d3_1_19.tokudb

Look at size of clustering indexes, they are almost the same as main table.

Here is the stat of Qcache:

show status like '%Qca%';
---------------------------------+

---------------------------------+

---------------------------------+

Just try it and you will see that front end will respond much faster, don't forget about pushdown -> off

tim, unfortunately we don't have any public unified performance tests available at this time

@vadim, I'll try to respond to your proposed schema and questions.

Clustering secondary index size
Yes, it is common for a clustering secondary index to be similarly sized to the PK index. It may be slightly smaller, depending on the number of fields in it's key as this will lead to better compression.

Clustering index advantages
You'd want to cluster a secondary index if you are querying it often, especially if you are querying ranges often (say > 10 rows). The reason for this is that a clustering index behaves like a covering index, in that it does not need to do additional work (IO) to get the columns needed for the particular SQL statement from the PK index.

Compression
I notice that you used LZMA compression for all tables. You might want to consider using zlib, which will mean less compression (but still very good) while requiring significantly less CPU for compression and decompression. Faster decompression means your queries will be faster as well. I'd suggest you try it and see. If you have a lot of unused CPU in your servers, then LZMA is likely fine.

USING BTREE syntax
This does nothing for TokuDB tables, and should be removed from your create table statements.

Replacing Primary Keys
I don't understand the point you are trying to make, or if you are asking a question. Can you elaborate?

@Tim,

Compression: we have 24 cores on server and we the load is very small, load average is about 2, we have 16GB RAM, and have 4GB free, tokudb is set to use 9GB. We woud like to have samller db.

KEY `clustering_idx_clock_itemid` (`clock`,`itemid`) USING BTREE - it is unchanged table from original zabbix schema.
mysqldump create this export.

Replacing Primary Keys - the main topic.

So if clustering is table, so I don't understand why to have secondary keys on history data?
In InnoDB - the PK is always clustering. (even if index was not created directly, it is created hidden)

So I think it is enough to have one PK clustering (item_id,clock) and don't have secondary indexes. Am I right?

Tim, another question: is it any value to use desc order while creating index (item_id,clock desc), cause I read that InnoDB don't look at it? What about TokuDB?

@vadim.

So if clustering is table, so I don't understand why to have secondary keys on history data?
In InnoDB - the PK is always clustering. (even if index was not created directly, it is created hidden)
So I think it is enough to have one PK clustering (item_id,clock) and don't have secondary indexes. Am I right?

Good question. One potential reason to cluster a secondary index rather than just making what would be the clustering secondary index the primary key is insert performance. If you have a PK on an auto-incrementing column then you are always inserting on the right side of the index, which means inserts are very fast. The same is true if you don't declare a primary key, as the hidden PK is auto-increment. The reason these are fast is that a primary key must be unique, and the unique check can be expensive, as in require an IO, if the insert pattern for the primary key is random. The clustered secondary index doesn't need a unique check so TokuDB's Fractal Tree indexing technology is fast forever, as opposed to InnoDB's which will slow down significantly when your secondary indexes don't fit in the cache.

Does that help?

To continue this topic I'd need to better understand the particular table in question (lets take this 1 table at a time), and understand the insertion pattern as well as update/delete/select statements.

Is it any value to use desc order while creating index (item_id,clock desc), cause I read that InnoDB don't look at it? What about TokuDB?

There isn't value in declaring it desc, an index can efficiently be scanned in either direction.

So here is from zabbix 2.2.4 original schema:

CREATE TABLE `history` (
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` double(16,4) DEFAULT '0.0000' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL
) ENGINE=InnoDB;
CREATE INDEX `history_1` ON `history` (`itemid`,`clock`);

Must be index history_1 be clustering? We talk only about for TokuDB.

@vadim.

CREATE TABLE `history` (
`itemid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`value` double(16,4) DEFAULT '0.0000' NOT NULL,
`ns` integer DEFAULT '0' NOT NULL
) ENGINE=InnoDB;
CREATE INDEX `history_1` ON `history` (`itemid`,`clock`);

In TokuDB you can optionally make secondary indexes clustering. A clustered secondary index is much like the primary key in that a complete copy of the row is stored in the index, which means the following:

• Since two full copies of each row are now being maintained (one in the PK and on in this index) you are using more space. TokuDB's compression usually makes this a non-issue as your on-disk size for both copies is generally much smaller than the on-disk size of InnoDB for the same data.
• All updates to any row in the table will require updating the clustered secondary index. If your table is insert only (or mostly) then this is not important. Also, TokuDB is great at secondary index maintenance so I haven't seen users complain about performance related to updates in TokuDB with clustering secondary indexes.

Now on to the good stuff!

Since the clustered secondary index has a full copy of the row, any select that uses the index has all the columns in the row available to it without having to do a look-up into the primary key index to get the other columns. Both InnoDB and TokuDB have this "look the rest of the row up in the primary key" behavior (which TokuDB avoids if the secondary index is clustered). You can think of clustered secondary indexes as a better version of a covered index.

So, I think for the above table you'd be best off leaving the create table alone (keep the hidden PK), but making the index clustering, as in:

CREATE TABLE `history` (
  `itemid` bigint unsigned NOT NULL,
  `clock` integer DEFAULT '0' NOT NULL,
  `value` double(16,4) DEFAULT '0.0000' NOT NULL,
  `ns` integer DEFAULT '0' NOT NULL,
  KEY `history_1` (`itemid`,`clock`) CLUSTERING=yes
) ENGINE=TokuDB `COMPRESSION`=TOKUDB_LZMA;