Sunday, January 17, 2016

MySQL Support People - The Next Generation

My first post in this series caused quite an active discussion on Facebook. Readers correctly noted some mistakes in dates and missing names in the list. I've corrected some of the mistakes already and will correct some others later. What was also noted is that initially support for MySQL was provided by developers (this is really important, we'll get back to this later), and of them many had never even got a title of Support engineer. Of those who had not I listed only Monty...

I just want to explain why I made these mistakes and/or why I (intentionally) had not listed developers etc. First of all, I based my post on my memories (so I could be also wrong about start dates and many details) and that's why I could not list those whom I had never closely worked with. I worked with Monty while in Support and he was involved, to some extent, into my work on the problems that were interesting for me, and he provided support well before I joined. That's why he is in the list. My posts are not about the history of MySQL (or even the history of MySQL Support) - they are about MySQL Support as I was introduced to it in 2005 and about people who made it look and feel as I understand it now - the best, most efficient and useful service for MySQL users.

There are pure managers among these people, those who had never reported any MySQL bug in public probably and rarely (if at all) resolved any pure technical problems for customers. They well deserve separate posts, but here I want to name two of them who played the most important role from my point of few: Tom Basil (Director of MySQL Support in MySQL and Sun till 2008) and Peter Farkas (Director of Support in Percona till 2015).

Now it's time to get back to the topic of this post. "The Next Generation" are engineers who started to provide support for MySQL some time in 2005 or later, when the system as I remember it (created mostly by those named in the first post led by Tom Basil) was already in place, with Eventum as an issue tracking system, public bugs database managed by Support, SOP in place in Wiki, SSC duties defined, and so on. I probably have to include myself into this new generation as well, but this series is not about me.

So, these great engineers joined MySQL Support in MySQL and/or Sun (that had not changed that much in the way we worked, mostly accepted and appreciated our experience) after me or before me, but whom I consider the "next generation" for sure. There are really famous people in MySQL world among them:
  • Geert Vanderkelen - he actually joined in April, 2005, so before me, as Senior Support engineer and MySQL Cluster expert. I just do not understand why I missed him in the previous list and no one corrected me. His list of public bugs reported (I see 171!) is quite impressive. He keeps reporting bugs in public, check Bug #79621, the last one he recently reported. Some day in 2011 he got tires of Support (this happens to many creative people) and moved to development where he works on his Connector/Python project, now in Oracle. 
  • Raymond DeRoo - I think he was already working in Support when I joined in 2005. At least there is his bug report dated 2004 and bug reports marked as affecting customers dated early 2005 (check, for example, Bug #9731). I see 11 public bug reports from him. It was really nice to spend time with him online and in real life, as he was just awesome and always funny. He had disappeared from the team probably at the end of 2007, then he was in Europe for some, presented MySQL-related topics at conferences, worked for Booking.com. I am not sure what exactly is he doing now.
  • Mark Leith - he had became an essential team member immediately when joined, some day in 2005 after me. With this Oracle database background he helped to make the entire MySQL more aware about Oracle database, related tools and approaches (before him there was only Peter Zaitsev who created many worklogs obviously inspired by Oracle, and me, "Oracle agent in MySQL", the rest of the company mostly pretended Oracle never existed, non-standard or they do not care about it, at least until Oracle acquired InnoDB at the end of 2005...). He was a manager of AMER Support team in MySQL and Sun. Mark had reported a lot of bugs in public (I see 177) both while in Support and later when he led MySQL Enterprise Monitor development. Check one of his recent bug reports, Bug #76049. For me he is also a "godfather" of Performance_Schema and father of sys schema in MySQL. Mark is a Senior Software Development Manager in Oracle now and still a very active community member contributing a lot with his blog and presentations.
  • Domas Mituzas - he is all times the greatest MySQL Support engineer I had ever worked with. I probably had to stop at this phrase, but as one of my main job duties used to be "PR manager for Domas" once, I'll continue. He managed to assign 14 issues per single 8 hours shift back in 2008, and resolve 7 or 8 of them completely by the end of that shift. During his first days in MySQL Support in 2005 he managed to teach customer from China how to use Chinese properly in Oracle RDBMS, while doing MySQL support. He managed MySQL in Wikipedia and had his own fork of MySQL maintained before most of current forks appeared. He reported some 53 or so MySQL bugs in public (check also his older account at bugs database), my all times favorite being Bug #12113, that allowed me to resolve at least a dozen of customer issues over years and was fixed somehow only in MariaDB 10.1 recently. He is well known as a speaker about MySQL and cool blogger. Check this his famous post about query cache! His work probably requires a book to describe it, not a post, and he is mentioned in the books. Domas, who joined MySQL Support in October, 2005 is a small data engineer in Facebook since 2009. I am proud that I worked in one team with him, honestly.
  • Chris Calender - he joined probably soon after me and he still provides support in MariaDB. He is an active blogger and bug reporter (I see 69 reports). Check his relatively recent Bug #71395. His posts on building MySQL and MariaDB from source on Windows helped me a lot back in 2012. I always had a huge respect to everything he did.
  • Morgan Tocker - now a MySQL Product Manager in Oracle (this seems to be a popular career path among former MySQL Support engineers...), Morgan joined MySQL Support back in January, 2006. He worked on MySQL in different roles and companies since that, including Percona where he was Director of Training, returning back to Oracle for MySQL Community Manager role. He is an active blogger and bug reporter (I see 41 public bug reports from him). Check his old Bug #28338 that is still "Verified".
  • Shawn Green - he joined MySQL in 2006 and had been a very reliable team member in AMER Support team from the very beginning, great SSC (no wonder, he served in the US Navy) and helpful friend to many of us. He had reported 25 bugs in public as far as I can see. Check his latest still "Verified" public feature request, Bug #74640. He is still a MySQL Senior Principal Technical Support Engineer in Oracle, even though he has enough problems to deal with outside of MySQL during last years.
  • Kyle Joiner - he joined in 2007 based on Shawn's recommendation and with the goal to help us with DB2 Storage engine support (MySQL used to work on IBM System i and use its DB2 database as a storage engine back then). He is probably the strongest MySQL Support engineer, literally. He was a great engineer and SSC in AMER Support (I always felt safe as weekend SSC knowing that Kyle is supposed to be on call, he was always ready to help). Kyle had reported 14 MySQL bugs in public. Check his Bug #48049 that is still "Verified". He quit Oracle to join MariaDB recently, where he is still a Support Engineer.
  • Sean Pringle - he joined MySQL Support in 2006 and worked in APAC Team that I always tried my best in cooperation with. He was a great Support Engineer and also was my guide in San Francisco and Cupertino when I visited USA for the first time in 2007. He left us in 2009 and worked in SkySQL later. I try to keep in touch and still hope to continue working with him one day in one team.
  • Tonči Grgin - he joined MySQL in March, 2006, during last (for me, in MySQL AB life) company meeting in Sorrento. We met in the airport of Munich and later Sinisa, who knew him for decades as a developer and MySQL user, just told me that I have to teach Tonči everything for him to be ready to work in the Bugs Verification Team by the end of the meeting. I did that, and he was my first successful student in MySQL Support. His is my family friend and I had an honor to visit his home near Zagreb and his father's home in Split. Tonči worked on processing bugs for various connectors and complex support issues related to connectors, and later, already in Oracle, he moved to development, to fix those connectors finally, and eventually he became a manager of MySQL Connectors team in Oracle. As far as I know that team recently disappeared and he is now working in Oracle on MySQL performance improvements on Windows. He was always active not only in processing bugs, but also in reporting them, so I see 67 he had reported in public. Check Bug #48228, still "Verified".
  • Sveta Smirnova - she joined probably in May, 2006, but back in Sorrento we were already discussing steps to hire her. As I insisted we should get her into the Bugs Verification Team, I've  got a task to be her mentor, and forced her to work on bugs even before she joined. Long story in short, she managed to follow my steps and do everything I did, but better, in MySQL, Sun, Oracle and now Percona, where she is my colleague and also a Principal Support Engineer. She also do a lot more than I did, as a developer, blogger and presenter at various conferences for years. She is well known as an author of a book on MySQL Troubleshooting published in 2012 by O'Reilly. She is famous. Sveta reported a lot of bugs, check also her older account, 256 in total. Check her latest bug report, Bug #79596. Awful bug, really... To summarize, I am proud to work with her these days, and she is a key member of our team in Percona now.
  • Todd Farmer had reported 327(!) bugs in public and is a very active blogger. He is one of they key contributors to MySQL Community for sure. He joined MySQL as a Support Engineer back in August, 2006 and then was AMER team manger. He was brave enough to step in as a Director of MySQL Support in Oracle back in 2010, and while he is not in my list of top 3 Directors of Support of all times, he still did a lot of useful things for MySQL Support in Oracle. Since 2013 Todd is a Director, Technical Product Management, MySQL in Oracle.
  • Matthey Montgomery - yet another great MySQL Cluster Support engineer and public bug reporter (I see 106 bug reports from him), he joined us in August, 2006. He is a Principal Technical Support Engineer in Oracle now and still deals with NDB. This is incredible! Check Bug #20924, one of his first bug reports that is still "Verified". MySQL had never been able to deal with data types properly, especially in explicit and implicit conversions... Topic for another post, sorry.
  • Johan Idrén - he joined MySQL Support in November, 2006, and worked on all kinds of issues initially, being very productive. Later he specialized in Merlin (a.k.a. MySQL Enterprise Manager now) and when Oracle acquired Sun he decided to move to SkySQL to continue doing MySQL Support. Recently he works as a Systems Engineer in DICE (EA Digital Illusions CE AB). I see 22 public bugs that he had reported, including Bug #28331 that is still "Verified". 
  • Susanne Ebrecht (now Holzgraefe) - yes, we have a family of MySQL support engineers who once worked in the company while not yet a family. Susanne joined us in March, 2007, and is actually of PostgreSQL community origin! She was an active bug reporter, I see 165 bugs she reported, so eventually she ended up in Bugs Verification team where we worked together. She worked mostly on bugs for GUI tools, and one of them is still "Verified", see Bug #55497. Susanne quit with her future husband Hartmut at the end of 2010, when they had either to join Oracle or move elsewhere. She is a consultant now.
  • Andrew Hutchings (a.k.a. Linuxjedi) - I thought he was with us in MySQL AB already, but quick check shows that he joined as MySQL Support Engineer for Sun Microsystems only in 2008. Andrew worked on very complex MySQL Cluster and C API issues. He also developed new features and bug fixes for MySQL and MySQL Cluster in his spare time. Andrew was one of only a few MySQL engineers to win a Sun employee recognition award. Later he had really hard times with Oracle and eventually quit to work on Drizzle. Andrew is a Technical Product Manager at NGINX, Inc now.
  • Gustaf Thorslund - he joined us in October 2007 and was Support Engineer specialized in MySQL Cluster and NDB API. He is probably the best one in anything related to NDB API. With long  breaks he took for parental leave, study etc, he is still working as a Senior (still?) Technical Support Engineer in Oracle. We always had a lot of fun discussing life, work, Ada programming and other matters (excluding NDB that I try to avoid by all means). I see he had reported 17 bugs in public, but they are all closed by now.
  • Gary Pendergast - he joined us in November, 2007, but some time after the meeting in Riga (and Sun's acquisition of MySQL) he had disappeared for a year, to travel in Europe. Probably it was early 2009 or so. He then came back when Oracle took over the team and eventually left in November 2011 to join Automattic, where he is still working on WordPress (and thus MySQL). He was a good Support Engineer in APAC Support Team. Check his blog.
  • Andrii Nikitin - he probably worked in MySQL as developer (of GUI tools) in 2007 already. Later he moved to Support and quickly became a great colleague for us. Switching roles is hard when you are already good in something, but he managed to deal with that as well as with many other hard problems in his life. He actively worked on bugs and I can see 86 bugs he had reported in public. Check his latest public bug that is still "Verified", Bug #77789. He is a Senior Principal Technical Support Engineer in Oracle now and is a core hard working EMEA Support team member.
  • Leandro Morgado - he joined us in Sun in 2008 and worked really well from day one in EMEA team. He used to be active bug reporter and I see 46 his public bug reports, including Bug #43523 that is still "Verified". He is still a Senior(?) Technical Support Engineer in Oracle.
  • Ligaya Turmelle - she joined us in April, 2008 and is well known as a full time Goddess, part time MySQL DBA, occasional PHP programmer and active member of both MySQL and PHP communities as a speaker at various conferences. She probably had several accounts in the public bugs database, but I was able to identify only two of them quickly. Check her Bug #67056 - it's full of words I am trying to avoid by all means, like NDB and memcached... I've got a chance to met her in person at Percona Live 2015 in Santa Clara and we had a drink (or two) together. She is a Principal Technical Support Engineer in Oracle now.
  • Rene' Cannao' - he joined us in Sun in August, 2008, and I remember him as a very capable and reliable Support Engineer in EMEA Team from day one. He had several accounts in public bugs database, so it's hard to aggregate all bugs he had reported over years, but there were many. Check his recent regression Bug #78680. He seems to be working for Dropbox now and also develops his ProxySQL software.
  • Trent Lloyd - he joined MySQL back in June, 2007 and since that time he works in Support (now he is a MySQL Principal Technical Support Engineer in Oracle), often on complex issues including those related to HA, DRBD and Pacemaker and tuning Linux for MySQL. I see 13 his public bug reports, including Bug #43775 that is still "Verified".
  • Mikiya Okuno - he joined MySQL in October, 2007, and was one of those few engineers who ran Japanese language support in MySQL (yes, we did that, with a separate project in Eventum and separate team of engineers in Japan that I worked with a lot!). He joined us from Sun to be soon aquired back by Sun with the rest of MySQL. Mikiya had reported 107 public bug for MySQL software. Check his Bug #65035 that is still "Verified". Mikiya is a MySQL Technical Analyst at Oracle now.
  • Meiji Kimura - he joined MySQL Support in September, 2007 and is a Support Engineer in MySQL KK, Sun and Oracle since that time. He is an active bug reporter and seems to have several accounts in public bugs database. Check his Bug #80018 opened today!
  • Roel Van de Paar leads QA in Percona now. With his 296 public bug reports he is a key contributor to MySQL quality in general. His first public bug report, Bug #41908, is still "Verified" and not fixed. Roel joined MySQL Support (already in Sun) in 2008 and was one of the key support providers in APAC team before he switched to QA. Roel contributes to community (and MySQL Support, indirectly) also with his numerous great posts in Percona's blog.
  • Oli Sennhauser - he was a MySQL Consultant from 2006 or so, but later in Sun he worked in MySQL Support for a couple of years, and I think he was great in this role. He reported 24 bugs recently (for some reason I think he had a second account as well), check the latest of them still "Verified", Bug #78898. Now Oli is a Senior MySQL Consultant and CTO at FromDual GmbH.
  • Bogdan Kecman - he joined us in October, 2007, specialized in MySQL Cluster support and quickly became one of the most useful engineers in this area. His knowledge of hardware also always impressed me. He is still in Oracle and now is probably a member of my former Bugs Verification Team there, as I see his name as bug assignee from time to time. He was an active bug reporter, with 44 bugs reported in public. Check Bug #44760, still "Verified".
  • Scott Noyes - he had joined MySQL Support in August, 2007 and had always been a very useful and reliable team member. I see 70(!) public bug reports from him, of them 49 under this account. Please, check his Bug #76072 reported last year and still "Verified". Scott is working in MySQL Support in Oracle.
  • Ben Krug - he joined back in December, 2007 and played mostly SSC and later TAM role for Facebook. He is a Senior MySQL Support Engineer in Oracle now. Ben was active enough bug reporter, but it's a bit hard to consolidate different accounts he seemed to use over these years to count all bugs. Check his Bug #70056 that I consider really serious for many users and customers who plan to upgrade.
  • Jonathon Coombes - he joined us in Sun in July 2008 as Senior MySQL Support Engineer, mostly to work on MySQL Cluster and MySQL Enterprise Monitor issues. He still works in Oracle and does more or less the same job as Principal MySQL Support Engineer. I remember him as a great team mate. He reported 45 bugs for MySQL software in public, including Bug #48789 that is still "Verified".
  • Roger Nay - he joined us in Sun in October, 2008. He worked a lot on MySQL Enterprise Monitor, as obvious from the list of his 42 public bug reports, including Bug #57574. But he was good in other MySQL software as well. Roger still works as a Senior (?) MySQL Support Engineer in Oracle. Not sure what is his current focus of interests though.
  • Donna Harmon - she joined us in Sun in 2008 and worked in AMER team. I see 15 public bug reports from her, including Bug #39618 that is still "Verified" (I'd re-check one day if the option mentioned there really affects the logging in 5.6 and 5.7). Based on her LinkedIn profile she is still working in Oracle, but she is probably not there for a long time. I am not sure what's up with her now, unfortunately.
  • Travis Rupe - he had actually joined in September, 2008, during good old Sun days. I remember him as a great colleague in AMER team, but I could not find any public bug reports by him, and I am not sure why is that so. In any case, he is one of the "Next Generation" dream team of MySQL Support. Travis is still working in Oracle now, as far as I know.
I have to stop for now. The post is already huge... This list may be not complete and I am ready to correct it and add new names based on your feedback provided either here in comments or on Facebook.

Stay tuned, next time I'll list those who joined MySQL Support only in Oracle, but still contributed (and still contributing) a lot for MySQL Community.

Sunday, January 10, 2016

MySQL Support People - Those Who Were There First

I'd like to devote this long weekend post, the first in a new series, to my current and former colleagues who once worked or still work in a company that provided public MySQL Support service and had a job role of MySQL Support engineer. The list of companies include MySQL AB/Inc ("good old MySQL"), Sun, Oracle, Percona, MariaDB, FromDual, maybe more (I named only those that surely provided or provides MySQL Support for customers outside of the company).

This is not the first time that I write about "people of MySQL", in a wider sense "those who contribute to MySQL Community". Last time it was about famous MySQL bug reporters. That's because for me, on personal and professional level, MySQL is about people who work on it, not about companies, services or money we all make while working on it. But today I want to concentrate on "MySQL Support People" mostly, and name many of them, those who still do MySQL Support right now when you read this, to those who are now CEOs, CTOs, Directors, VPs, Product Managers, DBAs and Developers. They all have two things in common: they once provided support for MySQL in the same team with me, and their contribution to MySQL Community is visible (can be found easily at http://bugs.mysql.com or elsewhere). Click on names to see some of their public contributions to MySQL (bugs reported).

You may want to ask why I highlight "Support" in this post? It happened so that recently I have to emphasize in public the importance of Support as a core service for MySQL. Regular readers probably noted this here and there...

Let me write today about those who started to provide MySQL Support in "good old MySQL" before me:
  • Shane Bester - he started to work in MySQL Support back in 2005 and he still actively provides support in Oracle, as far as I know. Click on his name to see a list of 1029 (at the moment of writing) public bug reports he created for MySQL software! Start with Bug #79591 if you are interested in regression bugs in MySQL 5.7. I wish I'd be able to do the magic he does every day with creating repeatable test case for MySQL bugs.
  • Sinisa Milivojevic - rumors say he probably was the first Support Engineer in MySQL. Maybe not, but he does this job to some extent since 1999 or so. I see 74 bugs reported, started from Bug #4. So, he was already contributing to MySQL Community back in 2002 for sure! Recently he seems to verify public bug reports from time to time while still working in Oracle. Not sure what else he does, but does it really matter? He was one of MySQL people who influenced me a lot and I tried to play "better Sinisa" for a couple of years at my current job.
  • Victoria Reznichenko - a well known Queen of MySQL Support in Oracle now probably enjoys her maternity leave (I hope Oracle does not force her to work during this time). She was already a well known Support provider in EMEA when I joined, as one can clearly conclude from the fact that her first Bug #860 (of 102 I can see) was reported back in July 2103, 2 years before I joined MySQL AB. Surely her opinion would always matter for me.
  • Alexander Keremidarski (a.k.a. Salle) - when I joined he was team manager of EMEA Support team. As far as I know he has a similar role now in MariaDB. A lof of MySQL support practices are related to his name in my mind. Based on Bug #11 he had reported, he contributes to MySQL Community at least since 2002. Maybe it was him, not Sinisa, who was the first employee with MySQL Support Engineer title. I let them argue about that.
  • Timothy Smith - he tested my knowledge when they decided to give me Support Engineer job in MySQL. In 2005 he was an APAC team manager. Probably his activity was the reason why I ended up in Support (they were faster). I've used his approach to testing candidates later. I see 130 bugs reported by Tim, and many are still not fixed. I am not sure if he is still related to MySQL in any way, but he built a great team and I always was happy to work with him.
  • Matt Lord - he was already a key Support engineer in AMER team when I joined. 123 public bug reports till he moved to the dark side in Oracle, where he is a MySQL Product Manager now.
  • Harrison Fisk - he probably did consulting or training when I joined, but I clearly remember him in Support soon after that and until he moved to Facebook (after playing TAM role for them from some time). I see 108 bug reports and Bug #53825, for example, makes me wanting to double check current InnoDB code. I wish I have that gdb functions they discuss with Sunny at hand. Harrison had the highest rank in Support all the time we worked together. I would not be surprised if I am still 5+ years behind Harrison in my understanding of InnoDB internals (or anything related to MySQL)...
  • Arjen Lentz - he was a key Support engineer in APAC team when I joined. Now he is the Exec. Director at Open Query Pty Ltd, yet another MySQL services provider. His first public bug report that I can see in the list of 84 total, Bug #108, is almost 13 years old.
  • James Day - he worked all kind of crazy hours (probably he is the only one besides Shane and me who was SSC for all 3 shifts, EMEA, AMER and APAC more than once) and was already a well known guru in complex issues handling back in 2005. I see just 25 public bug reports from him, but he had processed many bugs and many of his comments in InnoDB-related bug reports are priceless. Read Bug #69842, for example. I'll remember our busy night APAC shift we worked together (with nobody else around) forever... He still does MySQL Support in Oracle, as far as I know.
  • Kolbe Kegel - he joined MySQL several weeks before me and he still provides Support in MariaDB. He had always been a very reliable and smart engineer. Check the latest of his 133 public bug reports, Bug #74517.
  • Hartmut Holzgraefe - Hartmut helped me a lot during my very first days in MySQL. He was ready to answer any question, help with code (he probably pushed the patch to have my Celeron CPU supported by the build scripts), whatever I asked. He provided support for NDB cluster, and people who are able to do this form a separate "super team" in MySQL Support that gain my huge respect. The do magic by supporting software I consider NOT supportable without direct communication channel with its developers even today. He had reported 420 bugs as far as I can see, and Bug #77018 is the last "Verified" one of them reported for NDB cluster.
  • Axel Schwenke - he was a key support provider in EMEA when I joined and he is also from that "super team" of NDB-enabled magicians. By the way, originally I was hired to provide NDB support eventually and went to Bugs Verification Team only for some part of usual 6 months probation period. But I skipped getting back to NDB cluster happily, and was able to mostly ignore it for 10 next years... Axel works in MariaDB and recently tried to fight with Oracle on performance benchmarks several times. Check this, Bug #77586, the last of his 69 public bug reports.
  • Miguel Solorzano - my first and only team manager in MySQL/Sun/Oracle. He still manages the same Bugs Verification Team and still works on bugs processing in a very visible way (Google for his name and count hits). His first bug report out of 115 total, Bug #326, is almost 13 years old, and before devoting himself to bugs processing he worked as developer on porting MySQL to Windows. I've got my first email form him with instructions few days before I started, and this email was enough for me to start doing my job from day one. I've forwarded it to maybe a dozen of colleagues who joined later. To summarize, Miguel was an ideal manager for me at some stage. The amount of time and efforts he devoted to MySQL Community is incredible!
  • Bogdan Degtyariov - Bogdan joined MySQL Support few weeks before me and worked on Connectors (mostly ODBC, but others as well, for some time), both when they were mentioned in customer issues and in public bug reports. He had reported 64 bugs. I am not sure what happened to him recently, as his latest public bug report is 5 years old and we had no chance to get in touch since I left Oracle in 2012. I hope he still works on MySQL in Oracle, no matter what is his current job role.
  • Dean Ellis - he was (or soon became) AMER team manger in Support when I joined. He was also a key Support provider for AMER and defined many proper procedures over years. The last of them that still affects every MySQL user was so called "BPS process" for setting priorities of bug fixes in Oracle MySQL. It was him who set up the grounds of the process that was later implemented (from Support side) by Sinisa, me and Sveta Smirnova. I see 140 bugs reported but I am sure he had more than one account and reports date back to 2003. Some of his famous bug reports include Bug #4291 and Bug #1118 (implemented only in 5.7.5+). He was a Vice President, Technical Support at MariaDB last time I checked.
  • Vasily Kyshkin (a.k.a. WAX) - he was a key engineer in my Bugs Verification Team when I joined. No wonder he reported just few bugs - our task was to get the counter of open and not processed bugs smaller, not bigger. It had been <90 for months, not 528 as i see now (and 500+ for months recently). So, engineers from bugs team, mostly reported bugs when requested by customers. I was really sorry when he decided to leave MySQL, but the habit to not care much about Support engineers (and even less - about those working on community bug reports) was not invented recently. Some had it back during "good old MySQL" days...
  • Aleksey Kishkin (a.k.a. Walrus) - according to many, he was the best SSC (Support Shift Captain) of MySQL AB times. I can not argue with that (even if I'd like to think it was me :). He worked a lot and was always ready to help with bugs processing. He left MySQL AB in 2006 probably, then eventually returned. Not sure what is he doing now. As a side note, those engineers with nicknames in this list surely worked since the times were there were just a handful of people in entire MySQL... In 2005 I was asked about the preferred nickname (and I used "openxs", you can search bugs on that, it's still my main UNIX user name everywhere, but it was not widely used in the company).
  • Michael Widenius (a.k.a. Monty) - yes, creator of MySQL provided Support for it and worked in tight cooperation with first Support engineers to define some of the approaches and practices of doing Support. This is what I've heard and can conclude based on his bug reports. Check his Bug #18 to understand the state of MySQL quality back in 2002 :)
  • Jorge del Conde - when I joined Jorge worked in Bugs Verification Team, a lot, on all kinds of bugs. But essentially he was a developer in MySQL since 1998 (one of the very first employees). More than 7 last years he works as Senior Developer at Microsoft. I was really sorry back in 2006 when he had to quit from MySQL Support. Not the last time when I could do nothing to keep a team member, but it's always painful to me.
  • Peter Zaitsev - yes, founder and CEO of Percona was a leader of High Performance Team in Support back in 2005. this team worked on complex performance-related issues and run all kinds of benchmarks. Moreover, Peter had SSC shifts scheduled sometimes, and at least once was late for his shift so I had to call him. He had reported 133 public bugs. Check Bug #59899 as one of the last examples of my communication with Peter from the other side of public bugs database.
  • Vadim Tkachenko - current CTO and co-founder of Percona was the only person from MySQL AB whom I've seen in the real life before I joined. He met me to evaluate if I am good enough to join support. We had some coffee at talked about transaction isolation levels and probably something else. Vadim worked with Peter Zaitsev in High Performance Team and formally was a Performance Engineer, but he worked on usual support issues as well and took SSC shifts. Vadim managed to report 23 public bugs while working in MySQL, and his Bug #14347 is still just "Verified".
  • Alexey Kopytov (a.k.a. Kaamos) - he also worked in High Performance Team in 2005 when I joined, and is famous for his sysbench tool and numerous contributions to Percona Server and Percona XtraBackup since 2010 when he joined Percona. He left Percona somewhat silently some time ago, so I am not sure where exactly he is working know, but he still writes a lot about MySQL and keeps reporting bugs (I see 90 of them) and contributing patches. Check his last bug report and patch in Bug #79487.
  • Lachlan Mulcahy - I remember him as a great APAC team manager after Timothy Smith moved back to USA, but quick check shows that he was a Support Engineer when I joined. List of bugs he reported (49 in total) move us back to September 2004, that's probably when he started. Check his Bug #36151 that is still "Verified". Now he is a Production Engineering Manager at Facebook.
  • Sasha Pachev was a developer in MySQL, but some time in during 2006 or 2007 he worked in Support as well, part time. Check his last public bug report with a patch suggested (out of 8 that I see) that is still "Open", Bug #60593. There are also still "Verified" optimizer bugs reported by him.
  • Jani Tolonen was mostly a developer, but he played many roles and worked in Support part time. His 23 public bug reports covers everything from mysql command line client to Maria storage engine. I am not sure what he is doing now.
  • Indrek Siitan - when I joined and for some time after that he played mostly a "full time SSC" (Support Coordinator) role mostly in Support. But quick check for bugs gives a list of 15, starting with Bug #138, so he surely did other work as well. Now he seems to be a Full-Stack Web Developer (and probably he had always been a web developer in one of his roles).
I studied a lot from all these people while working with them. They surely know what MySQL Support is about and contributed a lot both to MySQL Community and to my personal ideas on what's good and bad in MySQL services.

UPDATE: my readers on Facebook noted that there were other engineers as well who worked in Support or who had provided Support in MySQL before I joined and before many people of those I've listed started to do that. I tried to work based on my memory and also, just because of format of this blog, I had to list those who has several real bugs reported in public. I did some corrections and additions in the list based on feedback I've got.

If I missed your name in the list or had written something about your work that is not correct, please, forgive me my old memory and correct me.

Next time I'll write about those great engineers who joined MySQL Support after me and became much better than I'd ever be able to become. Stay tuned!

For now just remember the following statement that I've used more than once in different discussions already:
"There are two kinds of people: people of MySQL and all other people. You can not judge them by the same rules."
I had not changed my mind since the first time I stated that while defending one of colleagues in internal discussion.

Saturday, January 9, 2016

"I'm Winston Wolf, I solve problems."

My (few) readers are probably somewhat tired of boring topics of metadata locks and gdb breakpoints that I discuss a lot this year, so for this weekend I decided to concentrate on something less technical but still important to me - the way I prefer to follow while providing support for MySQL.

Before I continue, it's time to add the explicit disclaimer: the views on how support engineer should work expressed below are mine alone and not those of my current (or any previous) employer. Specific case I describe may be entirely fictional and has nothing to do with any real life customer. I love thy customers in reality...

One of my favorite movies of all times is Pulp Fiction. Coincidentally, it was released in 1994, more or less at the same time when providing technical support started to become one of my regular job duties, not just a hobby or boring part of sysadmin job role I had to play even when not wanted. I had to provide support for the software I had written as soon as it had got first customer, then moved on to helping my colleagues (whom we developed software with together) with all kinds of technical problems they had, from proper coding style to linking Pro*C programs to, well, getting more disk space on NFS server that was actually my workstation. In 10 years or so this ended up with sending CV to MySQL AB and getting a Support Engineer job there (instead of a Developer one I applied for).

So, with this "support" role that I played already when I watched the "Pulp Fiction" for the first time, it was natural for me to find out that my favorite character there is "The Wolf". As soon as I started to provide MySQL Support, I've added the quote that is used as a title of this post to my LJ blog, as a motto. This person and his approach to "customers" of his very specific "service" (resolving all kinds of weird problems) looked ideal to me, and over years this had not changed. Let me remind you this great dialog with Vincent Vega (see the script for the context and details if you do not remember them by heart; I've fixed one typo there while quoting, bugs are everywhere, you know...):

               The Wolf and Jimmie turn, heading for the bedroom, leaving 
               Vincent and Jules standing in the kitchen.

                                     VINCENT
                              (calling after him)
                         A "please" would be nice.

               The Wolf stops and turns around.

                                     THE WOLF
                         Come again?

                                     VINCENT
                         I said a "please" would be nice.

               The Wolf takes a step toward him.

                                     THE WOLF
                         Set is straight, Buster. I'm not 
                         here to say "please."I'm here to 
                         tell you what to do. And if self-
                         preservation is an instinct you 
                         possess, you better fuckin' do it 
                         and do it quick. I'm here to help.
                         If my help's not appreciated, lotsa 
                         luck gentlemen.

                                     JULES
                         It ain't that way, Mr. Wolf. Your 
                         help is definitely appreciated.

                                     VINCENT
                         I don't mean any disrespect. I just 
                         don't like people barkin' orders at 
                         me.

                                     THE WOLF
                         If I'm curt with you, it's because 
                         time is a factor. I think fast, I 
                         talk fast, and I need you guys to 
                         act fast if you want to get out of 
                         this. So pretty please, with sugar 
                         on top, clean the fuckin' car.

Over the years of doing support I've found out how important it is to tell it straight and honestly from the very beginning: "I'm not here to say "please."I'm here to tell you what to do."

In services the approach is often the following: "The customer is always right". In reality, speaking about customers of technical services at least, it may NOT be so. Customer may be wrong with his ideas about the root cause of the problem, and (as the wiki page linked above says) can be dishonest, have unrealistic expectations, and/or try to misuse a software. What's even more important, customers are rarely right when they say how services should be provided for them.

All these:
"Can I speak on Skype with Valerii? ... Can I chat with him on Skype please? It's a lot easier ... and I have questions" 
may end up with a chat, or may end up with an email that was already sent while this chat with oncall engineer happened, explaining both possible root causes of the problem, asking followup questions to define the real root case, and listing next steps to pinpoint or workaround the problem.

As this is still a technical blog, the problem in the case I had in mind while writing the above, was a very slow query execution on MariaDB 10.x, where query had 80K IDs in the IN list and previously it was executed fast on Percona XtraDB Cluster 5.5.x. The range of root causes to suspect was wide enough initially, from Bug #20932 to Bug #76030 and maybe to some MariaDB specific bugs to search for, to disk I/O problems, lock waits (what if the SELECT query was executed from SERIALIZABLE transaction). So, I kept asking for more diagnostic outputs and insisting on getting them in emails, and yes, long outputs are better shared via emails! Sharing them in chat or, even more, discussing over phone, or seeing them over a shared desktop session (the approaches many customers insist on) is neither faster nor more convenient. I say what to execute, you do that, copy/paste the output and send email (or reply in the issue via web interface, if you prefer to paste there), "pretty please, with sugar on top".

As soon as evidence provided shown there is no locking or disk I/O problem, my investigation was concentrated around this important fact: it was fast on 5.5.x and is slow on MariaDB 10.x with the same data. What is the difference in these versions that matter? Most likely it's in the optimizer and new optimizations they have in MariaDB! 

Do I know all the MariaDB optimizations by heart? No, surely, I have to go read about them, check how proper optimizer switches are named and make up my mind about suggestions. Is it OK to do all these while hanging on phone with customer or chatting with him? Well, maybe, if customer prefers to listen to my loud typing and sounds in my neighborhood... I'd prefer NOT to listen to any of that, ever, and not to hang on the chat with 10 minutes in between messages. So, the only chat reply customer got was: "Please reply to his request and I'll have him follow up."

The last my request at this moment was simple:
"I do not see optimizer_switch set explicitly in your my.cnf, so I assume defaults there as https://mariadb.com/kb/en/mariadb/optimizer-switch/ shows. Can you, please, check if setting this:

set optimizer_switch='extended_keys=off';

before running the problematic query allows it to run faster?
"
Even though the bug that led me to this idea (after reading the details of what optimizations MariaDB provided and how they are controlled) is still "Open", and I had never been able to create a test case not depending on any customer data, I've seen the problem more than once. I think fast, and I type fast, so I shared this suggestion immediately (1h 20 minutes after customer started to describe the problem in chat, 43 minutes after customer provided all the outputs required in email). And you know what happened? Chat continued more or less like this:
"wow... optimizer_switch with extended_keys off worked... from 1000k+ seconds to ... 1 second ... rofl ...  so extended_keys=on by default ... it literally halted our database today almost ... thank you for your assistance ... i would have never of thought to change that... and you guys caught it, so props"
So, the immediate problem was resolved and, you know, the resolution started with my explicit refusal to join any chat until I see the evidence and outputs requested in emails. It also ended with email suggesting to switch off one specific optimization that is used by default and is known to me, the one pretending to be expert, from several previous cases (similar or not so much, as I never seen anything like this this happening on MariaDB before). It took 1 hour 20 minutes from initial problem statement communicated to problem resolved, and all this time I worked asynchronously and concurrently on this and few other issues, and had not said or typed a word to customer in chat.

This is how The Wolf solves problems: 
"I'm here to tell you what to do. And if self-preservation is an instinct you possess, you better fuckin' do it and do it quick. I'm here to help. If my help's not appreciated, lotsa luck gentlemen"
I do it the same way, and. IMHO, any expert should do it this way. If a customer always knows better what to do and how to communicate, why they ended up with a problem that brought them to me? 

I try to prove every point I make while working on problems, and I expect from the other side to apply the same level of efforts - they have to prove me the way they want issue handled is better than the one I prefer and suggest, if they think this is the problem. After that I'll surely do what's the best from them, knowing why do we both do it so. 

In reality, the problem usually lies elsewhere... Now, it's time to re-read my New Year wishes post.

Thursday, January 7, 2016

Exploring Metadata Locks with gdb - How One Can Use This?

In the previous post in this series I've concluded that metadata locks are acquired in "batches" and the function that implements this is called MDL_context::acquire_locks. Let's check quickly what it does to confirm where wait for metadata lock really happens. We need this to proceed finally from studying what locks are set and when (this is a long and complicated topic to spend time on out of general interest) to more practical topic: how to find the session that holds the blocking metadata lock in MySQL versions before 5.7.x.

I'll continue to use Percona Server 5.6.27 for now, just because I have it installed and have a source code at hand. So, MDL_context class is defined in sql/mdl.h file as follows:

/**
  Context of the owner of metadata locks. I.e. each server
  connection has such a context.
*/

class MDL_context
{
public:
  typedef I_P_List<MDL_ticket,
                   I_P_List_adapter<MDL_ticket,
                                    &MDL_ticket::next_in_context,
                                    &MDL_ticket::prev_in_context> >
          Ticket_list;

  typedef Ticket_list::Iterator Ticket_iterator;

  MDL_context();
...

  bool try_acquire_lock(MDL_request *mdl_request);
  bool acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout);
  bool acquire_locks(MDL_request_list *requests, ulong lock_wait_timeout);
...

  unsigned long get_lock_owner(MDL_key *mdl_key);...
private:
  THD *get_thd() const { return m_owner->get_thd(); }...

};

I've highlighted some of the functions that we may use later. Now, this is how MDL_context::aquire_locks() implementation looks like in mdl.cc:

/**
  Acquire exclusive locks. There must be no granted locks in the
  context.

  This is a replacement of lock_table_names(). It is used in
  RENAME, DROP and other DDL SQL statements.

  @param  mdl_requests  List of requests for locks to be acquired.

  @param lock_wait_timeout  Seconds to wait before timeout.

  @note The list of requests should not contain non-exclusive lock requests.
        There should not be any acquired locks in the context.

  @note Assumes that one already owns scoped intention exclusive lock.

  @retval FALSE  Success
  @retval TRUE   Failure
*/

bool MDL_context::acquire_locks(MDL_request_list *mdl_requests,
                                ulong lock_wait_timeout)
{

...
  for (p_req= sort_buf; p_req < sort_buf + req_count; p_req++)
  {
    if (acquire_lock(*p_req, lock_wait_timeout))
      goto err;
  }
  my_free(sort_buf);
  return FALSE;

err:
  /*
    Release locks we have managed to acquire so far.
    Use rollback_to_savepoint() since there may be duplicate
    requests that got assigned the same ticket.
  */
  rollback_to_savepoint(mdl_svp);
  /* Reset lock requests back to its initial state. */
...

  my_free(sort_buf);
  return TRUE;
}


Comments make me think that for some metadata locks we may see some different function called, but let's deal with exclusive ones for now, those that can be blocked. Now, the MDL_context::aquire_lock() we eventually call for each request:

/**
  Acquire one lock with waiting for conflicting locks to go away if needed.

  @param mdl_request [in/out] Lock request object for lock to be acquired

  @param lock_wait_timeout [in] Seconds to wait before timeout.

  @retval  FALSE   Success. MDL_request::ticket points to the ticket
                   for the lock.
  @retval  TRUE    Failure (Out of resources or waiting is aborted),
*/

bool
MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
{
  MDL_lock *lock;
  MDL_ticket *ticket= NULL;
  struct timespec abs_timeout;
  MDL_wait::enum_wait_status wait_status;
...

  /*
    Our attempt to acquire lock without waiting has failed.
    As a result of this attempt we got MDL_ticket with m_lock
    member pointing to the corresponding MDL_lock object which
    has MDL_lock::m_rwlock write-locked.
  */
  lock= ticket->m_lock;

  lock->m_waiting.add_ticket(ticket);

  /*
    Once we added a pending ticket to the waiting queue,
    we must ensure that our wait slot is empty, so
    that our lock request can be scheduled. Do that in the
    critical section formed by the acquired write lock on MDL_lock.
  */
  m_wait.reset_status();

  /*
    Don't break conflicting locks if timeout is 0 as 0 is used
    To check if there is any conflicting locks...
  */
  if (lock->needs_notification(ticket) && lock_wait_timeout)
    lock->notify_conflicting_locks(this);

  mysql_prlock_unlock(&lock->m_rwlock);

  will_wait_for(ticket);

  /* There is a shared or exclusive lock on the object. */
  DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");

  find_deadlock();

  struct timespec abs_shortwait;
  set_timespec(abs_shortwait, 1);
  wait_status= MDL_wait::EMPTY;

  while (cmp_timespec(abs_shortwait, abs_timeout) <= 0)
  {
    /* abs_timeout is far away. Wait a short while and notify locks. */
    wait_status= m_wait.timed_wait(m_owner, &abs_shortwait, FALSE,
                                   mdl_request->key.get_wait_state_name());

    if (wait_status != MDL_wait::EMPTY)
      break;
    /* Check if the client is gone while we were waiting. */
    if (! m_owner->is_connected())
    {
      /*
       * The client is disconnected. Don't wait forever:
       * assume it's the same as a wait timeout, this
       * ensures all error handling is correct.
       */
      wait_status= MDL_wait::TIMEOUT;
     break;
    }

    mysql_prlock_wrlock(&lock->m_rwlock);
    if (lock->needs_notification(ticket))
      lock->notify_conflicting_locks(this);
    mysql_prlock_unlock(&lock->m_rwlock);
    set_timespec(abs_shortwait, 1);
  }
...


So, here we check if our request is deadlocking and then we wait. It's clear that any session that is hanging while waiting for the metadata lock will have MDL_context::aquire_lock() in the backtrace.

To check this assumption let me set up a usual test with SELECT * FROM t1 executed in active transaction and TRUNCATE TABLE blocked. This is how it may look like in the SHOW PROCESSLIST:

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State                           | Info              | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
|  2 | root | localhost | test | Query   |  121 | Waiting for table metadata lock | truncate table t1 |         0 |             0 |
|  3 | root | localhost | test | Sleep   |    2 |                                 | NULL              |         1 |             0 |
|  4 | root | localhost | test | Query   |    0 | init                            | show processlist  |         0 |             0 |
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
3 rows in set (0.00 sec)

In general (and in this case) we may NOT be able to find a thread that is executing some statement for longer than out blocked session is waiting for the metadata lock. So, which one is the blocking one?

Let's try to find a thread that mentions  MDL_context::aquire_lock() in the backtrace:

[root@centos percona-server]# gdb -p `pidof mysqld` -ex "set pagination 0" -ex "thread apply all bt" -batch 2>/dev/null | grep 'MDL_context::acquire_lock'
#3  0x000000000064a74b in MDL_context::acquire_lock (this=0x7fc005ff5140, mdl_request=0x7fc00614e488, lock_wait_timeout=) at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:2380
#4  0x000000000064ae1b in MDL_context::acquire_locks (this=0x7fc005ff5140, mdl_requests=) at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:2500
[root@centos percona-server]#

Note that I've executed the command as root, because mysqld process is owned by root in this case.

So, we see exactly one backtrace with this call:

MDL_context::acquire_lock (this=0x7fc005ff5140, mdl_request=0x7fc00614e488, lock_wait_timeout=)

and from the code review we know that 0x7fc00614e488 is of type MDL_request *. We also know that 0x7fc005ff5140 is of type MDL_context * (this pointer in the method of that class). Now we can attach gdb to the mysqld running and try to see what we can do with that pointers:

[root@centos percona-server]# gdb -p `pidof mysqld`
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-83.el6)
...

(gdb) set $pmdlr=(MDL_request *)0x7fc00614e488
(gdb) p $pmdlr
$1 = (MDL_request *) 0x7fc00614e488
(gdb) p $pmdlr->key
$2 = {m_length = 9, m_db_name_length = 4,
  m_ptr = "\003test\000t1", '\000' <repeats 378 times>,
  static m_namespace_to_wait_state_name = {{m_key = 102,
      m_name = 0xb9eb8c "Waiting for global read lock", m_flags = 0}, {
      m_key = 103, m_name = 0xb9eba9 "Waiting for backup lock", m_flags = 0}, {
      m_key = 104, m_name = 0xb9ed68 "Waiting for schema metadata lock",
      m_flags = 0}, {m_key = 105,
      m_name = 0xb9ed90 "Waiting for table metadata lock", m_flags = 0}, {
      m_key = 106,
      m_name = 0xb9edb0 "Waiting for stored function metadata lock",
      m_flags = 0}, {m_key = 107,
      m_name = 0xb9ede0 "Waiting for stored procedure metadata lock",
      m_flags = 0}, {m_key = 108,
      m_name = 0xb9ee10 "Waiting for trigger metadata lock", m_flags = 0}, {
      m_key = 109, m_name = 0xb9ee38 "Waiting for event metadata lock",
      m_flags = 0}, {m_key = 110, m_name = 0xb9ebc1 "Waiting for commit lock",
      m_flags = 0}, {m_key = 79, m_name = 0xb9295a "User lock", m_flags = 0}, {
      m_key = 111, m_name = 0xb9ebd9 "Waiting for binlog lock", m_flags = 0}}}
(gdb) p &($pmdlr->key)
$3 = (MDL_key *) 0x7fc00614e4a8


So, we can get a pointer to the MDL_key. Now, in MDL_context class we had seen the following method:

  unsigned long get_lock_owner(MDL_key *mdl_key);

that returns the id of the owning thread, and lucky we are, we have a pointer to MDL_context, so we can refer to this function and call it if needed:

(gdb) set $pmdlc=(MDL_context *)0x7fc005ff5140
(gdb) p $pmdlc
$4 = (MDL_context *) 0x7fc005ff5140
(gdb) p $pmdlc->get_lock_owner(&($pmdlr->key))
$5 = 3


Now, what's that value printed, 3? It's the id of thread in the SHOW PROCESSLIST that holds the blocking metadata lock. So, we quit from gdb:

(gdb) q
A debugging session is active.

        Inferior 1 [process 3003] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/sbin/mysqld, process 3003


and then we know what thread to kill to release the blocking lock and let TRUNCATE to proceed:

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State                           | Info              | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
|  2 | root | localhost | test | Query   | 1291 | Waiting for table metadata lock | truncate table t1 |         0 |             0 |
|  3 | root | localhost | test | Sleep   | 1172 |                                 | NULL              |         1 |             0 |
|  4 | root | localhost | test | Query   |    0 | init                            | show processlist  |         0 |             0 |
+----+------+-----------+------+---------+------+---------------------------------+-------------------+-----------+---------------+
3 rows in set (0.00 sec)

mysql> kill 3;
Query OK, 0 rows affected (0.05 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State | Info             | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
|  2 | root | localhost | test | Sleep   | 1305 |       | NULL             |         0 |             0 |
|  4 | root | localhost | test | Query   |    0 | init  | show processlist |         0 |             0 |
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
2 rows in set (0.00 sec)


So, from now on you know how to find out in MySQL 5.6 the blocking session/thread for any session that is waiting on metadata with just gdb and some grep.

This is not the only way, you can get the same result from different functions in different MDL-related classes. Your findings may depend on what part of the code you traced or read first, but essentially that's it: you can easily find a blocking thread for any waiting MDL lock request, using just a couple of gdb commands! No need to kill them one by one in a hope.

For any MySQL DBA gdb can be the tool useful in their routine work, NOT only while studying core dumps.

In the following blog posts in this series I'll get back to studying source code and will try to work with MySQL 5.7 in a similar way. Stay tuned!

Wednesday, January 6, 2016

Exploring Metadata Locks with gdb - Double Checking the Initial Results

Some results in my initial post in this series led me to questions that I'll try to answer here. First of all, I noted that SELECT from a single table ended up with just one metadata lock request:

(gdb) b MDL_request::init
Breakpoint 1 at 0x648f13: file /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc, line 1266.
Breakpoint 2 at 0x648e70: file /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc, line 1245.
warning: Multiple breakpoints were set.
Use the "delete" command to delete unwanted breakpoints.
(gdb) c
Continuing.
[Switching to Thread 0x7ff224c9f700 (LWP 2017)]

Breakpoint 2, MDL_request::init (this=0x7ff1fbe425a8,
    mdl_namespace=MDL_key::TABLE, db_arg=0x7ff1fbe421c8 "test",
    name_arg=0x7ff1fbe421d0 "t1", mdl_type_arg=MDL_SHARED_READ,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {


MDL_SHARED_READ lock on the table is expected, we read it after all. Let's check with backtrace where this request happens:






(gdb) bt
#0  MDL_request::init (this=0x7ff1fbe425a8, mdl_namespace=MDL_key::TABLE,
    db_arg=0x7ff1fbe421c8 "test", name_arg=0x7ff1fbe421d0 "t1",
    mdl_type_arg=MDL_SHARED_READ, mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x00000000006d3033 in st_select_lex::add_table_to_list (this=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:7479
#2  0x000000000077eb6d in MYSQLparse (YYTHD=0x7ff1fbb65000)
    at /var/lib/jenkins/jobs/percona-server-5.6-source-tarballs/workspace/sql/sql_yacc.yy:10888
#3  0x00000000006decef in parse_sql (thd=0x7ff1fbb65000,
    parser_state=0x7ff224c9e130, creation_ctx=0x0)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:9005
#4  0x00000000006df1b1 in mysql_parse (thd=0x7ff1fbb65000, rawbuf=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:6878
#5  0x00000000006e0d1f in dispatch_command (command=<value optimized out>,
    thd=0x7ff1fbb65000, packet=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:1442
#6  0x00000000006ad692 in do_handle_one_connection (thd_arg=Unhandled dwarf expression opcode 0xf3
)
...


So, it happens as soon as we noted a table to read from while parsing the query text. What surprised me initially is no other MDL lock requests, nothing at schema level, for example. I expected that metadata locks should prevent dropping the schema when I read from some table there, but had not seen the schema-level lock for this.

To get more details on this and try to make sure I do not miss anything by studying a corner case, I've added a row to the table and then tried to do the same SELECT in explicit transaction, and (after SELECT completed, but transaction remained active) I tried to DROP DATABASE from the second session while this transaction was still active. (This test will also let us understand what metadata locks are requested for DROP DATABASE, by the way.) I've got the following in gdb:

[New Thread 0x7ff224c5e700 (LWP 2048)]
[Switching to Thread 0x7ff224c5e700 (LWP 2048)]

Breakpoint 2, MDL_request::init (this=0x7ff224c5a040,
    mdl_namespace=MDL_key::GLOBAL, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff224c5a040, mdl_namespace=MDL_key::GLOBAL,
    db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x00000000007d1ed7 in lock_schema_name (thd=0x7ff1fbbb5000,
    db=0x7ff1e581d0b0 "test")
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/lock.cc:794
#2  0x00000000006b03d4 in mysql_rm_db (thd=0x7ff1fbbb5000,
    db=0x7ff1e581d0b0 "test", if_exists=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_db.cc:787
...

---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a1f0,
    mdl_namespace=MDL_key::BACKUP, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a3a0,
    mdl_namespace=MDL_key::SCHEMA, db_arg=0x7ff1e581d0b0 "test",
    name_arg=0xba2ae4 "", mdl_type_arg=MDL_EXCLUSIVE,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff224c5a3a0, mdl_namespace=MDL_key::SCHEMA,
    db_arg=0x7ff1e581d0b0 "test", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_EXCLUSIVE, mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x00000000007d1fb2 in lock_schema_name (thd=0x7ff1fbbb5000,
    db=0x7ff1e581d0b0 "test")
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/lock.cc:801
#2  0x00000000006b03d4 in mysql_rm_db (thd=0x7ff1fbbb5000,
    db=0x7ff1e581d0b0 "test", if_exists=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_db.cc:787
#3  0x00000000006daaa2 in mysql_execute_command (thd=0x7ff1fbbb5000)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:4371
#4  0x00000000006df518 in mysql_parse (thd=0x7ff1fbbb5000, rawbuf=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_parse.cc:6972
...

---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff1e58313b0,
    mdl_namespace=MDL_key::TABLE, db_arg=0x7ff1e5831570 "test",
    name_arg=0x7ff1e5831575 "t1", mdl_type_arg=MDL_EXCLUSIVE,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff1e58313b0, mdl_namespace=MDL_key::TABLE,
    db_arg=0x7ff1e5831570 "test", name_arg=0x7ff1e5831575 "t1",
    mdl_type_arg=MDL_EXCLUSIVE, mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x00000000006b0857 in find_db_tables_and_rm_known_files (
    thd=0x7ff1fbbb5000, db=0x7ff1e581d0b0 "test", if_exists=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_db.cc:1108
#2  mysql_rm_db (thd=0x7ff1fbbb5000, db=0x7ff1e581d0b0 "test", if_exists=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_db.cc:812
...


Now it's clear what happens, eventually we have to drop the table while dropping the schema, and here we surely hit a blocking metadata lock that SELECT set! Let's continue:

---Type <return> to continue, or q <return> to quit---q
Quit

(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff1e581ece0,
    mdl_namespace=MDL_key::SCHEMA, db_arg=0x7ff1e5831570 "test",
    name_arg=0xba2ae4 "", mdl_type_arg=MDL_INTENTION_EXCLUSIVE,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff1e581ece0, mdl_namespace=MDL_key::SCHEMA,
    db_arg=0x7ff1e5831570 "test", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x000000000068da14 in lock_table_names (thd=0x7ff1fbbb5000,
    tables_start=0x7ff1e5831010, tables_end=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:5033
#2  0x00000000006b05df in mysql_rm_db (thd=0x7ff1fbbb5000,
    db=0x7ff1e581d0b0 "test", if_exists=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_db.cc:834
...


So, while our request for MDL_EXCLUSIVE lock for the table surely could not be satisfied, server continued with some further requests:

---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a1f0,
    mdl_namespace=MDL_key::GLOBAL, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a3a0,
    mdl_namespace=MDL_key::BACKUP, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.


and only at this stage it hanged (waiting for the transaction where SELECT happened to complete). As soon as I committed there:

Breakpoint 2, MDL_request::init (this=0x7ff224c5a260,
    mdl_namespace=MDL_key::TABLE, db_arg=0xb925ef "mysql",
    name_arg=0xb94906 "proc", mdl_type_arg=MDL_SHARED_READ,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a360,
    mdl_namespace=MDL_key::TABLE, db_arg=0xb925ef "mysql",
    name_arg=0xb94906 "proc", mdl_type_arg=MDL_SHARED_WRITE,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {




So, we tried to read and then maybe change the mysql.proc table (to drop procedures created in this database, honestly I was not sure if there was any one there at the moment). Next:

(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c59cb0,
    mdl_namespace=MDL_key::GLOBAL, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c599c0,
    mdl_namespace=MDL_key::BACKUP, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5a250,
    mdl_namespace=MDL_key::TABLE, db_arg=0xb925ef "mysql",
    name_arg=0xc111a8 "event", mdl_type_arg=MDL_SHARED_WRITE,
    mdl_duration_arg=MDL_TRANSACTION)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.




That is, some lock requests and we try to delete events in this database from mysql.event table obviously. Let's continue:

Breakpoint 2, MDL_request::init (this=0x7ff224c59900,
    mdl_namespace=MDL_key::GLOBAL, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff224c59900, mdl_namespace=MDL_key::GLOBAL,
    db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x000000000068c13a in open_table (thd=0x7ff1fbbb5000,
    table_list=0x7ff224c59eb0, ot_ctx=0x7ff224c59c10)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:2895
#2  0x0000000000694585 in open_and_process_table (thd=0x7ff1fbbb5000,
    start=0x7ff224c59e48, counter=0x7ff224c59e50, flags=2048,
    prelocking_strategy=0x7ff224c59ea0)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:4797
#3  open_tables (thd=0x7ff1fbbb5000, start=0x7ff224c59e48,
    counter=0x7ff224c59e50, flags=2048, prelocking_strategy=0x7ff224c59ea0)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:5304
#4  0x0000000000694d74 in open_and_lock_tables (thd=0x7ff1fbbb5000,
    tables=0x7ff224c59eb0, derived=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:5960


#5  0x000000000085039d in open_and_lock_tables (thd=0x7ff1fbbb5000, lock_type=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.h:477
#6  Event_db_repository::open_event_table (thd=0x7ff1fbbb5000, lock_type=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/event_db_repository.cc:619
#7  0x0000000000850bfc in Event_db_repository::drop_schema_events (this=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/event_db_repository.cc:1011---Type <return> to continue, or q <return> to quit---q
Quit
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c59610,
    mdl_namespace=MDL_key::BACKUP, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) bt
#0  MDL_request::init (this=0x7ff224c59610, mdl_namespace=MDL_key::BACKUP,
    db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_STATEMENT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
#1  0x00000000007d1dfe in Global_backup_lock::acquire_protection (
    this=0x7ff1fbbb6a08, thd=0x7ff1fbbb5000, duration=MDL_STATEMENT,
    lock_wait_timeout=31536000)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/lock.cc:1225
#2  0x000000000068ce20 in open_table (thd=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:3258
#3  0x0000000000694585 in open_and_process_table (thd=0x7ff1fbbb5000,
    start=0x7ff224c59e48, counter=0x7ff224c59e50, flags=2048,
    prelocking_strategy=0x7ff224c59ea0)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:4797
#4  open_tables (thd=0x7ff1fbbb5000, start=0x7ff224c59e48,
    counter=0x7ff224c59e50, flags=2048, prelocking_strategy=0x7ff224c59ea0)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:5304
#5  0x0000000000694d74 in open_and_lock_tables (thd=0x7ff1fbbb5000,
    tables=0x7ff224c59eb0, derived=Unhandled dwarf expression opcode 0xf3
)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/sql_base.cc:5960

...
---Type <return> to continue, or q <return> to quit---q
 at /uQuit
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c5ab00,
    mdl_namespace=MDL_key::BINLOG, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_INTENTION_EXCLUSIVE, mdl_duration_arg=MDL_EXPLICIT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

We ended up with writing to the binary log and DROP DATABASE was completed.

Let's summarize this experience. First of all, it seems in current implementation there is no need to set metadata locks at schema level to prevent dropping the database we read from with SELECT - table level metadata lock will eventually block the DROP DATABASE request. If we have many tables in the database this may happen not at the very first one, so we can drop some tables before getting blocked. This is something to study later. I'd really prefer DROP DATABASE to be atomic and do not even start if it can not complete at the moment. See Bug #79610 also.

We see table-level metadata lock requests for mysql.proc and mysql.events tables when DROP DATABASE is executed. This is expected and reasonable - we can NOT drop the database (schema) if there are some stored functions, procedures or events are defined there that are used.

We had also noted that even if some metadata lock is requested but can not be obtained, processing may continue for some time until stopped, and then processing resumes when we finally get the required metadata lock.

This is the code to study in sql/lock.cc:

 760 /**
 761   Obtain an exclusive metadata lock on a schema name.
 762
 763   @param thd         Thread handle.
 764   @param db          The database name.
 765
 766   This function cannot be called while holding LOCK_open mutex.
 767   To avoid deadlocks, we do not try to obtain exclusive metadata
 768   locks in LOCK TABLES mode, since in this mode there may be
 769   other metadata locks already taken by the current connection,
 770   and we must not wait for MDL locks while holding locks.
 771
 772   @retval FALSE  Success.
 773   @retval TRUE   Failure: we're in LOCK TABLES mode, or out of memory,
 774                  or this connection was killed.
 775 */
 776
 777 bool lock_schema_name(THD *thd, const char *db)
 778 {
 779   MDL_request_list mdl_requests;
 780   MDL_request global_request;
 781   MDL_request backup_request;
 782   MDL_request mdl_request;
 783
 784   if (thd->locked_tables_mode)
 785   {
 786     my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
 787                ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
 788     return TRUE;
 789   }
 790
 791   if (thd->global_read_lock.can_acquire_protection())
 792     return TRUE;
 793   global_request.init(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE,
 794                       MDL_STATEMENT);
 795
 796   if (thd->backup_tables_lock.abort_if_acquired())
 797     return true;
 798   thd->backup_tables_lock.init_protection_request(&backup_request,
 799                                                   MDL_STATEMENT);
 800
 801   mdl_request.init(MDL_key::SCHEMA, db, "", MDL_EXCLUSIVE, MDL_TRANSACTION)     ;
 802
 803   mdl_requests.push_front(&mdl_request);
 804   mdl_requests.push_front(&backup_request);
 805   mdl_requests.push_front(&global_request);
 806
 807   if (thd->mdl_context.acquire_locks(&mdl_requests,
 808                                      thd->variables.lock_wait_timeout))
 809     return TRUE;
 810
 811   DEBUG_SYNC(thd, "after_wait_locked_schema_name");
 812   return FALSE;


From this code we see that actually we acquire metadata locks in batches(!). This explaines why we had not stopped at our first "blocked" request immediately, and complicates tracing in gdb. We also see that we have some mdl_context object in THD structure, and we may use this later to study pending requests per thread. One day I'll start setting breakpoints at MDLcontext::acquire_locks as well.

It's also more or less clear what MDL_key::GLOBAL namespace is used for - this is a metadata lock requested by FLUSH TABLES WITH READ LOCK global lock, and thus we check it for any "write" operation, even before we try to do it. If I execute FLUSH TABLES WITH READ LOCK, I get the following in my gdb session with a breakpoint set:

[Switching to Thread 0x7ff224c9f700 (LWP 2017)]

Breakpoint 2, MDL_request::init (this=0x7ff224c9c9f0,
    mdl_namespace=MDL_key::GLOBAL, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_SHARED, mdl_duration_arg=MDL_EXPLICIT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.

Breakpoint 2, MDL_request::init (this=0x7ff224c9c9f0,
    mdl_namespace=MDL_key::COMMIT, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_SHARED, mdl_duration_arg=MDL_EXPLICIT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {
(gdb) c
Continuing.


and then it's completed. Now, on UNLOCK TABLES nothing happens at MDL level.

The last topic I'd like to discuss in this post are metadata locks with namespace defined like this: mdl_namespace=MDL_key::BACKUP, "backup locks". Manual for 5.7 does not mention them, but they still exist in the code even there:

[openxs@centos ~]$ grep -rn MDL_key::BACKUP ~/git/mysql-server/*
[openxs@centos ~]$ echo $?
1

In MySQL we see the following in mdl.h:

  enum enum_mdl_namespace { GLOBAL=0,
                            TABLESPACE,
                            SCHEMA,
                            TABLE,
                            FUNCTION,
                            PROCEDURE,
                            TRIGGER,
                            EVENT,
                            COMMIT,
                            USER_LEVEL_LOCK,
                            LOCKING_SERVICE,
                            /* This should be the last ! */
                            NAMESPACE_END };


So, backup locks is the unique feature of Percona Server, explained in the manual. It is implemented with MDL locks. As soon as we run LOCK TABLES FOR BACKUP, we see in gdb session:

Breakpoint 2, MDL_request::init (this=0x7ff224c9ca90,
    mdl_namespace=MDL_key::BACKUP, db_arg=0xba2ae4 "", name_arg=0xba2ae4 "",
    mdl_type_arg=MDL_SHARED, mdl_duration_arg=MDL_EXPLICIT)
    at /usr/src/debug/percona-server-5.6.27-76.0/sql/mdl.cc:1245
1245    {


This reminded me about the difference Percona Server can make, so I'll try to re-check my results with MySQL 5.6 next time as well. Stay tuned!