MySQL Server contains a wide variety of variables that can be modified according to different uses or to enhance performance. However, despite their detailed documentation, there is plenty of confusion regarding which of these variables are suitable only for storage engines and which ones are utilized on SQL layer, and are applicable for all storage engines as well.
An important factor to consider during MySQL SQL performance tuning is the storage engine in use. Keeping that in mind, this blog features a list of variables that are sometimes mistaken with similar variables.
MySQL Database and SQL: List of Confusing Variables
Let’s take a look at some variables that may seem confusing to users in terms of their applications (whether they are storage engine specific or used with the SQL layer) –
- Read_buffer_size/read_rnd_buffer_size – These variables are used in certain tables for full table scanning and for viewing rows in a sorted manner respectively. However, they are not compatible with all storage engines.
- Sort_buffer_size – This buffer is applied when the user needs the result set to be sorted, and is used on the SQL Layer, so it’s applicable for all storage engines and may even be helpful for performance tuning in SQL MySQL.
- Bulk_insert_buffer_size – This variable is only applicable for MyISAM tables for optimizing inserts in a large quantity or with numerous values. It is quite helpful when the user needs to insert hundreds or thousands of values in a single insert statement, and there are several such statements.
- Join_buffer_size – This buffer is used for specific cases, such as joins that do not include indexes. It can be utilized for all the storage engines.
- Max_write_lock_count – A variable that is applicable across memory and MyISAM tables, it is suitable for table locks and prevents read starvation in case of numerous table writes.
- Key_buffer_size – This is applied in the case of Index Blocks solely for MyISAM tables or, in rare cases, restricted to a range of 4-32MB for temporary tables.
- Delayed_insert_limit/delayed_insert_timeout, delayed_queue_size – These are used for inserting configuration and are not exactly dependent on any storage engine, yet it lacks support from some of them, such as InnoDB.
- Delay_key_write – This is used to improve MySQL database performance in MyISAM tables by extending the time for index updates. However, this variable may lead to table corruption, if a crash occurs.
- Low_priority_updates – This provides higher priority to select queries by putting the updates on low priority. It can be enabled when LOCK TABLES are in use, which is why it is storage engine-specific.
- Large_pages – This variable enables the utilization of large pages if big global areas need to be allocated, and is also storage engine-specific, such as Innodb and MyISAM. However, it can be used by certain SQL level components like Query Cache in MySQL database and SQL.
- Key_cache_age_threshold/key_cache_block_size/key_cache_division_limit Key Cache/Key Buffer – The Key_Buffer variable is only applicable to MyISAM. It is used for making changes to algorithm configuration.
- Ft_boolean_syntax/ft_max_word_len/ft_min_word_len/ft_query_expansion_limit/ft_stopword_file – These are search variables related to FullTEXT search. Again, these are only useful for limited storage engines for MySQL database and SQL.
- Flush/flush_time – Initially designed for MyISAM tables, this variable impacts all the tables once the query is over or for certain flush_time set intervals.
- Preload_buffer_size – Another buffer that is only useful for MyISAM tables in key preloading.
- Timed_mutexes – This variable has been designed to use on all storage engines to show the mutex status.
- Tmp_table_size – It is used to specify the maximum limit in size for implicit temporary tables. These are tables that are created automatically at the time of each execution.
These were some variables whose application is often confused by some users. Knowing more about these may turn out to be useful from the point of view of MySQL SQL performance tuning.
On a related topic, if you are in search of tools for improving MySQL database performance that don’t require expert knowledge about the database, then Tosska’s tools for database tuning can be a great fit for your organization. Our tools have been designed with cutting edge AI technology to make query tuning as easy as pointing and clicking.
So, make sure to explore all of our tools and find the right variant for your requirements, and if you aren’t sure about anything, just get in touch with our experts and have your SQL performance tuning related queries resolved today!