For the first time since the beginning of my long long career, I decided today to use an advice tool to check my MySQL configuration.

I decided to launch pt-variable-advisor and mysqltuner V2 on one of my MySQL server.
Here are the result outputs :

Then I ask myself what to do with these results, just raw results without much explanations, these tools are supposed to be done for beginners…

Just one example from mysqltuner : Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size
If I set the size of my temporary tables at 16Mb, what that’s means, I have to increase to 32Mb, 64Mb or more ?
…

There have been a few posts by Sheeri and Baron today on the MySQL sort_buffer_size variable. I wanted to add some more information about this buffer, what is impacted when it is changed, and what to do about it?

The first thing you need to know is the sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread. I’ve seen clients that set this assuming it’s a global buffer Don’t Assume – Per Session Buffers.

Second, internally in the OS usage independently of MySQL, there is a threshold > 256K. From Monty Taylor “if buffer is set to over 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but …

In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”

Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.

This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral part against the “Battle against any guess.” Baron’s recommendation to leave …

I perpetually see something like the following:

My server load is high and my queries are slow and my server crashes. Can you help me tune my server? Here is some information.

[random sample of SHOW GLOBAL STATUS, like the query cache counters]

my.cnf:

[mysqld]
key_buffer_size=1500M
query_cache_size= 64M
max_connections = 256
key_buffer = 8M
sort_buffer_size = 100M
read_buffer_size = 8M
delay_key_write = ALL

There are many problems in this my.cnf file, but the sort_buffer_size is a glaring one that identifies the user as someone who should not be playing with live ammunition. Therefore, I have developed an advanced process for tuning sort_buffer_size, which you can follow to get amazing performance improvements. It’s magical.

1. How expert are you?
   • I know that there is a sort buffer, and that it is related to sort_merge_passes. When …