[#MDEV-18226] Index cardinality is not synced across Galera cluster and stays at 0

[MDEV-18226] Index cardinality is not synced across Galera cluster and stays at 0 Created: 2019-01-14 Updated: 2019-03-05 Resolved: 2019-01-31
Status:	Closed
Project:	MariaDB Server
Component/s:	Galera, Storage Engine - InnoDB
Affects Version/s:	10.2
Fix Version/s:	N/A

Type:

Bug

Priority:

Major

Reporter:

Niels Hendriks

Assignee:

Jan Lindström (Inactive)

Resolution:

Not a Bug

Votes:

Labels:

cardinality, galera, index

Environment:

Debian 8 amd64

mariadb-server-10.2:
Installed: 10.2.21+maria~jessie
Candidate: 10.2.21+maria~jessie
Version table:

- - 10.2.21+maria~jessie 0
    500 http://ams2.mirrors.digitalocean.com/mariadb/repo/10.2/debian/ jessie/main amd64 Packages
    100 /var/lib/dpkg/status

Attachments:

data.sql

data2.sql

galera_index_cardinality.cnf

galera_index_cardinality.test

mysql_settings.txt

Issue Links:

Problem/Incident
is caused by	~~MDEV-18396~~	InnoDB table/index stats are incorrec...	Closed

Description

Hello,

We have several MariaDB galera clusters on Debian 8. On all these clusters, we've noticed some pretty significant performance issues that appear to be related to the index cardinality being incorrect.

I have managed to reproduce this issue. For this I have attached "data.sql" which has the table schema where I generated dummy data to reproduce it.

Steps are like this:
Setup:
3 MariaDB 10.2 Galera nodes

NODE 1:
1. Log in to MySQL and `create database cardtest02;`
2. Import the attached `data.sql` file into `cardtest02`: `mysql --defaults-file=/etc/mysql/mysql-root.cnf cardtest02 < ~/data.sql`
3. Verify index cardinality:

use cardtest02;

show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |         301 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

As you can see, in this case, on node 1, Cardinality is correct (301). However, if we now check this on another , for example node 2, we see the following:

NODE 2:

MariaDB [cardtest02]> show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

Because of this issue, indexes aren't properly being used and the performance is very bad, especially on the larger tables.

In addition, I have also noticed the `information_schema.tables` table being incorrect:

NODE 1:

MariaDB [(none)]> select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl' \G;

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: cardtest02

     TABLE_NAME: cardtest_tbl

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Compressed

     TABLE_ROWS: 301

 AVG_ROW_LENGTH: 81

    DATA_LENGTH: 24576

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 0

 AUTO_INCREMENT: 1324

    CREATE_TIME: 2019-01-14 12:37:20

    UPDATE_TIME: 2019-01-14 12:37:20

     CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

       CHECKSUM: NULL

 CREATE_OPTIONS: row_format=COMPRESSED

  TABLE_COMMENT: assessment table retrofitted from MySQL

1 row in set (0.01 sec)

NODE 2:

MariaDB [cardtest02]> select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl' \G;

*************************** 1. row ***************************

  TABLE_CATALOG: def

   TABLE_SCHEMA: cardtest02

     TABLE_NAME: cardtest_tbl

     TABLE_TYPE: BASE TABLE

         ENGINE: InnoDB

        VERSION: 10

     ROW_FORMAT: Compressed

     TABLE_ROWS: 0

 AVG_ROW_LENGTH: 0

    DATA_LENGTH: 8192

MAX_DATA_LENGTH: 0

   INDEX_LENGTH: 0

      DATA_FREE: 0

 AUTO_INCREMENT: 1324

    CREATE_TIME: 2019-01-14 12:37:20

    UPDATE_TIME: 2019-01-14 12:37:20

     CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

       CHECKSUM: NULL

 CREATE_OPTIONS: row_format=COMPRESSED

  TABLE_COMMENT: assessment table retrofitted from MySQL

1 row in set (0.00 sec)

A temporary workaround to fix this is running an `analyze table cardtest_tbl`; on one of the nodes and it is synced across all of them automatically after that, but it is of course only temporary. Even if I add more rows later (with the same .sql file for example when I only change IDs and skip the drop table if exists) the cardinality is only ever updated on the node where I added the rows and not on node 2 / 3.

I also tried to see what happens if I add new rows on node 2 instead after the initial dump, and it does increase the cardinality there with the amount of rows inserted on node 2, but it never reflects anywhere near the actual amount of rows.
Interesting to note is the following:
1. Import data.sql on node 1, which recreates an empty table and adds rows, bringing cardinality to 301 in node 1 and 0 on node 2/3
2. Run analyze table on on any node, resulting in a synchronisation of the cardinality; all nodes now have 301
3. Running single queries on node 1 and/node 2 do increase cardinality correctly by 1:

NODE 1:

INSERT INTO `cardtest_tbl` VALUES (3000,1,'','',1,1466430455,1471454450,NULL,10.00000,1,NULL,'','',NULL,NULL,1,NULL,NULL);

NODE 2:

INSERT INTO `cardtest_tbl` VALUES (3001,1,'','',1,1466430455,1471454450,NULL,10.00000,1,NULL,'','',NULL,NULL,1,NULL,NULL);

After this, cardinality is 303 on all nodes.
3. Import data2.sql (see attachment) through node 1, bringing cardinality to 604 on node 1, and back to 301 on node 2

I have also attached mysql_settings.txt which contains the output of `show variables`, which may be helpful.

Does anyone have any idea of how to fix / prevent this? Is it a bug or intended behaviour we're unaware of? Running analyze table on all tables is not something we like to do, especially as it is locking on the entire cluster and usually kills the entire cluster (which is another issue entirely I believe we can work around with by using wsrep_desync and RSU options)

Cheers,
Niels

Comments

Comment by Niels Hendriks [ 2019-01-15 ]

I have some more information to further narrow down the cause of this:

It appears to be related to the following piece of code that is part of the mysqldump (present by default in mysqldump)

/*!40000 ALTER TABLE `cardtest_tbl` DISABLE KEYS */;

...

...

/*!40000 ALTER TABLE `cardtest_tbl` ENABLE KEYS */;

So it appears that when the keys are disabled and inserts happen, the cardinality is correctly updated (even though I noticed a few seconds delay, but that doesn't really matter). However, afterwards, once the ENABLE KEYS command happens, all this cardinality is lost.In the case of importing these mysqldump files, this means cardinality stays at 0 because the table is empty when we start adding data to it.

One strange thing that is different when I try this manually: with manual commands the cardinality is also lost on the node where we execute commands; during the sqldump it is kept on the node where the commands are executed, but lost on node 2/3 in the cluster. Anyways, here some more minimal reproduction steps using the db scheme from my attachment earlier:

MariaDB [cardtest02]> analyze table cardtest_tbl;

+-------------------------+---------+----------+----------+

| Table                   | Op      | Msg_type | Msg_text |

+-------------------------+---------+----------+----------+

| cardtest02.cardtest_tbl | analyze | status   | OK       |

+-------------------------+---------+----------+----------+

1 row in set (0.01 sec)

MariaDB [cardtest02]> show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |         304 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

MariaDB [cardtest02]> ALTER TABLE `cardtest_tbl` DISABLE KEYS;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [cardtest02]> INSERT INTO `cardtest_tbl` VALUES (960,1,'','',1,1466430455,1471454450,NULL,10.00000,1,NULL,'','',NULL,NULL,1,NULL,NULL);

Query OK, 1 row affected (0.00 sec)

MariaDB [cardtest02]> INSERT INTO `cardtest_tbl` VALUES (961,1,'','',1,1466430455,1471454450,NULL,10.00000,1,NULL,'','',NULL,NULL,1,NULL,NULL);

Query OK, 1 row affected (0.00 sec)

MariaDB [cardtest02]> INSERT INTO `cardtest_tbl` VALUES (962,1,'','',1,1466430455,1471454450,NULL,10.00000,1,NULL,'','',NULL,NULL,1,NULL,NULL);

Query OK, 1 row affected (0.00 sec)

MariaDB [cardtest02]> show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |         307 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

MariaDB [cardtest02]> ALTER TABLE `cardtest_tbl` ENABLE KEYS;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [cardtest02]> show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |         304 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

MariaDB [cardtest02]> analyze table cardtest_tbl;

+-------------------------+---------+----------+----------+

| Table                   | Op      | Msg_type | Msg_text |

+-------------------------+---------+----------+----------+

| cardtest02.cardtest_tbl | analyze | status   | OK       |

+-------------------------+---------+----------+----------+

1 row in set (0.00 sec)

MariaDB [cardtest02]> show indexes from cardtest_tbl;

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| cardtest_tbl |          0 | PRIMARY  |            1 | id          | A         |         307 |     NULL | NULL   |      | BTREE      |         |               |

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

Another thing you might notice is the warning when doing DISABLE/ENABLE keys, this shows the following:

MariaDB [cardtest02]> ALTER TABLE `cardtest_tbl` ENABLE KEYS;

Query OK, 0 rows affected, 1 warning (0.02 sec)

MariaDB [cardtest02]> show warnings;

+-------+------+-----------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                 |

+-------+------+-----------------------------------------------------------------------------------------+

| Note  | 1031 | Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option |

+-------+------+------------------

Mysqldump adds this option automatically, but I find it very strange that an option which InnoDB "doesn't have", does appear to have an effect. Why is this?

Comment by Niels Hendriks [ 2019-01-25 ]

Hi,

Is there anything we can do to give this more attention as this is a very big issue for our users? We've had some reports that the workaround that we thought worked (removing ENABLE/DISABLE keys from mysqldump) doesn't work correctly either so at the moment we have no 100% workaround for this either.

Comment by Jan Lindström (Inactive) [ 2019-01-28 ]

Tested with both MariaDB 10.1.38 and MariaDB 10.2.22 using mtr tests attached. I do not see any difference on index cardinality between nodes or any difference on table info. ENABLE/DISABLE keys should not affect primary key on InnoDB, but indeed cardinality is lost if I add those alters there.

jan@jan-laptop-asus:~/mysql/10.2-sst/mysql-test$ ./mtr galera_index_cardinality

Logging: ./mtr  galera_index_cardinality

vardir: /home/jan/mysql/10.2-sst/mysql-test/var

Checking leftover processes...

Removing old var directory...

Creating var directory '/home/jan/mysql/10.2-sst/mysql-test/var'...

Checking supported features...

MariaDB Version 10.2.22-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

connection node_1;

create database cardtest02;

use cardtest02;

DROP TABLE IF EXISTS `cardtest_tbl`;

Warnings:

Note	1051	Unknown table 'cardtest02.cardtest_tbl'

CREATE TABLE `cardtest_tbl` (

`id` bigint(18) NOT NULL AUTO_INCREMENT,

`course` bigint(18) NOT NULL,

`name` varchar(200) DEFAULT NULL,

`intro` longtext DEFAULT NULL,

`introformat` smallint(4) DEFAULT 0,

`timecreated` bigint(18) NOT NULL,

`timemodified` bigint(18) NOT NULL,

`grademethod` varchar(255) DEFAULT NULL,

`grade` decimal(10,5) DEFAULT NULL,

`updategradeoncompletion` tinyint(2) DEFAULT 1,

`competencyoncompletion` varchar(255) DEFAULT NULL,

`evaluationmethod` varchar(255) DEFAULT NULL,

`completionmethod` varchar(255) DEFAULT NULL,

`alloweddates` varchar(1024) DEFAULT NULL,

`allowedslots` varchar(1024) DEFAULT NULL,

`permissions` smallint(4) DEFAULT NULL,

`notifications` varchar(1024) DEFAULT NULL,

`type` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';

# Inserting data...

use cardtest02;

show indexes from cardtest_tbl;

Table	cardtest_tbl

Non_unique	0

Key_name	PRIMARY

Seq_in_index	1

Column_name	id

Collation	A

Cardinality	301

Sub_part	NULL

Packed	NULL

Null

Index_type	BTREE

Comment

Index_comment

select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';

TABLE_CATALOG	def

TABLE_SCHEMA	cardtest02

TABLE_NAME	cardtest_tbl

TABLE_TYPE	BASE TABLE

ENGINE	InnoDB

VERSION	10

ROW_FORMAT	Compressed

TABLE_ROWS	301

AVG_ROW_LENGTH	27

DATA_LENGTH	8192

MAX_DATA_LENGTH	0

INDEX_LENGTH	0

DATA_FREE	0

AUTO_INCREMENT	1324

CREATE_TIME	2019-01-28 14:51:11

UPDATE_TIME	2019-01-28 14:51:11

CHECK_TIME	NULL

TABLE_COLLATION	utf8_general_ci

CHECKSUM	NULL

CREATE_OPTIONS	row_format=COMPRESSED

TABLE_COMMENT	assessment table retrofitted from MySQL

select count(*) from cardtest_tbl;

count(*)	301

connection node_2;

set session wsrep_sync_wait=15;

use cardtest02;

show indexes from cardtest_tbl;

Table	cardtest_tbl

Non_unique	0

Key_name	PRIMARY

Seq_in_index	1

Column_name	id

Collation	A

Cardinality	301

Sub_part	NULL

Packed	NULL

Null

Index_type	BTREE

Comment

Index_comment

select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';

TABLE_CATALOG	def

TABLE_SCHEMA	cardtest02

TABLE_NAME	cardtest_tbl

TABLE_TYPE	BASE TABLE

ENGINE	InnoDB

VERSION	10

ROW_FORMAT	Compressed

TABLE_ROWS	301

AVG_ROW_LENGTH	27

DATA_LENGTH	8192

MAX_DATA_LENGTH	0

INDEX_LENGTH	0

DATA_FREE	0

AUTO_INCREMENT	1324

CREATE_TIME	2019-01-28 14:51:11

UPDATE_TIME	2019-01-28 14:51:11

CHECK_TIME	NULL

TABLE_COLLATION	utf8_general_ci

CHECKSUM	NULL

CREATE_OPTIONS	row_format=COMPRESSED

TABLE_COMMENT	assessment table retrofitted from MySQL

select count(*) from cardtest_tbl;

count(*)	301

connection node_1;

use test;

drop database cardtest02;

galera.galera_index_cardinality 'innodb' [ pass ]   2189

--------------------------------------------------------------------------

The servers were restarted 0 times

Spent 2.189 of 19 seconds executing testcases

Completed: All 1 tests were successful.

ENABLE/DISABLE keys added:

jan@jan-laptop-asus:~/mysql/10.2-sst/mysql-test$ ./mtr galera_index_cardinality

Logging: ./mtr  galera_index_cardinality

vardir: /home/jan/mysql/10.2-sst/mysql-test/var

Checking leftover processes...

Removing old var directory...

Creating var directory '/home/jan/mysql/10.2-sst/mysql-test/var'...

Checking supported features...

MariaDB Version 10.2.22-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

connection node_1;

create database cardtest02;

use cardtest02;

DROP TABLE IF EXISTS `cardtest_tbl`;

Warnings:

Note	1051	Unknown table 'cardtest02.cardtest_tbl'

CREATE TABLE `cardtest_tbl` (

`id` bigint(18) NOT NULL AUTO_INCREMENT,

`course` bigint(18) NOT NULL,

`name` varchar(200) DEFAULT NULL,

`intro` longtext DEFAULT NULL,

`introformat` smallint(4) DEFAULT 0,

`timecreated` bigint(18) NOT NULL,

`timemodified` bigint(18) NOT NULL,

`grademethod` varchar(255) DEFAULT NULL,

`grade` decimal(10,5) DEFAULT NULL,

`updategradeoncompletion` tinyint(2) DEFAULT 1,

`competencyoncompletion` varchar(255) DEFAULT NULL,

`evaluationmethod` varchar(255) DEFAULT NULL,

`completionmethod` varchar(255) DEFAULT NULL,

`alloweddates` varchar(1024) DEFAULT NULL,

`allowedslots` varchar(1024) DEFAULT NULL,

`permissions` smallint(4) DEFAULT NULL,

`notifications` varchar(1024) DEFAULT NULL,

`type` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';

ALTER TABLE `cardtest_tbl` DISABLE KEYS;

Warnings:

Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option

# Inserting data...

ALTER TABLE `cardtest_tbl` ENABLE KEYS;

Warnings:

Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option

use cardtest02;

show indexes from cardtest_tbl;

Table	cardtest_tbl

Non_unique	0

Key_name	PRIMARY

Seq_in_index	1

Column_name	id

Collation	A

Cardinality	4

Sub_part	NULL

Packed	NULL

Null

Index_type	BTREE

Comment

Index_comment

select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';

TABLE_CATALOG	def

TABLE_SCHEMA	cardtest02

TABLE_NAME	cardtest_tbl

TABLE_TYPE	BASE TABLE

ENGINE	InnoDB

VERSION	10

ROW_FORMAT	Compressed

TABLE_ROWS	4

AVG_ROW_LENGTH	2048

DATA_LENGTH	8192

MAX_DATA_LENGTH	0

INDEX_LENGTH	0

DATA_FREE	0

AUTO_INCREMENT	1324

CREATE_TIME	2019-01-28 15:01:37

UPDATE_TIME	2019-01-28 15:01:37

CHECK_TIME	NULL

TABLE_COLLATION	utf8_general_ci

CHECKSUM	NULL

CREATE_OPTIONS	row_format=COMPRESSED

TABLE_COMMENT	assessment table retrofitted from MySQL

select count(*) from cardtest_tbl;

count(*)	301

connection node_2;

set session wsrep_sync_wait=15;

use cardtest02;

show indexes from cardtest_tbl;

Table	cardtest_tbl

Non_unique	0

Key_name	PRIMARY

Seq_in_index	1

Column_name	id

Collation	A

Cardinality	0

Sub_part	NULL

Packed	NULL

Null

Index_type	BTREE

Comment

Index_comment

select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';

TABLE_CATALOG	def

TABLE_SCHEMA	cardtest02

TABLE_NAME	cardtest_tbl

TABLE_TYPE	BASE TABLE

ENGINE	InnoDB

VERSION	10

ROW_FORMAT	Compressed

TABLE_ROWS	0

AVG_ROW_LENGTH	0

DATA_LENGTH	8192

MAX_DATA_LENGTH	0

INDEX_LENGTH	0

DATA_FREE	0

AUTO_INCREMENT	1324

CREATE_TIME	2019-01-28 15:01:37

UPDATE_TIME	2019-01-28 15:01:37

CHECK_TIME	NULL

TABLE_COLLATION	utf8_general_ci

CHECKSUM	NULL

CREATE_OPTIONS	row_format=COMPRESSED

TABLE_COMMENT	assessment table retrofitted from MySQL

select count(*) from cardtest_tbl;

count(*)	301

connection node_1;

use test;

drop database cardtest02;

galera.galera_index_cardinality 'innodb' [ pass ]   2221

--------------------------------------------------------------------------

The servers were restarted 0 times

Spent 2.221 of 19 seconds executing testcases

Completed: All 1 tests were successful.

Note that both nodes have incorrect information about cardinality, table_rows and avg_row_length.

Comment by Jan Lindström (Inactive) [ 2019-01-28 ]

I would use --skip-disable-keys to disable mysqldump to adding those lines there.

Comment by Jan Lindström (Inactive) [ 2019-01-28 ]

This does not seem to be Galera bug see https://jira.mariadb.org/browse/MDEV-18396

Comment by Jan Lindström (Inactive) [ 2019-01-31 ]

Based on testing on linked issue, problem seems to be on expectations. InnoDB queues the statistics recalculation to a background task and exact moment when this background task is executed is not deterministic and naturally the exact moment when results from this task is published is not also deterministic. In my test case sleeping 5 seconds is not enough always, sleeping 10 seconds may help but not guaranteed.

Comment by Niels Hendriks [ 2019-01-31 ]

This it not the case for us with Galera. We see the cardinality stay at 0 forever until we run the analyze table command on node 2 + 3. It is only a few seconds as you say in the node where we run the mysqldump import. But node 2/3 are like that forever.

Comment by Elena Stepanova [ 2019-02-04 ]

See also ~~MDEV-18443~~.
And please note that my comments in ~~MDEV-18396~~ only address the test case presented there, not the issue described here.

Comment by Robert Kirscht [ 2019-03-05 ]

This ticket should be reopened.

Generated at Thu Feb 08 08:42:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-18226] Index cardinality is not synced across Galera cluster and stays at 0 Created: 2019-01-14 Updated: 2019-03-05 Resolved: 2019-01-31