Lēni PostgreSQL vaicājumi: praktisks optimizācijas kontrolsaraksts

Kad PostgreSQL datubāze kļūst lēna, pirmā doma parasti ir palielināt resursus: vairāk CPU, vairāk atmiņas, lielāka instance. Dažreiz tas ir pareizais lēmums. Daudz biežāk datubāze lielāko daļu laika tērē nelielam skaitam vaicājumu, kurus var padarīt krietni lētākus — un jaudīgāks serveris tikai padara to pašu izšķērdību dārgāku.

Šis ir kontrolsaraksts, ko izejam veiktspējas projektos, aptuveni šādā secībā. Tas sakārtots pēc tā, cik bieži katrs solis izrādās īstā problēma, nevis pēc tā, cik tas ir interesants.

1. Atrodiet vaicājumus, kas patiešām patērē laiku

Nesāciet ar vaicājumu, par kuru kāds sūdzējās. Sāciet ar to, kur datubāze kopumā pavada laiku. Paplašinājums pg_stat_statements apkopo izpildes statistiku katram normalizētam vaicājumam. Pievienojiet to shared_preload_libraries, izpildiet CREATE EXTENSION pg_stat_statements un kārtojiet pēc kopējā laika:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Saraksta augšgalā parādās divas ļoti atšķirīgas problēmas. Vaicājumam, kas izpildās 4 ms, bet tiek izsaukts 30 miljonus reižu dienā, vajag citu risinājumu nekā 40 sekunžu atskaitei, kas tiek palaista divreiz. total_exec_time parāda, kuri vaicājumi ir svarīgi; mean_exec_time un calls — kāda veida problēma jums ir.

2. Lasiet izpildes plānu ar reāliem skaitļiem

Katram dārgajam vaicājumam izpildiet EXPLAIN (ANALYZE, BUFFERS) uz reālistiskas datu kopijas — nekad uz gandrīz tukšas testa datubāzes, kur katrs plāns izskatās ātrs. Atcerieties, ka ANALYZE vaicājumu tiešām izpilda, tāpēc rakstīšanas operācijas ietiniet transakcijā un atceliet to. Meklējiet:

• Kļūdainus rindu novērtējumus. Ja plānotājs gaidīja 50 rindas, bet saņēma 500 000, viss, kas balstīts uz šo novērtējumu — savienojumu secība, savienojuma metode, atmiņas izmantojums —, visticamāk, ir nepareizs.
• Secīgu skenēšanu lielās tabulās vaicājumos, kas atgriež tikai dažas rindas.
• Kārtošanu un jaukšanu, kas izlīst uz diska. Sort Method: external merge nozīmē, ka operācija neietilpa work_mem.
• Daudz nolasītu buferu uz maz atgrieztām rindām — pazīme, ka vaicājums aizskar daudz vairāk datu, nekā vajag.

3. Sakārtojiet indeksus — arī tos, kuru nevajadzētu būt

Lielākie ieguvumi parasti nāk tieši no šejienes. Visbiežāk sastopamie gadījumi:

• Saliktie indeksi ar nepareizu kolonnu secību. Indekss uz (created_at, customer_id) maz palīdz vaicājumam WHERE customer_id = $1 ORDER BY created_at DESC; indekss uz (customer_id, created_at) apkalpo gan filtru, gan kārtošanu.
• Funkcijas uz indeksētām kolonnām. WHERE lower(email) = … nevar izmantot parastu indeksu uz email; vajag izteiksmes indeksu uz lower(email).
• Trūkstoši daļējie indeksi. Ja gandrīz katrs vaicājums filtrē pēc status = 'active', indekss ar šādu nosacījumu ir mazāks un ātrāks nekā indekss pār visām rindām.
• Neizmantoti un dublēti indeksi. Katrs indekss palēnina rakstīšanu un vakuumēšanu. Indeksi ar idx_scan = 0 skatā pg_stat_user_indexes reprezentatīvā periodā ir kandidāti dzēšanai.

4. Nodrošiniet plānotājam precīzu statistiku

Slikti rindu novērtējumi parasti rodas no novecojušas vai pārāk rupjas statistikas. Pārbaudiet, kad lielās tabulas pēdējo reizi analizētas, palieliniet statistikas mērķi kolonnām ar nevienmērīgu sadalījumu, un korelētām kolonnām — piemēram, pilsētai un pasta indeksam — izveidojiet paplašināto statistiku ar CREATE STATISTICS, lai plānotājs vairs nepieņemtu, ka tās ir neatkarīgas.

5. Pārbaudiet, vai autovacuum tiek līdzi

Noslogotās tabulās noklusējuma autovacuum iestatījumi bieži ir pārāk piesardzīgi. Rezultāts ir uzpūšanās: vaicājumi nolasa vairāk lapu, lai atgrieztu tās pašas rindas, un tikai indeksa skenēšana pasliktinās, jo redzamības karte atpaliek. Sekojiet n_dead_tup skatā pg_stat_user_tables un noslogotākajām tabulām pielāgojiet autovacuum katrai atsevišķi, nevis globāli.

6. Tikai tad — atmiņa un konfigurācija

Konfigurācija ir svarīga, bet tā reti ir pirmais labojums. Iestatījumi, kuriem visbiežāk jāpievērš uzmanība:

• shared_buffers — aptuveni ceturtdaļa no RAM ir ierasts sākumpunkts uz dedicēta servera.
• work_mem — palieliniet uzmanīgi, ja kārtošana izlīst uz diska. Tā tiek piešķirta katrai operācijai un katram savienojumam, tāpēc liela vērtība, reizināta ar simtiem savienojumu, var iztērēt visu atmiņu.
• random_page_cost — uz SSD krātuves tās samazināšana tuvāk 1,1 palīdz plānotājam izvēlēties indeksa skenēšanu, kad tā patiešām ir lētāka.
• Savienojumu skaits. Simtiem lielākoties dīkstāvē esošu savienojumu patērē atmiņu un rada konkurenci. Savienojumu pūls, piemēram, PgBouncer, parasti palīdz vairāk nekā jebkurš atsevišķs parametrs.

7. Skatieties uz lietotni, ne tikai uz datubāzi

Dažas no dārgākajām "datubāzes problēmām" patiesībā ir piekļuves modeļi: ORM ģenerēti N+1 vaicājumi, lapošana ar lielām OFFSET vērtībām vai veselu rindu ielāde, kad tiek izmantotas divas kolonnas. Tās neparādās kā viens lēns vaicājums — tās parādās kā ātrs vaicājums ar milzīgu izsaukumu skaitu pg_stat_statements.