Postgres index kezelés
Postgresben van az adatbázisom.
Tárolt eljárásokkal matatok benne.
Az index kezelésével van egy kis problémám.
Vannak olyan tábláim, amelyeknek az egyedi kulcsa több mezőből áll össze ev,honap,ugyfelkod ....
Ezeket a mezőket egyedileg is indexeltem, és az egyedi kulcsra is készítettem egy indexet.
Adatokkal töltöm fel a táblát (tárolt eljárással), amikor a tábla üres, akkor gyors a felvitel (és az explain-nel ellenőrízve még a több mezős indexet használja), de később lelassul, és pont amikor az indexet jól tudná használni, elkezdi pl az "ev" indexét használni, és utána filterrel keresi a többi adatot (explain szerint).
Mit tudok tenni. Hogy tudom rávenni, hogy a létrehozott indexet használja?
■ Tárolt eljárásokkal matatok benne.
Az index kezelésével van egy kis problémám.
Vannak olyan tábláim, amelyeknek az egyedi kulcsa több mezőből áll össze ev,honap,ugyfelkod ....
Ezeket a mezőket egyedileg is indexeltem, és az egyedi kulcsra is készítettem egy indexet.
Adatokkal töltöm fel a táblát (tárolt eljárással), amikor a tábla üres, akkor gyors a felvitel (és az explain-nel ellenőrízve még a több mezős indexet használja), de később lelassul, és pont amikor az indexet jól tudná használni, elkezdi pl az "ev" indexét használni, és utána filterrel keresi a többi adatot (explain szerint).
Mit tudok tenni. Hogy tudom rávenni, hogy a létrehozott indexet használja?
WHERE sorrend
Az mindenképp jó, ha a három kulcsot ugyanabban a sorrendben adod meg a WHERE-nél, mint ahogy az indexet létrehoztad.
Where sorrend
Ugyanabban a sorrendben van, a tárolton belül ugyan azt a függvényt hívja meg.
index vs insert
Egyébként mit jelent a lelassul pontosabban?
Az adatfeltöltésen update-et vagy insert-tet értesz?
index vs insert
A lassulás úgy jelentkezik, hogy van egy mondjuk 40000 soros bizonylat adatbázis, amelyhez tartozik kb 1300000 tétel. Ezt kell egy speciális nézetet elkészíteni. Az első 1000 bizonylatot 14 perc alatt dolgozza fel, a következő 1000 már 50 perc alatt .........
Amennyiben túlindexelés lenne az elején is lassú volna, de már nem vagyok biztos semmiben.
Az adat feltöltés lehet insert is és update is.
erőforrások
Ha még nem tetted meg, nézd meg a postgres erőforrás használatát (főként memória), mert a menetközbeni startégiaváltásnak lehet olyan oka, hogy kifogy a szuflából, ezért megpróbál valami egyszerűbbet csinálni. (Néha olcsóbb memóriát vásárolni, mint végtelen időt tölteni egy problémával.)
Mondjuk ennyi adatnál már kis hibák nagy jelentőséget kaphatnak. Át kellene újból nézni a tárolt eljárásokat, nincs-e bennük valami logikai gubanc.
Az is lehet, hogy a postgres eddig ismeretlen gyengéjére tapintottál (remélem nem).
A régi szép időkben, amikor a vas jobban behatárolta, hogy mit lehet csinálni egy adatbázissal, ilyen esetekben ezresével töltöttük fel az adatokat -- tartott, amíg tartott. Persze ez nem megoldás élő rendszeren, vagy ha naponta/hetente kell ennyit felküldeni.
Ha mégis igazi intelligens megoldást találsz, megérne egy cikket (esettanulmányt) a téma.
csoportos feltöltés?
Felhő
Feltöltés
Ezért ezeket az infokat egy külön táblába is letárolnám, tárolt eljárással tölteném fel, és triggerekkel bizosítanám a sértetlenségét. Elméletben és gyakorlatban már működik, kisebb adatbázisokon le tudtam futtatni és jó is, de van egy nagyobb adatbázis, ahol a fenti probléma jelentkezik. Az autocommit kikapcsolva. Először megpróbáltam egy update-tel legenerálni a táblát (persze az egyik triggert kikapcsoltam) de 2 nap várakozás után lemondtam róla. Ettől úgy lelassult az egész, hogy pg_dump-dropdb-createdb segített csak (ezt javaslom időnkét, jót tesz:).
Megírtam egy kis programba, hogy az update egyenkét hajtódjon végre, ekkor derült ki, hogy a teljes adatbázison nem tudom egyszerre lefuttatni (olyankor tudok csak számítást végezni, ha nem dolgoznak rajta a triggerek miatt). Egy-egy hónapot próbálok leképezni, de sajnos már ott megjelenik a probléma .....
A db-ben az összes bizonylat száma : 494705 sor tételek száma : 6796307 sor.
A triggerek miatt a nap közbeni feldolgozást is jelentősen lelassítja, ha nagyok a nézettáblák.
vacuum analyze
Ezenkívül én használnék egy sima SERIAL mezőt, max lehetne az összetetten kulcson egy unique index. Gondold bele, hogy szeretnél egy másik táblát kapcsolni ehhez a táblához, akkor abban is mindhárom mezőt használnod kell.
Plusz szerintem érdmes lenne egy kicsit utánaolvasnod az indexeknek.[1] Az szinte biztosan nem jó stratégia, hogy csak úgy minden mezőre tegyél indexet, mert ezzel csak plusz terhet rósz a DB-re is íráskor, valamint az optimizernek is nehezebb lesz döntenie a több lehetőségből. Csak akkor hozz létre egy indexet, amikor ténylegesen szükség van rá.
És amit még érdemes szem előtt tartani, hogy az, hogy van egy indexed, az még nem jelenti azt, hogy az optimizer használni is fogja azt. A lekérdezés során a lemez olvasás a drága, utána a memóriában az átlag queryk esetén hamar elmatat a DB kezelő. Tehát az optimalizáló azt próbálja meg kisakkozni, hogy minél kevesebb lemez olvasással járjon a művelet. Ehhez jön hozzá, hogy az olvasás nem rekordonként történik, hanem mindig lapokat olvas be. Ennek fényében ha az index szerint a query feltétele alpaján sok sor felelne meg (és ezek valamilyen szinten szétszórva találhatóak a lemezen), akkor nem érdemes az indexet is külön beolvasni, mert az ezzel járó lap olvasások száma, plusz a szükséges sorok kinyeréséhez szükséges lap olvasások száma már több lenne, mint ha egyszerűen a teljes táblát beolvasná. Me pl. ha kevés adat van a táblában, akkor is simán lehet, hogy egyszerűen felrántja az egész táblát.
Felhő
[1] Itt annyi trükk van, hogy nem az indexekről szóló részben van a hasznos infó, hanem teljesítményről szóló részben.
vacuum analyze
A serial mező megvan, ez alapján írom felül a rekordokat.
Tudom hogy rossz ha minden mezőt indexelek, de a lekérdezéseknél nem csak összetetten használom, hanem egyedileg is. A múlt héten leszedtem a többi indexet, és akkor is lassú.
Megírtam ugyanezt fox alatt. Ott ugyanezen adatbázison 8 óra alatt végzett, de ott én mondtam meg, milyen indexet használjon :) P4 3Ghz gépen. A szerver meg egy dual xeon x-series.
A tábla mérete elég nagy, kb 500000 sort tartalmazna, mire azt felrántja ......, az adatbázis dump mérete 1.5 G tehát van adat is rendesen.
látatlanban
Ezenkívül érdemes lenne írnod SQL listára is, mert ott van aki elég jól benne van a különböző konfig paraméterekben, lehet, hogy egy kis tuningolással sokat lehetne javítani.
Esetleg a kérdéses queryt, meg a sémát beküldhetnéd még.
Felhő
jó ötlet
Melyik SQL listát javasolnád?
minta (nem a teljes, az adatmezőket nem raktam ide)
vevoarho (azon serial, arukod varchar(15), ugyfelkod varchar(5),szallckod varchar(5), ucsoport varchar(3),ev integer, ho integer.....)
Select count(azon) into xcount from vevoarho where
arukod='XXX' and ugyfelkod='XXX' and szallckod='XXX' and ucsoport='XXX' and ev=2006 and ho=3;
Ez nem a teljes, de a lényeg benne van (még több mező van :).
Gondolkodtam az optimalizálón is. Ha logikusan gondolkodom, akkor is az lenne a leggyorsabb, és a legolcsóbb keresési mód, ha az indexelt mezők esetén az indexbe eltárolná, hogy hány eltérő eleme van. Innetől kezdve egyszerű a helyzet, a legnagyobb elemszámú, és leginkább eltérő index szerint lehet a leghatékonyabban keresni, azon belül filter. Vagy nem?
sql lista
Felhő