We run an Percona server with the newest version(5.7.19-17). After a while the cardinality of multiple tables drop to zero. A new created index also have an cardinality of 0 if the existing are zero. I can repair this by do
set session tokudb_analyze_mode = TOKUDB_ANALYZE_RECOUNT_ROWS; ANALYZE table myTable;
ALTER TABLE `table_name` ENGINE=TokuDB;
After some tests I found the row count droping over time. As far as I understand the, this influences also the cardinality. The table contains around 800.000 entries and is written nearly the whole day.
CREATE TABLE `table_name` ( `val1` char(2) COLLATE utf8_bin NOT NULL, `val2` char(15) COLLATE utf8_bin NOT NULL, `val3` char(15) COLLATE utf8_bin NOT NULL, `val4` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`val1`,`val2`), KEY `reverse_key` (`val1`,`val3`) ) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=TOKUDB_LZMA;
Mostly I write with
REPLACE INTO and
INSERT ON DUPLICATE KEY UPDATE into this table and update nearly 100% of the data per day. Both insert methods are tested with the same behavior.
I start an simple script which just print the calculated row count over time
SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'shema_name' AND TABLE_NAME = 'table_name';
You can see the row count is decreasing very fast.
Can anyone explain to me where this behavior comes from and I can handle it?