Ako zlepšiť výkon PostgreSQL? Vyskúšaj optimalizáciu databázy a dopytov!

⚠️ UPOZORNENIE: Tento článok sa čiastočne zaoberá nastaveniami servera Postgres. Pri práci s nimi je potrebné pochopiť, že zmena takýchto nastavení môže viesť k zlepšeniu aj zhoršeniu výkonu servera.

Zdieľaná vyrovnávacia pamäť

PostgreSQL využíva na svoju prácu dve vyrovnávacie pamäte, prvou z nich je zdieľaná vyrovnávacia pamäť - natívna vyrovnávacia pamäť PSQL pracujúca na princípe Clock Sweeo Angry. Druhým je vyrovnávacia pamäť OS - vyrovnávacia pamäť operačného systému. OS cache väčšinou funguje na princípe algoritmu Least Recent.

‍

Hlavnou výhodou zdieľanej vyrovnávacej pamäte je, že napriek vyrovnávacej pamäti OS dokáže definovať, ktoré dáta sa používajú najčastejšie a uchovávať ich v pamäti oveľa dlhšie, a má skóre obľúbenosti od 1 do 5. Ak je skóre vyššie, tak sa dáta rýchlejšie odstránia z vyrovnávacej pamäte. To je dôvod, prečo sú dotazy, ktoré prechádzajú cez zdieľanú vyrovnávaciu pamäť, vždy lepšie obsluhované.

‍

Hlavným problémom zdieľanej vyrovnávacej pamäte je, že je optimalizovaná len na to, aby server mohol bootovať na systéme s nízkymi HW požiadavkami. Predvolená hodnota pre zdieľanú vyrovnávaciu pamäť v postgresql.conf je 128 MB (názov parametra je shared_buffer ) Predpokladá sa, že je to jeden z najťažších parametrov na predpovedanie dobrej hodnoty. Ak je server pomalý, podľa zásady je dobré dať databáze 25% RAM (po zmene hodnoty je potrebné reštartovať psql server - ~$ sudo služba postgresql restart ).

‍

‍

Aby sme lepšie vedeli predpovedať hodnoty pre tento parameter sa odporúča použiť pgbench - je to jednoduchý program na spúšťanie benchmarkových testov na PostgreSQL. Spúšťa rovnakú sekvenciu príkazov SQL znova a znova vo viacerých súbežných databázových reláciách, a potom vypočíta priemernú rýchlosť transakcie (transakcie za sekundu).

‍

Najprv musíme vytvoriť databázu väčšiu ako je zdieľaná vyrovnávacia pamäť a spustiť určité množstvo transakcií (v našom prípade 8 používateľov vykoná 25 000 transakcií):

~$ sudo -i -u postgres

~$ createdb test_buffers;

~$ pgbench -i -s 50 testovacích_bufferov;

~$ pgbench -S -c 8 -t 25000 testovacích_bufferov;

‍

Teraz pomocou buffercache môžeme získať najväčší vzťah a niektoré ďalšie údaje inštaláciou rozšírenia buffercahce a spustením nasledujúceho SELECT:

-- Install buffercache extention
CREATE EXTENSION pg_buffercache;
 
-- Get the largest relation names and buffer content summary
SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
   (SELECT setting FROM pg_settings
     WHERE name='shared_buffers')::integer,1)
   AS buffers_percent,
 round(100.0 * count(*) * 8192 /
   pg_table_size(c.oid),1)
   AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
   ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
   ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 10;

‍

Na obrázku „a“ môžeme vidieť, že tabuľka accounts a tabuľka accounts_pkey celkovo zaberajú takmer celú veľkosť zdieľanej vyrovnávacej pamäte, 119 MB, čo je viac ako 92 % celej zdieľanej vyrovnávacej pamäte. V stĺpci percent_of_relation je možné vidieť, že je veľmi dôležité, aby server uchovával tabuľku accounts_pkey v pamäti, napriek tomu, že tabuľka s účtami je uložená vo vyrovnávacej pamäti na 10,4 %, táto je uložená na 49,5 %. Po zvýšení veľkosti zdieľanej vyrovnávacej pamäte na 256 MB je možné vo výsledku „b“ vidieť, že tabuľka accounts_pkey má vyrovnávaciu pamäť na 89,7 %.

‍

Druhý dotaz zobrazuje počet použití podľa vzťahu:

-- usages count by relation
SELECT
c.relname, count(*) AS buffers,usagecount
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname, usagecount;

‍

‍

‍

Usage counter môže povedať veľa o zdieľanej vyrovnávacej pamäti. Ak má väčšina vzťahov malý usage caounter, napríklad 0 alebo 1, pravdepodobne bude dobré zväčšiť veľkosť zdieľanej vyrovnávacej pamäte. Po zväčšení veľkosti zdieľanej vyrovnávacej pamäte môžeme na obrázku „b“ vidieť, že sa objavilo viac populárnych stránok a je lepšia rovnováha medzi nízkou a vysokou využívanosťou. Ak vidíme vysokú akumuláciu vyrovnávacích pamätí s počtom využitia, znamená to, že shared_buffer je celkom dobre optimalizovaný a ďalej sa zväčšuje, ak jeho veľkosť nepovedie k lepšiemu výkonu.

Rovnaký súbor údajov bol testovaný pomocou nasledujúceho príkazu: ~$ pgbench -S -c 50 -t 25000 test_buffers; Výsledkom bolo:

• 128 MB zdieľanej vyrovnávacej pamäte ~ 140 ms čas pripojenia;
• 256 MB zdieľanej vyrovnávacej pamäte ~ 138 ms čas pripojenia;
• 1024 MB zdieľanej vyrovnávacej pamäte ~ 133 ms čas pripojenia;

WAL writer

WAL writer (Write Ahead Logging Write) je mechanizmus, ktorý ukladá neuložené údaje. Všetky segmenty, ktoré sa odohrávajú na serveri, sa aj tak zaznamenávajú do segmentov WAL. Je tiež zodpovedný za:

• Recovery;
• Spustenie servera;
• Replikáciu.

Vypnutie WAL writera (názov parametra: fsync ) zvyšuje výkon DB najmä pri hromadných operáciách, ale vedie k takým problémom, ako je nekonzistentnosť údajov v prípade výpadku napájania alebo zlyhania.

Najdôležitejšie premenné na nastavenie sú:

• max_wal_size - definuje mäkký limit pre celkovú veľkosť WAL;
• min_wal_size - definuje limit pre minimálnu veľkosť WAL;

Checkpointy

Kontrolné body sú body v sekvencii transakcií, pri ktorých je zaručené, že dátové súbory boli aktualizované so všetkými informáciami zapísanými pred týmto kontrolným bodom v sekvencii WAL. V čase kontrolného bodu sa všetky nečisté stránky s údajmi zdieľanej vyrovnávacej pamäte vyprázdnia na disk, označia sa ako čisté a do log súboru sa zapíše špeciálny záznam kontrolného bodu.

Zaručuje, že pri akomkoľvek korupcií dát/straty napájania sa uložia všetky údaje pred kontrolným bodom.

‍

‍

‍

Existuje niekoľko premenných, ktorých použitie môže zlepšiť výkon:

• checkpoint_flush_after - Predvolená hodnota je 256 kB (32 stránok) na Linuxe, 0 inde - ukladá dáta po definovanom počte stránok;
• checkpoint_timeout - predvolená hodnota je 5 minút - ukladá dáta po určitom čase;
• checkpoint_completion_target - predvolene 0,9 - určuje cieľ dokončenia kontrolného bodu, povie postgresu, ako rýchlo musí dokončiť kontrolný bod v každej iterácii.

Je veľmi jednoduché skontrolovať konfiguráciu WAL writera a kontrolných bodov:

-- Check WAL and checkpoints parameters
SELECT name, setting
FROM pg_settings
WHERE name LIKE '%wal_size%'
  OR name LIKE '%checkpoint%' ORDER BY name;

‍

‍

Na zlepšenie výkonu hromadných dátových operácií je potrebné zväčšiť veľkosť max_wal_size, checkpoint_flush_after , checkpoint_completion_target a checkpoint_timeout .

Po zvýšení hodnoty takého parametra ako checkpoint_timeout z 5 min na 20 min sa počet diskových I/O operácií zníži, ale samotné operácie zaberú oveľa viac času. Zníženie hodnoty bude mať opačný efekt.

Zvýšenie času kontrolného bodu má ešte jeden vplyv. Ak má DB veľa DML (INSERT / UPDATE / DELETE) dotazov a kontrolné body sa vyskytujú veľmi zriedkavo, potom existuje riziko, že zdieľaná vyrovnávacia pamäť bude zaplnená údajmi, ktoré je potrebné zapísať alebo budú operácie zápisu veľkého množstva údajov ovplyvniť ďalšie otázky.

Vo väčšine prípadov nám postgres povie, kedy treba niektoré z týchto parametrov zmeniť. Bude potrebné zapnúť inštrukciu logovania.

• LOG: kontrolné body sa vyskytujú príliš často (9 sekúnd od seba)
• TIP: Zvážte zvýšenie konfiguračného parametra "max_wal_size".
• LOG: kontrolné body sa vyskytujú príliš často (2 sekundy od seba)
• TIP: Zvážte zvýšenie konfiguračného parametra "max_wal_size".

The background writer

Beží ako samostatný serverový proces, jeho funkciou je vydávať zápisy „špinavých“ (nových alebo upravených) zdieľaných vyrovnávacích pamätí. Keď je nedostatok čistých vyrovnávacích pamätí, vezme niekoľko špinavých a zapíše ich do súborov a označí ich ako čisté. Takýto prístup znižuje pravdepodobnosť, že serverové procesy spracovávajúce používateľské dopyty nebudú schopné nájsť čisté vyrovnávacie pamäte a budú samy musieť zapisovať špinavé vyrovnávacie pamäte. Hlavnou úlohou je teda nechať ostatné procesy, aby sa venovali svojej činnosti, namiesto vyčistenia vyrovnávacej pamäte, keď neexistujú žiadne čisté stránky.

Obrovskou nevýhodou je, že zapisovač na pozadí spôsobuje celkové čisté zvýšenie záťaže I/O. Napriek kontrolným bodom zapisovateľ na pozadí napíše všetky opakovane znečistené stránky.

• bgwriter_delay - štandardne 200 ms - oneskorenie medzi zápismi;
• bgwriter_lru_maxpages – max. č. vyrovnávacích pamätí, ktoré budú zapísané procesom v každej iterácii

Vacuum

Databáza v skutočnosti okamžite neodstráni ani neaktualizuje žiadne riadky pri operáciách UPDATE alebo DELETE. Tieto riadky sú označené iba ako odstránené. Hlavnou úlohou vákua je označiť takéto riadky ako riadky na opätovné použitie, výsledkom čoho je, že tabuľky zaberajú menej miesta na disku (voľné miesto sa väčšinou nevracia do operačného systému, ale bude znovu použité) a zrýchli sa dopytovanie.

‍

Existujú dva typy vákua, prvý je autovakuum – robí všetko popísané vyššie a má nasledujúce nastavenia:

• autovaacuum_naptime - predvolená hodnota 60s - skontroluje, či existuje nejaká práca pre autovacuum;
• autovacuum_max_workers - predvolene 3 - max. autovakuoví pracovníci pre jednu databázu;
• autovacuum_vacuum_threshold - predvolených 50 n-tic - minimálny počet aktualizovaných alebo odstránených n-tic potrebných na spustenie vákua v ktorejkoľvek tabuľke;
• autovacuum_vacuum_scale_factor = predvolená hodnota 0,2 (20 %) – zlomok veľkosti tabuľky, ktorý sa pridá k autovacuum_vacuum_threshold pri rozhodovaní, či spustiť VACUUM

‍

Príklady vacuum-u:

-- Create a new table
CREATE TABLE vacuum_test (id int) WITH (autovacuum_enabled = off);
 
-- Insert data inside
INSERT INTO vacuum_test SELECT * FROM generate_series(1, 100000);
 
-- Check table size
SELECT pg_size_pretty(pg_relation_size('vacuum_test'));
 
--  Update each value
UPDATE vacuum_test SET id = id+1;
 
-- Check table size again
SELECT pg_size_pretty(pg_relation_size('vacuum_test'));
 
-- Vacuum the table
VACUUM vacuum_test;
 
-- Check table size again
SELECT pg_size_pretty(pg_relation_size('vacuum_test'));

‍

V dôsledku toho sa voľné miesto nevráti operačnému systému a server DB bude mať určité množstvo voľného miesta, takže v určitom množstve ďalších operácií nebude DB požadovať nový voľný priestor od OS.

Druhým typom vacuum cleaning je VACUUM FULL. Prepisuje údaje do nových súborov, štruktúruje a mení poradie údajov tabuľky a umožňuje vrátiť voľné miesto operačnému systému. Vyžaduje si to špeciálny zámok na prístup k tabulke, takže je lepšie nastaviť VACUUM FULL, keď sa server používa menej. Veľa času mu zaberie aj beh. Najdôležitejším bodom, prečo sa musí použiť tento druh vákua, je to, že aktualizuje štatistiky údajov, ktoré používa plánovač. Umožňuje plánovačubyť efektívnejší. Tiež je užitočné vyskúšať VACUUM FULL, keď je očakávaný čas vykonania veľmi odlišný od skutočného.

Parallelism

Paralelnosť umožňuje spúšťanie dotazov na niekoľkých CPU. Najužitočnejšie je použitie paralelizmu pre dotazy, ktoré zhromažďujú údaje z mnohých tabuliek, ale vracajú len niekoľko riadkov. Takéto dopyty môžu byť 2- alebo dokonca 4-krát rýchlejšie. Ktorý dotaz bude prebiehať paralelne, určuje plánovač.

• min_paralel_table_size - predvolená veľkosť 8 megabajtov - minimálna veľkosť tabuľky pre paralelné skenovanie;
• max_paralel_workers_per_geather - max. počet paralelných pracovníkov - nikdy sa nepoužije pre malé stoly. Je možné nastaviť max. počty paralelných pracovníkov pre presne jeden stôl;

Maximálny počet paralelných pracovníkov je obmedzený dvoma premennými:

• max_workers_processes – koľko pracovných procesov je celkovo dostupných;
• max_paralel_workers – koľko pracovníkov je dostupných pre paralelné dotazy.

Čas vykonania príkazu ako “ EXPLAIN ANALYZE SELECT count(*) FROM pgbench_accounts; ” s jedným paralelným pracovníkom je viac ako 320 ms, ale sedem, ako je možné vidieť na obrázku nižšie, sa dá skrátiť na 192 ms. Maximálny počet paralelných robotníkov pre druhý príklad je desať, ale používa sa ich len sedem, pretože veľkosť stola nie je dostatočná na použitie všetkých desiatich robotníkov.

‍

‍

‍

‍

Indexes

Po prvé, všetky indexy musia byť použité iba pre stĺpce s vysokou mohutnosťou (percento jedinečných hodnôt), napríklad stĺpec id má 100 % mohutnosť, pretože každá hodnota je jedinečná. Postgresql štandardne pridáva index B-Stromu do stĺpca id každej tabuľky.

‍

Indexy B-Stromu dokážu spracovať otázky rovnosti a rozsahu údajov, ktoré možno zoradiť do určitého usporiadania. Dobrý nápad je použiť B-Strom pre jedinečný stĺpec, napríklad s e-mailmi. Ale dopyty zahŕňajúce vzory, ako je tento " col LIKE '%bar' ." nebudú spustené pomocou indexov.

‍

PostgreSQL obsahuje množstvo ďalších indexov z indexov HASH, ktoré ukladajú dáta ako 32-bitový hash a dajú sa použiť iba pri porovnávaní rovnosti s indexmi SP-GIST, ktoré podporujú rôzne druhy vyhľadávaní (napríklad „najbližší sused“) a umožňujú implementáciu rôznych dátové štruktúry.

‍

PostgreSQL podporuje množstvo rôznych indexových skenov. Prvým z nich je bežné skenovanie indexu, ktoré vracia údaje podľa indexu. Ďalším je skenovanie iba indexu; to znamená, že potrebné údaje je možné vrátiť z indexového súboru aj bez otvorenia súboru údajov tabuľky.

‍

V prípadoch, keď je počet vrátených riadkov príliš malý na sekvenčné skenovanie a príliš veľký na skenovanie indexu, postgresql používa skenovanie bitovej mapy. Ide o kombináciu sekvenčného skenovania a indexového skenovania.

‍

Veľmi dobrou praxou je indexovanie cudzích kľúčov. Je to veľmi užitočné vo vzťahoch medzi rodičmi a deťmi, kde je podriadená tabuľka väčšia ako rodičovská. Vytvorme tabuľku objednávok s 1 000 000 riadkami, kde každá surovina bude mať 4 položky z tabuľky položiek:

‍

-- Create orders table
CREATE TABLE orders (id SERIAL PRIMARY KEY, order_date DATE);
 
-- Create items table
CREATE TABLE items (
   id SERIAL NOT NULL,
   order_id SERIAL,
   name VARCHAR,
   description VARCHAR,
   created_at TIMESTAMP,
   CONSTRAINT fk_items FOREIGN KEY (order_id)
   REFERENCES orders(id));
 
-- Insert the data
WITH order_rows AS (
   INSERT INTO orders(id, order_date)
          SELECT generate_series(1, 1000000), now()
          RETURNING id
)
INSERT INTO items(id, order_id, name, description, created_at)
   SELECT generate_series(1, 4) id, o.id, 'product',
          repeat('the description', 10), now()
FROM order_rows AS o;
 
-- Make a select
EXPLAIN SELECT * FROM orders
JOIN items i on orders.id = i.order_id
WHERE orders.id = 666666;

‍

Priemerný čas odozvy na DELL G15 so systémom Kubuntu 22.04 bol od 400 ms do 600 ms, ale po vytvorení indexu v stĺpci cudzieho kľúča sa znížil na 40-60 ms pre rovnaký dotaz:

-- Create and index of FK column
CREATE INDEX item_fk_index ON items(order_id);
 
-- Make a select
SELECT * FROM orders
JOIN items i on orders.id = i.order_id
WHERE orders.id = 666666;

‍

Dokonca aj pri 25% mohutnosti je vykonávanie dotazu takmer 5-krát rýchlejšie. Rovnaký výsledok bol s 10% mohutnosťou. Indexovanie cudzích kľúčov je veľmi užitočné, keď máme tabuľky s veľkým množstvom údajov, inak plánovač namiesto indexového skenovania zvolí sekvenčné skenovanie.

-- Cardinality check
SELECT (count(DISTINCT order_id)::FLOAT / count(*)) * 100 AS cardinality
FROM items;

‍

V prípade, že máme tabuľku s nejakým druhom transakcií, ale aktívne sa využívajú len neukončené, môžeme vytvoriť index len pre určité riadky, napríklad pre tie, kde nie je stav transakcie ukončený. Ďalšou výhodou takéhoto indexu je, že zaberá menej miesta na disku.

Indexy sa zvyčajne používajú na načítanie malého množstva údajov, ale v závislosti od frekvencie dotazov a času odozvy niekedy bude rozumné vytvoriť index zo stĺpca, ktorý používa ORDER BY s LIMIT. ORDER BY je sám o sebe veľmi náročný príkaz a vyžaduje veľa času na jeho vykonanie. V ďalšom prípade je uvedený príklad použitia indexu spolu s ORDER BY:

-- Select data
EXPLAIN ANALYZE SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 25;
 
-- Create index
CREATE INDEX order_date_index ON orders(order_date);
 
-- Select data after creating index
EXPLAIN ANALYZE SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 25;

‍

Napriek tomu, že mohutnosť stĺpca order_date je menšia ako 1%, vykonávanie dopytu je teraz 2-4x rýchlejšie. Je to vďaka tomu, že dáta sú už triedené v indexovom súbore.