Как устранить тормоза таблицы после большого delete?

Question

[vasdor]

Здравствуйте!

Имеется таблица со следующей структурой (ненужные поля я не указал):

CREATE TABLE `logs` (
  `INSERT_DATE` datetime DEFAULT NULL,
  `DATA` text NOT NULL,
  KEY `INSERT_DATE` (`INSERT_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

В неё вставляются данные, около 1 кб на строчку. INSERT_DATE заполняется через «now()»
В день новых данных поступает около 15 млн записей. Утром следующего дня забираются данные за вчерашний день в другое приложение по сети

SELECT INSERT_DATE, DATA from logs WHERE INSERT_DATE >= '2015-04-16' AND INSERT_DATE < '2015-04-17'

затем выполняется запрос вида, данные удаляются

DELETE FROM logs WHERE INSERT_DATE >= '2015-04-16' AND INSERT_DATE < '2015-04-17'

Без этого запроса всё прекрасно и если его не выполнять, табличка продолжает быстро вставлять около 200 запросов в секунду и спокойно дорастает до 50 млн, например.

Однако, если данные за предыдущий день удалить, то вставка начинает

Но после этого запроса вставка начинает тормозить, соединения накапливаются, приходится делать
optimize table logs;
который выполняется длительное время, таблица простаивает.

Есть мысль что проблема в фрагментации и перестроении индекса после многих вставок/удаления.
Куда можно смотреть для решения проблемы?
Заранее спасибо!

Answer 1

[Андрей]

Partitions, быстро и просто. Каждый день по скрипту можно добавлять partition на следующий день, а когда нужно удалить какую-нить таблицу - просто дропаешь partition, и остальные данные остаются. Не нужно ни переименовывать таблицы, ни удалять старые. Таблица остается жива и доступна, нет моментов, когда она может быть не доступна (напр., в момент переименования).
Данные по partition-ам распределяются как раз по значению ключа, поэтому проблем не возникнет.

Comments

[vasdor]

Спасибо, Андрей, это решение звучит интересно, почитаю про partitions

[Андрей]

У меня так реализовано на Zabbix. Когда места не хватает - просто дропаю партишн со старыми данными, и дело в шляпе. При чём, это происходит незаметно для самого ПО. И выборку, кстати, тоже можно по конкретным партишнам делать, что тоже несколько ускоряет процесс

[vasdor]

Андрей, подскажите, пожалуйста — где я неправ, делаю таблицу:
CREATE TABLE `logs` (
`INSERT_DATE` datetime DEFAULT NULL,
`DATA` text NOT NULL,
KEY `INSERT_DATE` (`INSERT_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (DAYOFWEEK(insert_date))
(PARTITION p01 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p02 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p03 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION p04 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p05 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p06 VALUES LESS THAN (7) ENGINE = InnoDB,
PARTITION p07 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION pmaxval VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Во-первых, часть с партициями закомментирована.
Когда делаю EXPLAIN PARTITION с выборкой по дате, показывает, что запрос включит в себя все партиции.

Опцию innodb_file_per_table включил, базу перезалил, таблицы стали в разных файлах

Куда можно смотреть?

Куда смотреть?

[Андрей]

/*50100 */ - это в MySQL не комментарий, это условный блок, выполняющийся, если версия mysql >= 5.0.1

Повторил эту же таблицу у себя - всё нормально:

mysql> explain partitions select * from `logs` where `INSERT_DATE` = '2015-03-13'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: logs
partitions: p06
type: ref
possible_keys: INSERT_DATE
key: INSERT_DATE
key_len: 9
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions select * from `logs` where `INSERT_DATE` = '2015-03-15'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: logs
partitions: p01
type: ref
possible_keys: INSERT_DATE
key: INSERT_DATE
key_len: 9
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

Вот если я делаю выборку по `DATA`, то конечно используются все партиции

[vasdor]

Всё верно, такой запрос срабатывает, но в реальной выборке он вернёт только те данные, где `INSERT_DATE` = '2015-03-15 00:00:00'
т.к. в INSERT_DATE находится время, а не только дата

а нужно выбрать данные за все сутки
SELECT INSERT_DATE, DATA from logs WHERE INSERT_DATE >= '2015-04-16' AND INSERT_DATE < '2015-04-17'
вот этот запрос включает все партиции

[Андрей]

Да, действительно. Но можно взять другую модель, которая не должна повлиять на суть таблицы:

CREATE TABLE `logs2` (
`INSERT_DATE` timestamp DEFAULT NULL DEFAULT CURRENT_TIMESTAMP,
`DATA` text NOT NULL,
KEY `INSERT_DATE` (`INSERT_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (unix_timestamp(insert_date))
(PARTITION p20150313 VALUES LESS THAN (unix_timestamp('2015-03-14 00:00:00')) ENGINE = InnoDB,
PARTITION p20150314 VALUES LESS THAN (unix_timestamp('2015-03-15 00:00:00')) ENGINE = InnoDB,
PARTITION p20150315 VALUES LESS THAN (unix_timestamp('2015-03-16 00:00:00')) ENGINE = InnoDB,
PARTITION p20150316 VALUES LESS THAN (unix_timestamp('2015-03-17 00:00:00')) ENGINE = InnoDB,
PARTITION pmaxval VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Здесь всё работает. Результат:
mysql> EXPLAIN PARTITIONS SELECT INSERT_DATE, DATA from logs2 WHERE INSERT_DATE >= '2015-03-15' AND INSERT_DATE < '2015-03-16'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: logs2
partitions: p20150315
type: range
possible_keys: INSERT_DATE
key: INSERT_DATE
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0,00 sec)

Или другой вариант, который теперь уже даже проще:
mysql> SELECT * FROM `logs2` PARTITION (p20150315);
+---------------------+------+
| INSERT_DATE | DATA |
+---------------------+------+
| 2015-03-15 00:00:00 | bla1 |
| 2015-03-15 03:00:00 | bla2 |
+---------------------+------+
2 rows in set (0,00 sec)

Ну и в конце:
ALTER TABLE `logs2` DROP PARTITION `p20150313`;
ALTER TABLE `logs2` ADD PARTITION (PARTITION p20150317 VALUES LESS THAN (unix_timestamp('2015-03-18 00:00:00')));

только pmaxval мешает добавлять очередные дни. Впрочем, можно или убирать/добавлять его каждый раз для подстраховки, или если скрипт будет четко выполняться каждый день, то и вообще убрать pmaxval за ненадобностью. Или накрайняк создать их на неделю вперед и не париться

[vasdor]

Да, спасибо, так можно.
Хотелось бы конечно, создать партиции по дням недели или месяца, чтобы не удалять/создавать, а только обслуживать. Поэкспериментирую с этим, ещё раз спасибо!

Answer 2

[Леонид Сысолетин]

Перестроение индексов, медленный диск.
Смотреть в сторону переименования старой таблицы, создания пустой новой для новых записей, а потом удаления старой ненужной. Тогда ни переиндексирования, ни долгой работы с диском - красота!

Comments

[vasdor]

Т.е. создавать новую таблицу без индекса для быстрой вставки, через день в ней делать индексирование для быстрой выборки и удаления и после этого удалять?

Диск — SSD, который предоствляется амазоновским AWS

[Леонид Сысолетин]

То есть всё, как у вас, только вместо шага "удаление записей" сделать "переименование таблицы - создание таблицы - удаление таблицы".

Answer 3

[Дмитрий Энтелис]

Если это логи, то:
a) зачем там индекс?
б) подумать нужно ли хранить сырые данные вообще, или можно сразу агрегировать что то?
Плюсую Леонид Сысолетин , решение с суточной таблицей живое.

Comments

[vasdor]

Индекс по дате для того, чтобы быстрее выбирать данные и удалять
Индекс вставлять не мешает по ~2000 строк в секунду при 50 млн записей (на пиках)
Проблемы начинаются после большого суточного удаления

Answer 4

[Игорь]

Для логов можно было бы сделать шардинг по дням. Таблицы: Logs_1, Logs_2, Logs_3 и т.д.
Старые таблицы дропать (это моментально!)
Номером таблицы может быть день недели, день месяца или года.

Answer 5

[lega]

Велосипед, но (т.к. задача простая) можно скидывать в файлы, один день - один файл, будет работать гораздо быстрее (передачу/чтение по сети можно ускорить до х100) + экономия диска и памяти. При желании можно делать сжатие - даст экономию диска и более быструю передачу по сети.