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;
or
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?
TABLE_ROWS
can be as much as a factor of two off.REPLACE
-- which doesDELETE
+INSERT
? Consider IODKU.SELECT COUNT(*) FROM tablename
to see how many rows there really are. It is the correct value, and likely to be different than any of the values you get the query we are discussing.