3 ms·
It sounds like you just hit the threshold where defaults don't cut it anymore. With >100 concurrent clients you need to tune your DB for your workload. innodb_
by mike_d 6y ago
It sounds like you just hit the threshold where defaults don't cut it anymore. With >100 concurrent clients you need to tune your DB for your workload.
innodb_thread_concurrency and innodb_concurrency_tickets would be a good starting point, and optimal values depend on your r/w balance and number of rows touched per type of query.
- jeffbee 6y agoI'm saying the innodb buffer pool mutex doesn't scale over 100 contenders and you are saying that I can tune mysql so there's never more than that, which it seems to me like we're in agreement.
- sradman 6y agoI did a quick search and it looks like InnoDB implements a spin-lock. Do you see increased CPU utilization when the buffer pool is overloaded? This could explain the behavior described in the article.
- jeffbee 6y agoCorrect, it uses an aggressive spinlock without fairness features for waiters like you'd see with a proper production-quality mutex. This makes it very efficient in the absence of contention (i.e. in trivial benchmarks) and extremely poorly behaved in times of contention.
- skyde 6y agoI assumed they would spin for only a short period of time only then fallback to a real lock. This is usually done to avoid blocking the thread before the end of it's time slice if there is a chance the lock would become available. If instead they implement a pure spin loop with sleep I can see why it does not perform well.
- skyde 6y agowould it scale better if we set innodb_sync_spin_loops=0 ? Causing the lock to not spin and give up the pending CPU cycles back to OS if the lock is not available.