Debugging Locking issues that may be related to INNODB AUTO_INC Locks
How innodb handles auto increment over there states that "When accessing the auto-increment counter InnoDB uses a special table level lock AUTO-INC lock which it keeps to the end of the current SQL statement, not to the end of the transaction."
- Does this mean that If the SQL statement take a long time to execute that a lock on the table is exists for that period of time
- Does this mean that no other statement can grab info since this is a mutex lock?
Update
So what tweaking of variables might make a difference.
4.1+ innodb_safe_binlog - sync's the binlog on ever update to innodb to make sure that if a crash occurs the chances on loosing slave events is very small.
thread_cache: - this keeps open treads open for frequest connections.
I reduced thread_cache to 45 from 300 and turned off innodb_safe_binlog to see if thread contention sould stop. I know I could mess with innodb_concurrent_threads but this is a different issue outside of that thread scope.
Update 2
Haha I'm such a n00b. Basically the issue was there wasn't a key on a table that just grew very large but still in memory. So disk io was low but CPU was very very high!! The reason even though a table with 150K rows is in memory processing all these rows is CPU IO intensive at high concurrency. Adding a index is all that was needed to fix the porblem.
Note: use top, vmstat, iostat -x 5
