mysql select gyorsítás

 ( martinb | 2010. március 27., szombat - 9:23 )

Sziasztok!

Van egy MySql táblám, ami 10000, átlagosan 20 kb méretű sorokat tartalmaz. Az egész táblát kell lekérdeznem C++ -ból, de 90% a táblának ugyanaz marad a következő lekérésnél. Egy ilyen lekérdezés nagyjából 5 sec-et vesz most igénybe, ezt szeretném csökkenteni. Memcached jó ötletnek tűnik ilyen helyzetre, illetve ennek C++-os kliense a libMemcached?

Hozzászólás megjelenítési lehetőségek

A választott hozzászólás megjelenítési mód a „Beállítás” gombbal rögzíthető.

Ha a lekerdezesnek nem ugyanaz az eredmenye, akkor erre nem jo a memcache, viszont a tabla merete alapjan az 5 masodperc eleg sok. Ha pastelsz egy show create table kimenetet meg a queryt, akkor megmondom, hogy lehet e rajta optimalizalni. Ha azert nincsenek rajta indexek, mert az insert performanciaja is szamit, akkor ez nem biztos, hogy jarhato ut. Az is sokat segit, ha elmondod, hogy milyen workload van a tablan, mit csinal meg ezen kivul a mysql szerver, mit csinal meg a gep, es tudsz e hatassal lenni a mysql konfiguraciora.

Köszi a válaszokat!
Biztos rengeted tervezési hiba van benne, mert kezdő vagyok ezen a téren. Szóval van egy longtext típusú oszlopa a táblának,ez viszi el viszi el egy sor (és így a tábla) méretének 99%-át. Olvastam, hogy ezt célszerű lenne egy másik táblába rakni és idegen kulccsal hivatkozni rá, mert így sokkal gyorsabban menne a keresés az első táblában. Van még egy hash oszlopom is, SHA1 hasítása a longtext típusú oszlopnak, akkor valószínüleg ezt is át kéne rakni a külön táblába és erre rakni az indexet. Az insert hatékonysága abszolut nem szempont, a keresesre kellene optimalizálni. A workloadot hol tudom megnézni/állítani? Konfighoz hozzáférek.

A milyen a workload-ra olyan mondasok kellenek, hogy sokkal gyakrabban olvasom, mint irom, ilyen gyakran torlok belole, stb stb. Mutasd a show create table kimenetet es a lekerdezest.

CREATE TABLE `articles` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`hash` varchar(255) DEFAULT NULL,
`text` longtext NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=10004 DEFAULT CHARSET=latin1

SELECT * FROM articles WHERE datediff(date,now()) = 0

A workload pedig akkor az, hogy sokkal sokkal többször olvasom mint írom vagy törlöm.

Mennyire csinalsz parhuzamosan dolgokat? Ha MyISAM engine-nel hasznalod, akkor a torles table lock-kal jar, es a torles utan lyukas lesz a tabla. Amig ez fel nem toltodik, addig minden insert is table lock-kal jar. A lekerdezesedre pedig csinalj egy indexet:
C REATE INDEX indexneve ON articles(date);

A primary key alapjan ugy tunik, hogy ezt valami orm generalta.

Így van, hibernate-en keresztül tartom karban a táblát, C++-ból mindíg csak lekérdezem QT-vel. Ráraktam az indexet, de így sem lett sokkal gyorsabb. Lehetséges, hogy kb 200 Mb beolvasása tart neki ennyi időbe? Nem lehet valahogy beállítani, hogy a memoriát is használja. Mert próbálkoztam query cache-el is, de ott ugye egy újabb sor beszúrása a táblába már elrontja a dolgot. insert, delete-nél nem fontos annyira a sebesség. Kipróbáltam innodb-vel így egy fél seccel több lett a select idő.

a query cache-be nem fer be ennyi adat, ha nem allitod be megfeleloen a query_cache_size es a query_cache_limit parametert

A függvényhívás jogos, de konstanssal se lett gyorsabb. Be van állítva mindkét cache paraméter, ugyanazt a lekérdezést másodszorra 0.2 sec alatt behozza. Különben sajnos még mindíg kb 4.5 sec. Érdemes lehet 2 táblára (id,date) és (hash,text) bontani, hogy date keresésnél gyorsabb legyen?

Indexeket csináltál már? Attól nem gyorsul? EXPLAIN mit mond?

http://haxel.hu/wodehouse-konyvek.html

plusz a feltetelben ne legyen mar fuggvenyhivas, ha nem szukseges... es akkor meg talan az indexet is hasznalni fogja

WHERE datediff(date,now()) = 0 helyett esetleg praktikus lenne egy date=now().
A SELECT-ek működéséről többet tudhatsz meg, ha elé írod, hogy EXPLAIN, és gondosan elolvasod a választ.
Pl. kiderül, ha nem talál használható indexet. A memcached-et akkor keverd a dologba, ha az adatbázison már nem tudsz úgy variálni, hogy gyorsuljon.

http://haxel.hu/wodehouse-konyvek.html

Szöveget -ha nem kell kereshetővé tenned- akkor célszerűbb BLOB -ként tárolni, gzipelve pl.
A SELECT * eleve rossz megoldás sebesség szempontjából, hacsak nincs minden mezőre szükséged egyszerre.

die(DIE_HARD);
azenoldalamponthu

BLOB-kent azert nem jo, mert a ORM-ek nem fogjak text tipusu dologkent felismerni. Raadasul a tabla neve alapjan ennek kereshetonek kene a jovoben lennie. Es szvsz nem a longtext mezo ertekenek lehivasa viszi el az idot.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

A longtext mező miatt lassul le, ha az nincs a select-ben, fél sec alatt lejön az egész. Ennek az oszlopnak nem kell mysqlben kereshetőnek lennie. Egyébként az normális, hogy a teljes 200 Mb-os tábla 5 sec alatt jön le?

Ez azert fura, mert en sima text mezot hasznalok ugyan, de nem tapasztalok ilyen mervu lassulast, gyorsan elo tudja szedni a cikkeket. Elvben nem is szabadna lassitania, hiszen azt csak az indexek alapjan eloszeded, nem vizsgalod, tehat nem kell ramennie.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

egy csúnya-csúnya tervezési hibát sejtek a háttérben....

+1, nem indexel a kollega olyan mezo"re, ami alapjan szelektal (es vegignyalazni 10k rekordot egy 200m-es adatbazisban 40m/sec diszk io/o mellett pont 5 masodperc :]). mondjuk a datetime helyett inkabb sima int(11) + timestamp-ot hasznalne'k, pont az indexeles miatt. bar valoszinuleg teljesen mindegy, mert a datetime is csak egy sima 32bites int formajaban jelenhet meg a belsejeben...

Úgy tudom, az index arra való, hogy egy indexelt oszlopban egy adott értéket, ami a feltételben szerepel gyorsan megtaláljon. De én nem keresni akarok, hanem 10000 sort minél gyorsabban betölteni, ami két select között csak kicsit változik és így talán érdemes lehet valahogy cachelni és csak a változásokat újra lekérdezni. Erre keresek valami technikát. Tahát, hogy lehet egy where feltétel nélküli selectet minél gyorsabban végrehajtani. Az egyértelmű, hogy a where részben megjelenő oszlopakat indexelni kell.

Ha jól értem, elég lenne egyszer beolvasni az egészet, utána pedig mindig csak a változásokkal frissíteni a c++-os adatszerkezeteket?
Ha igen, és a táblába írást is te kontrollálod, akkor:

-legyen a 10000-es táblában két unique oszlop: id, és revision_id.
-amikor változtatsz valamit a táblában, akkor a sor revision_id-jéhez egy új revision_id-t rendelsz. (nem eggyel növeled, unique id-ről van szó). Ez alapján mindig megtalálhatod a változásokat (a legnagyobb revision_id-t minden frissítéskor tárolod c++-ban), az id alapján pedig tudod, hogy melyik sort frissítették.
-új sorokra nem kell külön mechanizmus, a revision_id-vel jól lehet kezelni
-a törléseket vagy új táblában tárolod history-szerűen. egy idő után túl nagy lehet ez a tábla, de ezen lehet javítani úgy, hogy pl. minden 100000. revision-nál kiüríted a history-t (és kötelezően újra kell olvasni csak az id-ket az eredeti táblából, amiről a c++-ban tárolt utolsó revision_id-ből fogsz rájönni)

Köszönöm szépen, nagyon jó ötlet, ezt fogom alkalmazni.

szívesen:)

Erdekes az otlet, kerdes: revid-t te hogy generalnal? globalisan inkrementalodo valami, vagy sha1? A legegyszerubbnek nekem az ID-n beluli egyedi rev tunik (=eggyel noveled), ekkor kell egy (id, rev_id) index is, de szvsz ez meg nem olyan nagy baj.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Globálisan inkrementált értékre gondoltam, másnak nem nagyon lenne értelme, (sha1? wtf?) mivel 'where revision_id>N' alakú feltételt akarunk, de timestamp is jó, ld. eggyel lentebb.

Mondjuk szerintem a lokalisan inkrementaltnak is van ertelme, bar ilyenkor nem lesz UNIQE a rev_id onmaga, csak az (id, rev_id).
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Ha lokálisan (soronként) inkrementálsz, hogyan válogatod le a változásokat?

Nem soronkent, hanem adott id-hez tartozoan. Ket lekerdezes kell, de az elso index alapu, tehat nagyon gyors, es csak egy szamot vesz elo.
SELECT MAX(rev_id) FROM articles WHERE id=${id}
SELECT * FROM articles WHERE id=${id} AND rev_id=${rev_id}

--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Arról volt szó, hogy az id is unique..

Az id mindenkepp unique, mert az ORM rendszerek altalaban rogton nyomnak ra egy PK-t.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Ennek ellenére nem teljesen konzisztens, amit írsz:

SELECT MAX(rev_id) FROM articles WHERE id=${id}
Minek a MAX, ha unique?

SELECT * FROM articles WHERE id=${id} AND rev_id=${rev_id}
Meg az AND utáni rész, ha unique?

Meg úgy egyáltalán, azokra az id-kre akarjuk lekérdezni, amik változtak. Ezeket valahonnan ismerni kell. Ezt honnan szeded? Meg eleve mekkora overhead egyenként lekérdezni? Nem 2 select-ed van, hanem N*2.. Szemben azzal, ha timestamp-eled (vagy globális rev_id), hogy az adott sor mikor változott, és csak egy bizonyos időpont után változott sorokat kérdezed le _egy_ select-tel?

Egyáltalán érted, hogy miért nem jó, amit mondasz?

Timestamp hasonlóképpen használható lenne:

create table ...
`modositva` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
...
ALTER TABLE ... ADD INDEX `Index_modosit`(`modositva`);

Mikor leválogatja, megjegyzi a maximális időpontot, következő alkalommal csak a nagyobbakat (esetleg nagyobb egyenlőeket) szedi le.

Vagy egy 386sx procival felszerelt alaplapot, 1M RAM-mal...

miért van rá szükséged hogy a teljes adatbázist lássa a programod?

Csak a teljes táblát kell látnia, hogy el tudjon végezni egy komplex keresést a text mezőkön.

és miért gondolod, hogy erre a feladatra (gyakorlatilag full table scan) a mysql (sőt, bármilyen sql) a jó megoldás?
persze, ha adsz neki annyi cache-t, hogy az egész adatbázis memóriában legyen, akkor már csak egy picit lesz lassabb a full table scan, mintha ezt egy memória cache-ben tárolnád.

szerintem.

Tehát milyen megoldást javasolsz? Egyébként a legelső hozzászólásomban pont a memcached hasznosságát kérdezem.

Tehát milyen megoldást javasolsz?

mivel az alkalmazásod architektúrája nem ismert, így nem tudom, hogyan tudod optimálisan memóriában cache-elni az adatot. ez nyilván más, ha van egy single szerver példány, amire minden kliens rácsatlakozik, mintha az egyetlen közös elem az adatbázis.

Egyébként a legelső hozzászólásomban pont a memcached hasznosságát kérdezem.

nézd meg, hogyan működik, és találd ki, hogy egyáltalán tudnád-e használni (emlékeim szerint key-value párokat tud tárolni, amiből a key alapján tudsz kiolvasni, de mintha "get all" művelet nem lenne benne).

ha nem, akkor szerintem nem tart tovább pár délutánnál egy neked megfelelő tudású alternatívát összedobni.

Ha a feladat valami "select * from ... where ... like '%...%'", akkor nem az adatbázis a jó megoldás...

Tudnál esetleg alternatívá(ka)t vázolni?

Én kinyerném a tábla adatait, felépítenék belőle egy adatstruktúrát C++ segítségével a memóriában és valamilyen keresési algoritmus alapján keresnék benne.

biztos vannak rá remek library-k, amiket fel tudsz használni.
Ha jól értem, viszonylag kevés adathalmazban akarsz gyorsan keresni. Ez egy lehetséges megoldás.

LUCENE

erre van kitalálva.

Fő feladatként szövegek (átlagosan 20 kb méretűek) metszetét kellene előállítani gyorsan. Néztem a Lucene-t, de eddig nem bukkantam ilyen feature-re.

mit értesz két szöveg metszete alatt?

A leghosszabb olyan karaktersorozat, amelyik mindkét szövegben szerepel. Pl. "ababa" és "ccbabd" esetén "bab".

A probléma neve angolul esetleg? Mert egyáltalán nem triviális az optimális megoldása ennek.

Szavakat kell keresni, vagy szotoredekeket? Nem mindegy ugyanis.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Szótöredék is lehet.

Persze a rossz fogalmazas az oka.

Szoval, a normal (nyelvi) szovegkereses ugy mukodik, hogy szora lehet keresni, illetve szo elejen szotoredekre, illetve - ha van szotar - akkor nyelvi elemekre (tokenekre) (peldaul az elkelkaposztastalanitottatok eseteben a kelkaposztara), viszont random szotoredekre nem lehet, mert ez alapjan lehet indexelni.
A binaris keresessel meg az a baj, hogy akar koltseges is lehet, ha sok adatot kell atnyalni, mert ott nem nagyon lehet indexelgetni, hiszen akarmilyen toredeket akarhol is meg kell tudni talalni.

Ezert probaljuk megkerdezni, hogy pontosan mi a feladat? Altalanos keresest kell vegrehajtani, hogy pl. a 'macska' szora kijojjon az osszes macskas cikk, vagy egy nagy betukazalban kell megtalalni valamit?
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

Igen. Nálam a második eset van.

Mysql helyett sphinx es ngram indexing.

Marmint csak a kereseshez, az adatok tarolasa legyen mysql-ben.
--

()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.