Indexek, tömörítés és más állatfajták

Indexek, tömörítés és más állatfajták Lekérdezések a rendszer teljesítőképessége határán Kálmán György

Miről lesz szó I. A probléma felvetése II. Lehetséges eszközök III. További lehetőségek IV. Tanulságok és kiegészítő infók Úton vagyunk…

HOUG 2015.03.24.

Indexek, tömörítés és más állatfajták

2

A probléma jelentkezése Adva van egy tranzakciós (nem DW) rendszer Lekérdezések hosszú válaszidőkkel (sok TOP SQL bejegyzés generálása) Hatalmasra duzzadó adattáblák Sokasodó indexek Egyre nehezebben megjósolható teljesítményigény (és válaszidők) HOUG 2015.03.24.


3

A probléma gyökerei Eredetileg decentralizált iktató program, megyénként külön szervereken Áttérés fokozatosan, igazgatóságonként, centralizált Oracle alapú (2009 - 2010) Az eredetileg néhány milliós helyi adatbázisok helyett egyszerre 400 milliós adatbázissal találtuk szembe magunkat

HOUG 2015.03.24.


4

Mi kell az elfogadható válaszidőkhöz ? Jól megírt SELECT (és / vagy jó Optimizer ). •

Lehetőleg ne kelljen ugyanazt a halmazt többször végigolvasni !

Alkalmas index(ek) • Lehetőleg ne kelljen az egész táblá(ka)t végigolvasni ! „Indexes are one of many means of reducing disk I/O.”

Rendszeres takarítás és statisztikázás • •

Lehetőleg ne kelljen nem aktuális adatokban turkálni ! Az Optimizernek ne rég elavult adatok alapján kelljen dönteni !

HOUG 2015.03.24.


6

SELECT (és/vagy Optimizer)

HOUG 2015.03.24.


7

SELECT (és/vagy Optimizer)

HOUG 2015.03.24.


8

INDEX: Hasznosul-e az index ? (Meglévő index esetén) ALTER INDEX index MONITORING USAGE; Lekérdezés: V$OBJECT_USAGE táblából.

HOUG 2015.03.24.


10

Hasznosul-e az index ? (Mire kellhet egy index ?) Alkalmazás logika •

Szükség van-e arra, hogy a szülő táblához a gyermekeit kikeressük ? Nem triviális !

PK / UK megszorítások támogatására  Szükség van-e a táblán PK / UK megszorításra ? Nem triviális !

FK kapcsolatok gyermek oldali támogatására •

A szülő táblából való törlés, UPDATE vagy MERGE művelet, ami a kapcsolat szülő oldali kulcsát megváltoztatná – ha nincs gyermek oldali FK index, tábla szintű SHARE LOCK-ot akaszt a gyermek táblára. Ha van, akkor csak sor szintű RX lock-okat az érintett gyermek sorokra.

EZ utóbbi ellenőrzés nem jelenik meg az előző dián látott index usage statistics-ban !!!

HOUG 2015.03.24.


11

Hasznosul-e az index ? (Mi az eljárás, ha nem) ALTER INDEX index INVISIBLE; Megfigyelés értelmes ideig (ALTER INDEX index UNUSABLE;) DROP INDEX index; A gyermek oldali FK ellenőrzés azonban INVISIBLE állapotú indexet is felhasznál !!! Az INVISIBLE állapot csak a Query Optimizer számára teszi elérhetetlenné az indexet. Hogy FK gyermek oldalon szükség van-e rá, azt nekünk kell eldönteni (megengedett-e a szülő táblából törlés / UPDATE / MERGE, ami a szülő kulcsot megváltoztatná ?) !

HOUG 2015.03.24.


12

Hasznosul-e az index ? (Új index esetén is mérlegelendő.)

HOUG 2015.03.24.


13

Hasznosul-e az index ? (Lehet ügyesebben ?)  CREATE INDEX irat_erkeztetett_i ON dok_irat i (CASE WHEN i.irat_allapot_kod = '1' THEN i.irat_allapot_kod ELSE NULL END);

HOUG 2015.03.24.


14

Hasznosul-e az index ? (Túlságosan ?)

HOUG 2015.03.24.


15

Hasznosul-e az index ? (Túlságosan ?)

HOUG 2015.03.24.


16

Hasznosul-e az index ? (Minek alapján választ az Optimizer ?)  Statisztikák •Cost based Optimizer •Rendszer statisztikák (I/O , CPU, memória) •Tábla statisztikák (sorok száma, blokkok száma, átlagos sor hosszúság) •Oszlop statisztikák (Distinct értékek száma (NDV) , NULL (kitöltetlen) rekordok száma, histogram, ext.stat.) •Index statisztikák (levél blokkok, szintek, clustering factor)

 Ha nincs histogram, egyenletes eloszlást tételez fel  Ha nagyon egyenetlen az oszlop értékek eloszlása: Frequency / Height Balanced Histogram

HOUG 2015.03.24.


17

Histogram-ok H-B

HOUG 2015.03.24.


18

Histogram-ok 2. Új lehetőségek a 12c-ben  Top Frequency Histogram: Csak a leggyakoribb értékeket tárolja le a bucketekben, a többit ignorálja. Hybrid Histogram (a Frequency és H-B histogramok keveréke)

HOUG 2015.03.24.


19

Mit lehet tenni, ha nem jó a stat ? (Hintek és mínuszolás)

HOUG 2015.03.24.


20

Hasznosul-e az index ? (Összetett indexek ?)

HOUG 2015.03.24.


21

Összetett indexek (Miért ?) SELECT-ben együtt használt kereső feltételek esetén nagyobb szelektivitás érhető el Ha a lekérdezett oszlopok mindegyike szerepel az index-ben – nem kell a táblát lekérdezni VPD (sor szintű védelmi Policy) ezt megakadályozhatja

Tud-e hasznosulni, ha az index első komponense nem szerepel feltételként ? Igen, INDEX SKIP SCAN !

HOUG 2015.03.24.


22

Összetett indexek (Méret ? – Index tömörítés !) Amúgy is nagyméretű indexeink helyett még nagyobbat ? Tömörítsünk ! Egy (index) blokkon belül „tömörít”, pontosabban az ismétlődéseket szünteti meg (emeli ki). Az index alkotóelemeit (a mezőket, amikből az index összeáll) két fogalmi részre osztja: Prefix

Suffix

Ami ritkábban változik, sok rekord esetében azonos

Ami egyedivé teszi az index bejegyzést.

„tömörítődik”

„nem tömörítődik”

Alapértelmezésben: • Ha összetett és UNIQUE, akkor az első N-1 oszlop prefix, az N. oszlopot tekinti suffix-nek. Ha nem UNIQUE, akkor az összes elem a prefix-be tartozik, csak a rowid marad a suffix-ben. • Ha nem összetett – 1 elemű – akkor UNIQUE esetben nem enged tömöríteni, nem UNIQUE esetben az az 1 elem prefix és a ROWID lesz a suffix. Befolyásolhatjuk: CREATE UNIQUE INDEX index ON tábla (oszl1,oszl2,oszl3,oszl4) COMPRESS 2;

HOUG 2015.03.24.


23

Összetett indexek (Index tömörítés)

HOUG 2015.03.24.


24

Összetett indexek (Index tömörítés) A fenti eljárással ált. 20-40 %-os megtakarításokat tapasztaltunk (index méretben és INDEX SCAN műveletek esetén I/O-ban) Nem észleltünk lassulást az indexet használó SELECT-ek esetében. Nem kell hozzá külön licensz (mint némely tábla tömörítésekhez). HOUG 2015.03.24.


25

Lokális indexek Mennyire tudunk lokális indexeket használni ? Üzembiztonság javítása szempontjából lényeges – csökkenti a sérülékenységet. Lekérdezések szempontjából nem előny (igaz, az esetek jelentős részében nem is hátrány)

Nem csak úgy lehet, hogy a particionálási kulcs része legyen az indexnek ! Csak akkor kell, ha UNIQUE INDEX-et akarunk definiálni. Az Oracle nagyon ügyesen tud kezelni néhány (tucat) partíciót. Nincs tapasztalatunk, hogy mi történne, ha a partíciók számossága néhány ezer / néhány tízezer lenne.

HOUG 2015.03.24.


26

Particionálás Mire jó a particionálás, ha vannak indexeink? • • •

Van olyan lekérdezés, amit (B-tree) index nem tud támogatni Table Scan / index scan terheinek csökkentése és / vagy párhuzamosítás Adminisztrációs terhek csökkentése, üzembiztonság növelése

Az Oracle Database 12c tovább bővíti a lehetőségeket (pl. composite INTERVAL + Reference particionálás együttes alkalmazásának lehetősége).

HOUG 2015.03.24.


27

Tábla tömörítés Mire jó a tömörítés, ha vannak indexeink ? • •

A SCAN műveletek időigénye (és Cost-ja) radikálisan csökkenhet Csökken a buffer terhelés (több adat kisebb helyen elfér)

Lehetőségek • •

BASIC / OLTP – 20% - 30% - 50% megtakarítási lehetőség a tábla (és oszlopok) rendezettségétől függően. OLTP licensz köteles Hybrid columnar compression – 95%-os megtakarítás lehetősége (1/20-ra tud tömöríteni) – de speciális diszk rendszereket igényel

ILM lehetőségek a Database 12c-ben ! ILM – Információ életciklus menedzsment Az archív adatok is az adatbázisban maradhatnak (ne kerüljenek bele a keresésekbe, ne kelljen azokat menteni / visszatölteni, ne foglaljanak sok helyet !) HOUG 2015.03.24.


28

Tanulságok Amíg nem tudjuk, hogy mire és hogyan használják a felhasználók a rendszerünket, addig alig van esélye az optimalizálásnak. (naplózás fontossága). Vannak esetek, amikor az optimalizálás legjobb módja, ha felhívjuk a felhasználók figyelmét arra, hogy hogyan használják (vagy hogyan és mire ne használják) a rendszert.

HOUG 2015.03.24.


29

Tanulságok 2. Néha el kell gondolkodni az alkalmazás (vagy egy része) újratervezésén. A teszt adatbázis(ok) mérete az éles adatbázis mérete legyen (nagyságrendben legalábbis) ! Regressziós tesztelés fontossága, lehetőleg automatizált teszt eszközzel.

HOUG 2015.03.24.


30

Nagyságrendek • Rekordok számosságára példák: Ügy : kb. 500 millió, Irat : kb. 700 millió, Esemény : kb. 4 milliárd. • Tábla méretek: Ügy : kb. 55 GB, Irat : kb. 130 GB, Esemény : kb. 120 GB. • Index méretek sokszor hasonlóak (10 GB-os nagyságrend) . • Teljes rendszer: Táblák: kb. 500 db, Indexek: kb. 900 – 1000 db, Helyfoglalás: kb. 6 TB. • 2014-ben 6,5 millió Irat lekérdezést és több, mint 2 millió Ügy lekérdezést naplóztunk (nincsenek benne az egyediek). • Ha 260 munkanapot tételezünk fel, napi 9 óra folyamatos munkával, akkor kb. minden másodpercre jut egy lekérdezés.

HOUG 2015.03.24.


31

Nagyságrendek 2. Kapcsolatok a társrendszerekkel

HOUG 2015.03.24.


32

Nagyságrendek 3. Irat lekérdezés válaszidők 2014 30.0%

25.0%

20.0%

15.0%

10.0%

5.0%

0.0% 1 sec alatt 1 - 2 sec 2 - 5 sec 5 - 10 sec 10 - 30 sec 30 - 60 sec 1 - 2 perc 2 - 5 perc 5 - 10 perc 10 - 30 perc

HOUG 2015.03.24.


30 - 60 perc

1 - 2 óra

2 óra felett

33

Köszönöm a figyelmet! Kálmán György [email protected]

Indexek, tömörítés és más állatfajták

Recommend Documents