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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
6
SELECT (és/vagy Optimizer)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
7
SELECT (és/vagy Optimizer)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
12
Hasznosul-e az index ? (Új index esetén is mérlegelendő.)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
14
Hasznosul-e az index ? (Túlságosan ?)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
15
Hasznosul-e az index ? (Túlságosan ?)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
17
Histogram-ok H-B
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
19
Mit lehet tenni, ha nem jó a stat ? (Hintek és mínuszolás)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
20
Hasznosul-e az index ? (Összetett indexek ?)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
23
Összetett indexek (Index tömörítés)
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
31
Nagyságrendek 2. Kapcsolatok a társrendszerekkel
HOUG 2015.03.24.
Indexek, tömörítés és más állatfajták
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.
Indexek, tömörítés és más állatfajták
30 - 60 perc
1 - 2 óra
2 óra felett
33
Köszönöm a figyelmet! Kálmán György
[email protected]