Effectus bloat in tabulis et indicibus pervulgatum est et non solum in Postgres adest. Modi sunt de archa tractandi, sicut VACUUM PLENA vel TURBA, sed mensas claudunt in operatione et ideo semper adhiberi non possunt.
Articulus parvam theoriam continebit quomodo contigerit bloat, quomodo pugnare potes, de angustiis dilatis et quaestionibus ad usum extensionis pg_repack afferunt.
This article is written based on apud PgConf.Russia MMXX.
Cur bloat fit?
Postgres fundatur in exemplar multi-versio (). Essentia eius est ut quisque ordo in tabula plures versiones habere possit, cum res non plures quam unam ex his versionibus videantur, sed non unam necessario. Hoc permittit plures operationes simul laborare ac paene nullas inter se ictum habere.
Patet, omnes hae versiones opus esse reponendum. Postgres opera cum pagina memoriae pro pagina et pagina minimum est copia notitiarum quae ex orbe vel scripto legi possunt. Intueamus exemplum parvum quomodo hoc fiat ut intelligamus.
Dicamus nos tabulam habere, cui aliquot monumenta addidimus. Nova notitia in pagina prima tabellae ubi reposita est, apparuit. Hae sunt versiones versuum vivae quae praesto sunt aliis transactionibus post committere (pro simplicitate ponemus quod campus separatus est Read Committed).
Nos igitur unum ex visoribus renovatis, ita vetus versionem notantes iam non ad rem pertinentes.
Gradatim, adaequationis et versuum versiones deletis, finivimus cum pagina in qua dimidia fere notitiarum "coenum" est. Haec notitia nulli rei visibilis est.
Postgres habet mechanism quae antiquas versiones expurgat et novas notitias locum dat. Si autem infensi satis non figuratur vel in aliis tabulis versari laborat, tunc "coenum notitiarum" manet, et paginas additis novis datas utendum est.
In nostro exemplo, in quovis tempore tabula tabula quattuor paginis constabit, sed solum dimidium notitiae vivae continebit. Quam ob rem cum ad mensam accesserimus, multo plura notitia quam necesse est legemus.
Etiamsi VACUUM nunc omnes versiones actuarias inutiles delet, condicio dramatica non emendabit. Spatium liberum habebimus in paginis vel etiam paginis integris pro novis ordinibus, sed adhuc plures notitias quam necessarias legendi erimus.
Obiter si pagina blank (secunda in exemplo nostro) in fine tabellae esset, tunc VACUUM pampinare posset. Sed nunc est in medio, ut nihil cum illa agi possit.
Cum numerus paginarum tam vacuarum vel sparsimarum fit magnus, quae bloat appellatur, ad afficiendum incipit.
Omnia supra scripta sunt mechanica de bloate in tabulis occurrentia. In indicibus hoc longe secus.
Egone bloat?
Plures modi sunt ut si bloat habes. Primae idea est uti interna Postgres statistica, quae proxime informationem continet de numero ordinum in tabulis, de numero "viventium" ordinum, etc. Multas varietates in interreti paratae scriptorum invenire potes. Accepimus pro fundamento a peritis PostgreSQL, qui tabulas bloas cum tosti et btree indicibus aestimare possunt. Apud nos, eius error 10-20% est.
Alius modus est uti extensione quae permittit ut paginas intra inspicias et utrumque valorem extimatum et exactum accipias. Sed secundo casu totam mensam lustrare debebis.
Putamus parvum valorem parvum, usque ad 20% acceptum. Considerari potest analogon fillfactoris и . Ad L% et supra, perficiendi problemata incipere possunt.
Vias pugnare bloat
Postgres varias vias bloat ex archa tractandas habet, sed non semper omnibus aptae sunt.
AUTOVACUUM configurare ut non bloat. Vel pressius servare in aequo tibi. Hoc consilium "principis" videtur, sed re vera id consequi non semper facile est. Exempli gratia, progressionem activam habes cum regularibus mutationibus ad schema data, vel quaedam migratio notitiarum. Quam ob rem, profile onus tuum saepe mutare potest et a mensa ad mensam typice variare. Hoc modo necesse est ut constanter paulo ante operetur et AUTOVACUUM ad mutabilem cuiusque tabulae figuram componas. Sed hoc plane non est facile.
Alia ratio communis cur AUTOVACUUM tabulis cum tabulis tenere non potest, est quia res diuturnae sunt quae impediunt quominus notitias quae rebus illis praesto sunt expurgare possint. Commendatio hic patet etiam - removere transactionum "pendentium" et minuere tempus negotiorum actuum. Sed si onus applicationis tuae hybrid of OLAP et OLTP est, tunc simul multas res crebras et breves interrogationes habere potes, ac operationes diuturnae - verbi gratia, relationem aedificantes. In tali rerum statu, valet cogitare de onere per diversas bases disseminando, quod de unoquoque eorum subtilius permittet.
Alterum exemplum - etsi profanum homogeneum est, datorum autem sub praealto onere est, etiam acerrima AUTOVACUUM tolerare non potest, et bloat erit. Scalae (verticalis vel horizontalis) sola solutio est.
AUTOVACUUM quid agas eo loco ubi erexisti, sed bloat crescunt.
bigas VACUUM FULL contenta tabularum et indices et folia reaedificat tantum in eis notitias pertinentes. Ad bloatum tollendum, perfecte operatur, sed in executione clausum exclusivum in mensa capitur (AccessExclusiveLock), quae interrogationes in hac tabula exsequi non patitur, etiam eligat. Si officium tuum vel partem eius aliquandiu prohibere potes (a decem minutis ad plures horas secundum magnitudinem datorum et ferramentorum tuorum), haec optio optima est. Infeliciter, tempus non vacat ad currendum VACUUM PLENUM in actis sustentandis, ergo haec methodus nobis non convenit.
bigas RACEMUS Tabularum contenta eodem modo ac VACUUM PLENUS aedificat, sed permittit tibi indices denotare secundum quem data physice in orbe ordinata erunt (sed in futuro ordine novorum ordinum non praestatur). In quibusdam adiunctis, haec optima est pro pluribus quaestionibus - cum plures tabulas per indicem legere. Incommodum praecepti idem est ac VACUUM FULL. Mensam in operatione claudit.
bigas REINDEX similes duobus prioribus, sed certum indicem seu omnes indices tabulae reaedificant. Serae leviter debiliores sunt: ShareLock in mensa (modificationes prohibet, sed selectas permittit) et AccessExclusiveLock in indice reaedificatur (obstructiones queries hoc indice utentes). Sed in versione 12 Postgres parametri apparuit quae sinit reficere indicem sine impedimento concurrente additione, immutatione vel deletione monumentorum.
In prioribus versionibus Postgres, exitum consequi potes similes REINDEX CONVENIENTER utens . Liceat tibi indicem creare sine stricta obfirmatione (ShareUpdateExclusiveLock, quod quaestionibus parallelis non impedit), tum veterem indicem cum novo uno restitue et antiquum indicem dele. Hoc permittit ut index bloat abolere sine applicatione tua impedimento. Gravis est considerare quod cum indices reficiendi additamentum onus in orbe subsystem erit.
Ita si in indicibus modi sunt ad tollendum bloatum in musca, tunc nullae sunt tabulae. Hic variae extensiones externae oriuntur; (olim pg_reorg); , et alii. In hoc articulo, non comparabimus eos et solum loqui de pg_repack, quibus, post aliquam modificationem, ipsi utimur.
Quam pg_repack opera
Dicamus nos habere mensam omnino ordinariam - cum indicibus, restrictionibus et, proh dolor, cum bloate. Primus gradus pg_repack est truncum truncum creare ad notitias de omnibus mutationibus dum currit. Felis has mutationes pro singulis insertis, renovatis ac delebis reddet. Inde tabula creatur, in structura similis cum originali, sed sine indicibus et restrictionibus, ita ut processus notitiarum inserendi non retardet.
Deinde, pg_repack notitias e veteri tabula ad novam mensam transfert, ipso facto omnes ordines inutiles eliquare, deinde indices novae tabulae creat. Per executionem omnium harum operationum, mutationes cumulant in trunco.
Proximum est ut mutationes novae tabulae transferantur. Migratio per plures iterationes peragitur, et cum pauciores quam 20 introitus relicti sunt in tabula loga, pg_repack fortem crinem acquirit, notitias novissimas migrat et veterem mensam cum nova una in tabulis systematis Postgres reponit. Hoc solum et perexiguum est, cum in mensa laborare non poteris. Postea vetus mensa et mensa cum lignis deleta sunt et spatium in ratio tabellae liberatur. Processus perfectus est.
Omnia magna in ratione, sed in usu quid fit? Pg_repack sine onere et sub onere temptavimus, et operationem eius repressimus in casu praematurae (hoc est, utens Ctrl+C). Omnes probationes affirmativae sunt.
Ad cibum venimus - et tunc omnia non exspectavimus.
Primo subcinericius in sale
In primo racemo errorem accepimus de singulari coercitione violatione;
$ ./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.
Haec limitatio nomen auto-generati index_16508 habuit - a pg_repack creatum est. Ex attributis in compositione comprehensis, "nostrum" coactionem ei congruentem decrevimus. Problema evasit hanc non esse limitationem omnino ordinariam, sed differri.) i.e. eius verificatione serius perficitur quam mandatum sql, quod ad eventus inopinatos ducit.
Dilatae angustiae: quare opus sunt et quomodo operantur?
Theoria modica de restrictionibus differendis.
Simplex exemplum consideremus: habemus tabulam-referentiam libri carri cum duobus attributis, nomen et ordo in indicem autocinetorum.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Dicamus nos opus ad primam et secundam permutandam carros. Solutio recta directa est ad renovandum primum valorem ad secundum, et secundum ad primum;
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Sed cum hunc codicem currimus, coercitionem violationem exspectamus, quia ordo valorum in mensa unicus est;
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Quomodo possum aliter facere? Optio: adde valorem additicium repositum ordini qui praestatur non exsistere in mensa, exempli gratia "-1". In programmando hoc appellatur "valores duarum variabilium per tertiam commutans". Solum incommodum huius methodi addito renovatio est.
Optione duo: Redesign mensam ut punctum missilis notitia generis ad ordinem valorem pro integris. Deinde, cum adaequationis valorem ab 1, exempli gratia ad 2.5, primus ingressu statim inter secundum et tertium "stabit". Haec solutio operatur, sed duo sunt limitationes. Primum, non operabitur tibi si alicubi in pretio interface ponitur. Secundo, secundum praecisionem generis notitiae, numerum possibilium habebis antequam reputet valores omnium monumentorum insertas.
Optio tria: coacte differtur ut in tempore committi reprimatur;
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Cum logica nostra postulatio initialis efficit ut omnes valores singulares in tempore admittant, bene succedet.
Exemplum superius dictum est sane valde syntheticum, sed ideam revelat. In applicatione nostra angustiis dilatis ad logicam efficiendam utimur, quae certaminum solvendorum reus est, cum usores simul cum communibus obiectis in tabula operantur. Talibus restrictionibus nos permittit ut in applicatione codicis paulo simplicius faciamus.
In genere, pro genere coercitionis, Postgres tres gradus granularitatis habet ad eos cohibendos: ordo, res, gradus et expressio.
Source:
CHECK et NON NULLUS in gradu ordinis semper sedatus est: nam aliae restrictiones, ut ex tabula videri potest, variae sunt optiones. Potes legere plus .
Ut breviter compendio, angustiae dilatae in pluribus adiunctis codicem faciliorem praebent et mandata pauciora praebent. Sed hoc tibi solvendum est processus debugging implicando, cum error incidit et momentum temporis inveneris separari. Alia quaestio possibilis est quod schedula meliorem consilium construere non semper poterit, si postulatum coactionem differri implicat.
Improvement of pg_repack
Quas angustias tevimus, sed quomodo rem nostram narrant? Recordemur errorem quod antea accepimus;
$ ./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.Incidit cum notitia exscripta est ex tabula trunco ad novam tabulam. Mirum hic quia... notitia in tabula sextario committitur una cum notitia in mensa fontana. Si cohiberi satisfaciunt originalis tabulae, quomodo possunt easdem angustias in nova violare?
Cum evenit, radix quaestionis in priori gradu pg_repack iacet, quae indices tantum gignit, sed non angustias: vetus mensa unicum coactum habuit, nova vero in loco unicus index creatus est.
Illud hic notandum est, si necessitas normalis est et non differtur, tum unicus index creatus potius huic necessitati aequiparatur, quia. Singulae angustiae in Postgres efficiuntur ut unicum indicem creando. Sed in causa dilata coactionis non eadem ratio est, quia index differri non potest et semper inhibetur tempore imperati sql.
Ita essentia quaestionis in "mora" perscriptio iacet: in tabula originali occurrit in tempore committi, et in nova tabula tempore praecepti sql. Hoc modo opus est ut eadem in utroque casu compescat: aut semper moratus est, aut semper statim.
Quid igitur ideas habemus?
Create an indicem similis differatur
Prima opinio est utrumque compescere in immediato modo. Hoc potest generare plures falsas affirmativas restrictiones, sed si paucae sunt, hoc opus utentium non afficit, cum huiusmodi conflictus normales condiciones sint. Occurrunt, exempli gratia, cum duo usores utentes simul eodem modo edere incipiant, et client secundi usoris non vacat informationem recipere iam contentum esse pro usore primo emendo. In tali condicione, minister secundum usorem recusat, et client eius mutationes et clausurae content volvit. Paulo post, cum primus usor recensere compleverit, secundus informationem accipiet imaginem non amplius obumbratam esse ac actionem suam repetere poterit.
Ut schedulae semper in modo non differantur, novum indicem similem cum impedimento originali differendo creavimus;
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;In ambitu experimenti tantum paucos errores expectatos accepimus. Prosperitas! Pg_repack iterum ad productionem cucurrimus et 5 errores in botro primo horae laboris cepimus. Hoc est gratum exitum. Sed iam in secundo fasciculo numerus errorum significanter auctus est et debebamus prohibere pg_repack.
Quid accidit? Verisimilitudo erroris occurrentis pendet ex quot usoribus isdem contenta simul laborant. Videtur quod in illo tempore multo pauciores mutationes competitive fuerunt cum notitia in primo botro quam in aliis, i.e. fuimus sicut "felix".
Idea parum. In illo puncto vidimus duas alias solutiones: rescribe nostrum codicem applicationis ad dispensandum in angustiis dilatis, vel "docere" pg_repack ad operandum cum illis. secundum nos elegimus.
Repone indices in nova tabula, cum angustiis differtur ab originali mensa
Propositum recognitionis apparebat - si in prima mensa distulit necessitatem, tunc pro novo debes talem coactionem creare, non indicem.
Ut mutationes nostras probemus, simplex experimentum scripsimus;
- mensam cum coacte dilato et uno recordo;
- inserere notitia in ansa quod cum recordo existenti pugnat;
- do an update - the data no longer conflicts;
- vices committere.
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;Versio originalis pg_repack semper prima inserta ingruebat, versio mutata sine erroribus laboravit. Magna.
Ad productionem imus et rursus errorem in eodem periodo exscribendi notitias e tabula stipendii ad novam accipimus:
$ ./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.Classica condicio: omnia opera in ambitus test, sed non in productione?
APPLY_COUNT ac confluentes duarum batches
Codicem litteraliter lineam per lineam analysim incepimus et punctum ponderis deteximus: a mensa stipes ad novam in batches transfertur, APPLY_COUNT constantem massae magnitudinem significavit:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}Quaestio est quod notitia ab originali transactione, in qua plures operationes potentiae necessitatem violare potuerunt, cum translatae sunt, in confluentia duarum batches finire possunt - dimidium mandatorum committetur in prima massa, et altera dimidia. in secundo. Et hic secundum fortunam tuam: si iunctos non violant aliquid in prima massa, tum denique omnia, sed si faciunt, error occurrit.
APPLY_COUNT aequalis est 1000 monumentis, quae idcirco cur nostrae probationes successerint - casum "coniunctionis massam" non contexerunt. Duo imperia - inserta et renovatio usi sumus, adeoque 500 negotiorum duorum mandatorum semper in batch posita erant nec difficultates ullas experiebamus. Addita secunda renovatione, recensio nostra opere destiterat;
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;Proximum igitur munus est efficere ut notitia ex tabula originali, quae in uno negotio mutata est, in nova tabula etiam intra unam transactionem desinat.
Negatis batching
Et iterum duas solutiones habuimus. Primum: partitionem in batches omnino deseramus et notitias in uno negotio transferamus. Commodum huius solutionis erat eius simplicitas - mutationes codicis requisiti minimae erant (obiter in versionibus vetustis pg_reorg prorsus sicut illa laboraverunt). Sed quaestio est - creamus rem diuturnam, et haec, ut ante dictum est, periculum est cessum novi bloati.
Secunda solutio est complexior, sed probabiliter magis emendatior: columnam creare in tabula loga cum identificatorio transactionis quae datae tabulae additae sunt. Deinde, cum notitias imitamur, hoc attributo colligere possumus et efficere ut mutatae cognatae simul transferantur. Massa formabitur ex pluribus negotiis (vel unum magnum unum) et magnitudo eius variabit secundum quantitatem notitiae in his rebus commutatae. Interest notandum quod, cum notitia ex diversis negotiis intrat in truncum in temere ordine, non amplius poterit eam continue, sicut prius, legere. seqscan pro qualibet petitione eliquare per tx_id nimis carus est, index necessarius est, sed etiam retardabit methodum ad caput augendi. In genere, ut semper, aliquid sacrificare debes.
Itaque cum prima optio, ut simplicius, constituimus. Primo necessarium erat intelligere utrum longa transactio realis esset quaestio. Cum principalis translationis notitiarum ex veteri tabula ad novam etiam in longo negotio occurrat, quaestio in "quantum hanc rem augebimus?" Duratio primae transactionis maxime ex magnitudine mensae dependet. Duratio novi unius dependet ex quot mutationibus cumulant in tabula in notitia translationis, i.e. de intensione oneris. Decursu pg_repack accidit in tempore minimi oneris servitii, et volumen mutationum minus proportionabiliter erat comparatum ad magnitudinem tabulae originalis. Statuimus nos tempus novae transactionis negligere posse (pro comparatione, mediocris horae et 1-2 minutarum).
Experimenta positiva erant. Lorem in purus etiam. Ad evidentiam, hic est imago cum magnitudine unius databases post cursum;
Cum hac solutione omnino contenti essemus, alterum efficere non conaremur, sed possibilitatem tractandi cum extensione tincidunt tractamus. Nostra recognitio currentis, proh dolor, nondum ad publicationem parata est, quoniam problema solum cum restrictionibus singularibus differtur solvitur, et ad plenitudinem plenae discursionis ad alia genera sustentanda necessaria est. Id in futurum facere posse speramus.
Fortasse quaestionem habes, cur etiam in hac fabula cum modificatione pg_repack implicavimus, et non, exempli gratia, eius analogis usi sumus? Aliquando etiam de hoc cogitavimus, sed experientia positiva utendi ea antea, in tabulis sine angustiis dilatis, moverunt nos ad intellegendam quaestionem essentiae cognoscendae ac reficere. Praeter alias solutiones utentes etiam tempus ad probationes peragendas postulat, ita statuimus ut primum quaestionem in ea figere conemur, et si intellexerimus nos hoc tempore rationabili non posse facere, tunc analoga inspicere incipiamus. .
Inventiones
Quae commendare possumus in nostra experientia;
- Monitor vestri bloat. Ex magna notitia, intelligere potes quam bene autovacuum configuratur.
- AUTOVACUUM accommodare ut acceptum bloatum servare gradu.
- Si bloat adhuc crescit et instrumenta archa utens superare non potes, extensionibus externis uti noli timere. Summa est omnia probe temptare.
- Noli timere solutiones externas mutare ad usus tuos necessitates - interdum hoc efficacius et facilius etiam quam in codice tuo mutando potest.
Source: www.habr.com