Lassú lekérdezés MySQL view-ból
Következő a jelenség:
Egy durván 6millió soros táblából készítettem egy nézetet, ami átlagolások és csoportosítások segítségével visszaad egy kevesebb sorból álló táblát (percenkénti időjárási adatokból készít óránkénti átlagokat)
Oracle-ös tanulmányaim során elmondták, hogy a nézet eredménye a memóriában kerül letárolásra, ami kiderült, hogy a MySQL-ben nem így van...
Minden egyes lekérdezéskor újraszámolja ezt a bizonyos nézetet, ami szomorú, mert több mint 10mp-ig tart a folyamat.
A kérdésem az lenne, hogy van-e lehetőség arra, hogy a nézet újraszámolására csak akkor kerüljön sor, ha a szülő tábla módosul és először kérünk le adatot a nézetből? Illetve, van-e egyéb ötlet az alul felsoroltakon kívül, vagy azok egyáltalán helyes felvetések-e?
Eddigi ötleteim:
* Nézet algoritmusát MERGE-re állítani. (probléma: létrehozáskor hiába adom meg az algót, akkor is UNDEFINED lesz)
* Valamiféle cache alkalmazása (pl.:querycache?). (probléma: túl sok a lekérdezések variációja, túl sok cache-elt adat + nem vagyok otthon a cache-elési technikákban)
* Szülő táblába íráskor, módosításkor egy másik, sima táblába írni az átlagokat, illetve újraszámolni azokat (probléma: egyszerre sok adat íródik a táblába, ezért ez nagyban lassítaná az insertet)
A válaszokat előre is köszi!
■ Egy durván 6millió soros táblából készítettem egy nézetet, ami átlagolások és csoportosítások segítségével visszaad egy kevesebb sorból álló táblát (percenkénti időjárási adatokból készít óránkénti átlagokat)
Oracle-ös tanulmányaim során elmondták, hogy a nézet eredménye a memóriában kerül letárolásra, ami kiderült, hogy a MySQL-ben nem így van...
Minden egyes lekérdezéskor újraszámolja ezt a bizonyos nézetet, ami szomorú, mert több mint 10mp-ig tart a folyamat.
A kérdésem az lenne, hogy van-e lehetőség arra, hogy a nézet újraszámolására csak akkor kerüljön sor, ha a szülő tábla módosul és először kérünk le adatot a nézetből? Illetve, van-e egyéb ötlet az alul felsoroltakon kívül, vagy azok egyáltalán helyes felvetések-e?
Eddigi ötleteim:
* Nézet algoritmusát MERGE-re állítani. (probléma: létrehozáskor hiába adom meg az algót, akkor is UNDEFINED lesz)
* Valamiféle cache alkalmazása (pl.:querycache?). (probléma: túl sok a lekérdezések variációja, túl sok cache-elt adat + nem vagyok otthon a cache-elési technikákban)
* Szülő táblába íráskor, módosításkor egy másik, sima táblába írni az átlagokat, illetve újraszámolni azokat (probléma: egyszerre sok adat íródik a táblába, ezért ez nagyban lassítaná az insertet)
A válaszokat előre is köszi!
Csak találgatok
A view elvileg egy(több) select, amit szerintem illene önálló lekérdezésként kezelnie.
Kipróbálni nem tudod?
----
Bár nekem az se stimmel egészen, amit Oracle kapcsán írtál (igaz, én leragadtam valahol a 9i-10g között)
Az Oracle elvileg (egyetemi
Valami ilyesmit szeretnék elérni MySQL-ben is... A MySQL hivatalos fórumán felmerült már ez a téma, ott azt tanácsolták, hogy a nézet algoritmust MERGE-re kell állítani, és az valamelyest javít a helyzeten.
Egyébként nem tudom pontosan mire gondolsz query cache alatt. A my.cnf-ben kell beállítani neki valami méretet, és annyi, vagy kell még vele valami extrát csinálni?
Egyébként Yii keretrendszert használok, amiben számos cache-elési technika implementálva van, de nem tudom melyik lenne erre a célra a legmegfelelőbb.
(a QueryBuilder tud cache-elni, de az nem gyorsított rajta)
Trigger
Egyszer küzdöttünk hasonló problémával a megoldás a következő lett:
nyers bejövő adat táblára insert / update trigger, ami frissit egy másik táblában lévő timestampet.
A másik táblában lévő timestamp alapján fut egy tárolt eljárás (ha a timestamp régebbi mint x mp akkor lefut) ami újra számolta a "view" tábla tartalmát, majd egy tranzakció keretén belül kicserélte a tábla tartalmát.
Vagy ha tudod, hogy neked minden 30 mp-ben kell frissiteni a "view tábla" adatait akkor egyszerüen cron-ból frissited.
Jelenleg 20 percenként
A triggeres megoldás jó lehet... Ha nem találok rá egyszerűbb megoldást, kipróbálom. Szegény ember vízzel főz. :)
A MySQL-ben kétféle cache
A Query cache a legutóbb lefuttatott lekérdezések eredményeit tárolja, az alapértelmezett mérete 16 megabájt (query_cache_size=16M), igazából ritkán változó, kis adatbázisoknál van értelme használni (query_cache_type=1), minden más esetben állítólag lassít, mert ha bejön egy lekérdezés, akkor végig kell néznie a cache-ben tároltakat, hogy ott van-e.
Az InnoDB cache külön állítható, a méretét érdemes dedikált szerver esetén a teljes memória 80%-ára beállítani (innodb_buffer_pool_size=3G), ez egyszerűen a memóriában tárolja a táblákat.
A problémádra nekem a harmadik megoldás látszik a legcélszerűbbnek, de az első persze az legyen, hogy az EXPLAIN segítségével ellenőrizd le a VIEW-kben lévő SELECT-ek indexelését.
A szülő táblában 2 mező (egy
A nézetben a két kulcs alapján csoportosítok úgy, hogy a percenkénti adatok óránkénti átlagát számítom. Azon mezők amik az adatokat tartalmazzák (amiből átlagokat számítok), nincsenek indexelve.
A nézet táblát is ellenőriztem EXPLAIN-nel abban nem voltak kulcsok (és nem is tudom hogy lehet indexelni).
Amúgy igen, InnoDB táblákról van szó... :)
Materialized view
Itt a megoldás. Ez az Oracle féle materialized views MySQL portolása.
Köszi a válaszokat!