Hello, a mysql tunning primer script reproted me this: JOINS Current join_buffer_size = 4.00 M You have had 67631 queries where a join could not use an index properly You have had 102327 joins without keys that check for key usage after each row join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. Code (markup): It appears this script dont advice join buffer size to be more than 4MB, i want to ask what you advice me to do about this report? thx
Today, the result is like this: JOINS Current join_buffer_size = 32.00 M You have had 10941 queries where a join could not use an index properly You have had 20300 joins without keys that check for key usage after each row join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. Code (markup): TEMP TABLES Current max_heap_table_size = 1.00 G Current tmp_table_size = 1.00 G Of 347213 temp tables, 35% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Code (markup):
Todays result: JOINS Current join_buffer_size = 64.00 M You have had 182274 queries where a join could not use an index properly You have had 276542 joins without keys that check for key usage after each row join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. Code (markup): TEMP TABLES Current max_heap_table_size = 2.00 G Current tmp_table_size = 2.00 G Of 4797689 temp tables, 35% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Code (markup): So it appears that thr ratio stays same for both joins and tmp table. no matter how i increased values... I should probably not try increase more but decrease to its low values?
Are you having issues with load or are just tweaking things as much as possible? A hardware change as simple as adding a SSD drive for MySQL only can cause HUGE performance increases, especially if you have a lot of tmp table writes. Setting join_buffer_size higher than required globally will slow down most queries that sort. It is better to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where higher settings will slow down memory allocation. Keep trying what works best for you. Since there is no gain from setting the buffer higher than required for each row returned, and all joins require at least the minimum, the large global value is usually not preferred.