Administrare server open source

Platforma de hosting cu software liber, gratuit, open source.

Comentariile sunt închise pentru Verificarea starii si a parametrilor de functionare MySQL

Dupa cum am mentionat in articolul anterior, Optimizarea serverului MySQL, o optimizare rapida este usor de facut prin cateva calcule legate de memoria disponibila si numarul de procesoare. Pentru o optimizare mai buna este necesar sa urmarim periodic parametri de functionare a serverului MySQL si sa facem ajustarile necesare.

Pentru a prelua informatiile necesare pentru o optimizare buna folosim doua query-uri MySQL: „SHOW VARIABLES;” si „SHOW STATUS;„. Cu „SHOW VARIABLES;” putem verifica parametri de functionare a serverului MySQL. Cu „SHOW STATUS;” putem verifica starea serverului. Folosindu-ne de valorile preluate putem lua decizii in ceea ce priveste configurarea serverului pentru ca acesta sa ofere un randament mai bun.

Cache-ul queryurilor

Un exemplu de utilizare a celor doua queryuri ar fi pentru setarea corecta a valorilor pentru cacheul queryurilor efectuate. Stim ca parametri pentru cache-ul queryurilor incep cu „query_cache_„. Putem prelua o lista completa a parametrilor de functionare folosind comanda „SHOW VARIABLES LIKE „query_cache_%;„, comanda care va filtra parametri de functionare si va returna doar variabilele al caror nume incepe cu „query_cache_„:

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 65536    | 
| query_cache_min_res_unit     | 4096     | 
| query_cache_size             | 67108864 | 
| query_cache_type             | ON       | 
| query_cache_wlock_invalidate | OFF      | 
+------------------------------+----------+
5 rows in set (0.00 sec)
 
mysql>

Valorile returnate ne arata ca serverul este configurat sa foloseasca o zona de 64MB (67108864 bytes) pentru cacheul queryurilor iar marimea maxima a unui query salvat este de 65KB (65536 bytes). Verificand starea serverului putem vedea daca valorile setate trebuie sa fie marite sau nu. In starea serverului vom cauta variabilele al caror nume incepe cu „Qcache_„:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 8848     | 
| Qcache_free_memory      | 31083632 | 
| Qcache_hits             | 39588329 | 
| Qcache_inserts          | 6337025  | 
| Qcache_lowmem_prunes    | 1722259  | 
| Qcache_not_cached       | 384828   | 
| Qcache_queries_in_cache | 24625    | 
| Qcache_total_blocks     | 58642    | 
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql>

Din rezultatul returnat putem citi valoarea „Qcache_free_memory” care ne spune ca aproximativ 30MB din cei 64MB sunt liberi. De asemenea putem citi valoarea „Qcache_not_cached” care ne spune ca 384828 de queryuri nu au fost salvate in cache. Motivul pentru care queryurile nu au fost salvate este valoarea variabilei „query_cache_limit” care ii spune serverului MySQL sa nu salveze queryuri mai mari de 64KB. Pentru ca serverul MySQL sa ofere un randament mai bun ar trebui sa crestem in fisierul /etc/my.cnf valoarea pentru „query_cache_limit” astfel incat si queryurile mai mari sa fie salvate ceea ce va duce la un consum mai mic de resurse pentru ca practic vor fi rulate mai putine queryuri iar zona de 64MB alocata cache-ului nu va fi alocata si neutilizata.

Cacheul tabelelor

Pentru ca serverul MySQL sa deschida o tabela pe care urmeaza sa fie rulat un query, acesta are nevoie de un anumit consum de resurse (CPU si RAM), consum care creste in functie de numarul de cereri concurente. Acest consum poate fi evitat daca tabela este deschisa o singura data pentru toate queryurile iar apoi handlerul acesteia e salvata intr-un cache. Acest cache este controlat de variabila „table_cache” care poate fi modificata din fisierul /etc/my.cnf. Pentru a citi valoarea variabilei vom folosi comanda „SHOW VARIABLES LIKE ‘table_cache’;„:

mysql> SHOW VARIABLES LIKE 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 1536  | 
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

Observam ca serverul MySQL este setat sa salveze pana la 1536 de handlere in cache. Pentru a aprecia corect numarul de handlere care sunt necesare vom verifica numarul de tabele care sunt salvate in cache folosind queryul „SHOW STATUS LIKE ‘Open_tables’;„, query care ne spune exact numarul de handlere salvate:

mysql> SHOW STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 915   | 
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

Din rezultat putem deduce ca numarul de handlere salvate este de 915, ceea ce inseamna aproximativ 60% din numarul de handlere care pot fi salvate. In cazul de fata este vorba de valori mici, deci diferenta de 40% nu se simte in consumul de resurse, dar in cazul unui server ocupat a carui valoare pentru „Open_tables” depaseste cateva mii ar fi bine sa setam in fisierul /etc/my.cnf pentru parametrul „table_cache” o valoare cu aproximativ 10% mai mare decat cea citita. Deci, in cazul de fata, o setare ideala pentru parametrul „table_cache” ar fi ~1000.

Fire de executie

Pentru fiecare conexiune la baza de date creata de un utilizator, serverul MySQL deschide un fir de executie (thread). Pentru siteurile mari cu zeci de conexiuni concurente consumul de resurse poate ajunge la valori foarte mari daca numarul de threaduri pastrate in cache nu este optimizat. In general pe un sistem cu un singur procesor e recomandat sa nu se creeze mai mult de doua threaduri pe secunda. Numarul recomandat creste direct proportional cu numarul de procesoare sau core-uri. Avand in vedere ca un server decent are in jur de 4 core-uri active putem considera ca numarul de threaduri care pot fi deschise fara probleme este de 8. In cazul unui server mare, cele 8 threaduri nu sunt suficiente motiv pentru care va trebui sa fie crescut numarul de threaduri care pot fi salvate in cache pentru a evita deschiderea de noi threaduri si implicit supraincarcarea serverului.

Folosindu-ne de comanda „SHOW VARIABLES LIKE ‘thread_cache_size’;” putem citi numarul de threaduri care pot fi salvate in cache:

mysql> SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     | 
+-------------------+-------+
1 row in set (0.00 sec)
 
mysql>

In cazul de fata „thread_cache_size” are valoarea 8, deci se pot salva pana la 8 threaduri in cache. Ca sa vedem daca aceasta valoare e suficienta trebuie sa verificam starea threadurilor active. O putem face folosind queryul „SHOW STATUS LIKE ‘Threads_%’;„:

mysql> SHOW STATUS LIKE 'Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 7     | 
| Threads_connected | 1     | 
| Threads_created   | 11706 | 
| Threads_running   | 1     | 
+-------------------+-------+
4 rows in set (0.00 sec)
 
mysql>

Threads_cached” ne spune ca 7 threaduri se gasesc in momentul de fata in cache si avem un singur thread utilizat in momentul de fata, dupa cum ne arata „Threads_running„. Un thread pe secunda este mai putin decat poate suporta serverul. Am putea considera ca in cazul de fata nu este necesar cache-ul threadurilor, dar totusi din precautie e bine sa tinem un numar decent de threaduri in cache pentru e evita problemele care pot sa apara in cazul unui trafic marit. Avand in vedere situatia putem micsora numarul de threaduri din cache de la 8 la 4 fara sa punem in pericol stabilitatea aplicatiilor care ruleaza pe server.

Folosindu-ne de exemplele de mai sus putem verifica parametri de functionare a serverului MySQL si putem ajuta valorile impuse in fisierul de configurare, /etc/my.conf, pentru ca serverul sa dea randament maxim. Pentru o lista completa de variabile care pot fi setate in /etc/my.conf putem rula comanda „SHOW VARIABLES;” fara niciun filtru asa cum putem rula „SHOW STATUS;” pentru lista completa a parametrilor de functionare. Cele doua comenzi nefiltrate sunt un punct bun de pornire pentru studiul optimizarii serverului MySQL. Variabilele au nume sugestive si ne putem da seama usor daca ne vor ajuta sau nu in cazul serverului care urmeaza sa il optimizam.

MySQL

Comments are closed.