Emergency

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.
Working...
X