4 ms·
Any MySQL with more than about 100 concurrent queries of the same InnoDB table is going to be CPU bound on locks. Their whole locking scheme doesn't scale; it's
by jeffbee 6y ago
Any MySQL with more than about 100 concurrent queries of the same InnoDB table is going to be CPU bound on locks. Their whole locking scheme doesn't scale; it's designed to look great in benchmarks with few clients.
- mike_d 6y agoIt 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.
- skyde 6y agoI am very interested. Could you provide more detail or a link about this. I would love to understand what they are doing that look good on benchmark but does not actually scale.