Всем привет.
Недавно столкнулся со странной штукой. Резкий рост времени выполнения запроса по одной определенной таблице и огромная нагрузка на CPU. Вы сейчас скажите, индексы проморгал, не тут-то было.
По факту:
10.1.21-MariaDB
Трафик 8 ГиБ/час
Под БД выделяем ~200 Гб RAM
4xSSD 800Gb RAID 10
Размер БД 150 Гб
Таблицы InnoDB

Проблемная таблица:
Размер: 20 Гб. и ~ 120 млн. строк (12 ГиБ данных и 9.2 ГиБ индекса)
* на данный момент

CREATE TABLE data_table (
  id bigint(14) UNSIGNED NOT NULL AUTO_INCREMENT,
  id_out_data bigint(20) UNSIGNED NOT NULL,
  id_out_link int(11) UNSIGNED NOT NULL,
  type enum ('type1', 'type2') NOT NULL,
  link varchar(256) DEFAULT NULL,
  date_upd datetime NOT NULL,
  date_add datetime NOT NULL,
  PRIMARY KEY (id),
  INDEX id_out_link(id_out_link, date_add),
  UNIQUE INDEX udx_id_out_data (id_out_data)
)
ENGINE = INNODB;

Оптимизация не помогает.
Чтобы исправить ситуацию приходится чистить таблицу(удалять данные в ущерб проекту), после чистки нагрузка спадает почти до нулевой. Причем ни кол-во запросов, ни трафик - не меняются, и даже на оборот, возрастают. (т.к. сервер начинает быстрее обрабатывать запросы)

Скрин нагрузки на машину: http://joxi.ru/l2ZxW06t9kBQrJ

Со временем, этот безобидный запрос начинает вешать БД:

SELECT * FROM `data_table` WHERE `id_out_link` = '4584858094' LIMIT 50;

Индекс пробовал менять вместо id_out_link, date_add на id_out_link . Безрезультатно.

Заметил, чем больше общий объем БД, тем чаще приходится чистить таблицу data_table . Не далек тот день, когда чистка этой таблицы уже не будет помогать и придется чистить всю БД

На этом мои идеи кончились. Надеюсь вы мне поможете.

T1grOK, я так думаю, что без индексов у него вообще любой запрос завешивал бы наглухо весь сервер. 120 млн записей!!! Я помню, инпост как-то создавал тему про оптимизацию БД, у него было чуть более 10 млн. записей в таблице. И уже были проблемы с производительностью. Я так думаю, что у ТС реальные проблемы начинаются намного раньше, когда записей меньше, ну хотя бы 20 млн. или даже меньше.

_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

SELECT * FROM `data_table` WHERE `id_out_link` = '4584858094' LIMIT 50;

И не дал результаты EXPLAIN, реальные естественно.

_____________
Рекламка / ad.pesow.com Хрень / mr-1.ru

ну и как бы не партицирование, а шардирование уже если на то пошло

ну и как вариант опять же проверить индексы, вспоминаем причины почему индекс может не работать и что такое селективность =)

sergeiss
Там было 25 гигов данных на 10 миллионов записей. Сейчас 50 гигов, 30 миллионов, сервер обновлён до 96gb RAM. Пока без партиций живёт сервер, но я уже заранее подготовил переезд на таблицы с партициями. Переезд был не по этой причине, я тупо не успел мигрировать Поэтому жду проблем и сразу же буду мигрировать.

NitroGenerate
Рано или поздно ты всё равно упрёшься в необходимость использовать партиции, никакая оптимизация уже не поможет. 25 гигов ~ на 30gb RAM, 60-80 гигов будет на 90gb RAM, 120-150 на 200gb RAM. Конечно это всё на глаз, но принцип такой. Так что только партиции, и грамотно продумай алгоритм работы с ними и как грамотно разбить, чтобы 1 запрос сканировал лишь одну таблицу.

И когда разобьешь, обязательно поделись впечатлениями, помогло ли, на сколько скорости прибавилось и т.д.

_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).

Партиционирование меня особо не пугает. Т.к. запросы особо переписывать не нужно, исходя из мануалок по партиционированию мускула =) только лишь грамотно разбить партиции. Главное что бы на жесткий все влезло.

По поводу моего основного вопроса. Проблема решена. Ситуация оказалась очень глупая.
Отдельное спасибо S.Chushkin
Проблема была в id_out_link, при проектировании БД ни как не предполагалось, что данных будет на столько много. По этому поле было INT. Когда прилетали запросы, которые не входили в диапазон 0-4294967295, индекс не использовался, что очевидно.
При малом размере таблицы, мускул относительно спокойно к ним относится, но с каждым дополнительным гигабайтом данных, запрос такого рода, все больше нагружал MySQL. И тут начинался общий спад производительности таблицы, а за ней и БД в целом.
В результате, в лог долгих запросов падали все запросы. При тестах(я очень везучий человек оказывается), когда выбиралось несколько случайных медленных запросов и тестировалось, все они попадали в диапазон int, следовательно по индексам было без нареканий. В этот же раз, "везение" кончилось и попался запрос с данными вне индекста, ну я его и зафигачил сюда, даже не проверив. Эх...