Jak mogę sprawdzić, czy moja baza danych potrzebuje więcej pamięci RAM?

Jak sprawdzisz, czy instancja DB postgresql potrzebuje więcej pamięci RAM do obsługi bieżących danych roboczych?

Jeśli wszystko jest w systemie Linux, całkowita fizyczna pamięć RAM powinna być większa niż rozmiar bazy danych na dysku, aby zminimalizować operacje we / wy. Ostatecznie cała baza danych będzie w pamięci podręcznej odczytu systemu operacyjnego, a operacje we / wy będą ograniczone do zatwierdzania zmian na dysku. Wolę znaleźć rozmiar bazy danych, uruchamiając „du -shc $ PGDATA / base” - ta metoda agreguje wszystkie bazy danych w jedną liczbę. Tak długo, jak jesteś większy, powinno być dobrze.

Ponadto możesz sprawdzić współczynnik trafień w pamięci podręcznej dla pobierania stosu i bloków indeksu. Mierzą one liczbę trafień we współdzielone bufory PostgreSQL. Liczby mogą być nieco mylące - chociaż mogło to być brak w pamięci podręcznej buforów współdzielonych, wciąż może być hitem w pamięci podręcznej odczytu systemu operacyjnego. Mimo to trafienia we współdzielonych buforach są nadal tańsze niż trafienia w pamięci podręcznej odczytu systemu operacyjnego (które z kolei są tańsze o kilka rzędów wielkości niż konieczność powrotu na dysk).

Aby sprawdzić współczynnik trafień wspólnych buforów, używam tego zapytania:

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;

To daje ci 25 najgorszych przestępców, w przypadku których pamięć podręczna bufora została pominięta dla wszystkich tabel, w których co najmniej jeden blok musiał zostać pobrany z „dysku” (ponownie, którym może być albo pamięć podręczna odczytu systemu operacyjnego lub rzeczywiste operacje we / wy dysku). Możesz zwiększyć wartość w klauzuli WHERE lub dodać inny warunek dla heap_blks_hit, aby odfiltrować rzadko używane tabele.

To samo podstawowe zapytanie może być użyte do sprawdzenia całkowitej liczby trafień indeksu na tabelę poprzez globalne zastąpienie ciągu „sterty” ciągiem „idx”. Spójrz na pg_statio_user_indexes, aby uzyskać podział według indeksu.

Krótka uwaga na temat współdzielonych buforów: dobrą zasadą jest, aby w Linuksie ustawić parametr konfiguracyjny shared_buffers na 1/4 pamięci RAM, ale nie więcej niż 8 GB. Nie jest to trudna i szybka reguła, ale raczej dobry punkt wyjścia do strojenia serwera. Jeśli twoja baza danych ma tylko 4 GB i masz serwer 32 GB, 8 GB współdzielonych buforów jest w rzeczywistości nadmierną wydajnością i powinieneś być w stanie ustawić to na 5 lub 6 GB i nadal mieć miejsce na przyszły rozwój.

Zrobiłem ten SQL, aby pokazać stosunek tabel do liczby trafień dysku:

-- 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

Działa również, jak powiedziano w dokumencie 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;