Big mysql table

Limbaje de Programare Big Data
1

Salut,

avem un tabel intr-o BD MySQL cu multe inregistrari, de ordinul 20 mln in acest moment, si zilnic creste cu aproximativ 100-300k. Tabelul este relationat cu cateva alte entitati. Accesul la acest tabel pentru operatii se face simultan din mai multe sesiuni (50-100). Problema este ca, fiind multe intrari, dureaza destul de mult pana se ruleaza o procedura pe ele (care doar scoate date, face totalizari etc.).

Ideea este ca datele mai vechi de o saptamana gen putem sa le excludem, la ele accesul este destul de rar, dar totusi este nevoie de ele.
Indexari pe coloane s-au facut pe cat e posibil.

2

Hey @binaryk nu e clar din mesajul tau care-i problema.

Cat dureaza sa rulezi sumele etc? Minute, ore, zile?

3

@horia141 imagineaza-ti ca trebuie sa facem sum pe o coloana pentru un utilizator (avem user_id si coloana price pt sum) , toata procedura dureaza vreo 5 secunde.

O procedura mai complexa ar fi sa facem group pe zile, adica vin intrari pentru un user pe zile (in fiecare zi vreo 10k sa zic, si trebuie sa faca group pe zile pentru a vedea un raport cu sum de price pe acest user pe ultimele 7 zile, aceasta dureaza in jur de 10-15 sec. Cu cat marim intervalul creste si timpul de raspuns.

Oricum, nu stiu daca e ok sa avem un table cu atatea intrari, mi-a zis cineva ca ei aplicau ceva tabele de history, in care bagau datele vechi, si le accesau doar cand era nevoie, nu stiu insa prin ce mecanisme.

4

@binaryk
Ca referinta, 5 si 15 secunde mi se par timpi decenti pentru marimea tabelei. Am dat un query similar pe un tabel cu O(1m) rows si a durat O(1s), asa ca nu mi se pare neaparat ca e o “problema”. Probleme erau cand dura un query d’asta simplu o ora sau asa.

Acestea fiind, spuse, I’m not your DBA, asa ca orice zic e mai mai mult guesswork. Sper sa fie niste sfaturi utile, dar orice munca din asta implica sa stii schema, modelul de access al datelor etc.

  • Deduc ca ar fi vorba de un tabel “analytics”, nu neaparat unul unde tineti datele centrale ale aplicatiei.
  • Intr-un an o sa strangeti 100M rows. Congratulations! You might have big data!
  • Cat de fresh trebuie sa fie datele? Daca sunt folosite pentru a genera raporturi, poate e deajuns sa fie un job zilnic sau mai des care face toate calculele si le salveaza intr-o tabela cache. Cand utilizatorii vor sa vada rezultate, datele sunt luate din tabela cache in loc de a fi calculate pe loc. In principiu dai consistency pe eventual consistency si primesti ceva performanta inapoi.
  • Daca vrei si mai fresh (gen 1 minut), si daca ai un set limitat de query-uri pe care le executi, poti sa mentii tabele din astea cache cu rezultatele intr-o maniera iterativa. Daca ai de facut group-by-sum, poti sa incrementezi intr-o tabela cu (user_id, sum) de fiecare data cand scrii un record in baza de date. Poti sa decuplezi operatiunea de flow-ul principal printr-o coada sau ceva asemanator, de unde cate un procesator pentru fiecare tip de query face scrierea.
  • Nu stiu daca in MySQL exista, dar in SQL Server de la MS, exista optiunea schimbari strategiei de stocare pe tabel dintr-un row-oriented intr-una column oriented. Clusterd Column Index / Storage ii spune. E ca si cum ai avea un columnar DB in SQL Server cu toate bunatatile lui. Se preteaza fix pentru genul asta de date, generate in urma stream-urilor de activitate ale userilor/proceselor etc. Operatiile analitice sunt foarte rapide, si, mai important, stocarea este optimizata pentru astfel de date - compresie pe coloane, nu ai gap-uri etc.
  • Spargerea / shard-urirea tabelelor pe zi. Asta e o abordare clasica, si cred ca ce ti s-a sugerat deja (tabele de history). Practic ai o tabela X_yyyy_mm_dd (sau X_yyyy_mm) pentru fiecare zi (luna) in care scrii datele aferente. E mai greu pe partea de query-uri, dar marimea indexi-lor este limitata, si e in general mai usor de gandit, chiar daca infrastructura din jur e mai complicata. E destul de agnostica la ce query-uri ai.
  • Daca stii ca toate query-urile o sa fie relative la user_id, poti face sharding dupa user_id. E cam cea mai complexa abordare pe care ai putea lua-o / cot-la-cot cu schimbarea bazei de date. Nu stiu cat de buna este strategia MySql de sharding.
  • Nu recomand - incearca o alta baza de date, non-relationala. Cand te apropii de 1B records, nu stiu daca mai face fata vreo baza de date SQL, pe o singura masina.
  • [ INSERT MySQL SPECIFIC ADVICE HERE ]

Sper sa ajute.

3 Likes
5

Pune structura completă a tabelului și ceva date dummy dacă ai ca să ne dăm seama ce și cum țineți acolo. Un tabel „intermediar” pentru raportare ați încercat să faceți? Practic dacă aveți anumite statistici care vă interesează mai mult puteți să aveți un over-night cron care să agregheze (agrege?) datele și să nu mai frece tabelul mare pentru 90% din nevoi.

Se poate aplica la voi asta?

3 Likes
6

Da da. e super ideea asta, sa aplic un cronjob care o data la 1 saptamani sa zic, sa adune datele cumulate in ceva tabel pentru statistics. Doar ca totusi raman sceptic daca e ok ca sa avem tabele asa mari, in timp el va tot creste. Cat o sa suporte :slight_smile: ?

Merci

7

Exact asa am facut, foloseam cozi in care cumulat o tabela de asta cu user_id si sum, dar intr-un final nu se mai sincroniza sa zic. Adica in tabelul de baza injecta, aici (din coada) uneori nu se mai ajungea. Si la cateva mii de intrari se simtea treaba asta.

8

Ce mi-ai recomanda ? As putea sa trec, sau cel putin sa tin cateva tabele in alta baza de date, si restu sa ramana in MySQL.

9

In prima faza o sa merg pe abordarea asta, e ceva similar cu ce zice si @Bogdan_Ciubotariu.

De ce imi este frica aici: pai, ca sa rulezi un script care sa faca operatii pe tabelul initial, si sa faca sume si sa le injecteze in alt tabel pt fiecare user separat, ar dura vreo 15 minute probabil. Nu stiu daca nu se va bloca la un moment dat scriptul, timeout-uri sau chestii de genu asta. Si in alea 15 minute nu se tine blocat tabelul ? Adica oamenii pot lucra continuu pe el ?

10

eu as merge pe urmatorul aproach:

  • facut tabela identica ca si structura, stocat (mutat) in ea datele la care nu umbli (arhiva practic) si iti ramane un set de date pe care poti lucra mai usor
  • daca tot dureaza mult, poti face inca o tabela identica ca si structura, insert select where id_user = x, si lucrat pe tabela asta care o sa contina doar userul x. Aici am dubii ca truncate / insert select / rulat procedura n-o sa ia mai mult decat pe tabela cu toate datele, trebuie testat.
  • in functie de ce date ai, daca ai cautari poti incerca elasticsearch.
1 Like
11

Untitled.png1505×177 15.6 KB

12

Ideea este ca datele alea (arhivate) vor fi necesare in cazul in care userul vrea sa vada raportul sau de acum 3 luni.

13

O scurta intrebare:

Daca fac select * from tabelname where date(created_at) > date(2016-07-10) and user_id = 10 interogarea asta o sa mearga peste toate 20 M pana gaseste ? Sau se limiteaza cumva in functie de where ? Poate vorbesc prostii, dar nu stiu cat de mult se solicita db-ul la asa gen de interogari.

14

alta idee: partitii dupa id-ul userului, daca e folosit tot timpul, asa o sa ai y fisiere cu x useri fiecare in loc de un singur fisier mare in care faci operatiile. Caz in care nici nu mai conteaza cat de mare e tabela daca la query-uri pui where id_user =.

15 
DB::table('reports')
            ->select(DB::raw('
                FLOOR(count(1)) as clicks,
                ROUND(count(1) * avg(quality),0) as clicks_calitate,
                ROUND(avg(quality) * 100,2) as quality,
                ROUND(sum(price),3) as earnings,
                ROUND((sum(price) / (count(1) * avg(quality))),3) as ecpc,
                ROUND((sum(price) * 1000 / (count(1) * avg(quality))),3)  as ecpm,
                created_at,
                DATE_FORMAT(created_at, "%m-%d-%Y") as date
            '))
            ->where('user_id', '=', access()->user()->id)
            ->whereDate('created_at','>=',$start_date)
            ->whereDate('created_at','<=',$end_date)
            ->groupBy('date')
            ->get();

asta e unul dintre cele mai solicitante quere-uri.
Si mai este unul:

DB::table('reports')
            ->select(DB::raw('
                FLOOR(count(1)) as clicks,
                ROUND(count(1) * avg(quality), 0) as clicks_calitate,
                ROUND(avg(quality) * 100, 2) as quality,
                ROUND(sum(price),3) as earnings,
                ROUND(sum(price) / (count(1) * avg(quality)), 3) as ecpc,
                ROUND(sum(price) * 1000 / (count(1) * avg(quality)), 3)  as ecpm,
                created_at,
                DATE_FORMAT(created_at, "%m-%d-%Y") as date
            '))
            ->where('user_id', '=', access()->user()->id)
            ->whereDate('created_at','>=',$start_date)
            ->whereDate('created_at','<=',$end_date)
            ->groupBy('user_id')
            ->get();

Si asta, care e apelat printr-o procedura stocata:

SELECT * from (

            (SELECT if(sum(price),sum(price),0) as today FROM `reports` FORCE INDEX (created_at) WHERE user_id=_user_id AND DATE_FORMAT(created_at,'%m-%d-%Y') = DATE_FORMAT(NOW(),'%m-%d-%Y')) as today,
            (SELECT if(sum(price),sum(price),0)  as yesterday FROM `reports` FORCE INDEX (created_at) WHERE user_id=_user_id AND DATE_FORMAT(created_at,'%m-%d-%Y') = DATE_FORMAT(subdate(NOW(), 1),'%m-%d-%Y')) as yesterday,
            (SELECT if(sum(price),sum(price),0) as month_my FROM `reports` FORCE INDEX (created_at) WHERE user_id=_user_id AND DATE(created_at) > DATE(subdate(NOW(), 30))) as thisMonth )

Astea 3 dureaza cel mai mult. In rest e super ok.

16

mda, nu se preteaza elasticsearch.

17

alta idee: :)) rapoartele de acu 3 luni de ziceai tu, tine-le cache-uite in alt tabel, ca oricum nu se mai schimba cifrele, corect?

ideea e ca nu-ti mai trebuie raw data ci doar rezultatele procedurilor

1 Like
18

Mi se pare o abordate profi, dar nici macar nu stiu cum sa caut sa fac chestia asta. Asta inseamna ca si la injectari trebuie sa injectez in fisierul corespondent user_id-ului. Daca folosesc un ORM cum fac ? Un model / user :smiley: ?

19

Da, corect.

20

https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

in cod nu schimbi nimic. stie mysql-ul intern, trebuie doar sa setezi tabela

1 Like