Задать вопрос
@fedor_nefedov
postgresql

Как понять что виновато в медленном поиске на PostgreSQL?

Есть сервер в конфигурации: Intel Xeon 2.30GHz, 8 GB оперативная память, не SSD, CentOS 7, PostgreSQL 9.4. В базе данных 10М записей с текстовым полем максимум по 1 KB. Есть поле типа tsvector, составленное по текстовому полю. GIST индекс по полю tsvector и Btree индекс по полю ID. База занимает порядка 20 GB. Postgresql.conf через pgtune.

Складывается следующая ситуация:
1. Запрос типа SELECT count(*) по всей таблице выполняется порядка 5 мин. Второй такой же запрос порядка 20 сек.
2. Запрос типа SELECT * FROM table WHERE tsvector_field @@ to_tsquery('text'); выполняется минут 7 и естественно после повторения запроса выполняется миллисекунды. EXPLAIN показывает, что все нужные индексы используются.

Вопросы такие:
1. Как сделать так, чтобы после перезапуска сервера все эти запросы выполнялись также быстро, как после первого запроса? (Что то типа загрузить её в память или 'раскочегарить';)
2. Почему так долго выполняется запрос по индексированным полям?
3. Виноваты ли настройки сервера?
4. Как много нужно оперативной памяти? И виноват ли жесткий диск?
5. С каких количеств записей в базе нужно начинать масштабирование?
6. Какой размер базы оптимален для одного сервера, если масштабировать базу?
  • Вопрос задан
  • 3451 просмотр
Комментировать
Подписаться 5 Оценить Комментировать
Решения вопроса 1
@fedor_nefedov Автор вопроса
Итак, после недолгих тестов выснилось следующее: самое узкое место это жесткий диск, после него идет оперативная память и потом настройки в PostgreSQL.conf, после улучшения обоих характеристик допилил конфиг и запрос с результатом в 400000 строк строился 10 сек, а запрос на лимит 100 0,40 ms. Общий вывод: виноваты ресурсы
Ответ написан
1 комментарий
1 комментарий
Пригласить эксперта
Ответы на вопрос 2
benbor
@benbor
Помог ответ - не забудь лайкнуть
Очевидное решение знаменитым "костылем": после сатрта выполните эти запросы, и кеш прогреется.
Но лучше найдите тонкое место, htop, iotop, scout_realtime вам в помощь - делайете тот самый медленный запрос - смотрите, что проседает из железа.
Вангую, что проседать будет жесткий диск
Ответ написан
Комментировать
Комментировать
@smbd
Вообще постгри любит кэшировать индексы в оперативную память, и поэтому чем больше её отдать, тем лучше.
А разницу между запросами смотрите нормальным EXPLAIN, с буферами - наверняка первый запрос делал IO, а второй использовал cached buffers.

Общий вывод: виновато незнание матчасти, если честно. Ничего удивительного по результатам таких запросов не вижу - у нас грубо говоря также.

1. Можно руками прогреть кэш. В 9.4 кажется сделали фишку, чтобы перезапускаться, сохраняя кэши.
// оффтоп: у нас в продакшне за полтора года постгрес перезапускался кажется один раз - а вам зачем это? //
2. Потому что читается с жесткого диска, и индексы большие. См выше.
3. Виноваты только в плане повторных запросов.
4. См. выше, чем больше, тем лучше. На первый запрос - виноват.
5-6. Не совсем правильно. Всё зависит от характера нагрузки и самых медленных запросов, а не от от того, сколько и чего где.
Ответ написан
Комментировать
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Разработчик баз данных PostgreSQL
Объединенные системы управления транспортом • Москва
До 220 000 ₽
Администратор PostgreSQL
Гринатом
До 200 000 ₽
DBA / Администратор баз данных PostgreSQL
СберТех • Москва
от 320 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Установить вебхук на сайт
24 апр. 2024, в 16:19
4000 руб./за проект
Натянуть верстку Опенкарт
24 апр. 2024, в 15:34
20000 руб./за проект
SEO с опытом в контентных проектах (СМИ)
24 апр. 2024, в 15:32
130000 руб./за проект
Ещё заказы