MySQL Partitioning / Sharding / Splitting - куда идти?
У нас есть база данных InnoDB размером около 70 ГБ, и мы ожидаем, что она вырастет до нескольких сотен ГБ в следующие 2-3 года. Около 60% данных принадлежат одной таблице. В настоящее время база данных работает достаточно хорошо, так как у нас есть сервер с 64 ГБ ОЗУ, поэтому почти вся база данных умещается в памяти, но нас беспокоит будущее, когда объем данных будет значительно больше. Прямо сейчас мы рассматриваем какой-то способ разделения таблиц (особенно ту, которая составляет большую часть данных), и теперь мне интересно, как лучше всего это сделать.
В настоящее время я знаю следующие варианты:
- Использование MySQL Partitioning версии 5.1
- Использование какой-то сторонней библиотеки, которая инкапсулирует разделение данных (например, осколки спящего режима)
- Реализуем это сами внутри нашего приложения
Наше приложение построено на J2EE и EJB 2.1 (надеюсь, когда-нибудь мы перейдем на EJB 3).
Что ты предлагаешь?
РЕДАКТИРОВАТЬ (2011-02-11):
Просто обновление: в настоящее время размер базы данных составляет 380 ГБ, размер данных нашей «большой» таблицы - 220 ГБ, а размер ее индекса - 36 ГБ. Таким образом, хотя вся таблица больше не умещается в памяти, индекс подходит.
Система по-прежнему работает нормально (на том же оборудовании), и мы все еще думаем о разделении данных.
РЕДАКТИРОВАТЬ (2014-06-04): Еще одно обновление: размер всей базы данных - 1,5 ТБ, размер нашей «большой» таблицы - 1,1 ТБ. Мы обновили наш сервер до 4-х процессорного компьютера (Intel Xeon E7450) с 128 ГБ оперативной памяти. Система по-прежнему работает нормально. Что мы планируем сделать дальше, так это разместить нашу большую таблицу на отдельном сервере базы данных (мы уже внесли необходимые изменения в наше программное обеспечение) при одновременном обновлении до нового оборудования с 256 ГБ оперативной памяти.
Эта установка рассчитана на два года. Тогда нам придется либо наконец приступить к реализации решения для сегментирования, либо просто купить серверы с 1 ТБ оперативной памяти, что должно поддерживать нас в течение некоторого времени.
РЕДАКТИРОВАТЬ (2016-01-18):
С тех пор мы поместили нашу большую таблицу в собственную базу данных на отдельном сервере. В настоящее время размер этой базы данных составляет около 1,9 ТБ, размер другой базы данных (со всеми таблицами, кроме «большой») - 1,1 ТБ.
Текущая настройка оборудования:
- HP ProLiant DL 580
- 4 процессора Intel (R) Xeon (R) E7- 4830
- 256 ГБ RAM
Производительность в этом случае нормальная.
Не могли бы вы снова обновить текущее состояние?
Что в этом нового? Какое решение было использовано?
@sme: У меня похожая проблема, и мне интересно, какое решение вы использовали? Каким был ваш опыт и заметили ли вы какие-либо улучшения? Заботиться, чтобы поделиться? К вашему сведению, в моем случае у меня была огромная таблица (миллионы строк) с простой схемой (несколько столбцов), что мне пришлось решить узкое место чтения / записи. Моя первая попытка - попробовать горизонтальное разбиение (разбить строки на разные таблицы).
@sme: Не могли бы вы снова обновить текущее состояние?
Ответы 8
Прежде всего, не имеет большого значения разбиение таблиц, если вы также не переместите некоторые из таблиц на отдельный физический том.
Во-вторых, это не обязательно стол с самым большим физическим размером, который вы хотите переместить. У вас может быть таблица гораздо меньшего размера, в которой будет больше активности, в то время как ваша большая таблица останется довольно постоянной или только добавляет данные.
Что бы вы ни делали, не выполняйте сами. Позвольте системе баз данных справиться с этим.
Некоторое время назад на мероприятии Microsoft ArcReady я увидел презентацию о шаблонах масштабирования, которая может быть вам полезна. Вы можете использовать просмотреть слайды онлайн.
Что делает большой стол.
Если вы собираетесь разделить его, у вас есть несколько вариантов:
- Разделить его, используя систему баз данных (мало что знаю об этом)
- Разделить по строкам.
- разбить по столбцу.
Разделение по строкам будет возможно только в том случае, если ваши данные можно легко разделить на части. например Что-то вроде Базовый лагерь имеет несколько учетных записей, которые полностью разделены. Вы можете хранить 50% учетных записей в одной таблице и 50% в другой таблице на другом компьютере.
Разделение по столбцу подходит для ситуаций, когда размер строки содержит большие текстовые поля или BLOB-объекты. Если у вас есть таблица с (например) изображением пользователя и огромным блоком текста, вы можете поместить изображение в совершенно другую таблицу. (на другой машине)
Здесь вы нарушаете нормализацию, но я не думаю, что это вызовет слишком много проблем.
Если вы думаете, что у вас будет ограничение ввода-вывода / памяти, я не думаю, что разбиение на разделы поможет. Как обычно, бенчмаркинг в первую очередь поможет вам определить наилучшее направление. Если у вас нет запасных серверов с 64 ГБ памяти, вы всегда можете попросить своего поставщика предоставить «демонстрационный блок».
Я бы склонился к сегментированию, если вы не ожидаете сводной отчетности по 1 запросу. Я предполагаю, что вы разделите всю базу данных, а не только свою большую таблицу: лучше всего хранить целые объекты вместе. Ну, в любом случае, если ваша модель хорошо расколется.
ОП может решить, что конкретный ответ лучше всего отвечает на его вопрос, но все остальные могут не подумать, что это лучший совет. Однажды я увидел, что принятый ответ получил -10, потому что в то время как ответ отвечал, как чего-то достичь; многие люди считали своей обязанностью отговорить ОП от того, чтобы делать что-то таким особым образом.
В конце концов, вы, вероятно, захотите разделить этот большой стол. Вы, вероятно, захотите поместить его на отдельный жесткий диск, прежде чем думать о втором сервере. Сделать это с MySQL - самый удобный вариант. Если это возможно, то дерзайте.
НО
На самом деле все зависит от того, как ваша база данных используется. Статистика.
Вы обязательно начнете сталкиваться с проблемами в этой таблице размером 42 ГБ, как только она перестанет умещаться в памяти. Фактически, как только он перестанет умещаться в памяти, производительность упадет очень быстро. Один из способов тестирования - поместить эту таблицу на другой компьютер с меньшим объемом оперативной памяти и посмотреть, насколько плохо она работает.
First of all, it doesn't matter as much splitting out tables unless you also move some of the tables to a separate physical volume.
Это неверно. Разделение (либо с помощью функции MySQL 5.1, либо то же самое с использованием таблиц MERGE) может обеспечить значительные преимущества в производительности, даже если таблицы находятся на одном диске.
В качестве примера предположим, что вы выполняете запросы SELECT к своей большой таблице, используя диапазон дат. Если таблица целая, запрос будет вынужден сканировать всю таблицу (и при таком размере даже использование индексов может быть медленным). Преимущество разделения состоит в том, что ваши запросы будут выполняться только на тех разделах, где это абсолютно необходимо. Если размер каждого раздела составляет 1 ГБ, а вашему запросу требуется доступ только к 5 разделам, чтобы выполнить себя, то объединенная таблица размером 5 ГБ намного проще для MySQL, чем гигантская версия на 42 ГБ.
Вам нужно спросить себя, как вы запрашиваете данные. Если есть вероятность, что вашим запросам потребуется доступ только к определенным фрагментам данных (например, диапазону дат или диапазону идентификаторов), какое-либо разделение окажется полезным.
Я слышал, что с разбиением на разделы MySQL 5.1 все еще есть некоторые ошибки, особенно связанные с выбором правильного ключа MySQL. Таблицы MERGE могут обеспечивать ту же функциональность, хотя и требуют немного больше накладных расходов.
Надеюсь, это поможет ... удачи!
Запросы select будут ускоряться в разделе, но как насчет запросов insert? Будет ли MySQL строить индекс записи только в своем разделе?
Это отличный пример того, что может сделать разделение MySql на реальном примере огромных потоков данных:
Надеюсь, это будет полезно в вашем случае.
Выше ссылку закидывают 404!
@VardanGupta вот новая ссылка (но не могу обновить ответ, числовой адрес не допускается): 213.150.164.76/blog/2010/11/19/…
Я бы выбрал разделы MariaDB InnoDB + (либо по ключу, либо по дате, в зависимости от ваших запросов).
Я сделал это, и теперь у меня больше нет проблем с базой данных.
MySQL можно заменить на MariaDB за секунды ... все файлы базы данных остаются прежними.
Просто получите больше памяти через 2-3 года или воспользуйтесь твердотельным диском прямо сейчас. Как только вы потратите на это несколько сотен долларов, подумайте об оптимизации.