{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Andmete p\u00e4ringute optimeerimine B2B teenuse n\u00e4itel ehitajatele","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Kuidas kasvatada andmebaasi p\u00e4ringute arvu k\u00fcmme korda, mitte liikudes j\u00f5udsamale serverile ja s\u00e4ilitada s\u00fcsteemi t\u00f6\u00f6v\u00f5ime? R\u00e4\u00e4gin, kuidas me v\u00f5itlesime andmebaasi j\u00f5udluse languse vastu, kuidas optimeerisime SQL-p\u00e4ringuid, et teenindada v\u00f5imalikult palju kasutajaid ja mitte suurendada arvutusressursside kulusid.
\n<\/a><\/noindex>
\nMa teen teenuseid ehitusettev\u00f5tete \u00e4riprotsesside haldamiseks. Meiega t\u00f6\u00f6tab ligikaudu 3000 ettev\u00f5tet. Iga p\u00e4ev kasutab meie s\u00fcsteemi \u00fcle 10 000 inimese 4-10 tundi. See lahendab erinevaid \u00fclesandeid, sealhulgas planeerimine, teavitamine, hoiatamine, valideerimine jne. Me kasutame PostgreSQL 9.6. Andmebaasis on meil umbes 300 tabelit ja iga p\u00e4ev tuleb sinna kuni 200 miljonit p\u00e4ringut (10 000 erinevat). Keskmiselt on meil 3-4 tuhat p\u00e4ringut sekundis, k\u00f5ige aktiivsematel hetkedel \u00fcle 10 000 p\u00e4ringu sekundis. Suur osa p\u00e4ringutest on OLAP. Uute andmete lisamine, muudatused ja kustutamised on oluliselt v\u00e4hem, seega OLTP koormus on suhteliselt v\u00e4ike. K\u00f5iki neid numbreid olen v\u00e4lja toonud, et teil oleks v\u00f5imalik hinnata meie projekti ulatust ning m\u00f5ista, kui kasulik meie kogemus v\u00f5ib teile olla.<\/p>\n

Esimene pilt. L\u00fc\u00fcriiline<\/h3>\n

\nKui me arenduse alustasime, ei m\u00f5elnud me eriti sellele, milline koormus langeb andmebaasile ja mida me teeme, kui server enam ei suuda. Andmebaasi projekteerimisel j\u00e4rgnesime \u00fcldistele soovitustele ja p\u00fc\u00fcdsime mitte endale jalga tulistada, kuid edasi liikudes olid \u00fcldised n\u00f5uanded, nagu \"\u00e4rge kasutage mustrit Entity Attribute Values<\/a><\/noindex> Me ei olnudun. Me projekteerisime l\u00e4htudes normaliseerimise p\u00f5him\u00f5tetest, v\u00e4ltides andmete liigset kordumist ning ei muretsenud teatud p\u00e4ringute kiirendamise p\u00e4rast. Kui esimesed kasutajad tulid, kohtasime j\u00f5udlusprobleeme. Nagu tavaliselt, olime t\u00e4iesti selleks ette valmistamata. Esimesed probleemid olid lihtsad. Enamasti lahenesid asjad uue indeksi lisamisega. Kuid tuli hetk, mil lihtsad t\u00e4iendused enam ei t\u00f6\u00f6tanud. Teades, et meie kogemused ei piisa ja meil on \u00fcha raskem m\u00f5ista, mis probleemide p\u00f5hjused on, palkasime spetsialiste, kes aitasid meil serveri korralikult seadistada, monitorimise \u00fchendada ning n\u00e4itasid, kuhu vaadata, et saada. statistika<\/a><\/noindex>.<\/p>\n

Teine pilt. Statistika<\/h3>\n

\nNii et meil on umbes 10 000 erinevat p\u00e4ringut, mis meie andmebaasis t\u00e4idetakse \u00f6\u00f6p\u00e4evas. Nendest 10 000-st on koletised, mida t\u00e4idetakse 2-3 miljonit korda keskmise t\u00e4itmisajaga 0,1-0,3 ms, ja on p\u00e4ringud, mille keskmine t\u00e4itmisaja on 30 sekundit ja mis kutsutakse esile 100 korda p\u00e4evas.<\/p>\n

Kuna 10 tuhat p\u00e4ringut polnud v\u00f5imalik optimeerida, otsustasime, kuhu suunata j\u00f5upingutused andmebaasi j\u00f5udluse t\u00f5stmiseks. P\u00e4rast mitmeid iteratsioone hakkasime p\u00e4ringud liigitama t\u00fc\u00fcpide j\u00e4rgi.<\/p>\n

TOP p\u00e4ringud<\/h4>\n

\nNeed on k\u00f5ige raskemad p\u00e4ringud, mis v\u00f5tavad k\u00f5ige rohkem aega (kogu aeg). Need on p\u00e4ringud, mida kas kutsutakse v\u00e4ga sageli v\u00f5i p\u00e4ringud, mis vajavad v\u00e4ga pikka t\u00e4itmist (pikad ja sagedased p\u00e4ringud on juba varasematel iteratsioonidel kiirusv\u00f5itluses optimeeritud). Kokku kulutab server nende t\u00e4itmiseks k\u00f5ige rohkem aega. Oluline on eristada top p\u00e4ringud \u00fcldise t\u00e4itmise aja j\u00e4rgi ja eraldi IO aja j\u00e4rgi. Selliste p\u00e4ringute optimeerimise viisid on veidi erinevad.<\/p>\n

K\u00f5igi ettev\u00f5tete tavaline praktika on t\u00f6\u00f6tada TOP-p\u00e4ringutega. Need on v\u00e4he, \u00fche p\u00e4ringu optimeerimine v\u00f5ib vabastada 5-10% ressursse. Siiski, projekti \u201ek\u00fcpsemise\u201d k\u00e4igus muutub TOP-p\u00e4ringute optimeerimine j\u00e4rjest keerukamaks \u00fclesandeks. K\u00f5ik lihtsad meetodid on juba katsetatud ja isegi k\u00f5ige \u201eraske\u201d p\u00e4ring kasutab \u201eainult\u201d 3-5% ressursse. Kui TOP-p\u00e4ringud kokku kulutavad v\u00e4hem kui 30-40% ajast, siis olete t\u00f5en\u00e4oliselt juba pingutanud selle nimel, et need t\u00f6\u00f6taksid kiiresti, ja n\u00fc\u00fcd on aeg liikuda j\u00e4rgmise grupi p\u00e4ringute optimeerimisele.
\nJ\u00e4\u00e4b vastata k\u00fcsimusele, kui palju k\u00f5rgeid p\u00e4ringuid sellesse gruppi kuulutada. Ma v\u00f5tan tavaliselt mitte v\u00e4hem kui 10, kuid mitte rohkem kui 20. Proovin, et esimese ja viimase p\u00e4ringu aega TOP-grupis erineks mitte rohkem kui 10 korda. St kui p\u00e4ringute t\u00f6\u00f6tlemise aeg langeb j\u00e4rsult 1. kohalt 10. kohale, siis v\u00f5tan TOP-10, kui langus on sujuvam, siis suurendan grupi suurust 15 v\u00f5i 20-ni.
\n\"Andmete
\n<\/p>\n

Keskmikud (medium)<\/h4>\n

\nNeed on k\u00f5ik p\u00e4ringud, mis l\u00e4hevad kohe TOP-i j\u00e4rel, v\u00e4lja arvatud viimased 5\u201310%. Just nende p\u00e4ringute optimeerimises peitub sageli v\u00f5imalus serveri j\u00f5udlust m\u00e4rgatavalt t\u00f5sta. Need p\u00e4ringud v\u00f5ivad moodustada kuni 80%. Kuid isegi kui nende osakaal \u00fcletab 50%, on aeg neile t\u00f5sisemalt pilk heita.<\/p>\n

Sabap\u00e4ringud (tail)<\/h4>\n

\nNagu \u00f6eldud, l\u00e4hevad need p\u00e4ringud viimaste hulka ja nendele kulub 5\u201310% ajast. Nendest v\u00f5ib unustada ainult siis, kui te ei kasuta automaatseid anal\u00fc\u00fcsivahendeid; siis v\u00f5ib nende optimeerimine samuti odavalt k\u00e4ia.<\/p>\n

Kuidas hinnata iga gruppi?<\/p>\n

Kasutangi SQL-p\u00e4ringut, mis aitab sellist hindamist teha PostgreSQL-is (olen kindel, et paljudele muudele andmebaasis\u00fcsteemidele on v\u00f5imalik kirjutada sarnane p\u00e4ring).<\/p>\n

SQL-p\u00e4ring TOP-MEDIUM-TAIL gruppide suuruse hindamiseks<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800              THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
P\u00e4ringu tulemused - kolm veergu, millest iga\u00fcks sisaldab protsenti ajast, mis kulub selle grupi p\u00e4ringute t\u00f6\u00f6tlemiseks. P\u00e4ringu sees on kaks numbrit (minu juhtumi j\u00e4rgi on need 20 ja 800), mis eraldavad \u00fche grupi p\u00e4ringud teistest.<\/p>\n
Nii umbes jagunevad p\u00e4ringute osakaalid optimeerimise alguses ja praegu.<\/p>\n
\"Andmete
\n
\nDiagrammist on n\u00e4ha, et TOP p\u00e4ringute osakaal on j\u00e4rsult v\u00e4henenud, kuid \"keskmike\" osakaal on suurenenud.
\nAlguses sattusid TOP p\u00e4ringutesse ilmsed vead. Aja jooksul lastehaigused kadusid, TOP p\u00e4ringute osakaal v\u00e4henes ja tuli pingutada \u00fcha rohkem, et aeglaseid p\u00e4ringuid kiirendada. <\/p>\n
P\u00e4ringute tekstide saamiseks kasutame j\u00e4rgmist p\u00e4ringut<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Siin on nimekiri k\u00f5ige sagedamini kasutatud meetoditest, mis aitasid meil TOP p\u00e4ringute kiirus t\u00f5sta:<\/p>\n