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

Question

[fedor_nefedov]

Есть сервер в конфигурации: 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. Какой размер базы оптимален для одного сервера, если масштабировать базу?

Answer 1

[fedor_nefedov]

Итак, после недолгих тестов выснилось следующее: самое узкое место это жесткий диск, после него идет оперативная память и потом настройки в PostgreSQL.conf, после улучшения обоих характеристик допилил конфиг и запрос с результатом в 400000 строк строился 10 сек, а запрос на лимит 100 0,40 ms. Общий вывод: виноваты ресурсы

Comments

[raiboon]

fedor_nefedov а как вы выяснили, что самое узкое место - жесткий диск?

Answer 2

[Борис Беньковский]

Очевидное решение знаменитым "костылем": после сатрта выполните эти запросы, и кеш прогреется.
Но лучше найдите тонкое место, htop, iotop, scout_realtime вам в помощь - делайете тот самый медленный запрос - смотрите, что проседает из железа.
Вангую, что проседать будет жесткий диск

Answer 3

[Павел Назаров]

Вообще постгри любит кэшировать индексы в оперативную память, и поэтому чем больше её отдать, тем лучше.
А разницу между запросами смотрите нормальным EXPLAIN, с буферами - наверняка первый запрос делал IO, а второй использовал cached buffers.

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

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