SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 04.09.2020 12:37:14

Gelos
Участник
Зарегистрирован: 04.09.2020
Сообщений: 5

Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

Добрый день. Такая проблема, есть БД (размер около 1044 ГБ) забикса. Чтобы не бекапить регулярно террабайт данных, сделана реплика, которая регуляррно стопится и снимается снапшот всей ВМ где реплика крутится.

Проблема в том, что иногда, после некоторых бекапов, оптимизатор перестает использовать индекс item_discovery_1 на таблице item_discovery и почему то начинает использовать индекс PRIMARY который вообще тут не при чем.
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| item_discovery | 0 | PRIMARY | 1 | itemdiscoveryid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | item_discovery_1 | 1 | itemid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | item_discovery_1 | 2 | parent_itemid | A | 2189577 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | item_discovery_2 | 1 | parent_itemid | A | 2189577 | NULL | NULL | | BTREE | | |
+----------------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

сответственно запросы вида update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
начинают выполняться долго, и начинает расти лаг репликации.
Причем, если создать таблицу новую, по образу и подобию item_discovery перелить туда данные и переименовать новую в старую, а старую убрать, то проблема исчезает, оптимизатор тут же начинает использовать правильный индекс.
Воспроизвести проблему не получается, сколько я не ребутал субд и с остановой слейва, и без остановки, после старта все прекрасно работает, но иногда после ночных остановок происходит такая фигня с индексами и приходится пересоздавать таблицу.
Она хоть и не большая, всего 300мб, но хотелось бы найти корневую причину, почему оптимизатор вдруг именно на этой таблице иногда сбивается.
сама таблица такая
SHOW COLUMNS FROM zabbix_nohist.item_discovery;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| itemdiscoveryid | bigint(20) unsigned | NO | PRI | NULL | |
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| parent_itemid | bigint(20) unsigned | NO | MUL | NULL | |
| key_ | varchar(255) | NO | | | |
| lastcheck | int(11) | NO | | 0 | |
| ts_delete | int(11) | NO | | 0 | |
+-----------------+---------------------+------+-----+---------+-------+

Неактивен

 

#2 04.09.2020 12:51:18

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2310

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

analyze table
делали?

Если делали и не помогает, то, боюсь, что придется пользоваться хинтами -
ignore index, use index, force index.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 04.09.2020 13:02:37

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6727

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

Скорее всего, действительно сбивается статистика индекса. Проблему с репликацией можно обойти, если сделать binlog_format='row', но в общем случае действительно analyze должен помочь.

Как вариант — бэкапы можно делать не снэпшотом vm, а через xtrabackup.

Неактивен

 

#4 07.09.2020 10:41:19

Gelos
Участник
Зарегистрирован: 04.09.2020
Сообщений: 5

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

deadka написал:

analyze table
делали?

Если делали и не помогает, то, боюсь, что придется пользоваться хинтами -
ignore index, use index, force index.

попробую ещё раз, как ошибка появиться. На сколько я помню, после analyze table  запросы правильный индекс не начинали использовать.

ignore index, use index, force index - эти комбинации ведь в теле запросов должны быть? То есть нужно модифицировать приложение, чтобы в таких запросах использовался определенный индекс?

Неактивен

 

#5 07.09.2020 10:49:13

Gelos
Участник
Зарегистрирован: 04.09.2020
Сообщений: 5

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

paulus написал:

Скорее всего, действительно сбивается статистика индекса. Проблему с репликацией можно обойти, если сделать binlog_format='row', но в общем случае действительно analyze должен помочь.

Как вариант — бэкапы можно делать не снэпшотом vm, а через xtrabackup.

Спасибо, попробую, В 10.1.25-MariaDB вроде должна быть поддержка настройки формата бин логов.

Неактивен

 

#6 08.09.2020 10:49:44

Gelos
Участник
Зарегистрирован: 04.09.2020
Сообщений: 5

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

Проблема повторилась.
analyze table не помог, пробовал и при работающем слейве, и погашенном и после рестарта. Все равно индекс используется PRIMARY
analyze update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+
| id   | select_type | table          | type      | possible_keys     | key        | key_len | ref  | rows | r_rows        | filtered | r_filtered | Extra       |
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+
|    1 | SIMPLE      | item_discovery | index | item_discovery_1 | PRIMARY | 8       | NULL |    1   | 2960155.00 |   100.00 |       0.00 | Using where |
+------+-------------+----------------+-------+------------------+---------+---------+------+------+------------+----------+------------+-------------+

после пересоздания:
DROP TABLE item_discovery_backup;
CREATE TABLE new_item_discovery LIKE item_discovery;
INSERT INTO new_item_discovery SELECT * FROM item_discovery;
RENAME TABLE item_discovery TO item_discovery_backup;
RENAME TABLE new_item_discovery TO item_discovery;

все нормально становится:
analyze update item_discovery set lastcheck=1596021740 where itemid between 3304480 and 3304487;
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+
| id   | select_type | table          | type  | possible_keys    | key              | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+
|    1 | SIMPLE      | item_discovery | range | item_discovery_1 | item_discovery_1 | 8       | NULL |    8 |   8.00 |   100.00 |     100.00 | Using where |
+------+-------------+----------------+-------+------------------+------------------+---------+------+------+--------+----------+------------+-------------+


SHOW CREATE TABLE item_discovery\G
*************************** 1. row ***************************
       Table: item_discovery
Create Table: CREATE TABLE `item_discovery` (
  `itemdiscoveryid` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `parent_itemid` bigint(20) unsigned NOT NULL,
  `key_` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lastcheck` int(11) NOT NULL DEFAULT '0',
  `ts_delete` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemdiscoveryid`),
  UNIQUE KEY `item_discovery_1` (`itemid`,`parent_itemid`),
  KEY `item_discovery_2` (`parent_itemid`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

Можно как то использовать force index без вмешательства в код приложения?

Неактивен

 

#7 17.09.2020 09:26:14

Gelos
Участник
Зарегистрирован: 04.09.2020
Сообщений: 5

Re: Mysql оптимизатор выбирает некорректный индекс после рестарта (TokuDB)

Вылечено сменой формата бин логов на тип ROW smile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson