Not the answer you need?
Register and ask your own question!

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

Sebastian WjertzochSebastian Wjertzoch EntrantInactive User Role Beginner
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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.