|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Добрый день. Такая проблема, есть БД (размер около 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 | |
+-----------------+---------------------+------+-----+---------+-------+
Неактивен
analyze table
делали?
Если делали и не помогает, то, боюсь, что придется пользоваться хинтами -
ignore index, use index, force index.
Неактивен
Скорее всего, действительно сбивается статистика индекса. Проблему с репликацией можно обойти, если сделать binlog_format='row', но в общем случае действительно analyze должен помочь.
Как вариант — бэкапы можно делать не снэпшотом vm, а через xtrabackup.
Неактивен
deadka написал:
analyze table
делали?
Если делали и не помогает, то, боюсь, что придется пользоваться хинтами -
ignore index, use index, force index.
попробую ещё раз, как ошибка появиться. На сколько я помню, после analyze table запросы правильный индекс не начинали использовать.
ignore index, use index, force index - эти комбинации ведь в теле запросов должны быть? То есть нужно модифицировать приложение, чтобы в таких запросах использовался определенный индекс?
Неактивен
paulus написал:
Скорее всего, действительно сбивается статистика индекса. Проблему с репликацией можно обойти, если сделать binlog_format='row', но в общем случае действительно analyze должен помочь.
Как вариант — бэкапы можно делать не снэпшотом vm, а через xtrabackup.
Спасибо, попробую, В 10.1.25-MariaDB вроде должна быть поддержка настройки формата бин логов.
Неактивен
Проблема повторилась.
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 без вмешательства в код приложения?
Неактивен
Вылечено сменой формата бин логов на тип ROW 
Неактивен
Страниц: 1
