Gig Of The Year: Pillowtalk

I know, it’s pretty early on in the year to be making that claim. But, I think it’s justified.

First of all, the performers:

  • Tim Freedman, of The Whitlams
  • Pinky Beecroft, formerly of Machine Gun Fellatio, now of Pinky Beecroft and the White Russians
  • Dave McCormack, formerly of Custard, now of Dave McCormack and the Polaroids
  • Perry Keyes
  • Supported by Bernie Hayes


The gig was set at The Basement in Circular Quay on Saturday the 21st of March, 2009. Eventually called Pillowtalk, it was originally going to be called 40-something, Sexy (a reference to The Whitlams’ No Aphrodisiac), until Bernie turned 50.

Tim, Pinky and Dave are amongst my favourite performers, so this was certainly a show I had high expectations for. Best of all, it didn’t disappoint. All of the performers shared the stage fluidly, taking part in each others’ songs, or leaving the stage temporarily to let the others take up the limelight.

First up, Bernie Hayes. I hadn’t heard his music before, and I have to say, I’m a new convert.

The main act proceeded in two parts: set one being songs about when love is new and exciting, including The Whitlams’ Fall For You, The White Russians’ Someone For Everyone and Custard’s Anatomically Correct. Not your conventional love songs, certainly, but I’m sure the sentiment is there… somewhere.

The second set was all about when love goes horribly wrong, perhaps best characterised by The Whitlams’ Last of the Teenage Lovers and Beauty in Me, The White Russians’ My Ex-Girlfriend’s Boyfriend and his classic from MGF days, Unsent Letter. The set finished with Dave’s particular bitter A.V.O.: “Why are you wasting all my precious time?”.

For an encore, Tim and Pinky sang their respective parts of No Aphrodisiac for the first time ever. (Well, second time if you count the show from the previous night.)

Of course, no show involving Dave McCormack would be complete without his particular brand of crazy. From his frequent forays into the audience (both when he was singing, and when he got bored of sitting on stage doing nothing), through to his efforts after the encore to hijack the band and make them play whatever song came to his mind.

All up, a brilliant show consisting of some of Australia’s best live performers. They’re threatening to do it again next year, so for those that missed out, you just might get lucky.

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:

shell> mysqldump -u root -p --databases db1 db2 ... > backup.sql

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.

-- Grab the users with global permissions, 
-- with permissions to the databases you want, 
-- and tables/stored procedures in it.
mysql> SELECT u.* INTO OUTFILE '/tmp/user.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	FROM
		mysql.user u
	WHERE
		u.Select_priv='Y'
	UNION
	SELECT u.*
	FROM
		mysql.user u,
		mysql.db d
	WHERE
		d.Db IN('db1', 'db2', ...) AND
		d.User = u.user
	UNION
	SELECT u.*
	FROM
		mysql.user u,
		mysql.tables_priv t
	WHERE
		t.Db IN('db1', 'db2', ...) AND
		t.User = u.User
	UNION
	SELECT u.*
	FROM
		mysql.user u,
		mysql.procs_priv p
	WHERE
		p.Db IN('db1', 'db2', ...) AND
		p.User = u.User;

-- Now, grab the database permissions, and those of objects in the database.
mysql> SELECT * INTO OUTFILE '/tmp/db.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	FROM
		mysql.db
	WHERE
		Db IN('db1', 'db2', ...);
mysql> SELECT * INTO OUTFILE '/tmp/tables_priv.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	FROM
		mysql.tables_priv
	WHERE
		Db IN('db1', 'db2', ...);
mysql> SELECT * INTO OUTFILE '/tmp/procs_priv.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	FROM
		mysql.procs_priv
	WHERE
		Db IN('db1', 'db2', ...);

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

mysql> LOAD DATA INFILE '/tmp/user.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	INTO TABLE mysql.user;
mysql> LOAD DATA INFILE '/tmp/db.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	INTO TABLE mysql.db;
mysql> LOAD DATA INFILE '/tmp/tables_priv.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	INTO TABLE mysql.tables_priv;
mysql> LOAD DATA INFILE '/tmp/procs_priv.txt'
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
	LINES TERMINATED BY '\n'
	INTO TABLE mysql.procs_priv;

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:

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999', '9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 655360
        Extra: Using where
1 row in set (0.00 sec)

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.