Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
 
Фильтр авторов:    показать 
  скрыть
  Ответ в темуСоздание новой темыСоздание опроса

> Резкий спад производительности MySQL, (Запрос по индексу)
NitroGenerate  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Абориген
*****

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 243
Пользователь №: 27946
На форуме: 6 лет, 2 месяца, 1 день
Карма: 11




Всем привет.
Недавно столкнулся со странной штукой. Резкий рост времени выполнения запроса по одной определенной таблице и огромная нагрузка на 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 . Не далек тот день, когда чистка этой таблицы уже не будет помогать и придется чистить всю БД

На этом мои идеи кончились. Надеюсь вы мне поможете.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15394
Пользователь №: 4190
На форуме: 9 лет, 5 месяцев, 14 дней
Карма: 470




Цитата (NitroGenerate @ 30.03.2017 - 11:56)
~ 120 млн. строк

Вот тут корень твоей проблемы. Надо таблицу как-то делить на части. Смотри в сторону партицирования.
До тех пор, пока будет "сто мильёнов строк" (в данном случае факт, а не метафора) smile.gif, проблему не решишь никакими другими способами, кроме как партицированием. И касается это не только Мускуля, но и любой другой БД.


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

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

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

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
T1grOK  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 2792
Пользователь №: 24406
На форуме: 6 лет, 9 месяцев, 4 дня
Карма: 180




Цитата (NitroGenerate @ 30.03.2017 - 07:56)
Индекс пробовал менять вместо id_out_link, date_add на id_out_link . Безрезультатно.

А созданные индексы вообще применяются то? Наличие индекса, не значит, что он будет задействован.


--------------------
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
PMПисьмо на e-mail пользователюСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15394
Пользователь №: 4190
На форуме: 9 лет, 5 месяцев, 14 дней
Карма: 470




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


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

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

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

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 823
Пользователь №: 36058
На форуме: 4 года, 6 месяцев, 23 дня
Карма: 42




Цитата (NitroGenerate @ 30.03.2017 - 11:56)
Со временем, этот безобидный запрос начинает вешать БД:
SELECT * FROM `data_table` WHERE `id_out_link` = '4584858094' LIMIT 50;

Тип int, а искомое больше, чем 2^32. И зачем тут строка?
Мне кажется, что ТС что-то недоговаривает или параметры привёл от балды.


--------------------
PM
    1   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
S.Chushkin  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 823
Пользователь №: 36058
На форуме: 4 года, 6 месяцев, 23 дня
Карма: 42




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


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
bestxp  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



орангутанг
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 2066
Пользователь №: 36605
На форуме: 4 года, 5 месяцев, 4 дня
Карма: 113




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

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

PMПисьмо на e-mail пользователюСайт пользователяICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
inpost  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Помагите Здесь живу!!!
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 22932
Пользователь №: 20039
На форуме: 7 лет, 7 месяцев, 13 дней
Карма: 608




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

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

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


--------------------
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
PMПисьмо на e-mail пользователюICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
NitroGenerate  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Абориген
*****

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 243
Пользователь №: 27946
На форуме: 6 лет, 2 месяца, 1 день
Карма: 11




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

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

Пользователя сейчас нет на форуме



Пофигист
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 823
Пользователь №: 36058
На форуме: 4 года, 6 месяцев, 23 дня
Карма: 42




Цитата (NitroGenerate @ 31.03.2017 - 10:51)
Проблема была в id_out_link, при проектировании БД ни как не предполагалось, что данных будет на столько много. По этому поле было INT. Когда прилетали запросы, которые не входили в диапазон 0-4294967295, индекс не использовался, что очевидно.

Не очевидно.
В этом случае EXPLAIN должен выдать "Impossible WHERE noticed after reading const tables".
Т.е. вообще ничего искать не будет и запрос выполнится за доли милисекунды.

Т.е. где-то у вас явный глюк - то ли в движке, то ли в коде.

п.с. Специально проверил на 30М записей, 5Г размер, mySQL v5.6 - никаких тормозов, выполняется как положено (с "Impossible...").


--------------------
PM
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
  Быстрый ответ
Информация о Госте
Введите Ваше имя
Кнопки кодов
Для вставки цитаты, выделите нужный текст и
НАЖМИТЕ СЮДА
Введите сообщение
Смайлики
:huh:  :o  ;) 
:P  :D  :lol: 
B)  :rolleyes:  <_< 
:)  :angry:  :( 
:unsure:  :blink:  :ph34r: 
     
Показать всё

Опции сообщения  Включить смайлики?
 Включить подпись?
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темы Ответ в темуСоздание новой темыСоздание опроса