Backing up permissions for individual databases

Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump:

The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to help you out.

Then, re-loading the permissions onto the new server is simple:

All up, a few queries to account for everything, but pretty easy to include in your backup/restore process. For further development, you could put the database list in a variable, so that you only need to change it on one line, rather than 6.

A Brief Introduction to MySQL Performance Tuning

Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage.

Server Variables

For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory that InnoDB uses for caching data, indexes and various pieces of information about your database. The bigger, the better. If you can cache all of your data in memory, you’ll see significant performance improvements.

For MyISAM, there is a similar buffer defined by key_buffer_size, though this is only used for indexes, not data. Again, the bigger, the better.

Other variables that are worth investigating for performance tuning are:

query_cache_size – This can be very useful if you have a small number of read queries that are repeated frequently, with no write queries in between. There have been problems with too large a query cache locking up the server, so you will need to experiment to find a value that’s right for you.

innodb_log_file_size – Don’t fall into the trap of setting this to be too large. A large InnoDB log file group is necessary if you have lots of large, concurrent transactions, but comes at the expense of slowing down InnoDB recover, in event of a crash.

sort_buffer_size – Another one that shouldn’t be set too large. Peter Zaitsev did some testing a while back showing that increasing sort_buffer_size can in fact reduce the speed of the query.

Server Hardware

There are a few solid recommendations for improving the performance of MySQL by upgrading your hardware:

  • Use a 64-bit processor, operating system and MySQL binary. This will allow you to address lots of RAM. At this point in time, InnoDB does have issues scaling past 8 cores, so you don’t need to go out of your way to have lots of processors.
  • Speaking of RAM, buy lots of it. Enough to fit all of your data and indexes, if you can.
  • If you can’t fit all of your data into RAM, you’ll need fast disks, RAID if you can. Have multiple disks, so you can seperate your data files, OS files and log files onto different physical disks.

Query Tuning

Finally, though probably the most important, we look at tuning queries. In particular, we make sure that they’re using indexes, and they’re running quickly. To do so, turn on the Slow Query Log for a day, with log_queries_not_using_indexes enabled as well. Run the resulting log through mysqldumpslow, which will produce a summary of the log. This will help you prioritize which queries to tackle first. Then, you can use EXPLAIN to find out what they’re doing, and adjust your indexes accordingly.

Have fun!

Don’t Quote Your Numbers

It’s a fairly simple rule, and something that should be obeyed for your health and sanity.

There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.

Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:

Note that you only run into it when you quote multiple large numbers.

Anyway, the long and the short of this post is: if at all possible, don’t quote numbers. MySQL will love you for it.

The \G modifier in the MySQL command line client

A little publicized, but exceedingly useful feature of the MySQL command line client is the \G modifier. It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’.

For example, checking the master status:

Now try this for the much larger SHOW SLAVE STATUS. Or for the enormous SHOW ENGINE INNODB STATUS.

As you can see, this is a handy option to make your console output much easier to read.

Our Valve Overlords

So, it seems that Valve Software are yet again trying to stop people from gaining new weapons in Team Fortress 2. The Scout update was released yesterday, and people want to try out the new weapons a quickly as possible. As per normal, the targets were those who used the Steam Achievement Manager.

Dear Valve, here’s a hint: if I wanted to grind away for hours to get new weapons, I’d be playing World of Warcraft.

As with last time, only a handful of people are reporting their weapons being taken away from them. This isn’t a deterrent, it barely rates as news.

Valve, I implore you, don’t go down this road. We know you’re trying to encourage people to play more, that you want to reward regular players. The fact is, not all of us have copious quantities of spare time to devote to playing each class. We just want to try out the new weapons, have a bit of a mess around, then go about our lives.

A recurring comment is that you want people to gain achievements through their regular game play, that it should come as a surprise. I ask you, then, what is more in line with your philosophy: unlocking just the weapons using the Steam Achievement Unlocker, or grinding them out on achievement servers, blowing the fun of the achievements on repetitive work, rather than fun?

For reference, I used the unlocker, and I still have my weapons. Same as every other pack.

And for those wondering why I’m posting this on my blog that almost certainly isn’t being read by the TF2 team, it’s just a public copy of a similar email I’ve sent to Valve. They’ve been good about listening to public feedback in the past, I’m hoping that this time is no exception. If you feel the same, send an email to Gabe Newell.