TokuDB: Index cardinality drops to zero

Question

I use TokuDb from Percona on some of our db servers. I set up the db, add first chunks of data and every thing works fine.

After a while and adding millions of entries I notice a heavy lose in db performance. Turns out, the indexes of some tables have a cardinality of zero. This affects not all but mostly multiple tables at the same time.

The mysql error log list some problems like this

TokuDB: Auto running foreground analysis for [table_name], delta_activity 1827 is greater than 30 percent of 0 rows. - failed, likely a job already running.

So I try to disable the auto_analyze to prevent this problem. Now this log is not pushed anymore but the index cardinality is dropped again.

Tested solutions:

I try to repair the indexes by executing
```
ANALYZE table [table_name]
```
and it works. But now after some days, the same problem is returning.
Disabled auto_analyze by
```
set tokudb_analyze_time = 0
```

System:

Percona TokuDB 5.7.16-10

Table hold around 11 million rows

CREATE TABLE `table1` (
  `field1` char(2) COLLATE utf8_bin NOT NULL,
  `field2` char(15) COLLATE utf8_bin NOT NULL,
  `field3` char(15) COLLATE utf8_bin NOT NULL,
  ... a lot other field2 ...
 PRIMARY KEY (`field1`,`field2`),
 KEY `index_1` (`field3`,`field2`,`field1`) USING BTREE,
 ... other indexes ...
) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin    ROW_FORMAT=TOKUDB_LZMA;

Tables are multiple times a day under heavy load for like an hour.

TokuDB: Index cardinality drops to zero

0 Answers0