Ted Carnahan

I’m in the interesting position of using my laptop for both programming work and day-to-day web surfing. I’m the lead developer for SAGrader, a web-based computationally intelligent essay grading system. We use MySQL for the database, and naturally I run a copy on my laptop to develop with. Unfortunately, MySQL and laptop power-saving goes together like chocolate and arsenic.

Ubuntu solves this problem by stopping MySQL on each suspend and starting it again on each resume. This is horribly bad for performance if you have a large database like I do because each MySQL server startup incurs almost a minute of mysqlcheck running. Rather than disable this, here’s a way to make it hurt less.

1. Install the schedutils package: sudo aptitude install schedutils
2. Modify /etc/mysql/debian-start and change the “MYCHECK=…” link to this: MYCHECK="ionice -c3 /usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf"
3. Restart the MySQL server to observe the effects

As long as you are using the CFQ kernel scheduler (default on Ubuntu Edgy and newer kernels), ionice can set mysqlcheck’s IO priority to idle, which means your laptop is much more responsive much sooner. Well worth the effort in my opinion!

There are number of blog posts out there that mention --lock-all-tables as a good option to backup MySQL tables with mysqldump - even InnoDB tables. Don’t do that! That option is for MyISAM tables that don’t do transactions. InnoDB has ooey-gooey transactional goodness that will take a consistant backup without locking all your tables and keeping your applications from running in the meantime. Just do your InnoDB hot backups like this:

mysqldump -u USERNAME -p --add-drop-table --add-locks --create-options --disable-keys --extended-insert --quick --set-charset –single-transaction –all-databases | gzip > OUTPUTFILE

Then viola! No nasty table locks!