Mysql runs remarkably slower on my MacBook versus other environments

Question

I would like to speed up my MySQL on my MacBook. It's much slower than MySQL running on our servers or on virtual machines on other laptops.

All the tables I deal with are InnoDB. I run a lot of django unit tests so there are a lot of create table commands that get run.

Update:

I should note that I'm really comparing this to another laptop, running a Fedora VM, with no tweaks to my.cnf and not a particularly fast hard drive. I also know that our servers run this fairly fast, but I can accept that.

My guess is it still may be a hard disk issue.

Answer 1

Have you adjusted the default MySQL configuration on your Macbook to be closer to whats going on in your other environments? The default settings for MySQL are very minimal.

I am not familiar with where MySQL is installed on OSX, but you should have several examples of configuration files for different environments in: [mysql home]/support-files/

Experiment with these by copying them to /etc/my.cnf and restarting MySQL. These sample files are all well commented so this is quite a good hands-on way to learn some basic MySQL tunning. If you are indeed just running InnoDB, you can comment out or remove the MyISAM settings as you try each to save resources.

Answer 2

One thing to keep in mind: your MacBook has a laptop hard drive. Even if it's 7200 RPM, you should expect it to be slower, certainly slower than even a modest server hard drive.

And even on a super speed machine, django's test suite blows up all of the database caches, since it rebuilds the database each time it's run.

You can run the unit tests in memory though, which is orders of magnitude faster:

Create a new testsettings.py file next to your app’s settings.py containing:

from projectname.settings import *
DATABASE_ENGINE = 'sqlite3'

Then when you want to run tests real fast, instead of manage.py test, you run

manage.py test --settings=testsettings

Read more here.

I've also experimented with locating the MySQL database entirely on a RAM disk. Unfortunately, I haven't worked that out on Mac OS X yet.

(all of this assumes that there isn't something seriously wrong with your mysql configuration :)

Answer 3

Harddisk is one factor, but also make sure you check :

innodb_buffer_pool_size

If you are using innodb you might want to change this into something much higher (50% of your RAM, or the size of your database). But remember, this amount will actually be used by MySQL, so if you still want to use your RAM for other purposes.. you might be better off accepting that your development workstation is not as fast as a server tuned for being a MySQL database.

Answer 4

You didn't tell us what the workload was, reads or updates or both, but if it's write-intensive, you could try setting innodb_flush_log_at_trx_commit to 0 to have innodb relax on the *sync() calls - maybe there's a bad interaction with the kernel/disk-subsystem on the apple OS.

You could also post the my.cnf you're using. It could also be a dns lookup issue if the sluggishness is when connecting to the db. To rule out dns issue, set skip-name-resolve in my.cnf.