Оптимизация mysql, слишком большие таблицы?

Question

[kiranananda]

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

Есть база в которой куча таблиц от 10 до 50 гигов. Все это дело весит 350 гиг на диске. Если крашится одна из таблиц таких, ее вообще нереально восстановить. На днях упал слейв(запрос какой то не прошел) и тупое копирование базы заняло минут 20 простоя, но это пока место позволяет на сервере бд сделать копию файлов. После переноса на слейв, мускуль не запустился ругаясь на ошибки. Это уже насторожило, решили делать через mysqldump, но тут сами понимаете на всю ночь процесс....

Как что можно сделать с базой что бы избежать подобных проблем? Просто хотяб направления о чем задуматься...

Comments

[Владимир Коротенко]

вы бы хоть какие нибудь метрики привели
количество записей, какая нормализация, степень "горячести данных"

[kiranananda]

Владимир Коротенко, ну грубо говоря список товаров в магазине или список заказов... Вот к примеру продукты 66 гигов и 49 886 643 записей.

[Владимир Коротенко]

kiranananda, Попробуйте шардирование.
То есть допустим у вас есть продукты и категории
создайте
следующие таблицы:
product_consumer
product_manufactured
.....

Кстати разделите их на актуальные и не актуальные (исторические)

[kiranananda]

Владимир Коротенко, Спсибо! Именно таких я и ждал решений!

[d'Ivan]

Владимир Коротенко,

Попробуйте шардирование.

А почему не партицирование? И подходит ли шардирование для таких небольших объемов?

[kiranananda]

Román Mirilaczvili, а что из себя представляет партицирование ?

[Дмитрий]

kiranananda, https://dev.mysql.com/doc/refman/8.0/en/partitioni...

[kiranananda]

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

[Дмитрий]

kiranananda, вы создаете таблицу указываете на сколько партиций ее разбить и указываете как разбивать данные.

на больших таблицах процесс будет не быстрый - да.

Answer 1

[Ипатьев]

Ну как минимум xtrabackup а не mysqldump.

А в целом, я думаю, тут решение одно - нанять грамотного сисадмина/DBA
Чтобы лечил причину, а не следствие (то есть разбирался - почему "крашнулась" таблица, и делал так, чтобы это не повторялось)
Чтобы не писал ерунды типа "место кончилось" (как будто под линуксом проблема перекинуть файлы сразу на другую машину)
А если уж и писал в Спортлото, то делал это чётко и подробно, с максимумом технических подробностей - какие физические характеристики серверов, какая операционка, какая БД, все версии с точностью до последней буквы, какой тип таблиц, с какой ошибкой "крашнулось", какую "ошибку" пишет при переносе.

Comments

[kiranananda]

Ну тут вопрос более теоретический. Поэтому и без версий. Система дебиан 10ка, mariadb штатная. Крашнутся может по разному. Бывало через несколько месяцев работы мускуль просто вылетел. Ну это осовная проблема была один раз, но это может быть что угодно, в этом мире нельзя подстраховаться на 100%. Что нибудь да случиться и у супер крутого спеца :).

Копирование по сети занимает час, а не 20 минут, тут время простоя гораздо выше.

Вместо того что бы искать проблемы в моих кривых руках предложите схемы, а так толку нет... За xtrabackup спасибо, гляну на будущее...

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

[kiranananda]

Тут как бы мне не интересно разбирать причины падения. Ибо когда они будут тогда и разберем :). Важно именно как максимально быстро восстановиться. Ведь любой тип таблицы если он весит под 60 гигов за 5 минут не восстановится. Мы как то ждали около часа, потом плюнули и достали с реплики.

[Ипатьев]

Вопрос у вас "теоретический"? Ну и ответ вы получили тоже теоретический. Не вижу проблем.
Судя по всему, вы вообще не умеете задавать грамотные технические вопросы, даже после напоминания.
Вы даже тип таблиц не упомянули - а я не удивлюсь, если там MYSAM!

То есть вы даже приблизительно не понимаете смысла своих действий. В этом случае задавать "теоретические" вопросы бесполезно. Это всё равно что прийти на автофорум и написать "машина то едет, то не едет. Вот недавно остановилась, вышли, колеса пинали-пинали, двери открывали-закрывали, всё равно не едет. Почему это может быть?"
И ждёте, как все герои русских сказок, волшебную щуку, которая при таких невнятных вводных даст вам гениальный совет, который сразу решит все ваши проблемы.

[kiranananda]

Ипатьев, согласен, не умею :). Но учусь вашей помощью :). Нет таблицы InnoDB. Версия если важная 10.3.15-MariaDB-1-log . Просто если таблица под 60 гигов с несколькими миллионами записей и если она крашнулась это беда. Версии мускуля имхо тут не помогут. Вопрос как сделать так что бы минимальными издержками вылазить из сбоев?

[Дмитрий]

kiranananda, а смотрели здоровье дисковой подсистемы? Может там уже дискам плохо. Поставьте на рэйд базу.

[kiranananda]

Дмитрий, Да с серваками все хорошо, в том то и дело что проблемы щас нет никакой. Там рэйд стоит все в порядке... Просто сами понимаете иногда таблицы в база крашаться и тогда когда все хорошо, это бывает... У меня серьезно было 1 раз за 5 лет, но было.

[Ипатьев]

При таких объёмах уже поднимают кластер.
Который работает примерно как RAID массив - если один сервер вылетел, то система просто перераспределяет нагрузку на другие. HAPRoxy, Galerа
Но на коленке их не поднимают, тут нужен специалист.
Я честно удивляюсь, как вы дожили до таких объёмов без грамотного сисадмина/
девопса/dba

[kiranananda]

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

[kiranananda]

Ипатьев, Ну то есть таблица 60 гиговного размера это норм? или нужно стараться до такого не доводить?

[Ипатьев]

kiranananda, а вот тут я не понял. Совсем.
"Кластера подняты просто для веба больше" - это как? А в вопросе речь не про веб? А про что?

[kiranananda]

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

[kiranananda]

Ипатьев, ну кластер там для php memcached и прочее...

[Ипатьев]

kiranananda, бывает и больше.
Всё зависит от технических деталей, которые вы упорно отказываетесь сообщать.
innodb_file_per_table у вас используется?
Занимают ли данные весь объём файла?
Насколько показания такого запроса отличаются от размера файлов?

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "имя бд"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Возможно, надо один раз пересоздать БД на мастере из дампа.

[kiranananda]

Ипатьев, innodb_file_per_table да я поставил пару тройку лет назад :). Но конечно часть таблиц не пересоздавалась. И да, надо пересоздать из дампа все, это прям в планах стоит. Но сервис растет и я вопрошаю с уклоном на будущее... Все равно меньше уже не будет...

[kiranananda]

Ипатьев, За запросик спасибо :), отправил в заметки...

[Ипатьев]

Его не надо отправлять в заметки.
Его надо выполнить, чтобы получить ответ на свой вопрос, "60 - это много или нет???"
В общем, всё с вами ясно.

[kiranananda]

Ипатьев, ну не хотелось бы щас базу грузить сильно ), у меня count то висел 30 секунд )...

[kiranananda]

К моему вопросу все равно это мало имеет значение, у уменьшиться таблица после создания в 2 раза, 30 гигов это все равно дохренища, да даже 10 дохрена и за 5 минут она не восстановится )

[Ипатьев]

Это какой-то уже совсем фейспалм.
Этот запрос не выполняет никакой count. Вы реально никогда не слышали про БД information_schema?

[kiranananda]