Где и как держать большую базу данных?

Question

[intelligentpotato]

Привет, Тостер!
Я не имею опыта системного администрирования больших проектов. К сожалению, моя "верхушка" на данный момент – настроить LAMP по инструкциям из интернета.
Так получилось, что MySQL-база моего проекта разрослась до 1.1ТБ. Занят этот терабайт одной MyISAM-таблицей на 340 миллионов записей. Apache и MySQL сейчас живут на сервере у Kimsufi: i5-3570S, 16GB RAM, но этот сервер уже еле справляется. Средняя нагрузка – примерно 400 одновременных соединений, преимущественно INSERT. При перегрузке база падает, ибо MyISAM и table lock, соответственно. Да и мой быдлокод играет далеко не последнюю роль. Одно из последних падений, побившее 93% индексов, меня и подтолкнуло к действию.
Думаю, проект уже перерос возможности одного сервера и надо что-то предпринимать для дальнейшего роста, но вот что – ума не приложу. Рассматривал Google Cloud SQL/Amazon RDS, но получается слишком дорого. Хотелось бы уложиться в $250-300 ежемесячного бюджета, дабы проект хотя бы выходил в ноль. Полагаю, имеет смысл настроить шардинг. Каков оптимальный размер одного шарда и от каких именно характеристик сервера он зависит? Быть может, вообще имеет смысл переход на другую СУБД?

Comments

[lega]

Что у вас в базе и как используете?

[Andrey Kobyshev]

А что если эту одну таблицу вообще не в sql засунуть, а в какую-нибудь производительную узкозаточенную nosql? Что там за данные у вас? Тем более, которые, в основном, на insert идут. Логи?

Answer 1

[Алексей Яхненко]

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

Comments

[intelligentpotato]

С удовольствием, но я не знаю, что конкретно оптимизировать? Например, имеет ли смысл разбивать таблицу на несколько при запросах не сложнее SELECT/UPDATE/INSERT ... WHERE 'id' = ...?

[Алексей Яхненко]

а можно структуру вашей мега-таблицы увидеть? хотя бы примерно.

[intelligentpotato]

Алексей Яхненко lega puu.sh/gfag7/15fa62b260.png

[Алексей Яхненко]

т.е. самый ад у вас в поле json. а в этих jsonах - структура однородная? если да, то как раз ее и можно выделить в отдельные таблицы.

[Andrey Kobyshev]

intelligentpotato: такую можно в какое-нибудь индексированное хранилище запихнуть, хоть в монгу какую-нибудь, раз запросы простые и по индексам только.

Answer 2

[lega]

Все равно не достаточно информации, что у вас там в json и как вы используете данные.

Вот некоторые советы:
1) Прочитайте про партицирование, возможно вашу колонку ownerID можно вообще выкинуть, и разбить все данные на таблицы owner1, owner2..., таким образом можно сэкономить на индексах и данных, + можно будет проще размазать базу по серверам (шардинг), да и работать так будет быстрее.
2) Делайте архивирование json, это может уменьшить объем данных в 2..10 раз.
3) Складывайте старые данные в архив, например месяц прошел, делайте результирующие отчеты, кеши и т.п. что может запросить клиент, а сами данные отправляйте в архив.
4) Попробуйте другую бд: с postrgresql - можно использовать сжатый json по которому можно сделать индексы, таким образом ваши varchar'ы оптимизируются. с nosql/mongodb тоже есть плюсы, например 1 "запись" будет занимать 1 блок памяти, а не несколько как в sql базах, + тут выше скорость записи.

Так же по принципу партицирования можно делать чанки данных, например если вам данные нужно выбирать по дням и владельцу, то по окончанию дня можете паковать данные в чанки: data, ownerID, archived_json. таким образом размер индексов может уменьшится в 100 раз, данные в 10..20 раз, + скорость получения данных может вырасти до 50х раз (был у меня подобный проект).

Этими советами можно 1Тб "превратить" (например) в 10Гб - зависит от данных и использования.

Answer 3

[Saboteur]

Для начала, почему нельзя сделать несколько таблиц?
Если старые данные нужно просто хранить, изредка почитывая, а основная активность - инсерт и работа с последними данными, то давно следовало бы подумать, как можно разделить данные.

Можно помониторить, выяснить что именно нагружено больше всего - диск, память, сеть?
Может просто настроить репликацию, и разделить запросы на два сервера?

Answer 4

[Shaks]

Загляни в mysql-slow.log, посмотри на тяжелые запросы, подшамань да подкрути их. там 146% есть гайки которые можно подкрутить.
Пологируй все запросы чуток. Проанализируй каждый из них на ПРАВИЛЬНОЕ использование индексов. Поубирай лишние индексы (освободишь наверное процентов 20-30 пространства). Вобщем оптимизируй. И только если оптимизировать нечего - тогда уже стоит задумываться о шардах.

Comments

[intelligentpotato]

Индексы настроены, лишних нет. Запросы все имеют одинаковую структуру, разнится только фильтр выборки. Благодарю за напоминание про slow log, всё никак руки не доходили.

[Shaks]

Ну если EXPLAIN говорит что все индексы при разных фильтрах выборки работают правильно, то тогда шардить. Я не знаю что там у тя за проект такой, но раз ты говориш 250-300 бюдет чтоб в ноль работать.. то для начала бы попробовал самый бюджетный вариант, это в качестве slav''a взять какую нибудь вдску за 30$-50$, и шард сделать на неё.

[Tark]

...примерно 400 одновременных соединений, преимущественно INSERT. (c)

Таблицы MyISAM целиком блокируются во время вставки данных. Попробуйте сменить тип на InnoDB.

[Shaks]

Tark: кстати да ))

Answer 5

[Марк Розенталь]

Крутые мужики NoSQL используют

Answer 6

[Иван]

Можно ещё глянуть в сторону форков mysql типо MariaDB и тд
А так partition by для таблиц, как писали вше.
Кэширование memcache или redis. Или вариант с mongo для отдельных таблиц или полей.