Kysymys soveltuvasta SQL-arkkitehtuurista

Viestiketju alueella 'Ohjelmointi, pelikehitys ja muu sovelluskehitys' , aloittaja 2dogs1bowl, 12.09.2019.

  1. 2dogs1bowl

    2dogs1bowl

    Viestejä:
    87
    Rekisteröitynyt:
    02.01.2017
    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.
     
  2. hsalonen

    hsalonen

    Viestejä:
    8 526
    Rekisteröitynyt:
    17.10.2016
    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
     
    Viimeksi muokattu: 12.09.2019
  3. woodeye

    woodeye

    Viestejä:
    56
    Rekisteröitynyt:
    31.08.2018
    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.
     
  4. 2dogs1bowl

    2dogs1bowl

    Viestejä:
    87
    Rekisteröitynyt:
    02.01.2017
    "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.
     
  5. 2dogs1bowl

    2dogs1bowl

    Viestejä:
    87
    Rekisteröitynyt:
    02.01.2017
    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?
     
  6. hsalonen

    hsalonen

    Viestejä:
    8 526
    Rekisteröitynyt:
    17.10.2016
    Ehkä nyt sitten kannattaa tehdä se kanta kotikoneelle ja miettiä sen jälkeen optimointeja ja tilankulutusta, kun reunaehdot on selvillä.
     
  7. hsalonen

    hsalonen

    Viestejä:
    8 526
    Rekisteröitynyt:
    17.10.2016
    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.
     
  8. woodeye

    woodeye

    Viestejä:
    56
    Rekisteröitynyt:
    31.08.2018
    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.
     
    hsalonen tykkää tästä.
  9. 2dogs1bowl

    2dogs1bowl

    Viestejä:
    87
    Rekisteröitynyt:
    02.01.2017
    Ehdottomasti! AWS oli lähinnä jos ehkä joskus-heitto noin huvin, urheilun ja oppimisen takia
     
  10. ississ

    ississ

    Viestejä:
    414
    Rekisteröitynyt:
    17.01.2018
    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"
     
    2dogs1bowl tykkää tästä.
  11. Xiyng

    Xiyng

    Viestejä:
    1 090
    Rekisteröitynyt:
    19.10.2016
    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?
     
  12. woodeye

    woodeye

    Viestejä:
    56
    Rekisteröitynyt:
    31.08.2018
    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.
     
  13. David ":DA" Imodium

    David ":DA" Imodium

    Viestejä:
    1 888
    Rekisteröitynyt:
    19.10.2016
    Moi! Alkoi kiinnostaa tuo data. Onko se jokin avoin datasetti ja mistä olisi saatavilla?
     
    kaakau<"'\\/> ja nnaku tykkäävät tästä.
  14. hsalonen

    hsalonen

    Viestejä:
    8 526
    Rekisteröitynyt:
    17.10.2016
    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.
     
  15. woodeye

    woodeye

    Viestejä:
    56
    Rekisteröitynyt:
    31.08.2018
    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.
     
    hsalonen tykkää tästä.
  16. nnaku

    nnaku I'm object-oriented! Tukijäsen

    Viestejä:
    730
    Rekisteröitynyt:
    28.11.2016
    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. :tup:
     
  17. ississ

    ississ

    Viestejä:
    414
    Rekisteröitynyt:
    17.01.2018
    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.

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