Category Archives: Proven Scaling

Making connections more manageable

For the past few weeks off and on, as part of Proven Scaling‘s project to improve the MySQL server, I’ve been helping Joel Seligstein to really dig into the MySQL source code and add some features, in preparation for a much bigger feature coming up (more on that at a future date). He has now finished three smaller projects that have been on Proven Scaling’s and my own to-do list for quite some time: SET CONNECTION STATUS status, KILL connection_id WITH QUERY query_id, and SHOW ... FOR CONNECTION connection_id.1

SET CONNECTION STATUS

This patch adds a new SET CONNECTION STATUS status command, which allows each session to set a status which will be shown in a new Status column in SHOW FULL PROCESSLIST.

This allows a administrators to gain a bit more insight into complex multi-tier architectures, by having essentially a comment for each database connection. In SET CONNECTION STATUS, the status argument may be a complex expression, so CONCAT() and other string manipulations may be used. The current connection status may be retrieved with CONNECTION_STATUS(). Some things which I could imagine putting in the connection status are:

  • in pool — See at a glance which connections are idle in the connection pool, and which are checked out.
  • GET /foo.php — Easily see what request each connection is responsible for.
  • apache pid 672 — Allow you to easily correlate activity on a given server with activity on MySQL, without having to use netstat and friends to track things down.

Of course, there are many more creative people than me to figure out things to do with this useful feature!

KILL thread_id WITH QUERY query_id

In order to make this command useful, the query_id first had to be exposed in SHOW FULL PROCESSLIST. When WITH QUERY query_id is specified in a KILL command, KILL checks that the connection is still executing the query_id you’ve specified (and locks to ensure that it does not start a new query) before killing it. This solves a well-known race condition between SHOW PROCESSLIST and KILL, where the connection may have moved on to a potentially dangerous query to kill, such as a non-transactional UPDATE.

SHOW ... FOR CONNECTION connection_id

This patch extends the SHOW VARIABLES and SHOW STATUS commands with a FOR CONNECTION connection_id clause, which allows a user with either the same credentials as the connection, or with the SUPER privilege to view the connection’s status and variables.

All of these features will be great for debugging production systems, where it can be difficult or impossible to get any insight into what is happening at any given moment in time.

Thanks, Joel, for the hard work!

1 All three patches are against 5.0.26.

MySQL Camp Free Ride goes to Adam Ritter

And, the winner is……

Adam Ritter, of Nashville, Tennessee has been selected by Proven Scaling for a free ride to MySQL Camp!

Adam’s enthusiasm and very quick (within the first few hours of my original post), yet courteous and complete entry impressed us. His excitement to learn, and professionalism sealed the deal. Here’s an excerpt from his entry:

My name is Adam Ritter and I’m a recent Computer Science graduate from UT Knoxville. I’m currently looking for a job and I realized that being a DBA is what I want to do. I had started working with MySQL back in a course on Database Design and Management course I took in school. This course started the fire inside of me but unfortunately, it was over all too soon and I was left wanting to learn more. I’ve read through every intermediate MySQL source I could find, but I’m looking for as much advanced and expert level topics as I can find. I’ve worked on projects utilizing MySQL and have grown very fond of it.

If you’re attending MySQL Camp in a few weeks time, be sure to say hello to Adam!

Thanks, everyone! Keep an eye on my blog early next year for a similar offer for MySQL Conference & Expo 2007!

Want a Free Ride to MySQL Camp?

Have you heard of MySQL Camp at Google HQ in Mountain View, California, November 10-12?

Want to go? Can’t go because it’s out in sunny (ha, ha) California, hundreds or thousands of miles/kilometers from where you live?

Maybe you’re a bright student, or maybe you’re currently unemployed, or maybe you’ve got a job, but you just can’t afford to jet out to Mountain View for the weekend. Maybe you contribute code, bug fixes, tools; maybe you run a MySQL Meetup, and just can’t bear the airfare.

Proven Scaling would like to sponsora one such person to attend MySQL Camp! We’ll pay for your airfare and hotel—meals and transportation are on you. If you think you are qualified, you live somewhere in the USA or Canadab and you know you can make the trip, send us (with “MySQL Camp Free Ride” in the subject) the following information:

  • Your full, real name
  • Your blog or website, if any
  • Your company, school, or organization, if any
  • Your location, and closest airport
  • Any contributions you’ve made to MySQL or the MySQL community in the form of code, bug fixes, time, or otherwise
  • Why you feel you are the best or most qualified person for Proven Scaling to sponsor to MySQL Camp
  • Anything else you’d us like to know about yourself as we consider who to sponsor

However, you’d better hurry as you have only the next 60 hours or so to submit your proposal — no proposals will be accepted after midnight Pacific Daylight Time (GMT-8), Friday, October 20, 2006.

Good luck!

Update: Extended (and clarified) the deadline to midnight Friday instead of Thursday morning. Just to be clear: anyone is eligible, not just students.

a And, we’d encourage other companies attending or sponsoring MySQL Camp to do the same!

b If you live elsewhere, such as Europe, and you think you can get to the Bay Area fairly cheaply (lets say, under 1000 USD or so), or if you are just super qualified, feel free to send us your proposal anyway, and include how you propose to get to Mountain View cost effectively.

Leaving Yahoo!; storage, consulting

As many of you know already, I’ve decided to leave Yahoo!. Today was my last day. I’ve had a great time at Yahoo! and worked with a bunch of brilliant people. I will miss you all!

   

Where am I headed? That’s actually a complicated question. I’ll be working part time at Pivot3, a mature startup that’s doing great things in the storage industry. Contact me if you’re interesting in cutting storage costs, getting some amazing management tools, and some awesome performance. I would love to tell you more! MySQL will absolutely rock on this platform.

Yes, I said part time. I have a startup of my own, Proven Scaling, providing advanced MySQL consulting and training services. Looking to get more performance, scalability, or reliability out of MySQL? Need help with replication? Designing a database solution that really scales? Need help interviewing a candidate for a MySQL position? We can help.