Data postarii 2013-01-05      Subiect: Optimizare MySQL      Categorie: Securitate server

Optimizarea serviciului MySQL

Site-ul se misca foarte greu din cauza cererilor mysql!?

 

       Trebuie sa sterg  automat  inregistarile din tabela pentru ca sunt foarte multe, iar serverul mysql nu-mi mai face fata!

 

        Acestea sunt cateva dintre probleme cu care utilizatorii mysql se confrunta, dar raspunsul poate fi unul simplu, oare este optimizat serviciul de mysql cum trebuie, oare bazele de date sunt bine organizate?

 

        Daca sunteti in aceasta situatie si mysql proceseaza foarte multe cereri, iar datele sunt preluate dintr-o singura baza de date, timpul general de incarcare si raspuns va fi foarte  mare si uneori pot aparea erori grave. Daca in schimb avem un serviciu mysql bine optimizat si tabelele bine organizate, chiar daca aplicatia are nevoie de resurse multe, timpul de procesare a informatiei va fi mai mic.

 

         Cum rezolvati aceasta problema? Prin organizarea bazelor de date.

 

a) pentru fiecare site, va exista o baza de date  separata, astfel veti scadea numarul de conexiuni  si veti putea administra mai usor backupurile(ex. cu mysqldump).

 

b) pentru fiecare tabela este de preferat sa se foloseasca cate un index, acesta va ajuta la cautarea mai rapida de parametrii, chiar daca la inceput tabela este mica si nu ii veti vedea rostul indexului, veti realiza avantajul acesteia din momentul in care inregistrarile se inmultesc.

 

c) in cazul selectiilor din tabela inlocuiti steluta * cu lista campurilor pe care doriti sa le utilizati. Astfel nu se mai incarca in memorie toate campurile din tabela, ci doar cele de care aveti nevoie, salvandu-se o  cantitate  deloc neglijabila de memorie.

 

Exemplu: folositi

 

 mysql > select id, utilizator, parola, data_inregistrare from users;

 

 in loc de

 

mysql > select * users;

 

d) folositi parmetrul NOLOCK pentru a minimiza riscul aparitiei coliziunilor intre citiri si update-urile bazei de date.

 

 “NOLOCK” este un nivel de tranzactie de izolare, care defineste modul in care datele sunt disponibile in timpul unei actualizari, sau cu alte cuvinte este o proprietate care defineste, la ce punct schimbarile facute de un update este utilizabil intr-o linie, tabela sau baza de date  pentru alte procese.

 

Exemplu:

 

 select id, utilizator, parola, data_inregistrare from users NOLOCK where utilizator > dlsit;

 

e) eliberati memoria imediat dupa ce s-au preluat valorile dorite, prin stergerea variabilei “free” si prin inchiderea conexiunii cu baza de date.

 

 Exemplu:

 

mysql_free_result($free) si mysql_close($free);

 

 

         Optimizarea un alt aspect extrem de important, indiferent de situatie, este un proces care presupune cunoasterea in detaliu a serverului si a serviciilor ce ruleaza pe acesta.

 

Un server de baze de date poate fi optimizat suficient daca luam in calcul cativa parametri:

 

a)  numarul tabelelor;

b)  numarul de inregistrari din tabela;

c)  numarul de query-uri intr-un interval de o secunda;

d)  numarul maxim de conexiuni;

e)  buffer-ul si cache-ul tabelelor;

f)   numarul de procesoare;

g)  memoria RAM pusa la dispozitie pe acel server;

 

         Pentru a edita configurarile default ale serverului MySQL se deschide fisierul /etc/my.cnf, acesta este primul pas spre optimizarea MySQL. Dupa ce am instalat serverul MySQL, fisierul /etc/my.cnf se creaza automat continand anumite valori.

 

        Performanta unui server de baze de date este strans legata de cache-uri si limitarile de memorie. Daca avem o aplicatie care ruleaza in continuu foarte multe query-uri diferite, query-urile vor fi rezolvate mult mai rapid daca serverul MySQL isi face un cache rezonabil pentru rezultat. Daca serverul nu are limitari de memorie, e posibil ca acesta sa ceara mai multa memorie decat e disponibila pe sistem, iar ca rezultat o parte din informatie va fi pusa pe partitia swap(free -m pentru a vedea cate memorie swap este ocupata) a serverului pentru ca sistemul de operare sa poata gestiona cantitati mari de informatie.

 

Pentru ca cele doua aspecte sa aiba valori optime va trebui sa modificam urmatoarele variabile:

 

          a) max_allowed_packet

 

- este marimea maxima care o poate avea un pachet - o celula a unui tabel.


        Daca serverul este strict pentru web, sau un server de hosting, foarte rar e necesar ca valoarea pentru max_allowed_packet sa fie mai mare de 4MB.

 

Optimizare MYSQL

 

 

          b) max_connections

 

- ca optimizare data de noi, aceasta variabila trebuie sa fie egala cu numarul maxim de conexiuni care le accepta serverul web(numarul de conexiuni prezentat de noi in imagine este doar informativ si nu este folosit de serverele noastre).

 

Optimizare MYSQL

 

 

         c) max_user_connections

 

- aceasta variabila impune o limita de conexiuni la nivel de utilizator, nu la nivel de server cum e in cazul max_connections. Este o setare utila pentru serverele web care folosesc mai multe conturi pentru conexiunea la baza de date. O valoare de 16 ar trebui sa fie suficienta pentru un site cu trafic peste medie.

 

Optimizare MYSQL

 

         d) key_buffer_size 


- este variabila care seteaza c ata memorie poate rezerva pentru stocarea indecsilor daca tabelele utilizate folosesc indecsi.

 

Cu cat zona este mai mare cu atat se pastreaza mai multi indecsi care fac cautarea informatiei in tabele mai rapida.

Ca regula generala, valoarea trebuie sa aiba intre 25% si 50% din memoria totala care o putem aloca serverului MySQL.

 

Optimizare MYSQL

 

 

         e) join_buffer_size

 

- este valoarea maxima care o poate lua o zona de memorie folosita pentru query-uri care nu folosesc indecsi.

 Limitarea acestui gen de query-uri este intotdeauna o idee buna. Valoarea maxima care o poate lua aceasta variabila este 4GB pe sistemele pe 64 de biti sau 2GB pe sistemele pe 32 de biti. In practica aceste valori sunt mult prea mari. In general query-urilor fara indecsi nu ar trebui sa li se permita acces la mai mult de 1MB decat in cazuri exceptionale.

 

 

Optimizare MYSQL

 

 

          f) query_cache_type


- creaza un cache pentru query-urile rulate.

Poate lua 3 valori: 0 pentru dezactivarea cacheului, 1 pentru activarea cacheului pentru toate query-urile in afara de cele care incep cu “select sql_no_cache” sau 2 pentru activarea cache-ului pentru orice tip de query.

Cea mai folosita este valoarea 1, care functioneaza fara niciun fel de probleme.

 

 

Optimizare MYSQL

 

 

        g) query_cache_limit


- seteaza o limita pentru fiecare rezultat al unui query care urmeaza sa fie salvat in baza de date. Query-urile al caror rezultat depaseste valoarea impusa nu vor fi salvate in cache.

Aceasta valoarea depinde de tipul aplicatiilor rulate. In general 128KB e suficient, dar poate varia in functie de modul in care e folosit serverul MySQL si de serviciile care il utilizeaza.

 

 

Optimizare MYSQL

 

 

        h) query_cache_size


- este zona rezervata exclusiv pentru salvarea query-urilor.

Toate queryurile care sunt mai mici de query_cache_limit vor fi salvate pana la ocuparea spatiului alocat, iar apoi vor fi pastrate doar cele al caror cache este accesat cel mai des.

 

 

Optimizare MYSQL

 

 

       i) tmp_table_size


- este limita pentru tabelele care urmeaza sa fie create in memorie

- ex. urmare a “group by“.

In general tabelele nu ar trebui sa consume mai mult de 16MB.

 

 

Optimizare MYSQL

 

 

         j) table_cache


- este valoarea totala a tabelelor care sunt pastrate in cache.

         De fiecare data cand sunt accesate informatiile dintr-o tabela a unei baze de date, aceasta va fi deschisa, iar handlerul salvat intr-un cache.
E util sa avem o zona alocata pentru aceste tabele care, daca raman in cache, nu vor fi redeschise in cazul urmatorului query si implicit consumul de CPU va fi mai mic. Valoarea default este 64, valoare care este buna pentru serverele cu putine site-uri gazduite sau pentru serverele folosite exclusiv pentru o singura aplicatie. Daca serverul MySQL serveste cereri pe un server web va trebui verificata variabila “opened_tables” care ne spune cate tabele sunt deschise in momentul respectiv.     

 Directiva table_cache va trebui setata cu o valoare apropiata de media de tabele deschise.

 

 

Optimizare MYSQL

 

         k) thread_cache


- specifica numarul maxim de fire de executie care pot fi salvate in cache. Ca in cazul tabelelor, salvarea in cache a handlerului unui fir de executie scuteste serverul MySQL de crearea unui thread nou si implicit scade numarul de cicluri CPU necesare.

 Ca regula, thread_cache are ca valoare de doua ori numarul de procesoare sau core-uri disponibile in sistem.

Intr-un sistem QuadCore de exemplu, valoarea pentru thread_cache ar fi 16 (2CPU * 4 core-uri * 2).

 

Optimizare MYSQL

 

       Optimizarea unui server MySQL nu se opreste, binenteles mai sunt multe alte variabile si setari care duc la o buna functionare a serviciului mysql, dar acestea credem noi, ca sunt cele mai importante, fiecare optimizare depinzand de utilizarea mysql pentru fiecare utilizator in parte.

 

www.dlsit.ro   >> design - linux - servicii - it.ro Pentru orice problema tehnica contactati-ne