Month: September 2010

Yesterday marked my 5th anniversary since I signed on the dotted line, and starting working with MySQL AB! I celebrated yesterday with a BBQ with the family, and a few vodka shots for old times sake. 🙂 Interestingly, it was also the 5 year anniversary of the first 5.0 RC being announced.

Since that time I’ve transferred through to Sun, and now find my home to be Oracle (funnily, the user community that I left to join MySQL).

I’ve met a huge number of fantastically talented people, made a lot of new friends, seen a lot of new colleagues come on board as we expanded so quickly, and sadly seen a number of those colleagues leave to go onwards (and pretty much in all cases, upwards) within the IT world. Happily, a large number remain, and continue to amaze me with the work they have been doing, and focus that they have.

I’ve also gotten to know a number of users and customers pretty well, and have to say they are among the most passionate IT community that I’ve ever come across. Your will for MySQL to keep improving is amazing, and remains my inspiration.

It’s also taken me to new places, I’ve gotten to see New York, Las Vegas, San Francisco, Santa Cruz, Washington DC, Santa Clara, Cupertino, Austin, Montreal, Amsterdam, Frankfurt, Heidelberg, Hamburg, and Riga (the less said the better on that one).

For my part, I’m still as jazzed as ever to be working with the MySQL group within Oracle. We’re taking great strides at the moment, with performance, instrumentation, usability, and high availability within MySQL 5.5, and the roadmap ahead has a very bright future for both the Server and the tools that I get to spend my day to day life on (MEM and MySQL Proxy).

And so I raise my coffee cup (hey, I’m at work now), to the next 5 years, and to all the great people that have made my working life so enjoyable!

Like this:

Internally MySQL uses various methods to manage (or, block) concurrent access to shared structures within the server – the most common being mutexes (or Mutual Exclusion locks), RW Locks, or Semaphores. Each have slightly different properties on how different threads are allowed to interact when trying to access these synchronization points.

There has been much written on the various hot spots in the server around these locking/blocking constructs over the past couple of years, so I won’t go further in to that here. See the excellent blogs from Mikael or Dimitri from within Oracle, or those from the likes of Mark or Domas over at Facebook, for examples.

Visibility in to what was happening in the server at this level has been very lacking however, with only SHOW MUTEX STATUS available in the standard server distribution, which only tracked InnoDB mutexes and not those at the server level – which could often be the blocking factors for scalability.

I’ve blogged about PERFORMANCE_SCHEMA before, but never really shown what you can now do whilst trying to track down what sessions are doing, and what they are currently waiting for.

I also saw a hack recently that put the fact that a connection was trying to grab a Query Cache mutex in to the thread state (“Waiting on query cache mutex”), to track whether the query cache was causing too much contention. I thought that was entirely the wrong way to track this kind of detail (it misuses the thread state, which should be stages of execution, not each synchronization point, and doesn’t give you any meaningful way to track the entire overhead). Hopefully I can now show why I thought that.

So with that, let’s dive in to an example of how it should really be done, (you will probably have to scroll this to the right):

The first thing to notice is that not only can we see what external connections are doing – we can now see what internal threads are doing as well. All threads are easily identified by the PERFORMANCE_SCHEMA.THREADS.NAME column, with all client connections having the same name of thread/sql/one_connection (the internal function to manage database connections is handle_one_connection()), and each internal thread within the server and storage engines are tagged with their own identifiers, such as thread/sql/main, the main MySQL Server thread, or thread/innodb/srv_master_thread, the main InnoDB storage engine thread.

So we show the name of each thread, and then figure out the user@host if the thread is an external client connection. We then show all of the normal output that you would expect of SHOW PROCESSLIST, with DB, COMMAND, STATE and TIME (I emit the INFO column, but you could add that if you like to see the statement executing as well).

The last two columns come from the new EVENTS_WAITS_CURRENT table. As you can see, we can now show exactly what the current, or last completed, wait for each session was, and for exactly how long they waited (down to picosecond precision).

In my example above, the last thread listed is waiting for checkpointing to complete within InnoDB, whilst an internal thread is waiting on file IO, on the InnoDB transaction logs (i.e, he is the guy working on the checkpoint) – ooops, I was running a huge INSERT … SELECT on an instance with default settings for innodb_log_file_size. 🙂

If you start to see lots of lots of connections waiting on wait/synch/rwlock/innodb/checkpoint_lock like above, then you too know that you should probably be doing something about your log file sizes (or maybe check whether something else is causing too aggressive checkpointing, such as a very low innodb_max_dirty_pages_pct value).

Keep in mind this can only give you an indication that there are contention points that can be dealt with, as these statistics are very transient in nature.

Here’s an example of tracking down exactly what the contention points in your server are:

All of the above is aggregated server wide, since server start. Of course IO is my largest issue – I was doing write heavy work.

But then you can see exactly what I was waiting on after that – the first culprit was as above, way too much checkpointing. Next is waiting for wait/synch/rwlock/innodb/btr_search_latch, the synchronization point for the InnoDB Adaptive Hash Index. I might also do well to disable it with innodb_adaptive_hash_index = 0 in my case.

I can tell immediately that the query cache is not a contention point as well for example – just look for high values for wait/synch/mutex/sql/Query_cache::structure_guard_mutex for that.

5.5 is coming people, get downloading and trying! We want your feedback! 🙂

UPDATE: Note that in 5.5.6 and above, you should actually use performance_schema.THREADS instead of PROCESSLIST, and performance_schema.EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME instead of EVENTS_WAITS_SUMMARY_BY_EVENT_NAME, these were renamed within Bug#55416.