Different Percona XtraDB nodes use different indexes for the same query

Question

We have three Percona XtraDB nodes arranged in a master-master replication topology.

One of our databases contains a heavily utilized table. This table has multiple indexes, as well as a few foreign keys and their corresponding indexes.

Output of SHOW CREATE TABLE:

 CREATE TABLE `malfunctions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint unsigned DEFAULT NULL,
  `merge_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status_id` bigint unsigned DEFAULT NULL,
  `insphire_workorder_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device_system_id` bigint unsigned DEFAULT NULL,
  `system_id` bigint unsigned DEFAULT NULL,
  `malfunction_analyze_id` bigint unsigned DEFAULT NULL,
  `service_company_id` bigint unsigned DEFAULT NULL,
  `security_company_id` bigint unsigned DEFAULT NULL,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` int unsigned DEFAULT NULL,
  `reporter` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `malfunctions_uuid_unique` (`uuid`),
  KEY `malfunctions_type_index` (`type`),
  KEY `malfunctions_device_system_id_foreign` (`device_system_id`),
  KEY `malfunctions_system_id_foreign` (`system_id`),
  KEY `malfunctions_malfunction_analyze_id_foreign` (`malfunction_analyze_id`),
  KEY `malfunctions_service_company_id_foreign` (`service_company_id`),
  KEY `malfunctions_security_company_id_foreign` (`security_company_id`),
  KEY `malfunctions_insphire_workorder_id_index` (`insphire_workorder_id`),
  KEY `malfunctions_merge_id_index` (`merge_id`),
  KEY `malfunctions_parent_id_index` (`parent_id`),
  KEY `malfunctions_priority_index` (`priority`),
  KEY `malfunctions_status_id_priority_deleted_at_index` (`status_id`,`priority`,`deleted_at`),
  KEY `malfunctions_status_id_foreign` (`status_id`),
  KEY `idx_malfunctions_query` (`deleted_at`,`priority`,`status_id`,`device_system_id`,`system_id`,`malfunction_analyze_id`),
  CONSTRAINT `malfunctions_device_system_id_foreign` FOREIGN KEY (`device_system_id`) REFERENCES `device_system` (`id`) ON DELETE SET NULL,
  CONSTRAINT `malfunctions_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `malfunction_statuses` (`id`),
  CONSTRAINT `malfunctions_malfunction_analyze_id_foreign` FOREIGN KEY (`malfunction_analyze_id`) REFERENCES `malfunction_analyzes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `malfunctions_security_company_id_foreign` FOREIGN KEY (`security_company_id`) REFERENCES `security_companies` (`id`) ON DELETE SET NULL,
  CONSTRAINT `malfunctions_service_company_id_foreign` FOREIGN KEY (`service_company_id`) REFERENCES `service_companies` (`id`) ON DELETE SET NULL,
  CONSTRAINT `malfunctions_system_id_foreign` FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The offending query:

SELECT
    *
FROM
    `malfunctions`
WHERE
    EXISTS (
        SELECT
            *
        FROM
            `malfunction_statuses`
        WHERE
            `malfunctions`.`status_id` = `malfunction_statuses`.`id`
            and(`active` = 1
                OR `name` = 'processing_in_chain')
            AND `malfunction_statuses`.`deleted_at` IS NULL)
        AND `priority` > 0
        AND `device_system_id` = 39967
        AND `system_id` = 4135
        AND `malfunction_analyze_id` = 6
        AND `id` <> 92250574
        AND `malfunctions`.`deleted_at` IS NULL;

When running the query on the second node (sql002), the query planner decides to use the index malfunctions_status_id_priority_deleted_at_index which results in a query execution time of 1 row in set (1.22 sec).

When running the query on the first or third node (sql001 and sql003), the query planner decides to use the index malfunctions_status_id_foreign which results in a query execution time of 1 row in set (28.76 sec).

We obviously want to use the index that the sql002 server automatically uses, and I'm aware I can force MySQL to use that index, but there are more cases the other 2 servers choose the wrong index.

What we have tried:

Executing ANALYZE TABLE malfunctions on all nodes (even though 1 should be enough if I'm not mistaken), but this does not change anything.

Re-initializing one of the nodes by letting them rejoin the cluster as a clean node which triggers a full state-transfer.

We now force our application to connect to the fast node, but that's obviously not the right decision.

Why does this happen, and how can we resolve it?

score 0 · Answer 1 · answered Sep 04 '23 at 10:54

Im not familiar with Percona XtraDB but with MySQL InnoDB, but there you can provide a index hints to selects. like USE INDEX (malfunctions_status_id_priority_deleted_at_index)

But I also see some possible performance improvements in the query. Just its difficult with the provided info to do that, because I dont know the selectivity of the attributes and the size of the tables. It should be this way that the attribute with the highest selectivity used to filter first. I think also filtering the malfunctions table should be your priority 1. Actually I dont see why index malfunctions_status_id_priority_deleted_at_index is better than malfunctions_status_id_foreign. Because null values for deleted_at are any way not in the index and I guess priority > 0 is like not null. Which means all values are included. Can you provide both explain plan for the queries on the servers.

One thing I would change is the order of the attributes, so that the optimizer is happy, but there is a lot of guess in the query below. I also change the "select * from" in the sub-query to "select 1 from" this is a common mistake.

SELECT
    *
FROM
    `malfunctions`
WHERE `malfunctions`.`device_system_id` = 39967
AND `malfunctions`.`system_id` = 4135
AND `malfunctions`.`malfunction_analyze_id` = 6
and  EXISTS (
        SELECT
            1
        FROM
            `malfunction_statuses`
        WHERE
            `malfunctions`.`status_id` = `malfunction_statuses`.`id`
        AND `malfunction_statuses`.`priority` > 0
        and (`malfunction_statuses`.`active` = 1
            OR `malfunction_statuses`.`name` = 'processing_in_chain')
        AND `malfunction_statuses`.`deleted_at` IS NULL
        )
AND `malfunctions`.`id` <> 92250574
AND `malfunctions`.`deleted_at` IS NULL;

score 0 · Answer 2 · answered Sep 07 '23 at 12:21

Delete secondary checksum for secondary master file. It's already linked so it's call isn't returning a necessary response from a pre authorization for an account login or parse request when accelerating through common inputs. It's delays and checks user for error but initiates no responses remove secondary reference for sub folder content instead batch call or token reference it as an API analytic this will assimilate similar echo with a force attribute freeing up your currently indisposed call back form init info response, a variable is not an element should be it's node recall. Ending null agreement moving forward. FFT.

Different Percona XtraDB nodes use different indexes for the same query

2 Answers2