But times have changed and we really need to stop adding global mutexes to MySQL and by we I mean MySQL the company as the community has a limited role in their development process. Yet global mutexes continue to be added and continue to reduce SMP performance. Can the code and design review process be changed to prevent this?
Examples of recent problems include:
- LOCK_mdl - I don't know the impact of this
- LOCK_plugin - this was fixed
- LOCK_event_loop - this is a problem for the thread/connection pool code new in 6.0
- mutexes added by us for SHOW GLOBAL USER, TABLE STATISTICS. We will publish a fix soon.
- MyISAM engine has a global mutex on the key cache and this limits performance on SMP even when all data is cached. InnoDB can be much faster than MyISAM on SMP.
- HEAP engine has a global mutex. In a few places it is held when malloc and free are called. This is part of the reason why tcmalloc makes sysbench OLTP readonly so much faster.
- InnoDB has several hot global mutexes that are being fixed by InnoDB, Percona and Google. The biggest open problems are prepare_commit_mutex and kernel_mutex.
- LOCK_open is hot when queries are parsed and ended. acl_cache->lock is hot when access control checks are made.
I am sure this will be fixed. But a more interesting problem remains. The thread/connection pool allows thousands of connections to be run on hundreds of threads. The pool has a fixed number of threads. This can make a server hang when all of the threads are handling long-running queries or queries that are blocked in the server. A few changes can be made to reduce this problem:
- get MySQL to listen on multiple ports. Use the pool to handle requests that arrive on one port. Don't use the pool to handle requests that arrive on other ports. We already added code to MySQL to make it listen on a separate port but only accept connections from accounts with SUPER or REPL_SLAVE privileges. I don't want replication connections to get blocked waiting for a thread from the pool.
- limit the max number of concurrent queries per account. I don't want one bad app to prevent all others from connecting to a server. We have added a column to the mysql.user table to specify this limit.
- run with innodb_thread_concurrency=0 as the MySQL docs state
- make all blocked threads respond to KILL commands. InnoDB has a srv_lock_timeout_and_monitor thread that scans the array of threads blocked on InnoDB locks. This could be extended to wake KILLed connections.
- add another background thread that kills blocked connections when the thread pool runs out of threads
- LOCK TABLES - this command is blocked until the referenced tables are locked. Other connections with long-running connections will make this wait for a long time. When this has completed, other connections may be blocked by the locks held for this command. Timeouts are not supported in either case although the kill command can be used.
- FLUSH TABLES WITH READ LOCK - this has problems similar to LOCK TABLES
- GET_LOCK() - fortunately, this has a timeout
- innodb_lock_wait_timeout determines the number of seconds a connection waits for a row lock when the row is locked in a conflicting mode by another connection
- MyISAM tables are implicitly exclusively locked for the duration of DELETE, UPDATE and (non-appending) INSERT statements. Queries block until the statements complete.
- Tables are exclusively locked for the duration of DDL statements.
- innodb_thread_concurrency - the thread/connection pool docs state that you don't need to set this when the pool is used. I disagree. But if it is set, it is another thing that may block a running connection. You will still want to set this for performance reasons on an 8-core server for some workloads. If you have many long-running queries and don't have patches from Percona or Google, then things can be slower than expected unless innodb_thread_concurrency=4 is used.