Výkonnostní spiky v trvání queries v Postgres

[registrovany123]

Ahoj,

vím že tady občas zavítají experti na Postgres, tak třeba budu mít štěstí a pomůžou.

Mám v databázi ve 2 tabulkách asi 6 - 10 mil. záznamů, a service zpracovává přibližně 1 request za vteřinu, načež v rámci toho jednoho requestu se udělá asi tak 8 jednoduchých selectů a 2 inserty.

Žádné další requesty do service ani do databáze nechodí. Tedy databáze zpracovává jen výše uvedené requesty.

99% requestů je hotových do 50ms, ale 1% requestů má spike, v délce trvání 150ms - 400ms.

Měřením jsem zjistil, že k těmto spikům dochází jak v AWS RDS se 4GB RAM a 2 vcpu, tak i na mém localhostu, kde mám Postgres rozjeto v Dockeru, opět se 4GB RAM a 2 vcpu.

Dále jsem měřením zjistil, že spike se může objevit na libovolném query, a to jak na selectu, tak na insertu, tedy z toho usuzuji, že v queries problém není, ostatně, průměrná délka requestu je jen nějakých 30ms, což optimální queries potvrzuje.

Všechny query mají řádně udělané potřebné indexy a query plany jsou zkontrolovány a jsou optimální.

Měl jsem podezření, že za spiky můžou checkpointy, avšak nakolik se spike vyskytne pravidelné každých 2-10 minut, checkpoint mám za to že je přecejen pravidelnější, každých 5 minut, a takovoutu železnou pravidelnost se nezdá, že by spiky měly.

V databázovém logu vidím vypsané queries a checkpointy, nikdy zde však nic podezřelého nepozoruji, všechno se zdá být standardní a opakující se s každým requestem, nikde žádný hint.

Nesetkal se s tím někdo už?

[Reklama]

[Pavel Rauš]

Začal bych přidáním rozšíření pg_stat_statements, budete mít přesná data a uvidíte co přesně se Vám tam děje.

A jen hinty:
máte shared_buffers velký adekvátně velikosti dat?
máte dostatečně velkou work_mem?
jak velký máte bloat v indexech?
neběží Vám tam v tu dobu něco jiného, co ovlivní výkon nebo obsah bufferu, .... třeba autovacuum je kandidát, může být nastaveno příliš agresivně.

[Pavel Stěhule]

Zkusil bych jednoduchý select pouštěný z psql. Ta latence může vzniknout kdekoliv na jakékoliv vrstvě. To může dělat Java, podobné peeky jsem viděl a dělala to virtualizace. Zkuste z bashe v cyklu pustit SELECT 1 a sleep 1 .. pokud dochází k latencím v Postgresu, tak SELECT 1 by se měl zpomalit také. Můžete zkoušet odkud tento skript pustit. 100ms latenci vám může udělat síť, firewall, ...

[luvar]

Ak mate dost kapacity skusat, skuste analyzovat logy a hlavne zapnut viac logovania. Osobne som (hadam pred 6 rokmi) pouzival na produkcii https://github.com/darold/pgbadger . Teda na produkcii sa logovalo, ale analyza logov sa robila na inom stroji pravidelne asi raz tyzdenne a pozretie sa do reportu pomohlo odhalit zavedene drobne chyby.
Mozno Vas to niekam nasmeruje.

PS: Monitorovat ping , alebo ine vrstvy podobnym sposobom, moze byt tiez prospesne. Klnudne to moze skoncit na veci ako "esxi si raz za den uspi stroj na 7 sekund, lebo backup je tak nakonfigurovany a potom ntp riesi problem s posunom casu"... (teda ori localhostovom dockeri asi nie a ani pri aws. Skor myslim typovo na nejakej takejto haluzi)

[Pavel Rauš]

Zkusil bych jednoduchý select pouštěný z psql. Ta latence může vzniknout kdekoliv na jakékoliv vrstvě. To může dělat Java, podobné peeky jsem viděl a dělala to virtualizace. Zkuste z bashe v cyklu pustit SELECT 1 a sleep 1 .. pokud dochází k latencím v Postgresu, tak SELECT 1 by se měl zpomalit také. Můžete zkoušet odkud tento skript pustit. 100ms latenci vám může udělat síť, firewall, ...

Pán píše, že se mu to takto chová i na localhostu, kde mu to běží v dockeru. V tomhle scénáři je síťová vrstva asi nepravděpodobná.

[Reklama]

[Pavel Stěhule]

Zkusil bych jednoduchý select pouštěný z psql. Ta latence může vzniknout kdekoliv na jakékoliv vrstvě. To může dělat Java, podobné peeky jsem viděl a dělala to virtualizace. Zkuste z bashe v cyklu pustit SELECT 1 a sleep 1 .. pokud dochází k latencím v Postgresu, tak SELECT 1 by se měl zpomalit také. Můžete zkoušet odkud tento skript pustit. 100ms latenci vám může udělat síť, firewall, ...

Pán píše, že se mu to takto chová i na localhostu, kde mu to běží v dockeru. V tomhle scénáři je síťová vrstva asi nepravděpodobná.

Pokud si je jistý, že všude komunikuje přes socket, tak tam asi ne. Někde se může použít TCP vůči localhostu - a už jsem slyšel, že problémy dělalo třeba problematické DNSko. Je otázkou, co je pomalé - jestli samotný dotaz, nebo připojení do pg. S virtualizací už jsem viděl tolik problémů

[Wavelet]

Zkusil bych jednoduchý select pouštěný z psql. Ta latence může vzniknout kdekoliv na jakékoliv vrstvě. To může dělat Java, podobné peeky jsem viděl a dělala to virtualizace. Zkuste z bashe v cyklu pustit SELECT 1 a sleep 1 .. pokud dochází k latencím v Postgresu, tak SELECT 1 by se měl zpomalit také. Můžete zkoušet odkud tento skript pustit. 100ms latenci vám může udělat síť, firewall, ...

Pán píše, že se mu to takto chová i na localhostu, kde mu to běží v dockeru. V tomhle scénáři je síťová vrstva asi nepravděpodobná.

Pokud si je jistý, že všude komunikuje přes socket, tak tam asi ne. Někde se může použít TCP vůči localhostu - a už jsem slyšel, že problémy dělalo třeba problematické DNSko. Je otázkou, co je pomalé - jestli samotný dotaz, nebo připojení do pg. S virtualizací už jsem viděl tolik problémů

Nechci zakládat další vlákno, ale jaký je Váš názor na PostgreSQL v kontejneru? Dříve myslím nedoporučované pro databáze obecně, ale viděl jsem to už na několika produkcích.   

[Pavel Stěhule]

Nechci zakládat další vlákno, ale jaký je Váš názor na PostgreSQL v kontejneru? Dříve myslím nedoporučované pro databáze obecně, ale viděl jsem to už na několika produkcích.

Já to zrovna nemusím - a třeba provozovat kontejner třeba čistě pro Postgres mi přijde úlet (zvlášť na produkci - na testovacích prostředích nebo v lokále na vývoj to může být něco jiného). Praxe je, že když se to rozjede a nejsou s tím problémy, tak to běží. Ale pokud tam nějaké problémy jsou, tak je to další vrstva s kterou musíte počítat, a kde ty problémy jsou hezky skryté. Někdy jsou přímo zdrojem problémů, jindy multiplikují problémy na db úrovni. Virtualizace se běžně používá, pokud máte server s 0.5TB RAM a nemáte db, kterou byste to využili. Ale správně to nakonfigurovat nemusí být legrace. Mám zákazníka, kde db provozovali několik let ve virtualizaci s naprosto tragickým výkonem - fixlo se to spíš náhodně po 3 měsících analýz, kdy já už jsem byl v koncích a stejně se tam objevovala magická chyba, která se fixla až další rok. Teď už to dva roky běží suprově bez problémů.

[Tomáš Crhonek]

Já to zrovna nemusím - a třeba provozovat kontejner třeba čistě pro Postgres mi přijde úlet (zvlášť na produkci - na testovacích prostředích nebo v lokále na vývoj to může být něco jiného). Praxe je, že když se to rozjede a nejsou s tím problémy, tak to běží. Ale pokud tam nějaké problémy jsou, tak je to další vrstva s kterou musíte počítat, a kde ty problémy jsou hezky skryté. Někdy jsou přímo zdrojem problémů, jindy multiplikují problémy na db úrovni. Virtualizace se běžně používá, pokud máte server s 0.5TB RAM a nemáte db, kterou byste to využili. Ale správně to nakonfigurovat nemusí být legrace. Mám zákazníka, kde db provozovali několik let ve virtualizaci s naprosto tragickým výkonem - fixlo se to spíš náhodně po 3 měsících analýz, kdy já už jsem byl v koncích a stejně se tam objevovala magická chyba, která se fixla až další rok. Teď už to dva roky běží suprově bez problémů.

My jsme provozovali PG v vmware 10 let (kdy jsem u toho byl) a žádný problém. Jak jsem psal níže, vždy je potřeba dávat pozor na úložiště. S výkonem disků ve vmware jsme nikdy neměli problém, pokud nebyl problém vyloženě na vrstvě pod tím. Soukromě jsem ještě navíc provozoval KVM, kdy disky virtuálek byly jednotlivé LV v LVM a opět, rychlost nebyla žádný problém.

U virtualizace je obecně vhodné si dát pozor, co dalšího běží v ostatních virtuálkách. Dávat na jedno diskové pole 50 databázových serverů je problém vždy.

Nechci zakládat další vlákno, ale jaký je Váš názor na PostgreSQL v kontejneru? Dříve myslím nedoporučované pro databáze obecně, ale viděl jsem to už na několika produkcích.

Tak záleží na tom, co se přesně myslí tím "kontejner". Já provozuju už skoro 10 let DB servery v kontejnerech, jak na Linuxu (nspawn), tak na FreeBSD (jail). Kontejner je jen oddělený namespace pro procesy. Data se ukládají stejně tak jako tak na disky a u kontejneru odpadá virtualizace úložiště. Takže PG v Jailech ukládá na nativní ZFS. Úplně stejně jako bez kontejnerizace. Pokud nějaká jiná kontejnerizační služba ještě navíc nějak virtualizuje úložiště, tak tam dopad jistě bude.

[registrovany123]

Díky za tipy, musím to ještě přelouskat.

Zatím ale jen řeknu, že lokalizovat kde je problém, jestli síť nebo postgres, to už bych měl, kdyby postgres do logů zapisovalo i milisekundy a nejen sekundy toho, kdy jaký query proběhl.

[Pavel Stěhule]

Díky za tipy, musím to ještě přelouskat.

Zatím ale jen řeknu, že lokalizovat kde je problém, jestli síť nebo postgres, to už bych měl, kdyby postgres do logů zapisovalo i milisekundy a nejen sekundy toho, kdy jaký query proběhl.

Nastavte si log_line_prefix https://www.postgresql.org/docs/current/runtime-config-logging.html %m

[oss]

Podobny problem sme riesili aj mi. Zapnutie viac logovania to cele este k tomu spomalilo. jedine co sa podarilo vylucit bola siet a disky, tak zostal len Potgre.

[Pavel Stěhule]

Podobny problem sme riesili aj mi. Zapnutie viac logovania to cele este k tomu spomalilo. jedine co sa podarilo vylucit bola siet a disky, tak zostal len Potgre.

Jestli je několik málo dotazů za sec, tak i kdyby se logovalo vše, tak by to nic nemělo udělat. RDS má obecně negarantovaný výkon IO, a 4GB RAM stroje jsou na dema - takže garance výkonu IO tam budou minimální. Tam je otázkou  jestli tam aplikačně nedojde k nějakému problému s cache nebo k vynucenému checkpointu. Další věcí může být lagování file systému - občas je nutné na o.s. snížit dirty buffers ratio. Postgres nemá důvod, pokud nečeká na IO protahovat operace na stovky ms.

Určitě bych si napřed ověřil, jestli db není bloatnutá - https://wiki.postgresql.org/wiki/Show_database_bloat případně bych nad db pustil VACUUM FULL. Pár mých zákazníků mělo nedobrý nápad vypnout autovacuum, a zvlášť na slabých strojích to má dost neblahý dopad.

[registrovany123]

.

[Pavel Stěhule]

Díky všem za rady, byl jsem schopný izolovat ten problém. Hodně pomohl ten pgbadger.

Tedy po analýze jsem zjistil, že databáze v AWS RDS se 4GB RAM a 2vCPU všeobecně má problém, když se nad běžným procesováním requestů, které mi tam chodí v průměru 1 za vteřinu, bokem spustí nějaký jiný proces.

Konkrétně se mi spouštěl každých 30 vteřin proces, který dělal SELECT COUNT nad tabulkou, kde je oněch 10 mil. záznamů. Tento proces, pokud se dostal do blbé fáze s procesováním requestů, tak vytvořil spike, který mohl z průměrného času 30ms na request udělat 100-400ms na request.

Tedy velice pozitivní zpráva je, že není postgres asi viník.

No a teď přichází na řadu další věc a to je, co udělat s tím performance databáze, protože není možné, aby spuštění nějakého bočního sql query mi způsobovalo, že se request dostane přes 300ms trvání.

Tedy já se nejspíše vydám cestou partitioningu tabulek dle "id modulo 10", protože jsem s tímto měl dobrou zkušenost na minulém projektu. Dle query planneru se jeví, že Postgres nad partitioned tables spouští query paralelně. Tedy očekávám, že toto by mi mohlo zlepšit výkonnost databáze. Případně potom se vydat cestou silnějšího stroje.

Silnější stroj jsem zkusil dnes, 16GB RAM a 4vCPU, a nakolik to má značný vliv na rychlost zpracování requestů, a běží to "krásněji", neochránilo to před vznikem spiků, načež spike nad 300ms už by znamenal velký problém.

Tedy nezdá se, že by samotné zvýšení RDS vyřešilo ten problém, a proto to chci zkusit zkombinovat s partitioningem té velké tabulky, kde jednou bude 20 mil. záznamů.

To, že výrazně silnější stroj nepomohl - tak by to mohlo znamenat, že buďto tam máte problémy se zámky, nebo utavíte IO. Obecně RDS nabízí IO s málo IOPS, a jsou to stroje spíš na aplikace, které jsou náročné na CPU než na IO. A zkontrolujte si jestli tu tabulku nemáte bloatnutou. Schválně jsem si udělal čerstvou tabulku o 3 sloupcích s 10M řádky. Má 422 mega - SELECT count(*) mi běží 300 ms. Pokud se vám ta tabulka včetně indexů vejde do shared buffers, tak by to ostatní operace nemělo zpomalit. Pokud ne, a máte pomalé IO, tak to vliv mít může. Jinak jestli count chcete dělat často, tak Postgres možná nebude ta správná db - zkuste se podívat na DuckDB. Pár INSERTů a masivní SELECT může byt dobrý use case pro tuhle db. Vzhledem ke komprimaci dat může být tabulka na disku výrazně menší - což znamená, že to vyžaduje i méně IO.