Как я могу проверить, нужно ли моей БД больше оперативной памяти?

Если вы все работаете в Linux, ваша общая физическая RAM должна быть больше, чем размер вашей базы данных на диске, чтобы минимизировать ввод-вывод. В конце концов, вся база данных будет в кэше чтения ОС, а операции ввода-вывода будут ограничены фиксацией изменений на диск. Я предпочитаю определять размер БД, выполнив «du -shc $ PGDATA / base» - этот метод объединяет все базы данных в одно число. Пока вы больше этого размера, все должно быть в порядке.

Кроме того, вы можете посмотреть частоту попаданий в кеш для выборок блоков кучи и индекса. Они измеряют частоту попаданий в общие буферы PostgreSQL. Цифры могут немного вводить в заблуждение - даже если это могло быть пропущено в кэше общих буферов, оно все равно могло быть попаданием в кеш чтения ОС. Тем не менее, попадания в общие буферы по-прежнему менее затратны, чем попадания в кэш чтения ОС (которые, в свою очередь, на пару порядков дешевле, чем возвращение на диск).

Чтобы посмотреть частоту попадания в общие буферы, я использую этот запрос:

SELECT relname, heap_blks_read, heap_blks_hit,
    round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read),3)
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY 4
LIMIT 25;

Это дает вам 25 самых серьезных нарушителей, когда буферный кеш отсутствует для всех таблиц, где хотя бы один блок должен быть извлечен с «диска» (опять же, это может быть либо кеш чтения ОС, либо фактический дисковый ввод-вывод). Вы можете увеличить значение в предложении WHERE или добавить другое условие для heap_blks_hit, чтобы отфильтровать редко используемые таблицы.

Тот же самый базовый запрос можно использовать для проверки общего коэффициента попадания индекса для каждой таблицы, глобально заменив строку «heap» на «idx». Взгляните на pg_statio_user_indexes, чтобы получить разбивку по индексу.

Небольшое примечание об общих буферах: хорошее практическое правило для этого в Linux - установить параметр конфигурации shared_buffers до 1/4 ОЗУ, но не более 8 ГБ. Это не жесткое правило, а скорее хорошая отправная точка для настройки сервера. Если ваша база данных имеет размер всего 4 ГБ и у вас есть сервер на 32 ГБ, 8 ГБ общих буферов на самом деле слишком много, и вы должны иметь возможность установить это значение на 5 или 6 ГБ, и у вас все еще есть место для будущего роста.

Я сделал этот SQL, чтобы показать соотношение таблиц и обращений к диску:

-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with 
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name, 
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as 
(
SELECT  *
FROM    (
    SELECT  relname as table_name, 
        ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        ( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT  table_name as "table name",
    from_disk as "disk hits",
    round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
    round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
    (from_disk + from_cache) as "total hits"
FROM    (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER   BY (case when table_name = 'all' then 0 else 1 end), from_disk desc

Это также работает, как сказано в документе Heroku:

SELECT
    'cache hit rate' AS name,
     sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;