Keskustelu: Nettisivujen teko: SQL-kyselyn tulos cacheen

Ongelma on, että minulla on mielenkiintoinen sql-kyselyn tuottama tilasto, mutta tuloksen laskeminen kestää tolkuttoman kauan (1-2 min). Tulos pitäisi näyttää nettisivuilla. Kyselyn tiedot ovat ajantasaiset noin viikon kerrallaan, eli sen ajaksi voisi tuloksen tallentaa jonnekin.

Itse kyselystä: Taulusta valitaan noin 10-30 tuhatta riviä, joille jokaiselle etsitään samasta taulusta toinen rivi, jotka sitten liitetään. Lisäksi hakutulos vielä järjestetään liitoksessa löydettyistä luvuista lasketun tunnusluvun mukaan. Olen kyselyä testaillut vaikka miten kirjoitettuna, mutta tuo liitos vain kestää kauan, eikä sitä kai MySQL4:llä saa oikein nopeutettua.

Hakutuloksesta ovat kiinnostavia vain muutama kymmenen - sata ensimmäistä, eikä säilöminen siis vaadi juurikaan tilaa. Miten ja minne tuo säilö sitten kannattaisi toteuttaa? Kannattaako sitä varten luoda taulu tietokantaan vai tallentaa tiedostoon vai jotain ihan muuta?

Jos nyt pitäisi tuo väsätä, tallentaisin html-sivun, mutta mistä ratkaisua olisi järkevintä hakea?

Jos se nyt viikon pysyy samana niin crontabilla puskemaan viikon välein ulos uutta .html sivua vaan. Tai vaikka paria, toisella ne kiinnostavat tulokset, toisella vähemmän kiinnostavat.

Nuo crontabit ovat täysin uusi tuttavuus. Näköjään tuolla tulevalla palvelimella, jonne sivut kohta puoliin siirtyvät, on mahdollisuus crontjobeja käyttää. Pitääpä tutkia sitä vaihtoehtoa.

En ole itse tutustunut, mutta tutustumisen arvoinen voisi olla myös MySQL Query Cache. Jos tutustut, niin kerroppa tännekkin kokemuksia...

Tapahtuuhan liitos avaimilla? Eniten aikaa menee todennäköisesti tuossa laskemisessa, joten siinä voisi yrittää suorittaa seulontaa, turha laskea mitään sellaisille arvoille jotka eivät varmasti näy lopullisessa tuloksessa... Paha sitten sanoa tarkemmin, kun en itse tapausta sen tarkemmin tunne.

Mutta kuten todettua, jos tilastojen ajantasaisuus ei ole tärkeää, niin crontabia vain kehiin, silloin on kai turha paneutua tuon koodin optimoimiseen juuri sen tarkemmin... Ainakaan niin kauan kuin merkintöjen määrä ei kovin monesti moninkertaistu :)

Itse testasin nopeasti tuota query cachea ja raskain pikasesti keksimä kysely tipahti 4 s => 0.0002 s. Eli ihan vaan kyselyn alkuun SELECT SQL_CACHE COUNT(*) ... Mutta kuten JTS sanoi, onhan taulu indeksoitu? Jos mahdollista, niin pistä lause tänne. Alkoi kiinostamaan... :)

Itse en tuohon cronia ihan heti sotkisi. Jos käytössä olisi MySQL 5, niin tuohon varmaan löytyisi monta muutakin tietokannan sisäistä tapaa "cachettaa" data (triggaus kenties?). Mutta, toinen tykkää äidistä, toinen tyttärestä. Käyttäjällehän päin se on sama vaikka laskisit ne kerran viikossa laskimella :)

ajv kirjoitti:

En ole itse tutustunut, mutta tutustumisen arvoinen voisi olla myös MySQL Query Cache. Jos tutustut, niin kerroppa tännekkin kokemuksia...

Oikeastaan tuota tutkailin jo ennenkö postasin tänne, mutta ei näyttänyt cacheen mitään laittavan, vaikka ko. ominaisuus on käsittääkseni palvelimella päällä.

Tavallinen tiedostoon tallennus on käytännössä toimiva ratkaisu. Sivun voi toteuttaa siten, että tiedot ladataan tavallisesti tiedostosta, mutta jos tiedosto on liian vanha, tiedot haetaan ensin tietokannasta ja tallennetaan tiedostoon. Tosin jos tietojen hakeminen tietokannasta vie aikaa minuuttikaupalla ja tietoja täytyy päivittää vain kerran viikossa, minä varmaan tyytyisin päivittämään tietoja (käsin tai ajastetusti) erillisellä skriptillä ja kävijöille tiedot tulisivat aina nopeasti valmiista tiedostosta.

Antti Laaksonen kirjoitti:

Tavallinen tiedostoon tallennus on käytännössä toimiva ratkaisu. Sivun voi toteuttaa siten, että tiedot ladataan tavallisesti tiedostosta, mutta jos tiedosto on liian vanha, tiedot haetaan ensin tietokannasta ja tallennetaan tiedostoon. Tosin jos tietojen hakeminen tietokannasta vie aikaa minuuttikaupalla ja tietoja täytyy päivittää vain kerran viikossa, minä varmaan tyytyisin päivittämään tietoja (käsin tai ajastetusti) erillisellä skriptillä ja kävijöille tiedot tulisivat aina nopeasti valmiista tiedostosta.

Tuossa viittauksessani MySQL 5:een ja triggereihin oli vähän vastaava idea. Eli Kun tietokannan sisältö muuttuu, triggeri automaattisesti laskisi uudet tulokset johonkin toiseen tauluun, josta data sitten haetaan webbisivulle. Mutta MySQL 4 ei tunnetusti triggereitä hanskaa ja tekee homman vaikeammaksi... :(

LaNu kirjoitti:

Oikeastaan tuota tutkailin jo ennenkö postasin tänne, mutta ei näyttänyt cacheen mitään laittavan, vaikka ko. ominaisuus on käsittääkseni palvelimella päällä.

Huomasitko myös asetuksen query_cache_size, joka oli itselläni oletuksena arvolla 0 vaikka cache oli "asetettu päälle". Tämä taas yliajaa cachen pois päältä. Laita siihen vaikka arvoksi megan verran.

Lebe80 kirjoitti:

LaNu kirjoitti:

Oikeastaan tuota tutkailin jo ennenkö postasin tänne, mutta ei näyttänyt cacheen mitään laittavan, vaikka ko. ominaisuus on käsittääkseni palvelimella päällä.

Huomasitko myös asetuksen query_cache_size, joka oli itselläni oletuksena arvolla 0 vaikka cache oli "asetettu päälle". Tämä taas yliajaa cachen pois päältä. Laita siihen vaikka arvoksi megan verran.

Jotain tällaista arvelinkin, että joku tarpeellinen vipu on off-asennossa (en viitsi nyt kirjautua katsomaan).

Antti Laaksonen kirjoitti:

Tavallinen tiedostoon tallennus on käytännössä toimiva ratkaisu.

Juu, enköhän ala sitten näillä puheilla sitä ratkaisua tuosta kehittelemään. Tuleepahan tutkittua nuo tiedostofunktiot, joille ei tähän mennessä ole mitään käyttöä ollutkaan.

JTS kirjoitti:

Tapahtuuhan liitos avaimilla? Eniten aikaa menee todennäköisesti tuossa laskemisessa, joten siinä voisi yrittää suorittaa seulontaa, turha laskea mitään sellaisille arvoille jotka eivät varmasti näy lopullisessa tuloksessa... Paha sitten sanoa tarkemmin, kun en itse tapausta sen tarkemmin tunne.

Erilaisia paloja kyselystä testaillessa ajalla ei ollut suurta eroa laskujen ja järjestyksen kanssa tai ilman. Liitos oli se, joka kesti.

Liitos on tämän tyyppinen:

SELECT *
	FROM
		(SELECT *
			FROM taulu
			WHERE uid = 10
		)q1
	LEFT JOIN
		(SELECT *
			FROM taulu
			WHERE uid = 9
		)q2
	ON q1.pid = q2.pid;

Hm... kaipa tuo nyt suunnilleen ne jutut sisältää, mitä se todellinenkin liitos. Luonnollisestikin SELECT kohdassa on pieniä rajauksia, mutta suunnilleen noin. Nuo uid ja pid muodostavat yhdessä primary keyn.

Hmm, alikyselyitä liitoksessa, ne voivat tehdä siitä hausta raskaan (varsinkin jos hauissa ei käytetä avaimia, kuten vissiin ei käytetä)? Ja onko tosi että siinä joinataan tulokset samasta taulusta?

Eli mitä tuossa nyt oikeasti haetaan: taulusta tulokset missä uid on joko 9 tai 10, ja yhdistetään ne pidin perusteella...?

Epäilen vähän että vastaako tämä esimerkki sitä oikeaa hakua... Mutta paha taas sanoa sen tarkemmin, kun ei ole mitään hajua mitä tässä oikeasti ollaan tekemässä.

Kyllä tuo on juurikin se, mitä todellinenkin kysely. Kuten tuolla ihka ensimmäisessä viestissä parhaani mukaan kuvailin :-) Saman taulun riveistä siis kyse - ja tulos joukko on aikas iso.

lainaus:

Taulusta valitaan noin 10-30 tuhatta riviä, joille jokaiselle etsitään samasta taulusta toinen rivi, jotka sitten liitetään.

Tämänhän se tekee, eikä siihen taida paljoa muita vaihtoehtoja olla. Liitos tapahtuu ilman indeksejä, koska kumpikin liitosjoukko on alikyselyn tulos.

Idea: u tulee sanasta update ja p on player. Laskun lopputuloksena saadaan selville muutos kahden päivityksen välillä kullekin pelaajalle. Tarkemmin rajatuilla alikyselyillä tuo on riittävän nopeakin, mutta kyllähän ne kaikista parhaatkin pitää selvittää :-)

Moista toimenpidettä ei taida saada nopeaksi kuin cacheamalla tiedot. (jonka siis yllä olevien ehdotusten mukaisesti taidan tiedostoon tehdä.)