1

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';

enter image description here

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?

11
  • 3%/hour does not seem like much, especially considering that TABLE_ROWS can be as much as a factor of two off.
    – Rick James
    Nov 13 '17 at 18:24
  • Did you really want REPLACE -- which does DELETE + INSERT? Consider IODKU.
    – Rick James
    Nov 13 '17 at 18:25
  • @RickJames I just attached a longer log. I see your point. 3% per houre is not that mutch but it leads to full table scans after 2 days because the row count and so also the cardinality of all indexes for this table is zero. This could not be the normal behavior. Did I missunderstod something here?
    – Sebastian
    Nov 14 '17 at 8:22
  • @RickJames checked to change REPLACE to IODKU but it shows the same behavior. Table is down to 600k now.
    – Sebastian
    Nov 14 '17 at 9:13
  • Do 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.
    – Rick James
    Nov 14 '17 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.