Sut i dyfu 10 gwaith y nifer o ymholiadau i'r gronfa ddata heb symud i weinydd mwy cynhyrchiol a chynnal ymarferoldeb system? Dywedaf wrthych sut y gwnaethom ymdrin â'r dirywiad ym mherfformiad ein cronfa ddata, sut y gwnaethom optimeiddio ymholiadau SQL i wasanaethu cymaint o ddefnyddwyr â phosibl a pheidio â chynyddu cost adnoddau cyfrifiadurol.
Rwy'n gwneud gwasanaeth ar gyfer rheoli prosesau busnes mewn cwmnïau adeiladu. Mae tua 3 mil o gwmnïau yn gweithio gyda ni. Mae mwy na 10 mil o bobl yn gweithio gyda'n system bob dydd am 4-10 awr. Mae'n datrys problemau amrywiol cynllunio, hysbysu, rhybuddio, dilysu... Rydym yn defnyddio PostgreSQL 9.6. Mae gennym tua 300 o dablau yn y gronfa ddata a derbynnir hyd at 200 miliwn o ymholiadau (10 mil o rai gwahanol) bob dydd. Ar gyfartaledd mae gennym 3-4 o geisiadau yr eiliad, ar yr adegau mwyaf gweithredol mwy na 10 mil o geisiadau yr eiliad. Mae'r rhan fwyaf o'r ymholiadau yn OLAP. Mae llawer llai o ychwanegiadau, addasiadau a dileadau, sy'n golygu bod y llwyth OLTP yn gymharol ysgafn. Darparais yr holl rifau hyn fel y gallwch asesu maint ein prosiect a deall pa mor ddefnyddiol y gall ein profiad fod i chi.
Llun un. Telynegol
Pan ddechreuon ni ddatblygu, ni wnaethom feddwl mewn gwirionedd pa fath o lwyth fyddai'n disgyn ar y gronfa ddata a beth fyddem yn ei wneud pe bai'r gweinydd yn rhoi'r gorau i dynnu. Wrth ddylunio’r gronfa ddata, fe wnaethom ddilyn argymhellion cyffredinol a cheisio peidio â saethu ein hunain yn y droed, ond aethom y tu hwnt i gyngor cyffredinol fel “peidiwch â defnyddio’r patrwm aethon ni ddim i mewn. Fe wnaethom ddylunio yn seiliedig ar egwyddorion normaleiddio, gan osgoi dileu swyddi ac nid oedd ots gennym am gyflymu rhai ymholiadau. Cyn gynted ag y cyrhaeddodd y defnyddwyr cyntaf, daethom ar draws problem perfformiad. Yn ôl ein harfer, roedden ni’n hollol barod ar gyfer hyn. Trodd y problemau cyntaf allan i fod yn syml. Fel rheol, datryswyd popeth trwy ychwanegu mynegai newydd. Ond daeth amser pan roddodd clytiau syml y gorau i weithio. Gan sylweddoli bod gennym ddiffyg profiad a'i bod yn dod yn fwyfwy anodd i ni ddeall beth sy'n achosi'r problemau, fe wnaethom gyflogi arbenigwyr a'n helpodd i sefydlu'r gweinydd yn gywir, cysylltu monitro, a dangos i ni ble i edrych i gael .
Llun dau. Ystadegol
Felly mae gennym tua 10 mil o wahanol ymholiadau sy'n cael eu gweithredu ar ein cronfa ddata bob dydd. O'r 10 mil hyn, mae angenfilod sy'n cael eu dienyddio 2-3 miliwn o weithiau gydag amser gweithredu cyfartalog o 0.1-0.3 ms, ac mae yna ymholiadau gydag amser gweithredu cyfartalog o 30 eiliad a elwir 100 gwaith y dydd.
Nid oedd yn bosibl gwneud y gorau o bob un o'r 10 mil o ymholiadau, felly fe benderfynon ni ddarganfod ble i gyfeirio ein hymdrechion er mwyn gwella perfformiad y gronfa ddata yn gywir. Ar ôl sawl iteriad, dechreuon ni rannu ceisiadau yn fathau.
Ceisiadau TOP
Dyma'r ymholiadau trymaf sy'n cymryd y mwyaf o amser (cyfanswm amser). Mae'r rhain yn ymholiadau sydd naill ai'n cael eu galw'n aml iawn neu ymholiadau sy'n cymryd amser hir iawn i'w gweithredu (gwellwyd ymholiadau hir ac aml yn fersiynau cyntaf y frwydr am gyflymder). O ganlyniad, mae'r gweinydd yn treulio'r amser mwyaf ar eu gweithredu. Ar ben hynny, mae'n bwysig gwahanu prif geisiadau yn ôl cyfanswm yr amser gweithredu ac ar wahân yn ôl amser IO. Mae'r dulliau ar gyfer optimeiddio ymholiadau o'r fath ychydig yn wahanol.
Arfer arferol pob cwmni yw gweithio gyda cheisiadau TOP. Ychydig ohonynt sydd; gall optimeiddio hyd yn oed un ymholiad ryddhau 5-10% o adnoddau. Fodd bynnag, wrth i'r prosiect aeddfedu, mae optimeiddio ymholiadau TOP yn dod yn dasg fwyfwy dibwys. Mae’r holl ddulliau syml eisoes wedi’u gweithio allan, ac mae’r cais mwyaf “trwm” yn cymryd “dim ond” 3-5% o adnoddau. Os yw cyfanswm ymholiadau TOP yn cymryd llai na 30-40% o’r amser, yna mae’n debyg eich bod eisoes wedi ymdrechu i wneud iddynt weithio’n gyflym ac mae’n bryd symud ymlaen i optimeiddio ymholiadau gan y grŵp nesaf.
Erys i ateb y cwestiwn faint o brif ymholiadau y dylid eu cynnwys yn y grŵp hwn. Fel arfer rwy'n cymryd o leiaf 10, ond dim mwy na 20. Rwy'n ceisio sicrhau nad yw amser y cyntaf a'r olaf yn y grŵp TOP yn amrywio mwy na 10 gwaith. Hynny yw, os yw amser gweithredu'r ymholiad yn gostwng yn sydyn o'r lle 1af i'r 10fed, yna rwy'n cymryd TOP-10, os yw'r gostyngiad yn fwy graddol, yna rwy'n cynyddu maint y grŵp i 15 neu 20.
Gwerinwyr canol
Mae'r rhain i gyd yn geisiadau sy'n dod yn syth ar ôl TOP, ac eithrio'r 5-10% diwethaf. Fel arfer, wrth optimeiddio'r ymholiadau hyn mae'r cyfle i gynyddu perfformiad gweinydd yn fawr. Gall y ceisiadau hyn bwyso hyd at 80%. Ond hyd yn oed os yw eu cyfran wedi bod yn fwy na 50%, yna mae'n bryd edrych arnynt yn fwy gofalus.
Cynffon
Fel y crybwyllwyd, mae'r ymholiadau hyn yn dod ar y diwedd ac yn cymryd 5-10% o'r amser. Dim ond os na fyddwch chi'n defnyddio offer dadansoddi ymholiad awtomatig y gallwch chi anghofio amdanyn nhw, yna gall eu hoptimeiddio fod yn rhad hefyd.
Sut i werthuso pob grŵp?
Rwy’n defnyddio ymholiad SQL sy’n helpu i wneud asesiad o’r fath ar gyfer PostgreSQL (rwy’n siŵr y gellir ysgrifennu ymholiad tebyg ar gyfer llawer o DBMS eraill)
Ymholiad SQL i amcangyfrif maint grwpiau TOP-MEDIUM-TAIL
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
Canlyniad yr ymholiad yw tair colofn, ac mae pob un yn cynnwys canran yr amser y mae'n ei gymryd i brosesu ymholiadau gan y grŵp hwn. Y tu mewn i'r cais mae dau rif (20 ac 800 yn fy achos i) sy'n gwahanu ceisiadau gan un grŵp oddi wrth y llall.
Dyma sut mae'r cyfrannau o geisiadau yn cymharu'n fras ar yr adeg y dechreuodd y gwaith optimeiddio a nawr.
Mae’r diagram yn dangos bod cyfran y ceisiadau TOP wedi gostwng yn sylweddol, ond mae’r “gwerinwyr canol” wedi cynyddu.
Ar y dechrau, roedd y ceisiadau TOP yn cynnwys camsyniadau amlwg. Dros amser, diflannodd afiechydon plentyndod, gostyngodd cyfran y ceisiadau TOP, a bu'n rhaid gwneud mwy a mwy o ymdrechion i gyflymu ceisiadau anodd.
I gael testun ceisiadau rydym yn defnyddio'r cais canlynol
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
Dyma restr o'r technegau a ddefnyddir amlaf i'n helpu i gyflymu ymholiadau TOP:
- Ailgynllunio'r system, er enghraifft, ail-weithio'r rhesymeg hysbysu gan ddefnyddio brocer negeseuon yn lle ymholiadau cyfnodol i'r gronfa ddata
- Ychwanegu neu newid mynegeion
- Ailysgrifennu ymholiadau ORM i SQL pur
- Ailysgrifennu rhesymeg llwytho data diog
- Cadw drwy ddadnormaleiddio data. Er enghraifft, mae gennym gysylltiad bwrdd Cyflwyno -> Anfoneb -> Cais -> Cais. Hynny yw, mae pob dosbarthiad yn gysylltiedig â chais trwy dablau eraill. Er mwyn peidio â chysylltu'r holl dablau ym mhob cais, gwnaethom ddyblygu'r ddolen i'r cais yn y tabl Cyflawni.
- Cadw tablau statig gyda chyfeirlyfrau ac anaml y newidir tablau yng nghof y rhaglen.
Weithiau roedd y newidiadau'n gyfystyr ag ailgynllunio trawiadol, ond fe wnaethant ddarparu 5-10% o lwyth y system ac fe'u cyfiawnhawyd. Dros amser, daeth y gwacáu yn llai ac yn llai, ac roedd angen ailgynllunio mwy a mwy difrifol.
Yna fe wnaethom droi ein sylw at yr ail grŵp o geisiadau - y grŵp o werinwyr canol. Mae llawer mwy o ymholiadau ynddo ac roedd yn ymddangos y byddai'n cymryd llawer o amser i ddadansoddi'r grŵp cyfan. Fodd bynnag, roedd y rhan fwyaf o ymholiadau yn syml iawn i'w optimeiddio, a chafodd llawer o broblemau eu hailadrodd ddwsinau o weithiau mewn amrywiadau gwahanol. Dyma enghreifftiau o rai optimeiddiadau nodweddiadol y gwnaethom eu cymhwyso i ddwsinau o ymholiadau tebyg a dadlwythodd pob grŵp o ymholiadau optimized y gronfa ddata 3-5%.
- Yn hytrach na gwirio am bresenoldeb cofnodion gan ddefnyddio COUNT a sgan bwrdd llawn, dechreuwyd defnyddio EXISTS
- Cael gwared ar NODWEDDOL (nid oes rysáit cyffredinol, ond weithiau gallwch chi gael gwared arno'n hawdd trwy gyflymu'r cais 10-100 gwaith).
Er enghraifft, yn lle ymholiad i ddewis pob gyrrwr o fwrdd mawr o ddanfoniadau (DARPARU)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDgwneud ymholiad ar fwrdd cymharol fach 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)Mae'n ymddangos ein bod wedi defnyddio subquery cydberthynol, ond mae'n rhoi cyflymiad o fwy na 10 gwaith.
- Mewn llawer o achosion, rhoddwyd y gorau i COUNT yn gyfan gwbl a
- yn hytrach na
UPPER(s) LIKE JOHN%’defnyddio
s ILIKE “John%”
Roedd pob cais penodol weithiau'n cael ei gyflymu 3-1000 o weithiau. Er gwaethaf y perfformiad trawiadol, ar y dechrau roedd yn ymddangos i ni nad oedd unrhyw bwynt mewn optimeiddio ymholiad sy'n cymryd 10 ms i'w gwblhau, yn un o'r 3ydd cant o ymholiadau trymaf, ac yn cymryd canfed ran o'r amser llwyth cronfa ddata cyffredinol. Ond trwy gymhwyso'r un rysáit i grŵp o ymholiadau o'r un math, fe enillon ni ychydig y cant yn ôl. Er mwyn peidio â gwastraffu amser yn adolygu pob un o'r cannoedd o ymholiadau â llaw, fe wnaethom ysgrifennu sawl sgript syml a oedd yn defnyddio ymadroddion rheolaidd i ddod o hyd i ymholiadau o'r un math. O ganlyniad, roedd chwilio grwpiau o ymholiadau yn awtomatig yn ein galluogi i wella ein perfformiad ymhellach gydag ychydig o ymdrech.
O ganlyniad, rydym wedi bod yn gweithio ar yr un caledwedd ers tair blynedd bellach. Mae'r llwyth dyddiol cyfartalog tua 30%, mewn brigau mae'n cyrraedd 70%. Mae nifer y ceisiadau, yn ogystal â nifer y defnyddwyr, wedi cynyddu tua 10 gwaith. A hyn i gyd diolch i fonitro cyson yr un grwpiau hyn o geisiadau CANOLIG TOP. Cyn gynted ag y bydd cais newydd yn ymddangos yn y grŵp TOP, rydym yn ei ddadansoddi ar unwaith ac yn ceisio ei gyflymu. Rydym yn adolygu'r grŵp CANOLIG unwaith yr wythnos gan ddefnyddio sgriptiau dadansoddi ymholiad. Os byddwn yn dod ar draws ymholiadau newydd yr ydym eisoes yn gwybod sut i'w optimeiddio, byddwn yn eu newid yn gyflym. Weithiau rydym yn dod o hyd i ddulliau optimeiddio newydd y gellir eu cymhwyso i sawl ymholiad ar unwaith.
Yn ôl ein rhagolygon, bydd y gweinydd presennol yn gwrthsefyll cynnydd yn nifer y defnyddwyr 3-5 gwaith arall. Yn wir, mae gennym un ace arall i fyny ein llawes - nid ydym wedi trosglwyddo ymholiadau SELECT i'r drych o hyd, fel yr argymhellir. Ond nid ydym yn gwneud hyn yn ymwybodol, oherwydd yn gyntaf rydym am ddihysbyddu'r posibiliadau o optimeiddio “clyfar” yn llwyr cyn troi'r “magnelau trwm” ymlaen.
Gall edrych yn feirniadol ar y gwaith a wnaed awgrymu defnyddio graddio fertigol. Prynwch weinydd mwy pwerus yn lle gwastraffu amser arbenigwyr. Efallai na fydd y gweinydd yn costio cymaint â hynny, yn enwedig gan nad ydym eto wedi disbyddu terfynau graddio fertigol. Fodd bynnag, dim ond nifer y ceisiadau a gynyddodd 10 gwaith. Dros nifer o flynyddoedd, mae ymarferoldeb y system wedi cynyddu ac erbyn hyn mae mwy o fathau o geisiadau. Diolch i caching, mae'r ymarferoldeb a oedd yn bodoli yn cael ei berfformio mewn llai o geisiadau, a cheisiadau mwy effeithlon. Mae hyn yn golygu y gallwch chi luosi â 5 arall yn ddiogel i gael y cyfernod cyflymu go iawn. Felly, yn ôl yr amcangyfrifon mwyaf ceidwadol, gallwn ddweud bod y cyflymiad yn 50 gwaith neu fwy. Byddai swingio gweinydd yn fertigol yn costio 50 gwaith yn fwy. Yn enwedig o ystyried, unwaith y bydd optimeiddio yn cael ei wneud, mae'n gweithio drwy'r amser, a daw'r bil ar gyfer y gweinydd ar rent bob mis.
Ffynhonnell: hab.com