Quomodo 10 times crescere numerum quaesitorum ad datorum sine motu ad fecundiorem cultorem et systematis functionem conservandam? Dicam tibi quomodo egimus de fre- quenter in faciendis database nostri, quomodo optimized SQL queries ut serviat quam plurimis usoribus quam maxime, nec sumptus computandi facultates augeat.
Ministerium facio pro processuum negotiorum administrandi in societatibus constructionis. Circiter III milia societatum nobiscum laborant. Plus quam X milia hominum cum systemate nostro cotidie pro 3-10 horis laborant. Varias quaestiones solvit consilio, notificatione, admonitione, sanatione... PostgreSQL 4 utimur. Habemus circiter CCC tabulas in datorum datorum et usque ad CC miliones quaesitorum (X milia diversarum) cotidie recipiuntur. In mediocris habemus petitiones 10-9.6 milia secundo, momenta acerrima plusquam 300 milia petitionum secundorum. Plurimae interrogationes sunt OLAP. Multo pauciora sunt additamenta, modificationes et deletiones, quae significant OLTP sarcina levis respective. Hos omnes numeros ita dedi ut propositi nostri scalam perpendere possis et quam utilem experientiam nostram tibi esse intelligas.
Imaginare unum. Lyrical
Cum progressionem incepimus, vere cogitabamus quale onus in datorum caderet et quid faceremus si minister trahere desierit. Cum meditantes database, generales commendationes secuti sumus et nos in pede non iaculamur, sed consilium generale ultra modum "nolite uti exemplaris. non intravimus. Disposuimus secundum principia ordinationem, notitias nugationem vitantes et non curavimus de velocitate certarum inquisitionum. Ut primum utentes advenerunt, problema perficiendum invenimus. Ad hoc, ut fit, omnino imparati fuimus. Prima problemata simplex evasit. Pro regula omnia soluta sunt addito novo indice. Sed tempus venit cum simplex inaequaliter ab opere cessavit. Sentientes nos experientia carere et in dies magis difficilis est nobis ad intellegendum quid problemata moveant, artifices conduximus, qui nos adiuverunt ut servers recte, vigilantia coniungeret, et ostenderet nos ubi quaereret ut perveniamus. .
Imagine duo. Statistical
Ita habemus circiter X milia quaesitorum diversarum quae in nostro datorum diurno exsecutioni sunt. Horum X milia monstra sunt quae 10-10 decies centena tempora exsecutionis mediocris tempore 2-3 exercentur, et quaesita sunt cum mediocris exsecutionis tempore 0.1 secundis quae 0.3 vicibus in die appellantur.
Non potuimus omnia decem milia quaesitorum optimizare, itaque decrevimus ubinam conatus nostros dirigere ad rectam datorum observantiam emendare. Post aliquot iterationes postulationes in typos dividere coepimus.
TOP petitiones
Hae sunt interrogationes gravissimae quae tempus maxime capiunt. Hae sunt interrogationes quae vel persaepe dicuntur vel interrogationes quae longissimum tempus ad exsequendum capiunt (quaestiones longae et frequentes in primis iterationibus pugnae velocitatis sunt optimized). Quam ob rem servo maxime tempore in executione versatur. Praeterea interest summas petitiones summas exsecutionis tempus et separatim ab IO tempore separare. Modi optimizandi huiusmodi quaerendi leviter diversae sunt.
Solet consuetudo omnium societatum cum TOP petitionibus laborare. Pauci ex eis sunt, optimizing etiam una quaestio 5-10% opum liberare potest. Nihilominus, ut consilium maturescit, optimizing TOP queries, munus non leve magis magisque fit. Omnes methodi simplices iam peractae sunt, ac maxima petitio "tantum" 3-5% facultatum "gravis" sumit. Si TOP queries in summa minus quam 30-40% temporis capere, maxime verisimile es iam conatus es ut celeriter eas laboret et tempus est ad quaestiones optimizing ex proximo coetu movere.
Reliquum est ut quaestionem respondeam quot supremae interrogationes in hoc coetu includi debeant. Soleo saltem 10, sed non plus quam 20. efficere conor ut tempus primi et ultimi in circulo TOP a nemine plus quam 10 tempore differat. Hoc est, si inquisitionis executionis tempus acriter ab 1st loco ad 10th defluit, tunc sumo TOP-X, si gutta magis gradatim est, tunc amplitudinem globi ad 10 vel 15 augeo.
Medi rustici
Hae sunt omnes petitiones quae immediate post TOP veniunt, excepta ultima 5-10%. Solet in his quaestionibus optimizing occasionem praebere ut servo perficientur magnopere augeat. Hae petitiones usque ad 80% aestimare possunt. Sed etiamsi eorum participes L% excesserunt, tempus est eas diligentius intueri.
Cauda
Ut, hae interrogationes in fine veniunt et 5-10% temporis capiunt. De his tantum oblivisci potes si instrumenta analysis inquisitionis automatariae non uteris, tum ea optimizing possunt etiam vilia esse.
Quomodo singulos coetus aestimare?
SQL interrogatione utor, quae iuvat aestimationem talem facere pro PostgreSQL (certus sum quod simile quid pro multis aliis DBMSs scribi possit)
SQL quaesitum est ut magnitudinem TOP-MEDII CAUDAE TURBEN
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Proventus quaesitionis est tres columnas, quarum singulae recipis temporis spatium capit ut quaestionibus ex hoc circulo processum. Intus petitione duo numeri sunt (me in casu XX et DCCC) qui petitiones ab uno coetu ab altero separant.
Hoc est quomodo partes petitionum proxime comparant tempore optimization operis incohato et nunc.
Tabula ostendit portionem TOP petitionum acriter decrevisse, sed "medii rustici" auctos esse.
Primo, rogationes TOP inclusas blatantes culpas. Subinde morbi pueriles evanuerunt, petitiones TOP participes decreverunt, et magis ac magis laborandum est ut difficiles petitiones accelerare debeant.
Ut textus petitionum utimur sequenti petitione
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Hic est index technicorum vulgarium adhibitorum quae nos adiuverunt ad TOP queries:
- Redesign systematis, exempli gratia, notificationem logicam relaborare utens nuntium sectorem pro quaestionibus periodicis datorum datorum
- Addere vel mutantur indices
- Rewriting ORM queries ut pura SQL
- Rewriting piger data loading logica
- Caching per notitia denormalization. Exempli gratia: mensam nexum traditio -> Cautionem -> Request -> Applicatio. Id est, unaquaeque traditio cum applicatione per alias tabulas associatur. Ut omnes tabulas in unaquaque petitione non colligant, nexum petitionis in tabula Delivery duplicavimus.
- Tabulae staticae caching cum libris referentibus et raro tabulae in programmatis memoria mutantur.
Interdum mutationes ad infigmentum redesignum pertinebant, sed 5-10% oneris systematis dabant et iustificabantur. Subinde exhausti minores et minores facti sunt, et magis ac graviora redesigno requirebatur.
Deinde animum intendimus in alterum coetum petitionum β mediorum agrestium coetum. Plures in eo sunt queries et multum temporis ad totam coetum resolvendum visum est. Maxime autem queries evasit valde simplex ad optimize, et multae quaestiones justo temporum in diversis variationibus iteratae sunt. Hic sunt exempla quorundam optimizationum typicarum quae justos de similibus quaestionibus applicavimus et singulas coetus quaestionum optimizedium datorum per 3-5% exoneramus.
- Instead of checking for the presence of records using count and a full table scan, EXISTS adhiberi coepit"
- DISTINCTUM (nulla recipe generale, sed interdum facile eam removere potes per 10-100 temporibus instantiam accelerando).
Exempli gratia, loco quaesiti est omnes rectores eligere ex magna mensa traditionum (TRADITIO)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDquaesitum est in comparatione parva mensa PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Videtur quod subquerio connectuntur usi sumus, sed dat velocitatem plurium quam 10 temporum.
- In multis casibus, Comes omnino relictus est et
- pro
UPPER(s) LIKE JOHN%βusus
s ILIKE βJohn%β
Singulae instantiae specificae interdum 3-1000 temporibus incitatae sunt. Quamvis gravissima effectus, primum nobis visum est nullum punctum in optimizing investigatione quae 10 ms ad perficiendum capit, una ex 3 centum quaestionibus gravissimae est et centesimas centesimas totius oneris datorum sumit. Sed eandem recipe ad coetus eiusdem generis interrogationes applicando, paucas centesimas reportamus. Ne tempus manuale omnia centenis quaestionibus recensendis tereremus, plura scripta simplicia scripsimus quae regularibus expressionibus usi sunt ut interrogationes eiusdem generis invenirent. Quam ob rem, automatice circulos quaerendorum quaerentes cum modico labore ad ulteriora perficienda emendare nobis permiserunt.
Quam ob rem in eisdem ferramentis per tres annos iam laboravimus. Mediocris onus cotidianum est circiter 30%, in cacumina attingit 70%. Numerus petitionum, sicut numerus utentium, circiter X partibus auctus est. Atque haec omnia gratias constanti vigilantia eorundem coetuum TOP-MEDIUM poscit. Quamprimum in TOP coetus nova petitio apparet, statim eam resolvere ac maturare conamur. Medium coetus semel in hebdomada recenseamus utens scriptorum analysi interrogatione. Si novas interrogationes invenimus quas iam optimizare novimus, eas cito commutamus. Interdum novas optimizationes modos invenimus quae pluribus quaestionibus simul applicari possunt.
Secundum praenotiones nostras, server current incrementum in numero utentium ab alio 3-5 tempore sustinebit. Verum unum ace- manicam nostram habemus - SELECT queries ad speculum adhuc non transtulimus, ut commendatur. Sed hoc scienter non facimus, quia possibilitates "doloris" optimizationis ante omnia exhaurire cupimus antequam in "gravis tormentorum" convertatur.
Respectus criticus ad opus factum suggerere potest utendo scalae verticales. Potentiore servo eme loco tortorum tempus terendo. Servus non multum constare potest, praesertim cum limites scalarum verticalium nondum exsanguis. Sed numerus petitionum tantum decem partibus auctus est. Pluribus annis decursu functionis systematis auxit et nunc plura genera petitionum sunt. Gratias ad caching, functionality quae exstitit in paucioribus petitionibus et efficacibus petitionibus perficitur. Hoc modo tuto multiplicare potes ab alio 10 ut coefficiens realis acceleratio. Itaque, secundum plurimas aestimationes optimas, dicere possumus accelerationem 5 temporum vel plurium fuisse. Perpendiculariter inclinata servo plus 50 temporibus constaret. Imprimis considerans quod semel optimatio exercetur totum tempus operatur, et rogatio pro servo conducto quolibet mense venit.
Source: www.habr.com