Kysymys soveltuvasta SQL-arkkitehtuurista

[2dogs1bowl]

Taustana sen verran, että Pythonilla on joitain pienempiä projekteja tullut tehtyä mutta virallista koulutusta näistä aihealueista ei löydy.

Nyt on työn alla oma prokkis, jota varten tarvitsen tietokannan. Tietokanta on neliulotteinen; x- ja y-koordinaatti, aika sekä parametrit. Ulottuvuuksilla on kokoa noin 200 x 200 x 262800 x 5, tässä järjestyksessä. Tietokannan kasaamisen jälkeen sitä ei enää tulla muokkaamaan. Jokaisessa solussa (cell?) on yksi arvo. Nopean laskutoimituksen mukaan tämän pitäisi mahtua alle 100GB tilaan. Tietokannasta tehdään ensisijaisesti kutsuja koordinaatien ja ajan suhteen sekä lasketaan keskiarvoja, jakaumia yms. eri parametreista.

Olen tässä pari iltaa lueskellut MySQL:stä, mutta en millään saa päätettyä miten tämä kannattaisi rakentaa. Kannattaako jokaiselle dimensiolle ja parametrille luoda oma table ja linkittää niitä toisiinsa dimensoiden mukaan? Ainoastaan x, y ja aika dimensiot ja siihen sisään sanakirja parametreja varten? Onko jotain muuta tietokantakieltä, millä onnistuisi tehokkaammin?

Lähinnä haen vertaistukea sekä vinkkejä, mistä ja millä sanoilla tätä kannattaa lähteä purkamaan. Ongelma ei varmasti ole millään tavalla uniikki, mutta google-fu pettää nyt pahasti, sillä termit ovat (selvästikin) hakusessa. Yleisimmät tutorialit netissä tuntuvat olevan asiakastietokantoja yms. joista en keksi ainakaan suoraan vastausta ongelmaani.

Jossain vaiheessa olisi mukava testata myös tätä AWS:ssä omakustanteisesti, kunhan hinnat pysyvät edes jotakuinkin kohtuullisena.

 

[hsalonen]

But why? Miksi tarvitset puolen vuoden minuuttitarkan datan?

Tee tuollainen taulu päivätasolle, opettele vähän tilastotiedettä ja laske ne loppulaskennan tarvittavat arvot yöajossa päivätasolle. Tallenna pelkkä päivätaso kantaan. Kanta on kohtuullisen kokoinen.

Ei ne hinnat ole kohtuullisia, kun sensoridata ja tuollaiset 52.5 miljardin rivin datat menee kätevimmin Redshiftiin, eikä Auroraan. Siistimällä tietokantaa saat sen kohtuullisen veloituksen piiriin.

Tietenkin jos tuo on jatkuvasti tulevaa sensoridataa, eikä sinulla ole sitä vielä, niin sitten on Kinesis ja TimeStream, mutta vähän hankalasti oli kuvattu se data. Ja nuo on satoja euroja tuolla määrällä dataa kuukaudessa.

T: AWS Certified Solutions Architect

 

[woodeye]

Voi olla että ymmärsin ongelman asettelun väärin, mutta eikö tuo koko homma mene yhteen tauluun? Koordinaatit, aika ja parametrit olisivat jokainen oma kenttänsä.

Vaikka rivejä on kymmeniä miljardeja, voisi olla ns. kotikoneella kokeiltava juttu. Kannan luonti voi kestää mutta kyselyt toimia ihan järkevässä ajassa, varsinkin jos indeksit ovat kunnossa.

 

[2dogs1bowl]

"But why" on erittäin hyvä kysymys ja tässä vaiheessa kun sitä asiantuntemusta ei tästä aiheesta löydy pätkän vertaa niin olen lähtenyt näkökulmasta "why not".

Kyseessä on siis 30 vuoden globaalia säädataa tunnin intervallilla ja 0.5deg koordinaattiresoluutiolla. Mielikuvissa dataa voitaisiin kutsua mielivaltaisesti rajatulla alueella, yksittäisillä pisteillä taikka esimerkiksi reitiltä pisteiden väliltä, joten tässä vaiheessa kuvittelen ainakin raaka-datan olevan fiksumpi lähestymistapa ilman, että tarkkuus kärsii. Käytännön rajoitteitahan en tällaiselle järjestelmälle tunne.

 

[2dogs1bowl]

Voi olla että ymmärsin ongelman asettelun väärin, mutta eikö tuo koko homma mene yhteen tauluun? Koordinaatit, aika ja parametrit olisivat jokainen oma kenttänsä.

Vaikka rivejä on kymmeniä miljardeja, voisi olla ns. kotikoneella kokeiltava juttu. Kannan luonti voi kestää mutta kyselyt toimia ihan järkevässä ajassa, varsinkin jos indeksit ovat kunnossa.

Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?

 

[hsalonen]

"But why" on erittäin hyvä kysymys ja tässä vaiheessa kun sitä asiantuntemusta ei tästä aiheesta löydy pätkän vertaa niin olen lähtenyt näkökulmasta "why not".

Kyseessä on siis 30 vuoden globaalia säädataa tunnin intervallilla ja 0.5deg koordinaattiresoluutiolla. Mielikuvissa dataa voitaisiin kutsua mielivaltaisesti rajatulla alueella, yksittäisillä pisteillä taikka esimerkiksi reitiltä pisteiden väliltä, joten tässä vaiheessa kuvittelen ainakin raaka-datan olevan fiksumpi lähestymistapa ilman, että tarkkuus kärsii. Käytännön rajoitteitahan en tällaiselle järjestelmälle tunne.

Ehkä nyt sitten kannattaa tehdä se kanta kotikoneelle ja miettiä sen jälkeen optimointeja ja tilankulutusta, kun reunaehdot on selvillä.

 

[hsalonen]

Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?

Jos muutenkin heitetään ne tietokantojen suunnitteluperiaatteet roskiin, niin mihin tarvitset sitä primary keytä?

Nopeutusta varten @woodeye ehdotti indeksejä, joita kannattaa sitten tehdä x,y-pareille ja ajalle. Saa nähdä, nopeuttavatko nekään tarpeeksi.

 

[woodeye]

Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?

Ensinnäkin, riippumatta siitä onko sulla 10 vai 10 miljardia riviä, kyselyt ovat samanlaisia. Toiseksi voit ajaa tiedot kantaa millaisessa järjestyksessä tahansa eikä sillä ole merkitystä.

Tietyltä ajanjaksolta ja tiettyjen koordinaattien sisältä tehty haku on todella yksinkertainen, jotain tyyliin

SELECT arvo FROM taulu WHERE xcoord > X1 AND xcoord < X2 AND ycoord > Y1 and ycoord < Y2 and aika > '2019-09-01' and aika < '2019-09-12'

Tämä nyt olettaen ettei ole mitään joka monimutkaistaa asetelmaa. Usein kannoissa on myös laajennuksia joilla voi tehdä aitoja maantieteellisiä koordinaatteja ja kehittyneempiä kyselyjä kuin tuo.

 

[2dogs1bowl]

Ehkä nyt sitten kannattaa tehdä se kanta kotikoneelle ja miettiä sen jälkeen optimointeja ja tilankulutusta, kun reunaehdot on selvillä.

Ehdottomasti! AWS oli lähinnä jos ehkä joskus-heitto noin huvin, urheilun ja oppimisen takia

 

[ississ]

Ehdottomasti! AWS oli lähinnä jos ehkä joskus-heitto noin huvin, urheilun ja oppimisen takia

Kun rivejä tulee paljon niin partitiointi auttaa (vähän riippuen kannasta).
Esimerkiksi sql serverillä voi tehdä oman datatiedoston/partitio ja yhteen partitioon vaikka vuoden data. Tiedostoja tulee paljon mutta vuoden sisällä tapahtuva haku nopeutuu kummasti kun ei olekaan niin paljoa kahlattavaa.
Oracle osaa myös, mysql:n partitiointia en ole koskaan käyttänyt.

100M on vielä aika pieni, asenna kanta omalle koneelle ja testaa. Todennäköisesti et tarvitse muuta kuin muutaman indeksin jotka tukevat hakuja.

Mysql:ssä kannattaa valita sellainen storage engine joka ei lokita kaikkia kyselyitä (siis insert/update/delete) transaktiolokiin koska et niitä kuitenkaan tarvitse yhdellä syöttökerralla ja useilla hauilla. Perinteinen MyISAM toimii todennköisesti tässä paremmin kuin InnoDB.

Käytä oikeita datatyyppejä (siis ei stringejä joka paikassa) niin kyselyt nopeutuu ja tilaa kuluu vähemmän.
Jos päivämäärien ei tarvitse olla päivämääriä niin joissakin tilanteissa kokonaisluku muodossa yyyymmdd antaa paremman suorituskyvyn ja säilyttää ajan mukaisen vertailun oikein.

Jos nuo parametrit ovat tekstiä ja erillisiä arvoja on paljon vähemmän kuin datarivejä niin sitten omaan tauluun voi olla parempi ainakin tilankäytön kannalta. Kyselyteknisesti rankasti karkeistaen "sitä huonompi tehokkuus mitä enemmän tauluja kyselyssä on mukana"

 

[Xiyng]

Käytä oikeita datatyyppejä (siis ei stringejä joka paikassa) niin kyselyt nopeutuu ja tilaa kuluu vähemmän.
Jos päivämäärien ei tarvitse olla päivämääriä niin joissakin tilanteissa kokonaisluku muodossa yyyymmdd antaa paremman suorituskyvyn ja säilyttää ajan mukaisen vertailun oikein.

Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?

 

[woodeye]

Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?

Nykyään tietokantamoottorit ovat niin hyvin tehtyjä ettei tuollaisella kikkailulla taida voittaa yhtään mitään.

Mutta päivä+aika -kenttien kanssa tuleekin toinen ongelma jos meillä on miljardi riviä aikaan sidottuja tapahtumia, ja hakuja tehdään ajan perusteella. Nimittäin aikapohjaista kenttää on paha indeksoida jos mennään minuutti- ja sekuntitasolle, ehkä tuntitasollakin tulee vaikeuksia valtavan datamäärän kanssa. Indeksit kasvavat niin isoiksi että haut alkavat kestää. Tätä pulmaa voisi kiertää tekemällä apukentän, jossa on tapahtuman ajasta vain päivä ja indeksoimalla sen. Sitten haut kohdistetaan ensin apukenttään ja sen lisäksi vielä varsinaiseen pvm-aikakenttään.

Sama juttu parametrien kanssa, aloittaja ei tainnut sanoa mitä parametrit ovat mutta jos säädatasta on kyse niin varmaan jotain float -tyyppisiä (lämpötila 17.8 jne) jotka on myös vähän paha indeksoida. Tässäkin voisi auttaa arvojen kopioiminen kokonaislukuina apukenttiin ja tekemällä niistä indeksoituja hakuja.

 

[David ":DA" Imodium]

Moi! Alkoi kiinnostaa tuo data. Onko se jokin avoin datasetti ja mistä olisi saatavilla?

 

[hsalonen]

Nykyään tietokantamoottorit ovat niin hyvin tehtyjä ettei tuollaisella kikkailulla taida voittaa yhtään mitään.

Mutta päivä+aika -kenttien kanssa tuleekin toinen ongelma jos meillä on miljardi riviä aikaan sidottuja tapahtumia, ja hakuja tehdään ajan perusteella. Nimittäin aikapohjaista kenttää on paha indeksoida jos mennään minuutti- ja sekuntitasolle, ehkä tuntitasollakin tulee vaikeuksia valtavan datamäärän kanssa. Indeksit kasvavat niin isoiksi että haut alkavat kestää. Tätä pulmaa voisi kiertää tekemällä apukentän, jossa on tapahtuman ajasta vain päivä ja indeksoimalla sen. Sitten haut kohdistetaan ensin apukenttään ja sen lisäksi vielä varsinaiseen pvm-aikakenttään.

Sama juttu parametrien kanssa, aloittaja ei tainnut sanoa mitä parametrit ovat mutta jos säädatasta on kyse niin varmaan jotain float -tyyppisiä (lämpötila 17.8 jne) jotka on myös vähän paha indeksoida. Tässäkin voisi auttaa arvojen kopioiminen kokonaislukuina apukenttiin ja tekemällä niistä indeksoituja hakuja.

Indeksien lisäksi nuo apukentät vaan kasvattaa sitä kokoa ja tätä ei enää saa minnekään pilveen halvalla.

Tosin hieno harjoitus ja samalla tulee testattua se indeksointi + partitiointi.

 

[woodeye]

Indeksien lisäksi nuo apukentät vaan kasvattaa sitä kokoa ja tätä ei enää saa minnekään pilveen halvalla.

Tosin hieno harjoitus ja samalla tulee testattua se indeksointi + partitiointi.

Jos vaihtoehtoina on fyysisesti pienempi tietokanta, jossa haut kestävät liian kauan valmistua ja isompi kanta jossa pystyy tekemään järjellisiä hakuja niin aika selvä kumpi kannattaa tehdä?

Tämä tehtävänasettelu haiskahtaa siltä että ainakin testiversion pystyy toteuttamaan ihan peruskoneella jos viitsii vähän miettiä suorituskykyongelmia etukäteen.

 

[nnaku]

Ompa kiinnostava kysymys ja vielä kiinnostavampi datasetti. Ehkä lähtisin toteuttamaan ideksejä kerroksina koodinaateille ja/tai osiointia aikaleiman mukaa. Laitas data jakoon jos on avointa.

 

[ississ]

Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?

Kuten sanoin "joissakin tapauksissa". Monet optimointivaihtoehdot riippuvat valitusta kannasta ja myös siitä miten dataa käytetään.
On tullut vastaan tapauksia joissa esim date/datetime käyttö hakuehtona/joinissa on ollut hitaampi kuin pieni kokonaisluku.

Joka tapauksessa pitää käyttää tarkoitukseen sopivia datatyyppejä ja välttää stringejä jos massaa on tiedossa paljon, tämä ei muutu mihinkään kannasta riippumatta.
On myös ollut tapauksia joissa apukentät auttavat vaikka massa kasvaa, esimerkiksi minuutti/sekuntidata jonka seuraksi tehdään vuosikk tai päivä. Näihin joudutaan yleensä vain silloin kun rivejä on oikeasti paljon.

Esimerkkinä eräässä järjestelmässä on pääasiassa tuntitason dataa jota käsitellään ja myös näytetään käyttöliittymän kautta. Dataa on nyt noin 23M riviä ja taulu vie tilaa levyltä melkein 3G, kantana sql server 2014. Koska käyttö on pääasiassa kuukausitasolla niin vuosikk- kentän lisäys ja partitiointi sen avulla tuottaa hyvän nopeutuksen hakuihin. Sen indeksoinnillakin pärjää jo pitkälle.
Minuutti/sekuntitason päivämääräkenttää en ehkä lähtisi indeksoimaan ollenkaan.

Mutta kuten jo aiemmin sanoin, kannattaa kokeilla ensin ja sen perusteella sitten näkee pitääkö jotain säätää.

AWS- maailmassa pääsee aika hyvään tulokseen kun tallentaa datan parquet:iin, tallennus S3 ja luku Athenan kautta. S3 ja Athena on suhteellisen halpoja moneen muuhun verrattuna ja parkettien luku nopeaa.
Kirjoitus vaikka spark-shellin kautta käsin omalla koneella.

Ompa kiinnostava kysymys ja vielä kiinnostavampi datasetti. Ehkä lähtisin toteuttamaan ideksejä kerroksina koodinaateille ja/tai osiointia aikaleiman mukaa. Laitas data jakoon jos on avointa.

Tämä, vain näkemällä mitä pitää tehdä voi tehdä jotain oikeasti. Ilman aitoa dataa voi vain spekuloida kaikilla mahdollisilla malleilla ja arvailla

 

[Technocrat]

Itse lähtisin kokeilemaan ihan SQLitellä ja vaikka pythonilla miten taipuu, flatti taulu vaan johon kaikki datat ja kun datasetti on staattinen niin indeksoit vaan columnit joista haet, katso miten toimii. Tuollaista 100 gigan datasettiä pyörittelee kotikoneellakin aivan hyvin kunhan vaan valitsee oikeat työkalut, ellet nyt jotain todella monimutkaista tee sillä datalla. Unohda ainekin aluksi redshiftit ja pilvi DWH kilkkeet, niihin uppoaa fyrkkaa suuria määriä todella nopeasti eikä tuollainen 100 gigan datasetti vielä tee järkeväksi noiden järeiden työkalujen käyttöä kun niitä tosiaan pyörittelee vaikka sillä kuuluisalla omalla läppärillä.

Lisätään nyt vielä senverrran, että jos näitä datoja haluaa pilveen viedä niin varmaan tässä casessa edullisinta olisi katsoa Googlen BigQuery:ä, siellä saa partitioinnit tehtyä helposti ajan perusteella ja hintakaan ei päätä huimaa tuolla datamäärällä kunhan pitää ne kyselyt järkevinä.

 

[dropadrop]

Iso osa vanhempaa SQL dokumentaatiota perustuu normalisointiin jossa pyritään välttämään duplikaattia tietoa (siis lähinnä tilaa) optimoimalla rakennetta. Tämä voisi esim tarkoittaa, että yhdessä taulussa olisi koordinaatit ja id joka olisi toisessa taulussa foreign key.

Tuolla datalla voit päästä samaan suorituskykyyn helpommin yhdellä taululla riippuen lähinnä itse kyselyistä (ovatko koosteet koko datasta vai vain tietyistä koordinaateista / ajalta).

Sitä en alkuperäisestä viestistä ymmärtänyt, että onko 200 lokaatiota (uniikkia x/y paria) vai tosiaan 200x200, ja onko jokaisella ajalla mitattu jokaisella paikalla 5 eri arvoa?

 

[ississ]

Ja kuten tuolla aiemmin jo todettiin niin näyttämällä edes vähän dataa saa parempia vastauksia...

Tuo normalisointi on juuri noin, se on hyvä jos tietoja päivitellään paljon, siis yleensä jonkun softan takana.
Mitä lähemmäs raportointia mennään sitä mieluummin dataa kannattaa yhdistää/monistaa järjestellä niin että rakenteet tukee lukemista yleensä tilan kustannuksella.

 

[routa]

Jos dataa ei tosiaan tulla muokkaamaan, niin minusta järkevin ratkaisu olisi tuupata se yhteen columnstore-tauluun. Itsellä kokemusta vain MSSQL:n toteutuksesta, mutta uskoisin että clustered CS indeksit löytyvät myös opensource tietokannoista.

Klusteroidulla columnstore indeksillä tuollainen data pakkaantunee kymmenesosaan rowstoreen verrattuna ja perus select -kyselyt where -ehdoilla on usein kymmeniä kertoja nopeampia, koska io:ta ei ole läheskään niin paljon indeksien skannauksissa.

 

[JaniKari]

Numpy ja memmap käyttöön. Tietokoneeseen mahdollisimman nopea ssd (tai yli 100gb muisti). Varmasti nopein ja helpoin tapa analysoida dataa.

 

[SShadow]

Joku GIS-laajennos MySQL/MariaDB kantaan, tai jos tietokantahommat ovat uusia niin miksei suoraan PostgreSQL. Jos XY-koordinaatit olisivat vaikka WellKnownBinary-muodossa, niin sen saa indeksoitua aika hyvin. Aika jossain tunnetussa formaatissa ja päälle attribuutit.
Ainakin PostgreSQL PostGIS laajennoksella osaa aika hyvin nuo vertailut reitin ja kiinteiden pisteiden välillä. Aika hyvin löytyy Googlellakin neuvoja

 

[D8088-2]

Kun tässä ketjussa tuntuu olevan SQL kantoihin perehtyneitä, onko teistä joku yrittäjä tai muuten tietää mistä saisi jeesiä (maksua vastaan) yhden Percona (InnoDB) tietokantapalvelimen optimointiin? Kanta ja palvelin on pystytetty hosting firman toimesta, mutta kun dataa on kertynyt woocommercen kautta gigatavuja, niin ne cache ja swap asetukset ovat jääneet jälkeen. Tällä hetkellä kanta on todella hidasta ja vie ainakin cpu:ta liikaa.

 

[dropadrop]

Yleensä ongelma on kyllä pikemminkin kyselyissä ja kannan indekseissä ennemmin kuin noissa säädöissä.

Toki konffeilla saa hienosäädettyä paremmaksi.

 

[D8088-2]

Yleensä ongelma on kyllä pikemminkin kyselyissä ja kannan indekseissä ennemmin kuin noissa säädöissä.

Toki konffeilla saa hienosäädettyä paremmaksi.

No tässä tapauksessa phpmyadminin statukset kertoo vahvasti, että esim innodb buffer pool size on 72Mib kun sen pitäisi olla testin mukaan 7Gib. Senhän saa alla olevalla kutsulla laskettua (löysin googlella). Tämän lisäksi siellä on muitakin mitä se perus phpmyadmin advisor suosittelee kutsujen optimoinnin lisäksi.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

 

[jra72]

Miks SQL? Ehkä NoSQL?

 

[D8088-2]

Miks SQL? Eiks joku timeseries db olisi toimivampi ratkaisu?

Koska käytössä on wordpress/woocommerce kuten sanoin viestissäni.

 

[dropadrop]

No tässä tapauksessa phpmyadminin statukset kertoo vahvasti, että esim innodb buffer pool size on 72Mib kun sen pitäisi olla testin mukaan 7Gib. Senhän saa alla olevalla kutsulla laskettua (löysin googlella). Tämän lisäksi siellä on muitakin mitä se perus phpmyadmin advisor suosittelee kutsujen optimoinnin lisäksi.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Kun alustuksessa mainittiin, että kanta on kasvanut ”gigoja” niin tuntuu yllättävältä että cachen pitäisi olla noin iso?

Toki on mullakin alla kantoja joissa muistia on allokoituna enemmän kuin dataa. Nämä ovat poikkeuksetta semmoisia jossa dataa ei kerry paljoa, mutta kyselyitä on kymmeniä tuhansia sekunnissa. Softassa olis paljon optimoitavaa, mutta kiire tehdä muuta niin ratkaistaan raudalla...

pikaisesti katsottuna toi sun kysely laskee yhteen tallennetun datan ja indexien koon, ja muuntaa / pyöristää gigoiksi. Se ei välttämättä ole huono idea, mutta ei kannata kasvattaa sitä isommaksi kuin 70% muistista.

Mysql’n oletus on 128MB, eli tässä tapauksessa joku olis varta vasten pienentänyt? Kuullostaa kyllä erikoiselta, ellei sitten ole todella rajalliset resurssit.

Muistilla voi aika hyvin korjata perus paskat kyselyt. Se mikä ei tuolla korjaannu on lukot / levylle kirjoitettavat temp tiedostot.

 

[D8088-2]

Kun alustuksessa mainittiin, että kanta on kasvanut ”gigoja” niin tuntuu yllättävältä että cachen pitäisi olla noin iso?

Toki on mullakin alla kantoja joissa muistia on allokoituna enemmän kuin dataa. Nämä ovat poikkeuksetta semmoisia jossa dataa ei kerry paljoa, mutta kyselyitä on kymmeniä tuhansia sekunnissa. Softassa olis paljon optimoitavaa, mutta kiire tehdä muuta niin ratkaistaan raudalla...

pikaisesti katsottuna toi sun kysely laskee yhteen tallennetun datan ja indexien koon, ja muuntaa / pyöristää gigoiksi. Se ei välttämättä ole huono idea, mutta ei kannata kasvattaa sitä isommaksi kuin 70% muistista.

Mysql’n oletus on 128MB, eli tässä tapauksessa joku olis varta vasten pienentänyt? Kuullostaa kyllä erikoiselta, ellei sitten ole todella rajalliset resurssit.

Muistilla voi aika hyvin korjata perus paskat kyselyt. Se mikä ei tuolla korjaannu on lukot / levylle kirjoitettavat temp tiedostot.

Sanoin ehkä huonosti, mutta sivuston alusta kasvua on tullut gigoja tähän päivään asti. Siinä vaiheessa kun tuo siirrettiin toiselle tietokantapalvelimelle, jonka hosting firma oli tehnyt, niin nuo asetukset oli siellä, mutta myös useampi giga dataa.

Tuota kyselyä kehoitetaan juuri käyttämään sen cachen koon laskemiseksi ja olet oikeassa siitä 70% muistista, joten edelleen se on liian vähän. Sittenhän siellä on vielä ne blockit, eli vaikka cachen koko on iso vs. datan määrä, se pilkotaan blockeiksi. Ko. virtuaalikoneella jossa pyörii myös Litespeed, on 4Gt rammia, jotem 72Mib on selkeästi päin persettä.

 

[Frederikin rintakarva]

Ko. virtuaalikoneella jossa pyörii myös Litespeed, on 4Gt rammia, jotem 72Mib on selkeästi päin persettä.

Kantapannuilla lähdetään siitä, että kaikki vapaa käytettävissä oleva muisti allokoidaan cachelle. Jos datamäärä lasketaan gigaluokassa, voi hyvin lähteä siitä ettei 72Mb riitä alkuunkaan. Tai toki voi riittää, mutta se tarkoittaisi kannan olevan write-only.

Muistin hyötykäytön jälkeen kannattaa tarkastella jo aiemminkin mainittuja indeksejä, käytettyjä kyselyitä ja muuta toimintologiikkaa.