3 ms·
> traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation for this? I have seen CPU bound database
by mike_d 6y ago
> traditional OLTP row stores are I/O bound due to contention (locking and latching). Does anyone have an explanation for this?
I have seen CPU bound database servers when developers push application logic in to the database. Everything from using server-side functions like MD5() to needless triggers and stored procedures that could have been done application side.
- cdcarter 6y agoIndeed, at $PLACE_OF_WORK over the last 15 years a lot of logic was built in PL/SQL and dbcpu has become one of our most precious resources. For some applications its perfectly reasonable until you need to horizontally scale.
- jeffbee 6y agoAny 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.