Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19561

Cardinality error in InnoDB slave statistics

    XMLWordPrintable

Details

    Description

      Default configuration (engine-dependent) statistics do not update correctly on slave for InnoDB.

      CREATE TABLE `simple` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        `val` int(10) unsigned DEFAULT NULL,
        `blah` varchar(511) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `simple_modified` (`modified`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      

      Having this table in a 3 node MariaDB 10.3.14 Galera cluster, and a slave (configurations attached) with the following (default) stats configuration on all servers:

      +--------------------------------------+-------------+
      | Variable_name                        | Value       |
      +--------------------------------------+-------------+
      | innodb_stats_auto_recalc             | ON          |
      | innodb_stats_include_delete_marked   | OFF         |
      | innodb_stats_method                  | nulls_equal |
      | innodb_stats_modified_counter        | 0           |
      | innodb_stats_on_metadata             | OFF         |
      | innodb_stats_persistent              | ON          |
      | innodb_stats_persistent_sample_pages | 20          |
      | innodb_stats_sample_pages            | 8           |
      | innodb_stats_traditional             | ON          |
      | innodb_stats_transient_sample_pages  | 8           |
      +--------------------------------------+-------------+
      

      as rows are inserted all statistics update correctly except for non-unique key stats on the slave, which incorrectly show unique key cardinality.

      Galera node / master:

      MariaDB [test]> show index from simple;
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | simple |          0 | PRIMARY         |            1 | id          | A         |       14391 |     NULL | NULL   |      | BTREE      |         |               |
      | simple |          1 | simple_modified |            1 | modified    | A         |        2055 |     NULL | NULL   |      | BTREE      |         |               |
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      2 rows in set (0.000 sec)
       
      MariaDB [test]> select count(*) from simple;
      +----------+
      | count(*) |
      +----------+
      |    14722 |
      +----------+
      1 row in set (0.005 sec)
       
      MariaDB [test]> select count(distinct modified) from simple;
      +--------------------------+
      | count(distinct modified) |
      +--------------------------+
      |                      362 |
      +--------------------------+
      1 row in set (0.001 sec)
      

      (this error is statistical & not relevant)

      Slave:

      MariaDB [test]> show index from simple;
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | simple |          0 | PRIMARY         |            1 | id          | A         |       14383 |     NULL | NULL   |      | BTREE      |         |               |
      | simple |          1 | simple_modified |            1 | modified    | A         |       14383 |     NULL | NULL   |      | BTREE      |         |               |
      +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      2 rows in set (0.000 sec)
       
      MariaDB [test]> select count(*) from simple;
      +----------+
      | count(*) |
      +----------+
      |    14722 |
      +----------+
      1 row in set (0.005 sec)
       
      MariaDB [test]> select count(distinct modified) from simple;
      +--------------------------+
      | count(distinct modified) |
      +--------------------------+
      |                      362 |
      +--------------------------+
      1 row in set (0.001 sec)
      

      Attachments

        1. 28207_G0_global_variables.out
          666 kB
          Juan
        2. 28207_G0_server.cnf
          1 kB
          Juan
        3. 28207_slave_global_variables.out
          461 kB
          Juan
        4. 28207_Slave_server.cnf
          0.2 kB
          Juan

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.