MySQL isn't YourSQL
Ok, so I’ve already corrected gaudy and horrible behavior part and parcel with default PostgreSQL installs, but what about that… other open-source SQL database? Is it wrong too? Sure is!
Fixing Your Damn Server Again
My fake system still has 8GB of RAM, but we’ll be using more of it for a MySQL install. Why? Because MySQL’s planner makes different assumptions about memory allocation than PostgreSQL. Remember sysctl.conf? Put this in there:
vm.swappiness = 0
Then invoke it:
sudo sysctl -p
Why didn’t we do this last time? We only gave PostgreSQL about a quarter of available memory, but MySQL is getting three quarters. In doing that, the default swappiness setting will cause the kernel to occasionally swap parts of your running database to disk, causing tons of IO and terrible data performance.
And remember, this is a dedicated server, so we definitely don’t want it swapping out its main function because someone unzipped a log file or something.
Delusions of Grandeur
MySQL has a problem with labels. To be fair, PostgreSQL has the same problem. These databases came around when a gig of memory cost as much as a small car. What’s a huge amount of memory to MySQL? About 512MB.
Ok, I’ll wait until you’re done laughing.
I got that number from the my-huge.cnf file that comes as a documentation example on how you can improve MySQL’s performance by using it as a starting point instead of the default, which is a svelte 16MB. Let’s make this simple. In your /etc/my.cnf file, start with this:
[mysqld]
key_buffer = 256M
sort_buffer_size = 8M
tmp_table_size = 8M
query_cache_size = 128M
Wait, didn’t I just make fun of settings below 512MB? Yes indeed. But there’s a method to my madness. See, MySQL has two storage engines: MyISAM and InnoDB. The settings above are for MyISAM, and even though you shouldn’t be using that, it needs to be at some minimum amount just in case. Yes, that’s the minimum amount. Now for the real settings:
default-storage-engine = InnoDB
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
Woo! There we go. Yeah, I only gave half the memory straight to the buffer pool, but that’s because we had to leave room for incidentals allocated on a per-connection basis. That setting can go all the way to 6G with a little testing to make sure it won’t exhaust your system memory and cause swapping.
Oh, and so you don’t get tempted to use MyISAM tables, we set the default storage engine to InnoDB. Why? MyISAM tables are locked on a table-level basis. So, any time a record is inserted, updated, or deleted, nothing else can happen. This locking contention results in bad performance under high concurrency. InnoDB, for any of its faults, doesn’t have this limitation.
Friends don’t let friends use MyISAM.
The End!?
Look, I don’t know nearly as much about MySQL as I do PostgreSQL. But apparently there’s an InnoDB plugin that’s… different from the built-in one. It has some extra options that can help performance, but I couldn’t figure out how to even install the plugin. Every time I tried following the official instructions, MySQL wouldn’t even start.
But these settings will at least get MySQL bumped up in performance. Once again, the defaults assume a tiny machine that has several different services running on it. You’re a smart DBA, so you just have a single (or several) server dedicated purely to the database, and it has more RAM than you’ve ever seen. Hell, my 8GB assumption is outright laughable. Servers now should start at 32GB at least, in which case you’d have given 24GB to MySQL instead of between four and six.
But stay tuned… I’ve got an exciting rant planned against query hints!