- The overhead to parse, optimize and setup query execution structures is significant for fast statements. LOCK_open and mutexes used for access control have a lot of contention.
- The HEAP engine has a global mutex held when malloc is called and HEAP tables are used to process ORDER BY and GROUP BY clauses. This should be easy to fix.
And now, a few claims that are true for workloads similar to the sysbench readonly OLTP test.
- At high concurrency, tcmalloc doubles throughput.
- InnoDB is much faster than MyISAM and the advantage grows with the number of CPU cores.
- innodb_thread_concurrency=4 has a cost. It helps for CPU bound workloads dominated by long running statements. In other cases, it can hurt performance.
Is this motivation to buy a big SMP?
TCMalloc makes InnoDB much faster at 8+ concurrent users: