# Cardinality drops to 0 (zero) under write load in Version 5.7.19-17

**URL:** <https://forums.percona.com/t/cardinality-drops-to-0-zero-under-write-load-in-version-5-7-19-17/5995>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [November 9, 2017, 5:01am UTC](https://forums.percona.com/t/cardinality-drops-to-0-zero-under-write-load-in-version-5-7-19-17/5995 "2017-11-09T05:01:20Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sebastian\_Wjertzoch](https://avatars.discourse-cdn.com/v4/letter/s/6bbea6/32.png) [@Sebastian\_Wjertzoch](https://forums.percona.com/u/Sebastian_Wjertzoch)\
**Post date:** [November 9, 2017, 5:01am UTC](https://forums.percona.com/t/cardinality-drops-to-0-zero-under-write-load-in-version-5-7-19-17/5995/1 "2017-11-09T05:01:20Z")

</div>

Hi  
Over the last month we had problems with some Indexes in our PerconaDB (v. 5.7.17). From time to time, the cardinality of all indexes on 2 table are just dropping.  
We use

> [@](#):
>
> ANALYZE table asins;

to fix this.

Table 1:

- around 800k rows
- created with

> [@](#):
>
> CREATE TABLE `foooo1` (  
> `val1` char(2) COLLATE utf8\_bin NOT NULL,  
> `val2` char(20) COLLATE utf8\_bin NOT NULL,  
> `val3` char(20) COLLATE utf8\_bin NOT NULL,  
> `val4` mediumint(8) unsigned NOT NULL,  
> PRIMARY KEY (`val1`,`val2`),  
> KEY `reverse` (`val1`,`val3`)  
> ) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin ROW\_FORMAT=TOKUDB\_LZMA;

Table 2:

- around 400k rows
- created with

> [@](#):
>
> CREATE TABLE `foooo2` (  
> `val1` char(2) COLLATE utf8\_bin NOT NULL,  
> `val2` char(20) COLLATE utf8\_bin NOT NULL,  
> `val3` bigint(20) NOT NULL,  
> `val4` bigint(20) DEFAULT NULL,  
> `val5` text COLLATE utf8\_bin,  
> […multiple text columns like this…]  
> PRIMARY KEY (`val1`,`val2`)  
> ) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8\_bin ROW\_FORMAT=TOKUDB\_LZMA;

Logs:  
error.log and general.log/mysql.log does not contain anything suspicious at the specific drop times

This problem is hounting us for weeks now. First we observe an heavy performance drop in the database. After checking the tables, we found the missing cardinalities. This ends up in full table scans for every query.

Attempt 1:  
After reading a lot about problems like this in the current version, we decide to upgrade the server to v. 5.7.19-17.  
But the problems is still there.

Attempt 2:  
Disable all auto reanalyze to test if it kind of a failed background analyze.

> [@](#):
>
> ‘tokudb\_analyze\_delete\_fraction’, ‘1.000000’  
> ‘tokudb\_analyze\_in\_background’, ‘OFF’  
> ‘tokudb\_analyze\_mode’, ‘TOKUDB\_ANALYZE\_STANDARD’  
> ‘tokudb\_analyze\_throttle’, ‘0’  
> ‘tokudb\_analyze\_time’, ‘0’

But the problems is still there.

Attempt 3:  
Disable write on table ‘foooo2’ to check if it just under load  
But the problems is still there on ‘foooo1’, but table ‘foooo2’ is working perfectly fine.  
So the problem just appears under write load.

If some one could provide additional informations to track that down, I would be very grateful.