Fundamentals of MySQL concurrency and its Behavior – Part 1

Concurrency is one of the key features of Innodb and an interesting feature to understand to tune database systems. As per definition the concurrency is nothing but execution of multiple instructions in a sequence at the same time, and there are some commonly known topics around this concept such as parallel processing / multi-threading.

All Operating system are designed and programed to allow parallel processing. All the processes/threads always communicate with each other through the shared memory or message passing. As they share same underlying resources for attaining concurrency we encounter problems like deadlocks and resources contention.

In this blog we would cover how OS kernel handles concurrent processes using CPUs and how mysql inherits it within its storage engines. We would be covering some basics around these key concepts.

FAQ – concurrency

• Concurrency behavior , benefits , overhead and Issues
• How OS handles concurrency ?
• How mysql handles the concurrency and it behavior on Innodb ?
• How can it be tuned for my environment ?

Concurrency behavior

One of the expected behavior of concurrency is we cannot predict how much time each process is going to take. Only what the system would know is the process and its state and whether it needs to be send to queue and process again if it’s not complete.

• The activities that will be handled by each process
• How interrupts are handled by each operating system
• Scheduling policies supported by Operating system

Benefits of Concurrency

• Multi-processing
• Improved resource management
• Improved response time
• Improved Throughput

Overhead of Concurrency

• System overheads and complexities due to switching between processes.
• Too much concurrency is an open invitation for performance degradation

Issues due to Concurrency

• Simple processes can takes more time
• Deadlock
• Critical processes can get blocked by non critical processes
• Resource contention

How OS Handles concurrency ? ( Context Switching )

Concurrency is handled by CPU through a event called context switch in which CPU switches processing between processes or threads. We might be working with servers with 4 core , 8 core etc. servers/instances.

Context switch performs the following activities with regard to processes

• Suspend the progress of one process and storing its the CPU’s state or context for that process in the memory,
• Retrieving the context of the next process from memory
• Resume the suspended process and proceed with 1-3 step continuously till there is no process left for the CPU to process.

How mysql handles the concurrency and it behavior on Innodb ?

MySQL handles concurrency in a similar to how OS handles it on high level. It throttles the number of concurrent threads to the database through a variable innodb_thread_concurrency and how long it can be active before it goes for concurrency check is based on innodb_concurrency_tickets . By default many blogs and mysql documentation recommends to have the innodb_thread_concurrency set to 0 which means unlimited concurrent transactions however this can be a very wild setting on a very active server and could take increase the workload/load on the system in no time during peak traffic/usage.

What are the parameters that determine the behavior of Innodb ?

innodb_thread_concurrency  – This is the limit of number of concurrent threads that InnoDB can open concurrently. Best round number to set for this is between 1 X (Number of CPUs) to (2 X Number of CPUs).

innodb_concurrency_tickets – The number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency – The number of concurrent transactions that can be committed. Setting this to default 0 allows any number of transactions to committed simultaneously.

Internal Functionality flow

• innodb_thread_concurrency  determines the number of threads that can enter InnoDB concurrently.
• Any thread that comes after innodb_thread_concurrency limit is reached is placed in a queue when it tries to enter InnoDB.
• Now the thread that already entered in the innodb is given a number of “tickets” equal to the value of innodb_concurrency_tickets and the thread can enter and leave InnoDB freely until it has used up all its tickets.
• After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB.
• innodb_commit_concurrency is the number of threads that can commit at the same time.
• As we are already limiting the number of transactions that can enter innodb concurrently through innodb_thread_concurrency we don’t have to limit on how many transactions can be committed using innodb_commit_concurrency.

How are these innodb_concurrency_tickets used ?

• INSERT w/PRIMARY KEY defined: Number of rows inserted – 1
• INSERT w/FOREIGN KEY constraint: Number of rows inserted – 1
• SELECT: 1 ticket per row returned
• UPDATE: 1 ticket per row examined + 1 ticket per row updated
• DELETE: 1 ticket per row examined + 1 ticket per row deleted
• ALTER: (2 * rows in the table) – 1

Reference article – https://www.percona.com/blog/2010/05/24/tuning-innodb-concurrency-tickets/

Tuning InnoDB Concurrency for my environment ?

Lets try to understand how it works on a ideal situation with an example so its easy for us to answer this question.

Example Scenario

1. Innodb_thread_concurrency = 4 means only 4 concurrent transactions are allowed by innodb at give time
2. Those 4 threads which entered innodb would get 5000 records
3. In the above example we have 2 transactions one with accessing 10000 records , second transaction accessing 1000 records etc.
4. If you take transaction on table2 even a full table scan only scans 1000 records which is within the limit of 5000 innodb tickets so it can complete its processing during the first time it would enter innodb
5. now the transaction on table1 which has 10000 records with a max tickets of 5000 records and based on how many records any particular Transaction uses it would leave and enter innodb [ Update would take 4 times with 5000 tickets to examine and update however select would use only 1 record per row is being examined ]
6. On a database server if you see queries waiting in queue

Usual symptoms for such behavior is when you see queries go to a queue to wait for the turn to enter innodb engine . You can see those transactions in [ not started sleeping before entering Innodb ] state in innodb engine and high CPU usage around 80-90% during peek or all time based on your workload and this can mean concurrency is low or the system resources are low .

Within innodb engine status you would also see the number of queries in queue and queries that are being processed by InnoDB. This can be addressed by increasing thread concurrency [ NOTE : we didn’t try setting concurrency to 0 as per mysql’s default value because it would unleash too much traffic to the database all at once and it could take the system down ]

In this use case we increased concurrency settings by 10 times we were seeing approximately around 250-300 connections and observe performance due to these changes.

With higher cocurrency we can see innodb started processing more queries – Seems to be an improvement right ? But the fact is NO the system has become even more busier than before. On this server the CPU usage was already 90% with 32 concurrency however load was under 40 but now with the higher innodb_thread_concurrency the load on the server has shoot up more than 2 X which would degrade mysql’s performance even further.

Hence we reverted the concurrency settings to earlier values and the system performance comes back to normal as earlier.

So the learning for us on this is high concurrency can also be dangerous to the system and having it 0 is even more dangerous if application behavior is unpredictable on the mysql instance.

Conclusion

Which takes us towards the conclusion that on busier servers we should tune the queries as much as we can. Make sure it uses indexes efficiently. It’s always better to avoid bulk updates/inserts/deletes transactions and instead do them in smaller batches. Increasing resources would be a better option and match the concurrency settings to support it.

Its very evident that settings up concurrency to 0 may not work out for all workloads however if your database is few GBs and application traffic is normal setting it to 0 (unlimited) would be just fine, Just make sure you watch these things properly in innodb engine status and PMM/grafana dashboards.These parameters has to be set based on the resources on the server and application workload on the database.

At Genex we have engineers who have been supporting very critical large systems for very long time and we have helped lots of enterprise customers in tuning their systems for their workloads. Please reach out to us @ support@genexdbs.com if you need help in supporting your database systems. We support a wide range of database and data streaming softwares visit us genexdbs.com to learn more about us.

Reference articles –

https://www.percona.com/blog/2010/05/24/tuning-innodb-concurrency-tickets/

https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-thread_concurrency.html