Big mysql table

binaryk · august 18, 2016, 2:45pm

O sa incerc pe local sa testez, mie frica de o modificare asa serioasa, pentru ca in aceste tabele sunt salvate statistici despre banii oamenilor, e foarte important sa nu bulesc nimic Merci!

AdrianBasalic · august 18, 2016, 2:45pm

Ca idee, din toata experienta mea cu MySQL-ul, nu exista o solutie unica sau care e clar cea mai performanta, tot timpul trebuie sa incerci, testezi. Din pacate trial & error e calea de urmat cu MySQL.

binaryk · august 18, 2016, 2:46pm

Sper sa pot face modificarile de care zici cu datele existente,pt ca la acest volum de date, am incercat sa adaug o coloana noua, si se blocheaza socket-ul, nu ma lasa.

AdrianBasalic · august 18, 2016, 2:49pm

pregatesti structura la o tabela noua si muti datele in ea daca dureaza prea mult schimbarile. complicat daca e live si scrie tot timpul in ea, nu prea poti fara downtime.

Bogdan_Ciubotariu · august 18, 2016, 3:24pm

Probabil e redundantă întrebarea, dar nu a mai pus-o nimeni: ai indecși pe toate coloanele pe care trebuie să ai? 5 secunde la atâtea intrări în tabel înseamnă că probabil ai, dar ca să fim siguri…

binaryk · august 23, 2016, 7:10am

Da, am indexi pe coloanele care sunt in clauza where.

tekkie · august 23, 2016, 11:26am

La partea cu indecsii trebuie precizat de fiecare data si faptul ca s-a rulat explain si ca se folosesc acei indecsi. Am invatat multe despre optimizari din planul de explain.

horia141 · august 23, 2016, 11:30am

Cei de la MariaDB au un column store care ar fi ideal pentru astfel de operatii. Nu am reusit sa aflu daca e si pentru MySQL. Dar ar fi un upgrade destul de simplu ma gandesc[1], caci MariaDB e un fork compatibil cu MySQL. Si te-ar lasa sa faci query-uri mai flexibile decat un tabel cu chestii pre-calculate.

[1] Mai simplu decat sa adaugi un sistem de baze de date nou, cel putin.

AdrianBasalic · august 23, 2016, 11:48am

chiar daca ai indecsi in plus sau nefolositi, nu-ti incetinesc query-urile (selecturile cel putin). MySQL decide el daca foloseste sau nu un index existent.

IonutBotizan · august 23, 2016, 2:57pm

binaryk:

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();

N-a observat nimeni că face GROUP BY date iar date nu este o coloană în tabel ci o valoare generată de DATE_FORMAT()?

Eu zic să-ncerci să mai adaugi o coloană de tip DATE în care să ții doar data și pe care să faci GROUP BY.

Am încercat un query simplu cu GROUP BY created_on versus GROUP BY DATE_FORMAT(created_at) pe un set de date de aproximativ 4.300.000 de intrări (dintr-un proiect pe care lucrez) și durata a fost de cel puțin 3 ori mai mică.

O altă chestie ce mi-a redus timpii cu 30% a fost modificarea câmpurilor extrase. Din ce văd în codul tău, tu nu ai decât 3 valori de bază, restul fiind derivate din cele 3:

COUNT(1) AS clicks
AVG(quality) AS avg_quality
SUM(price) AS sum_price

iar query-ul tău ar putea arăta așa:

SELECT
    clicks,
    ROUND(clicks * avg_quality, 0) AS clicks_calitate,
    ROUND(avg_quality * 100, 2) AS quality,
    ROUND(sum_price, 3) AS earnings,
    ROUND((sum_price / (clicks * avg_quality)), 3) AS ecpc,
    ROUND((sum_price * 1000 / (clicks * avg_quality)), 3)  AS ecpm,
    created_on
FROM (
    SELECT 
        COUNT(1) AS clicks,
        AVG(quality) AS avg_quality,
        SUM(price) AS sum_price,
        created_on
    FROM reports
    WHERE
        /* condițiile tale */
    GROUP BY created_on
    ORDER BY created_on ASC
) AS aggregate_data

EDIT
Mi-am amintit încă o chestie ce s-ar putea să ajute…
Sper să nu greșesc, dar MySQL parcurge indecșii iar dacă unul se potrivește condițiilor, dă fuga la tabelă pentru a citi restul valorilor din câmpurile pe care le ceri. Dacă creezi un index care să cuprindă doar cele 4 coloane pe care le folosești (user_id, created_on, price, quality), MySQL nu va mai pierde timpul pentru a citi tot rândul din tabelă, ci va folosi valorile direct din index.

binaryk · august 23, 2016, 5:23pm

Am testat acum pentru un user care e in top ca inregistrari, si pentru el dashboardul (adica procedura aia stocata) ruleaza 1.3 minute, iar raportul pe ultima zi (primul query ce l-am pus) in jur de 1,17 minute E foarte lent, acum am un call cu clientul sa iau o decizie. E grav.

Catalin_Banu · august 24, 2016, 9:34pm

Partitionarea tabelei ai luat in calcul? https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

IonutBotizan · august 25, 2016, 1:59am

Așa-ți trebuie dacă folosești Laravel!!!
Am recitit mesajele tale pentru că mi se părea absurd ca rezultatele filtrate pentru o singură zi (maxim 300.000 de intrări zilnice, după cum ai zis) să dureze atât. Și mi-a picat fisa:

->whereDate('created_at','>=',$start_date)

este de fapt

WHERE DATE(`created_at`) >= '$start_date'

nu? De-aia ai întrebat într-un mesaj anterior, așa-i?

Ceea ce înseamnă că ți-ai băgat picioarele-n orice index aveai definit pe coloana aia!!! Normal că merge prost query-ul!

Fă

$start_date .= ' 00:00:00';
$end_date .= ' 23:59:59';

și renunță la ->whereDate(). Nu văd cum ar rula mai mult de o secundă query-ul cu raportul zilnic după ce faci asta!

binaryk · august 25, 2016, 6:13pm

Pana la urma am migrat pe MariaDB, a facut cineva niste optimizari prin ceva .bin-uri (eu nu le am cu astea) si a iesit foarte rapid. De exemplu aveam un user cu vreo 2 mln intrari, si dura o procedura 2 minute, acum dureaza fix 10s. E super. Merci toata lumea pentru integrare!