Effekten av uppblåsthet på tabeller och index är allmänt känd och finns inte bara i Postgres. Det finns sätt att hantera det ur lådan, som VACUUM FULL eller CLUSTER, men de låser bord under drift och kan därför inte alltid användas.
Artikeln kommer att innehålla lite teori om hur uppblåsthet uppstår, hur du kan bekämpa det, om uppskjutna begränsningar och de problem de medför för användningen av tillägget pg_repack.
Den här artikeln är skriven utifrån på PgConf.Russia 2020.
Varför uppstår uppblåsthet?
Postgres är baserad på en multiversionsmodell (). Dess kärna är att varje rad i tabellen kan ha flera versioner, medan transaktioner inte ser mer än en av dessa versioner, men inte nödvändigtvis samma. Detta gör att flera transaktioner kan fungera samtidigt och har praktiskt taget ingen inverkan på varandra.
Uppenbarligen måste alla dessa versioner lagras. Postgres arbetar med minne sida för sida och en sida är den minsta mängd data som kan läsas från disk eller skrivas. Låt oss titta på ett litet exempel för att förstå hur detta händer.
Låt oss säga att vi har en tabell som vi har lagt till flera poster till. Ny data har dykt upp på första sidan i filen där tabellen lagras. Dessa är liveversioner av rader som är tillgängliga för andra transaktioner efter en commit (för enkelhetens skull kommer vi att anta att isoleringsnivån är Read Committed).
Vi uppdaterade sedan en av posterna och markerade därmed den gamla versionen som inaktuell.
Steg för steg, uppdatering och radering av radversioner, slutade vi med en sida där ungefär hälften av datan är "skräp". Denna data är inte synlig för någon transaktion.
Postgres har en mekanism , som rensar ut föråldrade versioner och ger plats för ny data. Men om den inte är tillräckligt aggressiv eller upptagen med att arbeta i andra tabeller, så finns "skräpdata" kvar och vi måste använda ytterligare sidor för ny data.
Så i vårt exempel kommer tabellen någon gång att bestå av fyra sidor, men bara hälften av den kommer att innehålla livedata. Som ett resultat kommer vi att läsa mycket mer data än nödvändigt när vi kommer åt tabellen.
Även om VACUUM nu tar bort alla irrelevanta radversioner kommer situationen inte att förbättras dramatiskt. Vi kommer att ha ledigt utrymme på sidor eller till och med hela sidor för nya rader, men vi kommer fortfarande att läsa mer data än nödvändigt.
Förresten, om en helt tom sida (den andra i vårt exempel) fanns i slutet av filen, skulle VACUUM kunna trimma den. Men nu är hon i mitten, så det går inte att göra något med henne.
När antalet sådana tomma eller mycket glesa sidor blir stort, vilket kallas bloat, börjar det påverka prestandan.
Allt som beskrivs ovan är mekaniken för förekomsten av uppblåsthet i tabeller. I index sker detta på ungefär samma sätt.
Har jag uppblåsthet?
Det finns flera sätt att avgöra om du har svullnad. Tanken med den första är att använda intern Postgres-statistik, som innehåller ungefärlig information om antalet rader i tabeller, antalet "live"-rader, etc. Du kan hitta många varianter av färdiga skript på Internet. Vi tog som grund från PostgreSQL Experts, som kan utvärdera uppblåstningstabeller tillsammans med toast- och bloat-btree-index. Enligt vår erfarenhet är dess fel 10-20%.
Ett annat sätt är att använda tillägget , vilket gör att du kan titta inuti sidorna och få både ett uppskattat och ett exakt uppblåst värde. Men i det andra fallet måste du skanna hela tabellen.
Vi anser att ett litet uppblåst värde, upp till 20 %, är acceptabelt. Det kan betraktas som en analog av fillfactor för и . Vid 50 % och över kan prestationsproblem börja.
Sätt att bekämpa uppsvälldhet
Postgres har flera sätt att hantera svullnad ur lådan, men de är inte alltid lämpliga för alla.
Konfigurera AUTOVACUUM så att uppblåsthet inte uppstår. Eller mer exakt, för att hålla den på en nivå som är acceptabel för dig. Detta verkar vara "kaptens" råd, men i verkligheten är detta inte alltid lätt att uppnå. Du har till exempel aktiv utveckling med regelbundna förändringar av dataschemat, eller så pågår någon form av datamigrering. Som ett resultat kan din belastningsprofil ändras ofta och kommer vanligtvis att variera från tabell till tabell. Det betyder att du hela tiden måste arbeta lite framåt och anpassa AUTOVAKUUM till varje bords föränderliga profil. Men detta är uppenbarligen inte lätt att göra.
En annan vanlig anledning till att AUTOVACUUM inte kan hålla jämna steg med tabeller är att det finns långvariga transaktioner som hindrar det från att rensa upp den data som är tillgänglig för dessa transaktioner. Rekommendationen här är också uppenbar - bli av med "dinglande" transaktioner och minimera tiden för aktiva transaktioner. Men om belastningen på din applikation är en hybrid av OLAP och OLTP, så kan du samtidigt ha många frekventa uppdateringar och korta frågor, såväl som långsiktiga operationer - till exempel att bygga en rapport. I en sådan situation är det värt att tänka på att sprida belastningen över olika baser, vilket kommer att möjliggöra mer finjustering av var och en av dem.
Ett annat exempel - även om profilen är homogen, men databasen är under en mycket hög belastning, kanske inte ens den mest aggressiva AUTOVACUUM klarar sig, och uppblåsthet kommer att inträffa. Skalning (vertikal eller horisontell) är den enda lösningen.
Vad du ska göra i en situation där du har ställt in AUTOVACUUM, men uppblåstheten fortsätter att växa.
Team VAKUUM FULL bygger om innehållet i tabeller och index och lämnar endast relevant data i dem. För att eliminera bloat, fungerar det perfekt, men under dess körning fångas ett exklusivt lås på bordet (AccessExclusiveLock), vilket inte kommer att tillåta exekvering av frågor på denna tabell, även selects. Om du har råd att stoppa din tjänst eller en del av den under en tid (från tiotals minuter till flera timmar beroende på storleken på databasen och din hårdvara), så är det här alternativet det bästa. Tyvärr hinner vi inte köra VAKUUM FULL under det schemalagda underhållet, så denna metod är inte lämplig för oss.
Team KLUNGA Bygger om innehållet i tabeller på samma sätt som VACUUM FULL, men låter dig ange ett index enligt vilket data kommer att beställas fysiskt på disk (men i framtiden är ordningen inte garanterad för nya rader). I vissa situationer är detta en bra optimering för ett antal frågor - med att läsa flera poster efter index. Nackdelen med kommandot är densamma som VACUUM FULL - det låser bordet under drift.
Team REINDEXERA liknande de två föregående, men bygger om ett specifikt index eller alla index i tabellen. Låsen är något svagare: ShareLock på bordet (förhindrar ändringar, men tillåter val) och AccessExclusiveLock på indexet som byggs om (blockerar frågor som använder detta index). Men i den 12:e versionen av Postgres dök en parameter upp , som låter dig bygga om indexet utan att blockera samtidig tillägg, ändring eller radering av poster.
I tidigare versioner av Postgres kan du uppnå ett resultat som liknar REINDEX CONCURRENTLY med . Det låter dig skapa ett index utan strikt låsning (ShareUpdateExclusiveLock, som inte stör parallella frågor), ersätt sedan det gamla indexet med ett nytt och ta bort det gamla indexet. Detta gör att du kan eliminera indexuppblåsthet utan att störa din applikation. Det är viktigt att tänka på att när du bygger om index kommer det att bli en extra belastning på diskundersystemet.
Således, om det för index finns sätt att eliminera uppblåsthet "i farten", så finns det inga för tabeller. Det är här olika externa tillägg kommer in i bilden: (tidigare pg_reorg), , och andra. I den här artikeln kommer jag inte att jämföra dem och kommer bara att prata om pg_repack, som vi efter en viss modifiering använder själva.
Hur pg_repack fungerar
Låt oss säga att vi har en helt vanlig tabell – med index, restriktioner och, tyvärr, med uppblåsthet. Det första steget i pg_repack är att skapa en loggtabell för att lagra data om alla ändringar medan den körs. Utlösaren kommer att replikera dessa ändringar för varje infogning, uppdatering och borttagning. Sedan skapas en tabell, liknande den ursprungliga i strukturen, men utan index och begränsningar, för att inte sakta ner processen med att infoga data.
Därefter överför pg_repack data från den gamla tabellen till den nya tabellen, filtrerar automatiskt bort alla irrelevanta rader och skapar sedan index för den nya tabellen. Under utförandet av alla dessa operationer ackumuleras ändringar i loggtabellen.
Nästa steg är att överföra ändringarna till den nya tabellen. Migreringen utförs över flera iterationer, och när det finns färre än 20 poster kvar i loggtabellen, får pg_repack ett starkt lås, migrerar den senaste datan och ersätter den gamla tabellen med den nya i Postgres systemtabeller. Detta är den enda och mycket korta tiden då du inte kommer att kunna arbeta med bordet. Efter detta raderas den gamla tabellen och tabellen med loggar och utrymme frigörs i filsystemet. Processen är klar.
Allt ser bra ut i teorin, men vad händer i praktiken? Vi testade pg_repack utan belastning och under belastning, och kontrollerade dess funktion vid för tidigt stopp (med andra ord med Ctrl+C). Alla tester var positiva.
Vi gick till mataffären - och sedan blev allt inte som vi förväntat oss.
Första pannkakan på rea
På det första klustret fick vi ett felmeddelande om en överträdelse av en unik begränsning:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.
Denna begränsning hade ett automatiskt genererat namn index_16508 - det skapades av pg_repack. Baserat på attributen som ingår i dess sammansättning bestämde vi "vår" begränsning som motsvarar den. Problemet visade sig vara att detta inte är en helt vanlig begränsning, utan en uppskjuten (), dvs. dess verifiering utförs senare än kommandot sql, vilket leder till oväntade konsekvenser.
Uppskjutna begränsningar: varför de behövs och hur de fungerar
En liten teori om uppskjutna restriktioner.
Låt oss överväga ett enkelt exempel: vi har en tabellreferensbok med bilar med två attribut - namnet och ordningen på bilen i katalogen.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Låt oss säga att vi behövde byta första och andra bil. Den enkla lösningen är att uppdatera det första värdet till det andra och det andra till det första:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Men när vi kör den här koden förväntar vi oss en begränsningsöverträdelse eftersom ordningen på värdena i tabellen är unik:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Hur kan jag göra det annorlunda? Alternativ ett: lägg till en extra värdeersättning till en order som garanterat inte finns i tabellen, till exempel "-1". I programmering kallas detta att "byta ut värdena för två variabler med en tredje." Den enda nackdelen med denna metod är den extra uppdateringen.
Alternativ två: Gör om tabellen för att använda en flyttalsdatatyp för ordningsvärdet istället för heltal. Sedan, när du uppdaterar värdet från till exempel 1 till 2.5, kommer den första posten automatiskt att "stå" mellan den andra och den tredje. Denna lösning fungerar, men det finns två begränsningar. För det första kommer det inte att fungera för dig om värdet används någonstans i gränssnittet. För det andra, beroende på datatypens precision, kommer du att ha ett begränsat antal möjliga insättningar innan du beräknar om värdena för alla poster.
Alternativ tre: gör begränsningen uppskjuten så att den endast markeras vid tidpunkten för commit:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Eftersom logiken i vår första förfrågan säkerställer att alla värden är unika vid tidpunkten för förpliktelsen, kommer det att lyckas.
Exemplet som diskuteras ovan är naturligtvis väldigt syntetiskt, men det avslöjar idén. I vår applikation använder vi uppskjutna begränsningar för att implementera logik som är ansvarig för att lösa konflikter när användare samtidigt arbetar med delade widgetobjekt på tavlan. Genom att använda sådana begränsningar kan vi göra applikationskoden lite enklare.
I allmänhet, beroende på typen av begränsning, har Postgres tre nivåer av granularitet för att kontrollera dem: rad-, transaktions- och uttrycksnivåer.
Källa:
CHECK och NOT NULL är alltid kontrollerade på radnivå, för andra begränsningar, som framgår av tabellen, finns det olika alternativ. Du kan läsa mer .
För att kort sammanfatta, uppskjutna begränsningar i ett antal situationer ger mer läsbar kod och färre kommandon. Du måste dock betala för detta genom att komplicera felsökningsprocessen, eftersom det ögonblick då felet uppstår och det ögonblick du får reda på det separeras i tid. Ett annat möjligt problem är att schemaläggaren kanske inte alltid kan konstruera en optimal plan om begäran innefattar en uppskjuten begränsning.
Förbättring av pg_repack
Vi har täckt vad uppskjutna begränsningar är, men hur relaterar de till vårt problem? Låt oss komma ihåg felet vi fick tidigare:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Det inträffar när data kopieras från en loggtabell till en ny tabell. Det här ser konstigt ut eftersom... data i loggtabellen committeras tillsammans med data i källtabellen. Om de uppfyller begränsningarna i den ursprungliga tabellen, hur kan de bryta mot samma begränsningar i den nya?
Som det visar sig ligger roten till problemet i det föregående steget av pg_repack, som bara skapar index, men inte begränsningar: den gamla tabellen hade en unik begränsning, och den nya skapade ett unikt index istället.
Det är viktigt att notera här att om begränsningen är normal och inte uppskjuten, så är det unika index som skapas istället likvärdigt med denna begränsning, eftersom Unika begränsningar i Postgres implementeras genom att skapa ett unikt index. Men i fallet med en uppskjuten begränsning är beteendet inte detsamma, eftersom indexet inte kan skjutas upp och kontrolleras alltid när sql-kommandot körs.
Sålunda ligger problemets kärna i "fördröjningen" av kontrollen: i den ursprungliga tabellen inträffar det vid tidpunkten för commit, och i den nya tabellen vid den tidpunkt då sql-kommandot körs. Det betyder att vi måste se till att kontrollerna utförs på samma sätt i båda fallen: antingen alltid försenade eller alltid omedelbart.
Så vilka idéer hade vi?
Skapa ett index som liknar deferred
Den första idén är att utföra båda kontrollerna i omedelbart läge. Detta kan generera flera falska positiva begränsningar, men om det finns få av dem bör detta inte påverka användarnas arbete, eftersom sådana konflikter är en normal situation för dem. De uppstår till exempel när två användare börjar redigera samma widget samtidigt och klienten till den andra användaren inte har tid att ta emot information om att widgeten redan är blockerad för redigering av den första användaren. I en sådan situation vägrar servern den andra användaren och dess klient rullar tillbaka ändringarna och blockerar widgeten. Lite senare, när den första användaren slutför redigeringen, kommer den andra att få information om att widgeten inte längre är blockerad och kommer att kunna upprepa sin åtgärd.
För att säkerställa att kontroller alltid är i icke-uppskjutet läge, skapade vi ett nytt index som liknar den ursprungliga uppskjutna begränsningen:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;I testmiljön fick vi bara ett fåtal förväntade fel. Framgång! Vi körde pg_repack igen i produktionen och fick 5 fel på det första klustret på en timmes arbete. Detta är ett acceptabelt resultat. Men redan på det andra klustret ökade antalet fel markant och vi var tvungna att stoppa pg_repack.
Varför hände det? Sannolikheten för att ett fel inträffar beror på hur många användare som arbetar med samma widgets samtidigt. Tydligen var det i det ögonblicket mycket färre konkurrensförändringar med data som lagrades på det första klustret än på de andra, dvs. vi hade bara "tur".
Idén fungerade inte. Vid den tidpunkten såg vi två andra lösningar: skriv om vår applikationskod för att undvika uppskjutna begränsningar, eller "lära" pg_repack att arbeta med dem. Vi valde den andra.
Ersätt index i den nya tabellen med uppskjutna begränsningar från den ursprungliga tabellen
Syftet med revisionen var uppenbart - om den ursprungliga tabellen har en uppskjuten begränsning, måste du skapa en sådan begränsning för den nya, och inte ett index.
För att testa våra ändringar skrev vi ett enkelt test:
- tabell med en uppskjuten begränsning och en post;
- infoga data i en loop som är i konflikt med en befintlig post;
- gör en uppdatering – uppgifterna kommer inte längre i konflikt;
- begå ändringarna.
create table test_table
(
id serial,
val int,
constraint uk_test_table__val unique (val) deferrable initially deferred
);
INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
COMMIT;
END;
END LOOP;Den ursprungliga versionen av pg_repack kraschade alltid vid första insättningen, den modifierade versionen fungerade utan fel. Bra.
Vi går till produktion och får återigen ett fel i samma fas när vi kopierar data från loggtabellen till en ny:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Klassisk situation: allt fungerar i testmiljöer, men inte i produktion?!
APPLY_COUNT och korsningen av två partier
Vi började analysera koden bokstavligen rad för rad och upptäckte en viktig punkt: data överförs från loggtabellen till en ny i omgångar, konstanten APPLY_COUNT indikerade partiets storlek:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}Problemet är att data från den ursprungliga transaktionen, där flera operationer potentiellt skulle kunna bryta mot begränsningen, när de överförs, kan hamna i korsningen av två batcher - hälften av kommandona kommer att utföras i den första batchen och den andra hälften på sekunden. Och här, beroende på din tur: om lagen inte bryter mot något i den första omgången, är allt bra, men om de gör det, uppstår ett fel.
APPLY_COUNT är lika med 1000 poster, vilket förklarar varför våra tester var framgångsrika - de täckte inte fallet med "batch junction". Vi använde två kommandon - infoga och uppdatera, så exakt 500 transaktioner av två kommandon placerades alltid i en batch och vi upplevde inga problem. Efter att ha lagt till den andra uppdateringen slutade vår redigering att fungera:
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
UPDATE test_table set val = i where id = v_id; -- one more update
COMMIT;
END;
END LOOP;Så, nästa uppgift är att se till att data från den ursprungliga tabellen, som ändrades i en transaktion, hamnar i den nya tabellen också inom en transaktion.
Vägra från batchning
Och återigen hade vi två lösningar. Först: låt oss helt överge partitionering i partier och överföra data i en transaktion. Fördelen med denna lösning var dess enkelhet - de nödvändiga kodändringarna var minimala (förresten, i äldre versioner fungerade pg_reorg exakt så). Men det finns ett problem - vi skapar en långvarig transaktion, och detta är, som tidigare sagt, ett hot mot uppkomsten av en ny uppblåsthet.
Den andra lösningen är mer komplex, men förmodligen mer korrekt: skapa en kolumn i loggtabellen med identifieraren för transaktionen som lade till data till tabellen. När vi sedan kopierar data kan vi gruppera dem efter detta attribut och se till att relaterade ändringar överförs tillsammans. Batchen kommer att bildas av flera transaktioner (eller en stor) och dess storlek kommer att variera beroende på hur mycket data som ändrades i dessa transaktioner. Det är viktigt att notera att eftersom data från olika transaktioner kommer in i loggtabellen i en slumpmässig ordning, kommer det inte längre att vara möjligt att läsa den sekventiellt, som det var tidigare. seqscan för varje förfrågan med filtrering efter tx_id är för dyrt, ett index behövs, men det kommer också att sakta ner metoden på grund av omkostnader för att uppdatera den. I allmänhet, som alltid, måste du offra något.
Så vi bestämde oss för att börja med det första alternativet, eftersom det är enklare. Först var det nödvändigt att förstå om en lång transaktion skulle vara ett verkligt problem. Eftersom huvudöverföringen av data från den gamla tabellen till den nya också sker i en lång transaktion, förvandlades frågan till "hur mycket kommer vi att öka denna transaktion?" Längden på den första transaktionen beror huvudsakligen på bordets storlek. Varaktigheten av en ny beror på hur många förändringar som samlas i tabellen under dataöverföringen, d.v.s. på belastningens intensitet. Pg_repack-körningen inträffade under en tid med minimal servicebelastning, och volymen av ändringar var oproportionerligt liten jämfört med den ursprungliga storleken på tabellen. Vi bestämde oss för att vi kunde försumma tiden för en ny transaktion (som jämförelse är det i genomsnitt 1 timme och 2-3 minuter).
Experimenten var positiva. Lansering på produktion också. För tydlighetens skull är här en bild med storleken på en av databaserna efter körning:
Eftersom vi var helt nöjda med den här lösningen försökte vi inte implementera den andra, men vi överväger möjligheten att diskutera det med tilläggsutvecklarna. Vår nuvarande revision är tyvärr ännu inte klar för publicering, eftersom vi bara löste problemet med unika uppskjutna begränsningar, och för en fullfjädrad patch är det nödvändigt att tillhandahålla stöd för andra typer. Vi hoppas kunna göra detta i framtiden.
Du kanske har en fråga, varför blev vi ens involverade i den här historien med modifieringen av pg_repack, och använde till exempel inte dess analoger? Vid något tillfälle tänkte vi också på detta, men den positiva upplevelsen av att använda det tidigare, på tabeller utan uppskjutna begränsningar, motiverade oss att försöka förstå kärnan i problemet och åtgärda det. Att använda andra lösningar kräver dessutom tid för att genomföra tester, så vi bestämde oss för att vi först skulle försöka fixa problemet i det, och om vi insåg att vi inte kunde göra detta inom rimlig tid, då skulle vi börja titta på analoger .
Resultat
Vad vi kan rekommendera baserat på vår egen erfarenhet:
- Övervaka din uppblåsthet. Baserat på övervakningsdata kan du förstå hur väl autovakuum är konfigurerat.
- Justera AUTOVAKUUM för att hålla uppblåstheten på en acceptabel nivå.
- Om uppsvällningen fortfarande växer och du inte kan övervinna den med hjälp av out-of-the-box-verktyg, var inte rädd för att använda externa förlängningar. Huvudsaken är att testa allt väl.
- Var inte rädd för att modifiera externa lösningar för att passa dina behov – ibland kan detta vara mer effektivt och till och med enklare än att ändra din egen kod.
Källa: will.com