GDB stack trace analysis of Sysbench benchmark

After many unsuccessful attempts to get MySQL to run faster I got
a very simple but effective gdb script courtesy of Intel. The script
is very simple and attaches to the mysqld process and does a
backtrace on all threads in the process and then it gathers
statistics on all stacktraces.

With this script I did some analysis on what goes in sysbench
readonly and sysbench readwrite.

Starting with sysbench readonly I discovered a lot of things I
already knew such that LOCK_open is a major bottleneck.
There were also many other things that popped up such as:
LOCK_alarm, LOCK_threadcount, LOCK_grant,
btr_search_latch and the mutex on the table object which is
used to discover TABLE level locks on the MySQL level. This
last lock is contended mostly because sysbench is only
operating on one table. So most normal benchmarks will
not have any major problems with this mutex since it's rare
with applications that put so heavy weight on a single table.

Interestingly the kernel_mutex in InnoDB wasn't so prevalent
in sysbench readonly. Also I was a bit surprised to find the
btr_search_latch there since it's a RW-lock, but it seemed
like every now and then someone took a X-lock on the
btr_search_latch even in readonly queries. Probably has
something to do with InnoDB adaptive hash index.

One surprising lock here is the LOCK_grant which is also
a RW-lock and this is never taken in anything else than
the Read mode unless one changes the grants which
doesn't happen in a sysbench run. Some discussions
concluded that pthread_rwlock is actually implemented
by using a mutex and this is the cause of the contention
on LOCK_grant. So to resolve that a read-optimised
RW-lock is needed for the MySQL Server code.

To remove LOCK_alarm there is already code in the MySQL
Server prepared to do that so the patch to remove
LOCK_alarm is fairly straightforward.

To remove LOCK_threadcount isn't necessary, it's sufficient
to remove two variables thread_running and global_query_id
from being protected by this mutex and instead using
atomic variables. To handle this one can use the my_atomic
framework and add 64-bit support to it. Then the fix of
LOCK_threadcount is straightforward.

To resolve LOCK_open is obviously a bigger problem but a
first step is to simply remove the hash calculation from
LOCK_open.

The MySQL runtime team is working on a metadata locking
infrastucture that was in MySQL 6.0 but still have some
quality issues. But when this code is ready it will also make
it possible to resolve the LOCK_open problem. Actually the
problem then is both about the new lock LOCK_mdl added
by the metadata locking code and LOCK_open. But the new
structure makes it possible to have more aggressive
solutions on both LOCK_open and LOCK_mdl code.

In analysing the sysbench readwrite benchmark the main
contender together with LOCK_open was not very
surprisingly the kernel_mutex in InnoDB. There are some
ideas on how to improve this as well but first things first.