Exposing Innodb Internals via system variables: part 4, concurrency

by Pythian Marketing on Sep 2, 2016 12:00:00 AM

Introduction

This is part four of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to explore the mechanics that impact CPU resourcing and how InnoDB handles concurrent threads. You’ll notice that a lot of the variables covered in section relate to features that are now disabled but were a lot more prevalent in previous versions of MySQL, particularly those that were released at a time where system context switching had a greater cost than it does today. These variables are still worth discussing as you may run into older systems that utilize these mechanics, and there are even modern systems I’ve worked with that have implemented these features to tune performance to its highest potential. Just like previous sections, I would like to emphasize something that was written in part one of this blog post series. "I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would like to advise against reading this guide and trying to fine tune all of the available InnoDB variables. System variable tuning is an exercise in diminishing returns, the most benefit you’ll get out of tuning your MySQL server will occur within the first 15 minutes of configuration. In order to help prevent excess tuning I will provide a list of variables out of each section that should be set when configuring a new system."

Variables

With that, let’s have a look at InnoDB system variables that impact the mechanics of InnoDB concurrency. innodb_thread_concurrency Definition: Innodb uses operating system level threads to handle processing of requests. This variable specifies the number of threads that are available for InnoDB to use when interacting with the operating system and thus limits the number of MySQL threads that have access to the InnoDB engine at any given time. This was put in place in order to limit the number of threads so to try and reduce the number of system context switches within MySQL . However, on newer hardware context switching isn’t as expensive, so the default is to not limit the amount of available threads. Default: 0 (threads) (no limit on generated threads) Tuning notes: With modern systems and MySQL versions it’s considered best to leave this variable at it’s default value, but you may want to consider tuning it if you are hosting MySQL on server that is also hosting other processes. Recommendations for tuning this variables can be found in the reference guide. innodb_thread_sleep_delay Definition: If the number of executing threads matches the value of innodb_thread_concurrency (assuming it’s value is not 0), a new operation/query requesting a thread will be denied access to the InnoDB kernel, and it will then wait a for a number of microseconds equal to the value of this variable before trying one more time to complete its storage engine request. If it’s still unable to do so after that single retry then it’s placed into the queue of operations waiting for a thread, initiating a context switch. By doing one check before going into the queue it reduces the chance of system level context switching. Default: 10000 (microseconds) (0.01 seconds) Tuning notes: You should only tune this variable if innodb_thread_concurrency has a value other than 0. This variable requires experimentation to determine the appropriate value. Consider testing with a full stack benchmark, or you can allow MySQL to adjust this value automatically by setting a value other than 0 in innodb_adaptive_max_sleep_delay (see below). innodb_adaptive_max_sleep_delay Definition: When this value is set to a value other than 0, it enables the adaptive adjustment of innodb_thread_sleep_delay up to the maximum value of this variable. Default: 150000 (microseconds) (0.15 seconds) Tuning notes: You should only tune this variable if innodb_thread_concurrency has a value other than 0. If you have a system with higher concurrency, it’s best to leave this option enabled. If you typically have less than 16 threads running at any given time, and if you have innodb_thread_concurrency set to a value other than 0, it may be worth it to consider disabling this feature and experimenting to find a static value to store in innodb_thread_sleep_delay. innodb_concurrency_tickets Definition: When a SQL statement starts and has established an InnoDB thread (IE: once it’s moved past any thread concurrency checks, see above) it enters InnoDB and is given a number of tickets that represents the number of InnoDB row operations it can request while ignoring InnoDB thread concurrency before it’s evicted and has to try and re-establish a thread. This is to ensure that one large process doesn’t hog a thread, preventing other smaller queries from being able to operate. Default: 500 (tickets) Tuning notes: You only have tune this variable if innodb_thread_concurrency has a value other than 0. Setting this variable is going to depend largely on your workload, if you have occasional large queries and want to be able to place higher emphasis on smaller queries, set the tickets lower. If you want to allow larger queries to run without having to queue up for a thread as much, set it higher. You can see this blog post by Ryan Lowe on tuning concurrency tickets for more info. Associated Metrics: In the SHOW ENGINE INNODB STATUS output, in transactions section, certain transactions may have the output ' thread declared inside InnoDB X'. In this case, X represents the number of InnoDB concurrency tickets that the thread has left to spend before it’s evicted. innodb_sync_spin_loops Definition: Once a thread has been established inside of InnoDB and has concurrency tickets to spend (if applicable), it’s possible that the thread can hit a mutex or exclusive lock while attempting to gain access to data in the storage engine. These mutexes can be seen in the SHOW ENGINE INNODB STATUS output. When a mutex is encountered, InnoDB is going to check to see if the mutex has cleared N times where N is the value of this variable before the thread context switches. The difference between this and how thread concurrency is handled (see above) is that this is trying to get around mutex contention within the InnoDB operation when the thread is in InnoDB while thread concurrency is dealing with threads contending to gain access to the InnoDB kernel in the first place. Default: 30 (spins) Tuning notes: The point of this variable is to reduce the amount of context switching within InnoDB and gives threads ample time to wait within their CPU time slice before the context switch occurs. However if you have a lot of threads that are context switching, increasing the spin loops may just add more wait time before the inevitable context switch occurs and this would decrease efficiency, so decreasing would be the better option. In contrast, you may have a situation where threads are context switching because they just didn’t have enough time to wait before the context switch occurred, in this case increasing spin loops would be beneficial. Tuning this variable requires experimentation. Associated Metrics: In the SHOW ENGINE INNODB STATUS output, in the semaphores section, you’ll see a line for mutex spin waits. Example: Mutex spin waits (the number of threads that had to spin), rounds (the total number of spins that have occurred on the system), OS waits (the number of threads that waited too long and resulted in a context switch). Also, you’ll see line showing the average number of spin rounds per wait at the very end of the semaphores section. innodb_spin_wait_delay Definition: This is the delay that will occur between each spin when a mutex or rw-lock is encountered by an InnoDB thread. See innodb_sync_spin_loops above. The length of the delay varies based on the compiler that was used to compile MySQL as well as the processor that’s receiving the request for wait, as the amount of time that each processor interprets as ‘wait’ can be different. Default: 6 (processor delay requests) Tuning notes: The cost of a processor delay request is less than that of a mutex check that’s performed each time a spin occurs, this is especially true if you have a multi CPU / core system where you frequently may have to check the cache of another core where the processor cache isn’t shared. If you’re on a single processor with multiple cores, you may want to lean on the shared cache and reduce this variables, if you have multiple physical processor chips, you may want to increase this. Associated Metrics: See above for the metrics associated with innodb_sync_spin_loops. innodb_commit_concurrency Definition: This is the number of threads that can simultaneously be in a commit phase inside of InnoDB. The purpose of this variable is to limit contention on the log buffer, the redo log, and to put a throttle on I/O bound redo log flushing. Default: 0 (simultaneously allowed transaction commits) (unlimited) Tuning notes: If you aren’t in a situation where you have to adjust the innodb_thread_concurrency variable away from it’s default value, you’re likely not going to have to adjust this variable either. It should be noted that while this variable is dynamic, you cannot change the value from zero to a non-zero value or vice versa without restarting MySQL. Associated Metrics: Performance schema: wait/synch/mutex/innodb/log_flush_order_mutex, wait/synch/mutex/innodb/log_sys_mutex, wait/io/file/innodb/innodb_log_file innodb_autoinc_lock_mode Definition: When new records are inserted into a table with an auto_increment column, a lock is put in place to ensure that the order of inserts is repeatable when being played back via the binary log, or through replication. For information on this locking mechanism, please see the MySQL reference guide. Acceptable values are 0, 1, and 2 Default: 1 (consecutive lock mode) Tuning notes: If you have a single server with no slaves where binary logging is not required for point in time recovery, set the lock mode to 2. This will ensure that no insert statements of any kind will use the auto increment table level lock. It should be noted that if you do set this value to 2, the lack of locking can cause gaps in the auto increment value, and if your auto increment column data type is small (tinyint, smallint, etc) it can have an impact on the number of rows you can store in your table. If you do change this, it’s crucial you change this value back to 1 before enabling binary logging. I would not recommend ever setting this variable to 0. Associated Metrics: Performance schema: wait/synch/mutex/innodb/autoinc_mutex and wait/synch/mutex/partition/Partition_share::auto_inc_mutex

Closing Thoughts

Out of the variables listed above, I would recommend that you tune none of them when configuring a new MySQL instance. You’re likely not going to have a good performance baseline to compare to until you’ve had your system up and running for a while, and even then you may not see a noticeable performance gain from adjusting these variables. You will likely see greater performance gains by adjusting variables in other areas. Stay tuned for the final installment of this blog series where we will cover the remaining InnoDB variables, specifically those that related to data consistency and statistics handling.

Topics: MySQL Innodb Technical Track Mysql Configuration Innodb File Structure

No Comments Yet

Let us know what you think

Share this