Мемориски аспекти и кеширање кај базите на податоци

PostgreSQL управува со меморијата преку неколку компоненти:

• shared_buffers - претставува главниот мемориски кеш на PostgreSQL. Ова е споделена меморија достапна за сите конекции и служи за чување на податочни страници (data pages) и индексни страници. Кога PostgreSQL треба да прочита некоја страница од диск, прво проверува дали таа страница е веќе во shared buffers. Доколку е, се избегнува читање од диск (cache hit). Доколку не е, страницата се вчитува од диск во shared buffers (cache miss). Тековната вредност на shared_buffers може да се провери со SHOW shared_buffers. Стандардно PostgreSQL ја поставува оваа вредност на 128MB, но препораката е да се постави на приближно 25% од вкупната RAM меморија на серверот. Параметарот се менува во конфигурациската датотека postgresql.conf но исто така може преку SQL со помош на ALTER SYSTEM SET shared_buffers TO <your_value>;

• work_mem е количина на меморија која PostgreSQL ја користи за секоја поединечна операција како сортирање, hash join и aggregate операции. Ако work_mem има премала вредност, PostgreSQL ќе започне да користи привремени датотеки на диск, што ги намалува перформансите. Вредноста на work_mem може да се види со SHOW work_mem и вредноста може да се постави глобално во postgresql.conf или локално за тековната сесија со SET work_mem = '64MB';

• maintenance_work_mem е параметар кој одредува колку меморија ќе користат операциите за одржување на базата како што се VACUUM, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY и слично. Стандардната вредност и е 64mb и бидејќи само една од овие операции може да се изврши во исто време од сесијата на базата на податоци безбедно е да се намести на вредност повисока од work_mem.

• effective_cache_size не алоцира реална меморија. Овој параметар служи како проценка за optimizer-от да зне колку меморија е достапна за кеширање (shared buffers + OS page cache). Оваа вредност влијае на одлуката дали да се користи индекс или sequential scan. Ако се процени дека поголем дел од податоците се веќе кеширани, planner-от ќе биде повеќе склон да користи индекс.

Механизам на кеширање во PostgreSQL

Buffer Cache

PostgreSQL кешира цели страници од 8KB во shared_buffers. Не се кешираат поединечни редови, туку цели страници. Кога страница еднаш ќе се вчита во меморија, таа може повторно да се искористи од наредни прашалници без повторно читање од диск.

Buffer Hit Ratio

Еден од најважните индикатори за мемориските перформанси е cache hit ratio односно колку пати PostgreSQL ги нашол податоците во меморија наместо да чита од диск. Идеално оваа вредност треба да биде над 95%.

SELECT 
  relname AS tabela,
  heap_blks_read AS citanja_od_disk,
  heap_blks_hit AS citanja_od_kesh,
  ROUND(
    heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0) * 100, 2
  ) AS hit_ratio_procent
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;

Со овој прашалник може да видиме за секоја табела во FilmRentalDB колку читања дошле од кешот, а колку биле потребни читања од диск.

SELECT
    datname,
    ROUND(
        100.0 * blks_hit /
        NULLIF(blks_hit + blks_read, 0),
    2) AS hit_ratio
FROM pg_stat_database;

Со овој прашалник може да го видиме процентот на читања за секоја база кои доаѓале од кешот во однос на читањата кои доаѓале од диск.

SELECT
  relname AS tabela,
  indexrelname AS indeks,
  idx_blks_read AS citanja_od_disk,
  idx_blks_hit AS citanja_od_kesh,
  ROUND(
    idx_blks_hit::numeric / NULLIF(idx_blks_hit + idx_blks_read, 0) * 100, 2
  ) AS hit_ratio_procent
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC;

Со овој прашалник може да видиме за секој индекс во FilmRentalDB колку читања дошле од кешот, а колку биле потребни читања од диск.

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT
  c.relname AS relacija,
  c.relkind AS tip,
  COUNT(*) AS strani_vo_kesh,
  ROUND(COUNT(*) * 8192.0 / (1024 * 1024), 2) AS mb_vo_kesh
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND n.nspname = 'public'
GROUP BY c.relname, c.relkind
ORDER BY strani_vo_kesh DESC;

Со овој прашалник може да видиме кои табели и индекси од FilmRentalDB моментално заземаат место во shared buffers и колку MB зафаќаат.

Cold vs Hot cache

Во овој дел е демонстрирано како PostgreSQL при првото извршување на прашалникот ги нема складирано потребните податоци во кеш (shared_buffers), па затоа ги чита од диск (cold cache). При второто извршување на истото прашање, истите страници веќе се наоѓаат во меморија (hot cache), па PostgreSQL ги користи директно од кешот без повторно читање од диск. За тестирање беше користено следното прашање:

SELECT *
FROM rental
WHERE customer_id BETWEEN 100000 AND 300000;

shared_buffers параметарот беше наместен на 4GB.

При првото извршување (cold run) во резултатот од EXPLAIN се појавува голема вредност за shared read, што значи дека страниците се вчитуваат од диск во меморија. Во нашиот случај беа прочитани 105353 страници. Бидејќи PostgreSQL користи страници со големина од 8KB, тоа значи дека се вчитани приближно 823MB податоци ((105353 страници x 8KB) / 1024 ~ 823MB).

Buffers: shared hit=3 read=105353
Execution Time: 1085.069 ms

При второто извршување (warm run) се појавува shared hit наместо shared read, што значи дека истите страници се користат директно од кешот (shared_buffers) без дополнително читање од диск. Извршувањето е побрзо бидејќи нема I/O операција.

Buffers: shared hit=105353
Execution Time: 651.663 ms

Ова резултира со намалување на времето на извршување од 1085 ms на 651 ms, што претставува приближно 40% подобрување на перформансите.