High performance MySQL (8) Optimizing Server configuration: Security and stability

In the previous section we've covered some options, and there are some remaining important options, and let's go on to explain:

First, the basic configuration

Tmp_table_size and Max_heap_table_size

These 2 settings control how much memory can be used by the memory temp table using the memory engine. If the Hermit memory temp table is larger than these 2 settings, it will be converted to a disk temp table. The Hermit temp table is a table that is not created by itself but is created by the server because of the intermediate results of saving the query in execution.

Temporary tables are best to stay in memory, but if they are large, it is better to actually use the disk, or you may overflow memory.

You can use Show status to see how often temporary tables are used.

Max_connections

The role of this setting to ensure that the server is not overwhelmed by the surge of application links.

Thread_cache_size

Thread cache size, you can observe the threads_connected state variable to find the maximum and minimum values in general. You can also observe threads_created, if the value is large or constant, you may need to increase the thread_cache_size. Check the threads_cached to see how many threads are already in the cache.

Table_cache_size

This should be set large enough to avoid the need to reopen and reparse the table definition. You can examine the variable by observing the change in the value of the open_tables. If you see a large change in opened_tables per second, the Table_cache value may not be large enough.