L'efecte de les taules i índexs inflats (inflor) és àmpliament conegut i està present no només a Postgres. Hi ha maneres de tractar-ho "fora de la caixa", com VACUUM FULL o CLUSTER, però bloquegen les taules durant el funcionament i, per tant, no sempre es poden utilitzar.
L'article tindrà una mica de teoria sobre com es produeix l'inflor, com es pot tractar, sobre les restriccions ajornades i els problemes que comporta l'ús de l'extensió pg_repack.
Aquest article es basa en a PgConf.Russia 2020.
Per què hi ha una inflor
Postgres es basa en un model multiversió (). La seva essència és que cada fila de la taula pot tenir diverses versions, mentre que les transaccions no veuen més d'una d'aquestes versions, però no necessàriament la mateixa. Això permet que diverses transaccions funcionin simultàniament i tinguin poc o cap efecte les unes sobre les altres.
Evidentment, totes aquestes versions s'han d'emmagatzemar. Postgres treballa amb memòria pàgina per pàgina i una pàgina és la quantitat mínima de dades que es poden llegir des del disc o escriure. Vegem un petit exemple per entendre com passa això.
Suposem que tenim una taula a la qual hem afegit diversos registres. La primera pàgina del fitxer on s'emmagatzema la taula té dades noves. Aquestes són versions en directe de files que estan disponibles per a altres transaccions després de la confirmació (per simplificar, assumirem que el nivell d'aïllament és Read Committed).
Després vam actualitzar una de les entrades i, per tant, vam marcar la versió antiga com a obsoleta.
Pas a pas, actualitzant i suprimint versions de fila, hem acabat amb una pàgina en què aproximadament la meitat de les dades són "escombraries". Aquestes dades no són visibles per a cap transacció.
Postgres té un mecanisme , que neteja les versions obsoletes i deixa espai per a dades noves. Però si no està configurat de manera prou agressiva o està ocupat treballant en altres taules, les "dades d'escombraries" es mantenen i hem d'utilitzar pàgines addicionals per a dades noves.
Així, en el nostre exemple, en algun moment, la taula constarà de quatre pàgines, però només hi haurà la meitat de les dades en directe. Com a resultat, en accedir a la taula, llegirem moltes més dades de les necessàries.
Fins i tot si ara VACUUM elimina totes les versions de fila irrellevants, la situació no millorarà dràsticament. Tindrem espai lliure en pàgines o fins i tot pàgines senceres per a línies noves, però encara llegirem més dades de les que necessitem.
Per cert, si una pàgina completament en blanc (la segona del nostre exemple) estigués al final del fitxer, VACUUM podria tallar-la. Però ara està al mig, així que no es pot fer res amb ella.
Quan el nombre d'aquestes pàgines buides o molt escasses es fa gran, cosa que s'anomena inflor, comença a afectar el rendiment.
Tot el que s'ha descrit anteriorment és la mecànica de l'aparició d'inflor a les taules. En els índexs, això passa de la mateixa manera.
Tinc una inflor?
Hi ha diverses maneres de determinar si teniu una inflor. La idea del primer és utilitzar les estadístiques internes de Postgres, que conté informació aproximada sobre el nombre de files a les taules, el nombre de files "en directe", etc. Hi ha moltes variacions d'scripts ja fets a Internet. Vam prendre com a base d'Experts de PostgreSQL, que poden avaluar la inflació de la taula juntament amb els índexs btree toast i bloat. Segons la nostra experiència, el seu error és del 10 al 20%.
Una altra manera és utilitzar l'extensió , que us permet mirar dins de les pàgines i obtenir tant el valor estimat com el valor exacte de la inflor. Però en el segon cas, hauràs d'escanejar tota la taula.
Una petita quantitat d'inflor, fins a un 20%, considerem acceptable. Es pot considerar com un anàleg del factor de farciment per и . Al 50% o més, poden començar problemes de rendiment.
Maneres de fer front a la inflor
Hi ha diverses maneres de fer front a la inflor a Postgres, però estan lluny de sempre i potser no s'adapten a tothom.
Configureu AUTOVACUUM perquè no es produeixi inflor. I per ser més precisos, mantenir-lo en un nivell acceptable per a vostè. Sembla un consell del "capità", però en realitat no sempre és fàcil d'aconseguir. Per exemple, teniu un desenvolupament actiu amb un canvi regular a l'esquema de dades o s'està produint algun tipus de migració de dades. Com a resultat, el vostre perfil de càrrega pot canviar amb freqüència i tendeix a ser diferent per a diferents taules. Això vol dir que cal estar constantment una mica per davant de la corba i ajustar AUTOVACUUM al perfil canviant de cada taula. Però és obvi que això no és fàcil de fer.
Un altre motiu habitual pel qual AUTOVACUUM no pot mantenir-se al dia amb les taules és perquè hi ha transaccions de llarga durada que impedeixen netejar les dades disponibles per a aquestes transaccions. La recomanació aquí també és òbvia: desfer-se de les transaccions "penjants" i minimitzar el temps de les transaccions actives. Però si la càrrega de la vostra aplicació és un híbrid d'OLAP i OLTP, podeu tenir simultàniament moltes actualitzacions freqüents i consultes breus, així com operacions a llarg termini, per exemple, crear un informe. En aquesta situació, val la pena pensar en repartir la càrrega entre diferents bases, la qual cosa permetrà ajustar cada una d'elles.
Un altre exemple: fins i tot si el perfil és homogeni, però la base de dades està sota una càrrega molt elevada, fins i tot l'AUTOVACUUM més agressiu pot no ser capaç de fer front i es produirà una inflor. L'escala (vertical o horitzontal) és l'única solució.
Què fer en una situació en què heu configurat AUTOVACUUM, però la inflor continua creixent.
Equip BUIT COMPLET reconstrueix el contingut de taules i índexs i només hi deixa dades actualitzades. Per eliminar la inflor, funciona perfectament, però durant la seva execució, es captura un bloqueig exclusiu a la taula (AccessExclusiveLock), que no permetrà consultes a aquesta taula, ni tan sols selecciona. Si us podeu permetre el luxe d'aturar el vostre servei o part d'ell durant un temps (des de desenes de minuts fins a diverses hores, depenent de la mida de la base de dades i del vostre maquinari), aquesta opció és la millor. Malauradament, no tenim temps d'executar VACUUM FULL durant el manteniment programat, per la qual cosa aquest mètode no ens convé.
Equip CLUSTER reconstrueix el contingut de les taules de la mateixa manera que VACUUM FULL, alhora que us permet especificar un índex segons el qual les dades s'ordenaran físicament al disc (però l'ordre no està garantit per a noves files en el futur). En determinades situacions, aquesta és una bona optimització per a diverses consultes, amb la lectura de diversos registres a l'índex. El desavantatge de l'ordre és el mateix que el de VACUUM FULL: bloqueja la taula durant el funcionament.
Equip REÍNDEX similar als dos anteriors, però reconstrueix un índex específic o tots els índexs d'una taula. Els bloquejos són una mica més febles: ShareLock en una taula (impedeix modificacions, però permet seleccions) i AccessExclusiveLock en un índex reconstruible (bloqueja les consultes mitjançant aquest índex). Tanmateix, Postgres 12 va introduir l'opció , que us permet reconstruir un índex sense bloquejar l'addició, modificació o supressió simultània de registres.
En versions anteriors de Postgres, podeu aconseguir un resultat semblant a l'ús de REINDEX CONCURRENTLY . Us permet crear un índex sense bloqueig estricte (ShareUpdateExclusiveLock, que no interfereix amb les consultes paral·leles), després substituir l'índex antic per un de nou i eliminar l'índex antic. Això us permet eliminar la inflor de l'índex sense interferir amb la vostra aplicació. És important tenir en compte que en reconstruir els índexs hi haurà una càrrega addicional al subsistema de disc.
Per tant, si hi ha maneres perquè els índexs eliminen la inflor calent, no n'hi ha per a les taules. Aquí és on entren en joc les extensions externes: (abans pg_reorg), , i altres. En el marc d'aquest article, no els compararé i només parlaré de pg_repack, que, després d'una mica de perfeccionament, fem servir a casa.
Com funciona pg_repack
Suposem que tenim una taula força normal, amb índexs, restriccions i, malauradament, amb inflor. Com a primer pas, pg_repack crea una taula de registre per fer un seguiment de tots els canvis mentre s'executa. L'activador replicarà aquests canvis a cada inserció, actualització i supressió. Aleshores es crea una taula semblant a l'original en estructura, però sense índexs ni restriccions, per no alentir el procés d'inserció de dades.
A continuació, pg_repack transfereix dades de la taula antiga a la taula nova, filtrant automàticament totes les files irrellevants i després crea índexs per a la taula nova. Durant l'execució de totes aquestes operacions, els canvis s'acumulen a la taula de registre.
El següent pas és transferir els canvis a la nova taula. La migració es fa en diverses iteracions, i quan queden menys de 20 entrades a la taula de registre, pg_repack adquireix un bloqueig fort, migra les últimes dades i substitueix la taula antiga per la nova a les taules del sistema Postgres. Aquest és l'únic i molt curt moment de temps en què no podreu treballar amb la taula. Després d'això, s'eliminen la taula antiga i la taula amb els registres i s'allibera espai al sistema de fitxers. Procés completat.
En teoria, tot sembla genial, però a la pràctica, què passa? Hem provat pg_repack sense càrrega i sota càrrega, i hem comprovat el seu funcionament en cas d'aturada prematura (és a dir, amb Ctrl+C). Totes les proves van ser positives.
Vam anar al prod, i després tot va sortir malament, com esperàvem.
Primer pancake a la venda
Al primer clúster, vam rebre un error sobre una violació de restricció única:
$ ./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.
Aquesta restricció tenia el nom generat automàticament index_16508, creat per pg_repack. Pels atributs inclosos en la seva composició, vam determinar la "nostra" restricció que li correspon. El problema va resultar ser que no es tracta d'una restricció del tot normal, sinó retardada (), és a dir la seva validació es realitza més tard que l'ordre sql, la qual cosa comporta conseqüències inesperades.
Restriccions diferides: per què són necessàries i com funcionen
Una mica de teoria sobre les restriccions diferides.
Considerem un exemple senzill: tenim una taula de llibres de referència de cotxes amb dos atributs: el nom i l'ordre del cotxe al directori.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Suposem que havíem de canviar el primer i el segon cotxe per llocs. La solució directa és actualitzar el primer valor al segon i el segon al primer:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Però quan executem aquest codi, s'espera que obtinguem una infracció de restriccions perquè l'ordre dels valors de la taula és únic:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Com fer-ho de manera diferent? Opció 1: afegiu una substitució addicional del valor amb una comanda que es garanteix que no existirà a la taula, per exemple "-XNUMX". En programació, això s'anomena "intercanviar els valors de dues variables per una tercera". L'únic inconvenient d'aquest mètode és l'actualització addicional.
Segona opció: redissenyeu la taula per utilitzar un tipus de dades de coma flotant per al valor de l'exponent en lloc dels nombres enters. Aleshores, quan actualitzeu el valor d'1, per exemple, a 2.5, la primera entrada es "posarà" automàticament entre la segona i la tercera. Aquesta solució funciona, però hi ha dues limitacions. En primer lloc, no us funcionarà si el valor s'utilitza en algun lloc de la interfície. En segon lloc, depenent de la precisió del tipus de dades, tindreu un nombre limitat d'insercions possibles abans de tornar a calcular els valors de tots els registres.
Opció tercera: feu que la restricció s'ajorna perquè només es comprovi en el moment de la confirmació:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Com que la lògica de la nostra sol·licitud inicial assegura que tots els valors són únics en el moment en què es fa la confirmació, la confirmació tindrà èxit.
L'exemple comentat anteriorment és, per descomptat, molt sintètic, però revela la idea. A la nostra aplicació, fem servir restriccions diferides per implementar la lògica que s'encarrega de resoldre conflictes quan els usuaris interactuen amb objectes de widgets compartits al tauler alhora. L'ús d'aquestes restriccions ens permet fer el codi de l'aplicació una mica més senzill.
En general, depenent del tipus de restricció a Postgres, hi ha tres nivells de granularitat de la seva validació: fila, transacció i expressió.
Font:
CHECK i NOT NULL sempre es marquen a nivell de fila; per a altres restriccions, com es pot veure a la taula, hi ha diferents opcions. Podeu llegir més .
Per resumir breument, les restriccions diferides en diverses situacions condueixen a un codi més llegible i menys ordres. Tanmateix, cal pagar per això complicant el procés de depuració, ja que el moment en què es produeix l'error i el moment en què se n'aprèn estan separats en el temps. Un altre possible problema és que el planificador no sempre serà capaç de crear un pla òptim si hi ha una restricció retardada a la consulta.
Millora de pg_repack
Hem tractat quines són les restriccions diferides, però com es relacionen amb el nostre problema? Recordeu l'error que vam tenir abans:
$ ./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.Es produeix quan les dades es copien d'una taula de registre a una taula nova. Això sembla estrany perquè... les dades de la taula de registre es comprometen juntament amb les dades de la taula d'origen. Si compleixen les restriccions de la taula original, com poden violar les mateixes restriccions a la nova?
Com va resultar, l'arrel del problema es troba en el pas anterior de pg_repack, que només crea índexs, però no restriccions: la taula antiga tenia una restricció única i la nova creava un índex únic.
És important tenir en compte aquí que si la restricció és normal i no diferida, aleshores l'índex únic creat en lloc d'ell és equivalent a aquesta restricció, perquè les restriccions úniques a Postgres s'implementen mitjançant la creació d'un índex únic. Però en el cas d'una restricció retardada, el comportament no és el mateix, perquè l'índex no es pot ajornar i sempre es verifica en el moment en què s'executa l'ordre sql.
Així, l'essència del problema rau en la comprovació "diferida": a la taula original, es produeix en el moment de la confirmació, i a la nova, en el moment de l'execució de l'ordre sql. Per tant, hem d'assegurar-nos que les comprovacions es realitzen de la mateixa manera en ambdós casos: sempre amb retard, o sempre immediatament.
Aleshores, quines idees teníem.
Creeu un índex similar al diferit
La primera idea és realitzar ambdues comprovacions en mode immediat. Això pot generar diverses restriccions de falsos positius, però si n'hi ha poques, això no hauria d'afectar el treball dels usuaris, ja que aquests conflictes són una situació normal per a ells. Es produeixen, per exemple, quan dos usuaris comencen a editar el mateix giny al mateix temps, i el client del segon usuari no té temps de rebre informació que el giny ja està bloquejat per editar-lo pel primer usuari. En aquesta situació, el servidor rebutja el segon usuari, i el seu client torna enrere els canvis i bloqueja el giny. Una mica més tard, quan el primer usuari finalitzi l'edició, el segon rebrà informació que el giny ja no està bloquejat i podrà repetir la seva acció.
Per garantir que les comprovacions estiguin sempre en mode no diferit, hem creat un nou índex similar a la restricció diferida original:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;A l'entorn de prova, només hem rebut alguns errors esperats. Èxit! Vam tornar a executar pg_repack en prod i vam tenir 5 errors al primer clúster en una hora de treball. Aquest és un resultat acceptable. Tanmateix, ja al segon clúster, el nombre d'errors va augmentar significativament i vam haver d'aturar pg_repack.
Per què va passar? La probabilitat que es produeixi un error depèn de quants usuaris treballin simultàniament amb els mateixos ginys. Pel que sembla, en aquell moment, hi havia molts menys canvis competitius amb les dades emmagatzemades al primer clúster que a la resta, és a dir. només som "afortunats".
La idea no va funcionar. En aquell moment, vam veure altres dues solucions: reescriure el codi de la nostra aplicació per abandonar les restriccions ajornadas o "ensenyar" pg_repack a treballar-hi. Vam triar el segon.
Substituïu els índexs de la taula nova amb restriccions diferides de la taula original
El propòsit de la revisió era obvi: si la taula original té una restricció diferida, per a la nova és necessari crear aquesta restricció i no un índex.
Per provar els nostres canvis, vam escriure una prova senzilla:
- taula amb restricció diferida i un registre;
- inserim dades al bucle que entren en conflicte amb el registre existent;
- fer una actualització: les dades ja no entren en conflicte;
- cometre canvis.
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;La versió original de pg_repack sempre es va bloquejar a la primera inserció, la versió modificada va funcionar sense errors. Genial.
Anem a prod i tornem a obtenir un error en la mateixa fase de copiar les dades de la taula de registre a una de nova:
$ ./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.Situació clàssica: tot funciona en entorns de prova, però no en producció?!
APPLY_COUNT i la unió de dos lots
Vam començar a analitzar el codi literalment línia per línia i vam descobrir un punt important: les dades es transfereixen de la taula de registre a la nova per lots, la constant APPLY_COUNT indica la mida del lot:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}El problema és que les dades de la transacció original, en què diverses operacions poden violar potencialment la restricció, poden acabar a la unió de dos lots durant la transferència: la meitat de les ordres es comprometran al primer lot i l'altra meitat. en el segon. I aquí, quina sort: si els equips de la primera tanda no violen res, tot està bé, però si ho fan, es produeix un error.
APPLY_COUNT és igual a 1000 registres, la qual cosa explica per què les nostres proves van tenir èxit: no van cobrir el cas de la "unió per lots". Hem utilitzat dues ordres: inserir i actualitzar, de manera que sempre es van col·locar exactament 500 transaccions de dues ordres en un lot i no vam experimentar problemes. Després d'afegir la segona actualització, la nostra edició va deixar de funcionar:
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;Per tant, la següent tasca és assegurar-se que les dades de la taula original que s'han canviat en una transacció també entren a la taula nova dins d'una transacció.
Rebuig del lot
I de nou vam tenir dues solucions. Primer: abandonem completament la partició en lots i transferim dades en una transacció. L'avantatge d'aquesta solució era la seva senzillesa: els canvis de codi necessaris eren mínims (per cert, en versions anteriors pg_reorg funcionava exactament així). Però hi ha un problema: estem creant una transacció de llarga durada, i això, com s'ha dit anteriorment, és una amenaça per a l'aparició d'una nova inflor.
La segona solució és més complexa, però probablement més correcta: crear una columna a la taula de registre amb l'identificador de la transacció que ha afegit dades a la taula. Aleshores, quan copiem dades, podem agrupar-les per aquest atribut i assegurar-nos que els canvis relacionats es transfereixen conjuntament. El lot es formarà a partir de diverses transaccions (o una de gran) i la seva mida variarà en funció de quantes dades s'hagin canviat en aquestes transaccions. És important tenir en compte que, com que les dades de diferents transaccions entren a la taula de registre en un ordre aleatori, ja no serà possible llegir-la de manera seqüencial, com era abans. seqscan per a cada sol·licitud amb filtratge per tx_id és massa car, es necessita un índex, però també alentirà el mètode a causa de la sobrecàrrega d'actualitzar-lo. En general, com sempre, cal sacrificar alguna cosa.
Així doncs, vam decidir començar per la primera opció, com a més senzilla. En primer lloc, calia entendre si una transacció llarga seria un problema real. Com que la transferència principal de dades de la taula antiga a la nova també es produeix en una transacció llarga, la pregunta s'ha transformat en "Quant augmentarem aquesta transacció?" La durada de la primera transacció depèn principalment de la mida de la taula. La durada del nou depèn de quants canvis s'acumularan a la taula durant la transferència de dades, és a dir. sobre la intensitat de la càrrega. L'execució de pg_repack es va produir en un moment de càrrega de servei mínima i la quantitat de canvi era incomparablement petita en comparació amb la mida de la taula original. Vam decidir que podem descuidar el temps d'una nova transacció (per a comparació, la mitjana és d'1 hora i 2-3 minuts).
Els experiments van ser positius. Llançament també a la venda. Per a més claredat, aquí teniu una imatge amb la mida d'una de les bases després de la carrera:
Com que aquesta solució ens va adequar completament, no vam intentar implementar la segona, però estem considerant la possibilitat de parlar-ne amb els desenvolupadors de l'extensió. La nostra revisió actual, malauradament, encara no està preparada per a la seva publicació, ja que només hem resolt el problema amb restriccions retardades úniques, i per a un pedaç complet, cal fer suport per a altres tipus. Esperem poder fer-ho en el futur.
Potser teniu una pregunta, per què fins i tot ens hem involucrat en aquesta història amb el perfeccionament de pg_repack i, per exemple, no hem utilitzat els seus anàlegs? En algun moment també hi vam pensar, però l'experiència positiva d'utilitzar-lo abans, sobre taules sense restriccions retardades, ens va motivar a intentar comprendre l'essència del problema i solucionar-lo. A més, utilitzar altres solucions també requereix temps per realitzar proves, així que vam decidir que primer intentaríem solucionar el problema i, si ens adonem que no ho podríem fer en un temps raonable, començaríem a considerar anàlegs.
Troballes
Què podem recomanar segons la nostra pròpia experiència:
- Vigila la teva inflor. A partir de les dades de monitorització, podeu entendre com de bé està configurat l'autobuit.
- Configureu AUTOVACUUM per mantenir la inflor a un nivell acceptable.
- Si la inflor continua creixent i no podeu fer-hi front amb eines noves, no tingueu por d'utilitzar extensions externes. El més important és provar-ho tot bé.
- No tingueu por de modificar les solucions externes per adaptar-les a les vostres necessitats; de vegades això pot ser més eficient i fins i tot més fàcil que canviar el vostre propi codi.
Font: www.habr.com