Fed Square Microbreweries Showcase

Yesterday, I went to the Microbreweries Showcase, held at Federation Square. With 17 breweries showing their product, there were plenty to try from. Here are my notes from the variety I tasted:

Brewery Brew Notes
Arctic Fox Brewery Ice Cap Lager Light flavour, with a slightly fruity aftertaste. Drinkable, but not particularly exciting.
Bridge Road Brewers Dark Ale A solid porter. Nice smoky flavour.
Buckley’s Beers Pilz Lager Very sweet. Easy to drink.
Original Ale Sweet, light flavour.
Coldstream Brewery Cider Boring. Really unimpressive. Tasted like watered down apple juice.
Grand Ridge Brewery Natural Blonde A sweet wheat beer, with a hinte of orange. An excellent summer beer.
Holgate Brewhouse White Ale A clove flavour, reminded me of spiced wine.
Big Red Pilsner Brilliant, rich red colour. Boring flavour.
Temptress Dark Ale A rich porter, made with vanilla beans and Dutch cocoa. Could work even as a dessert beer.
Matilda Bay Brewing Co Sebastian Reserve Dunkelweizen Sweet and dark, with a hint of apple. Tasty.
Mildura Brewery Mallee Bull Brilliant colour, uninspiring flavour.
Mountain Goat Brewery Fancy Pants Amber Ale Similar to their Hightail Ale, but not quite as strong a flavour. Slighty sweet, slightly spiced. Smelled of celery, for some unknown reason.
Prickly Moses – Otway Estate Summer Ale Bleh. Reminds me of Carlton Draught.
Red Duck Brewery Pale Ale Light flavour. Nice summer beer.
Honey Porter Light porter flavour, strong honey flavour. Nice, but similar to Beez Neez, I couldn’t drink much of it.
Southern Bay Brewing Co Effen Premier Lager A fairly standard lager. Good to drink, but nothing exciting.
Sweetwater Brewing Company Golden Bitter Tastes like it should have flavour, but has been watered down instead.
Temple Brewing Company Saison Good flavour, reminded me of Hoegaarden. A hint of cloves.
The 3 Ravens Brewing Co Dark Smoke Beer An excellent stout. Traditional, tasty.
2 Brothers Brewery Growler An American brown ale. Nice, but nothing special. Kind of a light porter.
Three Troupers Pilsner Light colour and flavour, like a Pilsner should be. Small bite in the aftertaste to keep things interesting.

All up, a good variety from some of Australia’s microbreweries. Particularly worth mentioning is Holgate’s Temptress Dark Ale, which is probably the best chocolate beer I’ve had. Buckley’s Beers also gets a second mention, for making my two favourite beers for the night, though the Original Ale just beats the Pilz Lager.

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.