<h4>Tuning Primer Kurulumu ve Kullanımı</h4>
<p>Tuning Primer bir Mysql Tuner Alternatifi , aşağıdaki komutları sırası ile dosyasını indirip çalıştırabilirsiniz. Mysql Tuner kadar olmasada yeterli bilgiler mevcut ve kullanmaya değer.</p>
<p>[root@ayhanarda ~]# <strong>cd /usr/local/bin</strong></p>
<p>[root@ayhanarda bin]# <strong>wget https://launchpadlibrarian.net/78745738/tuning-primer.sh</strong></p>
<p>Şimdi sıra çalıştırmaya geldi.</p>
<p>[root@ayhanarda bin]# <strong>tuning-primer.sh</strong></p>
<p>Çalıştırıldığında mysql in conf u ile elde ettiği değerleri en iyi optimizasyon için karşılaştırarak size öneriler sunuyor.</p>
<p>Mysql in versiyonundan ne kadar süredir servis verdiğine , açık olduğu son süre içinde kaç query ye cevap verdiğine, anlık bağlantı sayısına , slow query olup olmadığına , cache size ınızın yeterli olup olmadığına , max connections değerinizin anlık değerinizi karşılayıp karşılamadığına , memory kullanımınıza , join lerinize , temp alanınıza , tablo lock sayınıza kadar ölçüp biçip size öneriler sunuyor. Örneğin ben çaqlıştırdımda çıktısı aşağıdaki gibi oldu.</p>
<p>Ancak öncesinde 48 saat kadar mysql servisinizin açık olması daha doğru sonuçlar almanızı sağlayacaktır.</p>
<p>MySQL Version 10.0.34-MariaDB x86_64</p>
<p>Uptime = 1 days 18 hrs 21 min 54 sec<br />
Avg. qps = 1402<br />
Total Questions = 213875408<br />
Threads Connected = 34</p>
<p>SLOW QUERIES<br />
The slow query log is NOT enabled.<br />
Current long_query_time = 10.000000 sec.<br />
You have 8 out of 213875496 that take longer than 10.000000 sec. to complete<br />
Your long_query_time seems to be fine</p>
<p>WORKER THREADS<br />
Current thread_cache_size = 200<br />
Current threads_cached = 103<br />
Current threads_per_sec = 0<br />
Historic threads_per_sec = 0<br />
Your thread_cache_size is fine</p>
<p>MAX CONNECTIONS<br />
Current max_connections = 200<br />
Current threads_connected = 35<br />
Historic max_used_connections = 137<br />
The number of used connections is 68% of the configured maximum.<br />
Your max_connections variable seems to be fine.</p>
<p>No InnoDB Support Enabled!</p>
<p>MEMORY USAGE<br />
Max Memory Ever Allocated : 13.54 G<br />
Configured Max Per-thread Buffers : 8.38 G<br />
Configured Max Global Buffers : 7.79 G<br />
Configured Max Memory Limit : 16.18 G<br />
Physical Memory : 23.36 G<br />
Max memory limit seem to be within acceptable norms</p>
<p>KEY BUFFER<br />
Current MyISAM index space = 1.53 G<br />
Current key_buffer_size = 1.00 G<br />
Key cache miss rate is 1 : 99348<br />
Key buffer free ratio = 54 %<br />
Your key_buffer_size seems to be too high.<br />
Perhaps you can use these resources elsewhere</p>
<p>QUERY CACHE<br />
Query cache is enabled<br />
Current query_cache_size = 800 M<br />
Current query_cache_used = 800 M<br />
Current query_cache_limit = 100 M<br />
Current Query cache Memory fill ratio = 100.00 %<br />
Current query_cache_min_res_unit = 4 K<br />
MySQL won&#8217;t cache query results that are larger than query_cache_limit in size</p>
<p>SORT OPERATIONS<br />
Current sort_buffer_size = 1 M<br />
Current read_rnd_buffer_size = 768 K<br />
Sort buffer seems to be fine</p>
<p>JOINS<br />
/usr/local/bin/tuning-primer.sh: line 402: export: `2097152&#8242;: not a valid identifier<br />
Current join_buffer_size = 40.00 M<br />
You have had 576657 queries where a join could not use an index properly<br />
You have had 1303 joins without keys that check for key usage after each row<br />
join_buffer_size >;= 4 M<br />
This is not advised<br />
You should enable &#8220;log-queries-not-using-indexes&#8221;<br />
Then look for non indexed joins in the slow query log.</p>
<p>OPEN FILES LIMIT<br />
Current open_files_limit = 138451 files<br />
The open_files_limit should typically be set to at least 2x-3x<br />
that of table_cache if you have heavy MyISAM usage.<br />
Your open_files_limit value seems to be fine</p>
<p>TABLE CACHE<br />
Current table_open_cache = 69120 tables<br />
Current table_definition_cache = 65960 tables<br />
You have a total of 59378 tables<br />
You have 65487 open tables.<br />
The table_cache value seems to be fine</p>
<p>TEMP TABLES<br />
Current max_heap_table_size = 256 M<br />
Current tmp_table_size = 1.46 G<br />
Of 17855233 temp tables, 32% were created on disk<br />
Effective in-memory tmp_table_size is limited to max_heap_table_size.<br />
Perhaps you should increase your tmp_table_size and/or max_heap_table_size<br />
to reduce the number of disk-based temporary tables<br />
Note! BLOB and TEXT columns are not allow in memory tables.<br />
If you are using these columns raising these values might not impact your<br />
ratio of on disk temp tables.</p>
<p>TABLE SCANS<br />
Current read_buffer_size = 1 M<br />
Current table scan ratio = 309 : 1<br />
read_buffer_size seems to be fine</p>
<p>TABLE LOCKING<br />
Current Lock Wait ratio = 1 : 1582<br />
You may benefit from selective use of InnoDB.<br />
If you have long running SELECT&#8217;s against MyISAM tables and perform<br />
frequent updates consider setting &#8216;low_priority_updates=1&#8217;</p>
<p>Ayhan ARDA</p>