{"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 baasi p\u00e4ringute optimeerimine B2B teenuse n\u00e4itel ehitajatele","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Kuidas kasvatada B2B p\u00e4ringute arvu k\u00fcmme korda, kolimata j\u00f5udsamale serverile ja s\u00e4ilitades s\u00fcsteemi t\u00f6\u00f6v\u00f5ime? R\u00e4\u00e4gin, kuidas me v\u00f5itlesime andmebaasi j\u00f5udluse langusega, kuidas me optimeerisime SQL p\u00e4ringud nii, et teenindada v\u00f5imalikult palju kasutajaid ja mitte suurendada kulutusi arvutusressurssidele.
\n<\/a><\/noindex>
\nTegelen teenuse loomisega \u00e4riprotsesside haldamiseks ehitusettev\u00f5tetes. Meiega t\u00f6\u00f6tab umbes 3000 ettev\u00f5tet. \u00dcle 10 000 inimese t\u00f6\u00f6tab igap\u00e4evaselt meie s\u00fcsteemis 4\u201310 tundi. See lahendab mitmesuguseid planeerimise, teavitamise, hoiatamise ja valideerimise \u00fclesandeid... Me kasutame PostgreSQL 9.6. Meie andmebaasis on umbes 300 tabelit ja iga p\u00e4ev laekub sinna kuni 200 miljonit p\u00e4ringut (10 000 erinevat). Keskmiselt on meil 3\u20134 tuhat p\u00e4ringut sekundis, k\u00f5ige aktiivsematel hetkedel \u00fcle 10 000 p\u00e4ringu sekundis. Suur osa p\u00e4ringutest on OLAP. Lisamised, muutmised ja kustutused on oluliselt v\u00e4hem, seega OLTP koormus on suhteliselt v\u00e4ike. K\u00f5iki neid numbreid t\u00f5in v\u00e4lja, et saaksite hinnata meie projekti ulatust ja m\u00f5ista, kui v\u00e4\u00e4rtuslik meie kogemus teie jaoks v\u00f5iks olla.<\/p>\n

Esimene pilt. L\u00e4\u00e4neline<\/h3>\n

\nKui me arendust alustasime, ei m\u00f5elnud me eriti sellele, milline koormus langeb andmebaasile ja mida me teeme, kui server enam ei jaksaks. Andmebaasi projekteerimisel j\u00e4rgnesime \u00fcldistele soovitustele ja p\u00fc\u00fcdsime mitte endale jalga tulistada, kuid ei l\u00e4inud kaugemale \u00fcldistest n\u00f5uannetest nagu \"\u00e4rge kasutage mustrit Entity Attribute Values<\/a><\/noindex> ,\" millega me ei olnud tuttavad. Projekteerisime normeerimise p\u00f5him\u00f5tetest l\u00e4htuvalt, v\u00e4ltides andmete liigset kopeerimist, ja ei tundnud muret erinevate p\u00e4ringute kiirususe p\u00e4rast. Kui esimesed kasutajad ilmusid, seisime silmitsi j\u00f5udlusprobleemidega. Nagu tavaliselt, olime selleks t\u00e4iesti ette valmistamata. Esimesed probleemid olid lihtsad. K\u00f5ik lahendati tavaliselt uue indeksi lisamisega. Kuid j\u00f5udis hetk, mil lihtsad lahendused enam ei t\u00f6\u00f6tanud. Teades, et meie kogemus ei ole piisav ja meil on \u00fcha keerulisem aru saada, mis probleemide p\u00f5hjuseks on, palkasime spetsialiste, kes aitasid meil serverit \u00f5igesti seadistada, \u00fchendada j\u00e4lgimisega ja n\u00e4itasid meile, kuhu vaadata, et saada statistikat<\/a><\/noindex>.<\/p>\n

Teine pilt. Statistiline<\/h3>\n

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

Kuna ei olnud v\u00f5imalik optimeerida k\u00f5iki 10 000 p\u00e4ringut, otsustasime m\u00f5ista, kuhu suunata oma j\u00f5upingutusi, et t\u00f5sta andmebaasi j\u00f5udlust \u00f5igesti. P\u00e4rast mitu iteratsiooni hakkasime p\u00e4ringud liigitama t\u00fc\u00fcpideks.<\/p>\n

TOP p\u00e4ringud<\/h4>\n

\nNeed on k\u00f5ige raskemad p\u00e4ringud, mis v\u00f5tavad k\u00f5ige rohkem aega (kokku aega). Need on p\u00e4ringud, mida kas kutsutakse v\u00e4ga sageli v\u00f5i p\u00e4ringud, mis t\u00e4idetakse v\u00e4ga kaua (pikad ja sagedased p\u00e4ringud on optimeeritud juba varasemates iteratsioonides kiirusv\u00f5itluses). L\u00f5ppkokkuv\u00f5ttes kulutab server nende t\u00e4itmiseks k\u00f5ige rohkem aega. Samuti on oluline eristada tipup\u00e4ringud \u00fcldise t\u00e4itmise ajaga ja eraldi IO ajaga. Nende p\u00e4ringute optimeerimise viisid on veidi erinevad.<\/p>\n

Tavaline praktika k\u00f5igis ettev\u00f5tetes on t\u00f6\u00f6tada TOP p\u00e4ringutega. Neid on v\u00e4he, \u00fchegi p\u00e4ringu optimeerimine v\u00f5ib vabastada 5-10% ressursse. Kuid projekti \u201ekasvades\u201d muutub TOP p\u00e4ringute optimeerimine \u00fcha keerulisemaks. K\u00f5ik lihtsad meetodid on juba rakendatud ning isegi k\u00f5ige \u201eraske\u201d p\u00e4ring v\u00f5tab \u201evaid\u201d 3-5% ressursse. Kui TOP p\u00e4ringud kokku v\u00f5tavad alla 30-40% ajast, siis t\u00f5en\u00e4oliselt olete juba teinud j\u00f5upingutusi, et need t\u00f6\u00f6taksid kiirelt, ja on aeg liikuda j\u00e4rgmise grupi p\u00e4ringute optimeerimise juurde.
\nJ\u00e4\u00e4b vastata k\u00fcsimusele, kui palju \u00fclemisi p\u00e4ringuid sellesse gruppi lisada. Ma tavaliselt v\u00f5taksin mitte v\u00e4hem kui 10, kuid mitte rohkem kui 20. P\u00fc\u00fcan tagada, et TOP grupi esimese ja viimase t\u00e4itmise aeg ei erine rohkem kui 10 korda. See t\u00e4hendab, et kui p\u00e4ringute t\u00e4itmise aeg langeb j\u00e4rsult 1. kohalt 10. kohale, 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 tulevad kohe p\u00e4rast TOP-i, v\u00e4lja arvatud viimased 5-10%. Tavalise optimeerimise puhul seisneb just nende p\u00e4ringute juures v\u00f5imalus oluliselt t\u00f5sta serveri j\u00f5udlust. Need p\u00e4ringud v\u00f5ivad \u201ekaaluda\u201d kuni 80%. Kuid isegi kui nende osakaal \u00fcletab 50%, on aeg vaadata neile l\u00e4hemalt.<\/p>\n

Saba (tail)<\/h4>\n

\nNagu \u00f6eldud, tulevad need p\u00e4ringud l\u00f5pus ja nendele kulub 5-10% ajast. Neist v\u00f5ib unustada, kui te ei kasuta automaatseid anal\u00fc\u00fcsivahendeid, siis v\u00f5ib nende optimeerimine samuti olla odav.<\/p>\n

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

Kasutame SQL-p\u00e4ringut, mis aitab sellist hinnaandmist teha PostgreSQL jaoks (olen kindel, et paljude teiste andmebaasis\u00fcsteemide jaoks saab kirjutada sarnase p\u00e4ringu).<\/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 tulemus \u2013 kolm veergu, millest iga\u00fcks sisaldab protsent aega, mis kulub selle grupi p\u00e4ringute t\u00f6\u00f6tlemiseks. P\u00e4ringu sees on kaks numbrit (minu puhul need on 20 ja 800), mis eraldavad grupi \u00fchte t\u00fc\u00fcpi p\u00e4ringud teistest.<\/p>\n
Nii seondub p\u00e4ringute osakaal optimeerimise algusaegadel ja praegu.<\/p>\n
\"Andmete
\n
\nDiagrammist on n\u00e4ha, et TOP p\u00e4ringute osakaal on j\u00e4rsult v\u00e4henenud, aga \u201ckeskmike\u201d osakaal on suurenenud.
\nAlguses t\u00f5id TOP p\u00e4ringud sisse ilmseid vigu. Aja jooksul kadusid lapsehaigused, TOP p\u00e4ringute osakaal v\u00e4henes, 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 loetelu k\u00f5ige sagedamini kasutatud meetoditest, mis aitasid meil kiirendada TOP p\u00e4ringuid:<\/p>\n