MySQL Performance Blog

For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.

With this new functionality, it becomes possible for mk-query-digest to stand on the sidelines and watch queries fly by over TCP. It is only an observer on the sidelines: it is NOT a man in the middle like mysql-proxy, so it has basically zero impact on the running server (tcpdump is very efficient) and zero impact on the query latency. There are some unique challenges to watching an entire server’s traffic, but we’ve found ways to solve those. Some of them are harder than others, such as making sense of a conversation when you start listening in the middle. In general, it’s working very well. We can gather just about every bit of information about queries that mysql-proxy can, making this a viable way to monitor servers without the disadvantages of a proxy. In theory, we can gather ALL the same information, but in practice we are going for the 95% case.

[read more...]

The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for “mysql convert charset to utf8″ results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I’ll outline those approaches here and show why they don’t work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.

[read more...]

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for example).

The benefit is that you have another server with all the same data, up and running, ready to serve queries. In theory, it’s a truly hot standby (stay with me — that’s not really guaranteed). You don’t get this with shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby (passive) master for serving some SELECT queries, taking backups, etc as usual. However, if you do this you actually compromise your high-availability plan a little, because you can mask the lack of capacity that will result when one of the servers is down and you have to rely on just one server to keep everything on its feet.

[read more...]

These days I’m working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven’t worked much with stored procedures, I though it’s going to be a piece of cake. In the end - it was, but there’s a catch.

[read more...]

Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right now. Either way the data is out and ready to save someone’s life (or job at least). Unfortunately taking backup does not come free of any cost. On the contrary, it’s more like doing very heavy queries against each table in the database when mysqldump is used or reading a lot of data when copying physical files, so the price may actually be rather high. And the more effectively the server resources are utilized, the more that becomes a problem.
[read more...]

There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.

I don’t know exactly the percentage, but quite a few of the servers I take a look at have been “optimized” with some tuning primer or question-and-answer script that spits out “optimal” parameters for my.cnf.

[read more...]

Daniel Nichter, the author of several very useful MySQL tools, has joined with Percona to continue to improve Maatkit — the “other” toolkit we rely on daily. Daniel is a skilled Perl programmer who understands MySQL. He is in a perfect position to move the project forward on a more regular basis than I’ve been able to commit time to since I’ve taken on a leadership role in Percona.

Right now he’s working on an unreleased tool we will use to make our performance audits more efficient. As a result, we’ll be able to deliver more value to our customers and assess servers more quickly in the event of an emergency. (By the way, if you’d like to sponsor features you need for a Maatkit tool, contact us and we’ll be glad to discuss it with you.)

Welcome, Daniel! And a toast to Maatkit’s continued improvement and usefulness!

How many of you use the mysql command-line client?  And did you know about the pager command you can give it?  It’s pretty useful.  It tells mysql to pipe the output of your commands through the specified program before displaying it to you.

Here’s the most basic thing I can think of to do with it: use it as a pager.  (It’s scary how predictable I am sometimes, isn’t it?)

[read more...]

There are some tools we commonly use doing performance review and optimization and we often ask each other where that particular stuff is located on the web or what is exactly name of the command what does that.

Initially I thought creating internal Percona Wiki page, but thought there is no reason this information should not be public instead.

So now you can find our favorite MySQL Performance Review Tools on the site.

This is just list of tools which came from the top of my head and I’m sure it is far from complete. We will extend it and we would like to hear your suggestions on what else we should add to it.

In this list we focused on Open Source tools which are helpful for “in time” performance audit - for example you see no graphing software in this list or any commercial offerings.

Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt - adding/dropping columns or indexes, changing data type, converting data to different character set - MySQL master-master replication especially accompanied by MMM can be very handy to do the changes with virtually no downtime.
[read more...]