Burt blog

MySQL is a really good database. It’s super fast, stable, reliable and you can work around pretty much anything. There’s lots of different types of storage engines and you can use different ones for every table to optimize your performance.

Just a single MySQL instance is surprisingly capable. A master-multiple slave setup of MySQLs can handle ridiculous amounts of requests. Building and testing such a monolithic cluster will give you the impressions that it’s invincible and that it can handle pretty much anything.

But we’re getting ahead of ourselves. Let’s start at the beginning.

You start with a single mysql instance that you store everything in. It works well on your computer so you push it to production. Then you realize that MyISAM, which is default table type, locks the entire table for each operation. Bummer. Who wants that in a multiple user environment. So you read up and change to InnoDB.

Everything runs smoothly for a while. You get a little performance issues, but you check out mysqltuner.pl and it gives you some hints that make the application fly. Awesome. Then you realize that you need to add another column. But now you database is over 100GB. But you do it anyway. And it takes forever. And your application is dead while you’re doing it. Buuurn. Nothing much to do here, you just realize that you will have to take particular care when adding columns in the future. And indexes. +1 exp, -20 momentum.

Time goes on. Your database grows to about 400GB and you have the ‘foresight’ to have a stupid amount of temporary files laying around on your disk. When MySQL dies because it ran out of disk space and you can quickly remove the files and restart it. You just bought yourself 10Gigs of time to fix this. Your natural reaction is to go into your databases and delete a bunch of stuff that’s ‘not necessary’. Right. Grab a cup of coffee and get to work.

It takes you the best part of a day until you realize that you’re not getting any of your space back. WTF dude!? Reading up on InnoDB you find out that InnoDB doesn’t return space. It’s just a big blob that’s only gets bigger. FFFFFFFUUUUUUUUUUUUU…..

There are ways around that. MyISAM returns space. So you change the table types to MyISAM, export everything, clean up, setup a slave replica, change the application to read against that and deploy everything. Then you go to town on cleaning up and returning everything to normal.

You leave about 30 Gigs of ballast on that disk, because goddamnit that saved your ass last time. If the disk had gotten completely full you’d have been screwed.

Time goes by, things are a bit buggy, but everything holds together. Sort of. You get the disk space warning from the monitoring you’ve set-up in advance. Nice. Let’s go ahead and remove some data from the databases and regain some space. So you do. And you get no space back.

…

What? Dude!

You specifically selected MyISAM because it returns the space on delete. But you see, it doesn’t. It returns the space on ‘table repair’. That’s NOT the same. Table repair locks everything good ‘n’ snug for a looong time.

So you devise a system where you create a new database as a copy of the old one, rename them (RENAME TABLE current_db.tbl_name TO other_db.tbl_name;), create temporary tables, repair the ‘old’ ones, regain space, rename everything back, use your hacked little script to combine the temporary and the repaired tables and restart everything hoping for the best. It actually works out.

But god damnit it’s TOO MUCH WORK for a bunch of stuff that you don’t really need. It’s just… stuff. No one cares about any of this… until it goes missing. Then everyone goes all depressed and suicidal talking about how we ‘must keep all this data as it is vital. Our customers. Think of the customers’.

So no. Don’t save temporary stuff in MySQL. And any time you start writing code that stores unimportant stuff in an important infrastructure just because it’s convenient you run the risk of me grabbing you by the neck and rubbing your nose in it.

You have been warned. =P

#engineering, #Burt, #burtcorp, #MySQL,