Optimizare baze de date foarte mari

[Marius Mailat]

Daca sunt bine pusi indexii, prima varianta e ceea mai buna.

[Netul]

Tabelele au campurile:
Tabel:
ID, url, domeniu, titlu, descriere, continut (de tip mediumtext), data_indexare, marime, rang, info

Nu ar fi mai bine sa impart informatiile in 2 tabele? Cautarea se face doar in url, titlu, descriere si continut. Sa fie de genul:

Tabel1:
ID, url, titlu, descriere, continut

Tabel2:
ID, domeniu, data_indexare, marime, rang, info

La tabele mici nu cred ca ar fi nici nici un castig, dar daca sunt milioane de inregistrari, poate ca o cautare se face in timp mai scurt.

[Marius Mailat]

Important e sa ai indexi pe url, titlu, descriere si continut. Parerea mea e ca e bine sa faci arhitectura la DB acum inainte sa ai probleme mai mari decat pe parcurs. Si eventual sa apelezi la persoane cu experienta la asa ceva.

La numarul de inregistrari ce il ai poate face diferenta un index in plus sau in minus. Diferente de seunde la query.

[Toto]

Am schimbat in php.ini:

php.ini se ocupa cu configul php-ului... Tu trebuia sa modifici configul mysql-ului... Nu are treaba php-ul cu limita DB...

Am dat si restart la apache, dar tot degeaba, imi ramane limita in phpmyadmin tot la 51 MB

Din nou, trebuia dupa ce modificao mysql config sa restartezi mysql-ul nu apache-ul... Iar cei 51 MB iti sunt aratati doar de phpmyadmin, dar acolo iti afiseaza setarile mysql-ului ... nu ale phpmyadmin...

[Netul]

@toto:
Cum am citit pe net, chiar si pe forumul CPanel, toata lumea zicea sa schimb datele alea in php.ini.
Chiar si pe siteul oficial PhpMyAdmin scrie:
phpMyAdmin 3.0.0-dev - Documentation
"The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize."

Ma miram de ce nu are efect.

[Toto]

Si acum ca sa iti dau un raspuns la problema ta, pentru ca m-am lovit si eu la un moment dat de ea, cu murfi...

Intr-adevar, indexand, ai parte de DB-uri f mari, cu tabele uriase... De aici vii cu doua solutii:
1 - pui site-ul pe un cluster, unde ai masina dedicata de crawling, masina dedicata de search, etc ...
2 - indexezi din alta parte (ex acasa) si updatezi fisierele DB-ului...

Daca optezi pentru solutia 2, cum ai facut tu, este ce-a mai proasta idee sa updatezi tu manual ... iti ia timp, te lovesti de tot felul de probleme, ca cele pe care le-ai enumerat mai sus...

In schimb, poti apela la o aplicatie de back-up pe care sa o folosesti in interesul tau, adica, sa fac back-up la DB de acasa pe serverul public... de unde evident, se excuta cautarile... Si mai mult de atat, ar trebui sa folosesti o solutie de backup incremental, care doar sa it updateze fisierele, nu sa le mute cu co totul zilnic... pentru ca iar, nu faci nimic...

Sper ca ti-am dat de gandit... bafta!

[Netul]

@toto
Folosesc metoda a doua.
Problema mare acum este alta: o cautare simpla se executa f rapid. Insa daca vreau sa fac o cautare cat mai relevanta, cu cat mai multe filtre, cautarea dureaza f mult. Nu mai zic in cazul cand cautarea se face dupa mai multe cuvinte....
Nu cred ca ar sta nimeni sa astepte mai mult decat cateva secunde ca sa se returneze rezultatele.

[Netul]

Din experienta de pana acum, si din documentatia de pe net, am facut o lista cu cele mai importante sfaturi in caz ca lucrati cu baze de date foarte mari. Sper sa va fie de ajutor.


Nu folositi configuratia default, deoarece este optimizata pentru baze de date mici sau medii.

Folositi des comanda SHOW [GLOBAL] STATUS ca sa vedeti ce se intampla la nivelul low.

tmp_table_size poate fi marit pentru a reduce nr de tabele creeate pe disc.

Mariti de asemenea si valorile key_reads si key_writes.

Nu permiteti prea multe tabele deschise f repede - maxim 1 / secunda

Activati slow query log pentru a putea analiza ce query se executa lent(long_query_time)
Mariti sort_buffer_size

Threads_running va arata numarul de conexiuni active. Nu lasati ca sa numarul lor sa fie foarte mare

Bufferele de memorie sunt f importante pentru performanta:
innodb_buffer_pool_size, key_buffer_size

Nu alocati foarta multa memorie in caz ca nu aveti f multa memorie disponibila. Folosirea unei memorii prea apropiata de memoria disponibila a sistemului poate duce la micsorarea performantelor. De asemenea, folosirea unei memorii mai mari pt mysql decat memoria sistemului poate duce la blocarea mysql.

Folositi Table_cash = 512 si NU 512K (mai bine numar de intrari decat numar de bytes)
De asemenea folositi key_buffer_size = 32M (dati valoarea marimii si nu a numarului de blocuri)

Nu folositi valori f mari daca nu aveti nevoie. Valori prea mari pot duce la micsorarea performantelor.
sort_buffer_size = 512K poate fi mai rapid decat 16M in cazul unor sortari de dimensiuni mici.

Faca folositi Join, folositi neaparat Indecsi:
SELECT client_nume,pret FROM comanda, client WHERE comanda.client_id=client.id
Trebuie neaparat ca sa existe un index la client(id)
!!! Join-urile fara indecsi sunt foarte lenti

F. IMPORTANT: Folositi tipuri de date corecte
Exemplu GRESIT:
id este VARCHAR(20)
SELECT titlu FROM carti WHERE id=5 , unde id este PRIMARY KEY.
In acest caz SELECT va face o scanare a intregii tabele. id = 5 se potriveste si in cazul valorilor de '5' , '05' , '5.0' etc.

Folositi tipuri de date ce ocupa cel mai mic spatiu:
INT este mult mai bun decat BIGINT, iar TINYINT este si mai bun decat INT (in caz ca nu aveti nevoie de valori mai mari).
Limitati valoarea lui VARCHAR. Nu folositi de ex. nume VARCHAR (255)
Folositi NOT NULL in toate cazurile posibile.
Tipurile intregi sunt mult mai rapide in cazul unor sortari, comparatii, grupari, si de asemenea ocupa mult mai putina memorie.

In cazul JOIN: ele genereaza cautari foarte multe in vazul datelor de tip VARCHAR. Folositi (daca e permis) tipuri INT.

IMPORTANT: Setul de caractere UTF8 ocupa de 3 ori mai mult spatiu rezervat. De ex. VARCHAR (100) va ocupa nu 100 ci 300 bytes in cazul cand setul de caractere pe acel camp este UTF8.
Nu folositi UTF8 daca nu aveti nevoie (de ex in cazul unor campuri ca url, email, coduri ...)
De asemenea sortarile si comparatiile pe campuri cu UTF8 sunt mult mai lente

Folositi indecsi pe campurile ce le scanati cel mai des.
Folositi indecsi cu prefix, de exemplu: KEY cod_postal(cod_postal(6)) - codul postal e format din 6 cifre.

Nu folositi mai multi indecsi pe acelasi camp (indecsi duplicat)
Ex: PRIMARY KEY(id), UNIQUE(id), KEY(id) va creea 3 indecsi pe acelasi camp id.
Sau: nu folositi de ex. KEY(nume), KEY(nume,varsta). KEY(nume) poate fi sters.

Folositi OPTIMIZE TABLE pentru a reface un tabel prea fragmentat.

NU folositi * cand folositi comanda SELECT.
GRESIT: SELECT * FROM tabel - deoarece se selecteaza toate campurile tabelului, ceea ce duce la marirea timpului in care se returneaza rezultatele.
Folositi doar campurile de care aveti nevoie sa le analizati / afisati.
CORECT: Dac aveti nevoie doar de cateva campuri din acel tabel folositi de ex:
SELECT nume, varsta, oras FROM tabel.

Folositi LIMIT in cazul unor rezultate foarte numeroase, in caz ca nu aveti nevoie decat de un anumit numar de rezultate
Ex: SELECT nume, varsta FROM clienti LIMIT 0,100 - va afisa primele 100 inregistrari.
Folositi indecsi pentru ORDER BY / GROUP BY.
Atentie: un limit de genul: LIMIT 10000,10 poate fi extrem de lent.

Optimizati GROUP BY:
- Folositi SQL_BIGRESULT in cazul unui nr mare de grupari
- Adaugati ORDER BY NULL daca nu aveti nevoie de o ordine anume.

[Toto]

Problema mare acum este alta: daca vreau sa fac o cautare cat mai relevanta, cu cat mai multe filtre, cautarea dureaza f mult.

Aici deja intri in alte probleme... Adica:
1 - proiectarea bazei de date - felul cum ai proiectat baza de date
2 - query-ul - modul in care faci query-ul
3 - setarile serverului mysql - daca e mysql - pe care se face cautarea.

Astea 3 sunt cele mai importante elemente care ingreuneaza - sau nu - cautarile, evident, in cazul cautarilor complexe...