November 27, 2014

Tuning InnoDB Concurrency Tickets

InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up 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. The default value is 500…”

What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it enters InnoDB, this number is decremented until it reaches zero (“entering InnoDB” appears only to occur when a row is accessed). When it reaches zero, it may-or-may-not be put into a queue and wait to continue execution. InnoDB doesn’t provide us a way in which to determine how many concurrency tickets a query uses, making this parameter notoriously difficult to tune. It is important to note that this variable only comes in to play when innodb_thread_concurrency is greater than zero.

On a stock install of MySQL, here are some example queries and the corresponding number of concurrency tickets used for each:

And now on to a more interesting scenario: foreign keys

So, how can we put this into practice, since this information isn’t available to most users?

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

As with any performance optimization effort, you will want to optimize for the common case. If you have a very simple workload, you can calculate these values by hand. But for most workloads with a complex access pattern, we’ll need to estimate or wait for InnoDB to expose this information to us.

What happens in the case where I have two distinct access patterns: single row primary-key lookups and SELECT statements that examine 900 rows? If innodb_concurrency_tickets is set to 500, then all of the single row PK lookups will execute without ever being subject to an additional concurrency check (there is always one when a thread first enters InnoDB) while the 900-row SELECT statements will always be subject to one additional concurrency check (we actually care less about the concurrency check itself than the possibility that it may become queued). Your first instinct may be to increase innodb_concurrency_tickets to >=900 in this case, but that isn’t necessarily the best decision. As stated in the docs, the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these two variables are most often tuned in concert). To continue the example, if innodb_thread_concurrency is set to 8 and eight 900-row-SELECT statements come in, they will effectively block the PK lookups until one of them is subject to a concurrency check or complete execution and exit InnoDB. If innodb_concurrency_tickets had been increased to >= 900, then ALL of the PK lookups would be blocked until the 900-row-SELECT statements complete execution.

With a maximum value of 4,294,967,295 this has the potential to block other queries for a significant amount of time. Setting innodb_concurrency_tickets too high can have startlingly negative performance implications. On the other hand, if we determine that 99% of the traffic are these single row PK lookups and only 1% are the 900-row SELECTs, we may be tempted to lower the setting to 1 to accommodate the “typical case”. The effects of this, though, would be to cause the 900-row SELECT statements to be subject to 899 concurrency checks. This means 899 potential opportunities to be queued! So, as with most other parameters, this is a balancing act.

It really comes down to the importance of the applicable queries. Imagine those 900-row SELECT statements were actually 10,000 row selects, this would become a more pressing issue. If they are reporting queries used only internally, then it is not so much of an issue and you can leave innodb_concurrency_tickets rather small. If, on the other hand, these are the queries that lead to revenue generation, you may want to give them a bit more dedicated CPU time so they execute that much faster (even at the expense of the PK lookups). In other words, if you’re optimizing for throughput in this scenario, you will tune innodb_concurrency_tickets to the 99th percentile of small PK lookups. If you’re optimizing for response time, you would set it larger to accommodate the larger (important) select statements.

A quick sysbench run gives us the following results (X-axis is innodb_concurrency_tickets, Y-axis is txn/sec. More is better). Since all sysbench queries are 10 rows or less, we don’t really expect to see much of a difference here:

Details:

Applicable my.cnf settings:

Comments

  1. Webinar: Introduction to Percona Server, XtraDB and Xtrabackup | MySQL Performance Blog says:

    […] will be a technical talk, but perhaps less technical than some of our other posts. I recommend inviting a few friends & colleagues that are already familiar with MySQL, but […]

  2. Nilesh says:

    how to check that > maximum number of concurrent threads that the InnoDB plug-in can create

  3. should be worth noting that “show engine innodb status” does show some limited info:

    —TRANSACTION 0 58505, COMMITTED IN MEMORY, process no 1366, OS thread id 68725056 committing, thread declared inside InnoDB 17

    That 17 is number of trx->n_tickets_to_enter_innodb

  4. peter says:

    Ryan,

    I am not sure I understand why higher innodb_concurrency tickets will reduce throughput ? For me it looks opposite – the higher value will allow less “interruptions” while running queries and less overhead while checking concurrency. It will however increase variance – the larger is the value there is a longer period the query can starve for.

    The trick is as thread is considered “inside innodb” when it is doing IO as well as when it is processing data in memory, so the time to which given number of tickets correspond can vary.
    500 tickets may amount to less than ms if small rows are accessed in memory or more than as second if accessing all rows requires physical disk IO

  5. Elaine says:

    How are tickets affected by sorting and limits? If I have a 20,000 element table called users and I run a query like:

    select name from users where id > 19,990 … => 9 tickets?

    but

    select name from users limit 20 …=> 20 tickets or 20,000 tickets?
    select name from users order by id desc limit 20 …=> 20 tickets or 20,000 tickets?

Speak Your Mind

*