mysql select gyorsítás

C/C++

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?

  • 2967 megtekintés

( petya | 2010. 03. 27., szo – 09:04 )

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.

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 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?

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

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.

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.

( radix | 2010. 03. 27., szo – 09:07 )

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)

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.

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.

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.

( radix | 2010. 03. 28., v – 12:15 )

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

é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?

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.

É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.

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.