Как оптимизировать БД (2 Гб. на жестком диске)?

Question

[Runcorn]

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

Есть БД, которая занимает приблизительно 2 Гб. на жестком диске. Предполагается, что большинство запросов будет SELECT (95%), почти всегда с JOIN, иногда с LIKE оператором, выборка всегда около 100 записей, и мало INSERT, UPDATE (5%). Как оптимизировать БД, чтобы не упасть при нагрузках и БД обрабатывала хотя бы несколько тысяч запросов в секунду? Есть ли смысл вынести всю БД как нибудь в память и использовать синхронную/асинхронную репликацию с другим сервером БД на том же сервере, но который будет вносить изменения уже на жесткий диск? Или может в серверов БД есть какие то особенные настройки?

БД может быть MySQL или PostgreSQL.

Comments

[Kerman]

Если нет реального проседания производительности - разговор ни о чём. Если не указана структура баз и что именно будет выбираться - то же самое. Короче, нужен план использования.

[Runcorn]

Реальное проседание гарантировано появится под нагрузкой, потому что скорость обработки запросов при большом количестве SELECT'ов упрется в жесткий диск. Я знаю, что современные ОС могут кэшировать файлы в оперативку. Но с другой стороны хочется научиться самому явно задавать, что нужно кэшировать, а что нет.

Answer 1

[Юрий Ярош]

2Гб база - это не много.
Нужно смотреть в сторону нормализации модели базы.
Left / Right JOIN запросы не критичны, вот Inner / Outer очень сильно могут подпортить производительность. На моей практике денормализировать и партицировать модель для предотвращения JOIN'ов приходилось только при работе с 30Гб+ табличками.

Выносить БД на B-tree в память нет смысла, проще использовать Redis или hashtable индексы.
В случае с MySQL у InnoDB есть встроенный кэш для ключей первого порядка...

Вам нужно просто прикрутить нормально кэш второго уровня типа memcached или ehcache и не заморачиваться.

Как сказал @affka полнотекстовые поисковые движки нужны 100%.
Лично мне Sphinx не очень нравится, больше склоняюсь к Solr / Elastic Search и к встроенному полнотекстовому движку PosgreSQL.

Можно почитать это и это.
Для PostgreSQL можно глянуть это
Также желательно помнить про VACUUM и пользоваться pg_reorg для предотвращения блокировок.

В принципе ваши проблемы 100% решаются EXPLAIN'ом и нормальным кэшированием с нормализацией модели.

Шардинги / репликации и партицирование слишком большой геморой если не решены самые элементарные вопросы. Тем более что у MySQL что у PostgreSQL master-master репликация вообще не торт. Это "План Я" для любого проекта.

Answer 2

[Владимир Кожин]

Если в запросах участвуют не сильно много данных (не целиком несколько таблиц, а лишь некоторые колонки), то помогут грамотно проставленные индексы. СУБД индексы кешируют в памяти сами и поиск по ним очень быстр.
Если у вас запросы LIKE по каким-нить большим blob полям, то вероятно структура БД неверная и нужно искомую информацию заранее, на этапе записи, выносить в отдельные столбцы. Тем более что у вас 95% select запросов.
В любом случае от LIKE запросов лучше избавляться, сервер провалится от нагрузок даже если у вас всё в памяти будет лежать. Если это поиск по тексту, то советую посмотреть в сторону поисковых движков, например sphinx.

Answer 3

[Sergey]

2Гб - это смехотворно мало и безусловно должно находиться в памяти целиком. Но это всего лишь один из шагов оптимизации.

Comments

[Runcorn]

Ну я тоже так думаю. А как правильно выносить БД в оперативную память? Монтировать виртуальный диск в оперативке?

[Виталий Архипов]

Просто выделите ей в настройках это место.