Pythontr

husonet | Tarih: 01.07.2015

MariaDB, MySQL thread_cache_size hesaplama

MariaDB ve MySQL için thread_cache_size hesaplaması nasıl yapılır?

Öncelikle eş zamanlı olarak Connections, Threads_created, ve Max_used_connections verilerinin son haline ulaşalım.


Veritabanına bağlandıktan sonra aşağıdaki queryleri çalıştırarak bu sonuçlara ulaşabiliriz.


MariaDB > SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 1750 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB > SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 19 |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB > SHOW GLOBAL STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 19 |
+----------------------+-------+

{reklam}
Dikkat

Threads_created / Connections < 0.01 ( eğer sonuc 0.01 den küçük değilse thread_cache_size arttırmalısınız)
Ayrıca thread_cache_size > Max_used_connections dan büyük olmalıdır.


Thread cache hit oranı = 100 – ((Threads_created / Connections) * 100)

Bağlantı Formülleri:

Kullanılan bağlantıların yüzdesi: processlist / max_connections


MariaDB > select (( pl.connections / gv.max_connections ) * 100) as percentage_used_connections from ( select count(*) as connections from information_schema.processlist ) as pl, ( select VARIABLE_VALUE as max_connections from information_schema.global_variables where variable_name = 'MAX_CONNECTIONS' ) as gv;
+-----------------------------+
| percentage_used_connections |
+-----------------------------+
| 13 |
+-----------------------------+
1 row in set (0.00 sec)