Geeky Peek

The world of databases has changed a lot in the past 5-ish years. 5 years go the approach to data was basically “buy a huge honking RAID and put all data in it with a $50k Oracle license”. I worked in teleco where that approach was extremely entrenched. Sun + Oracle.

Well since then things have changed dramatically. Amazon S3, MapReduce/Hadoop, better distributed caching, and new distributed databases like couchdb have come along. Now a days, its almost as if the best practice is to avoid the database at all costs in favour of more distributed systems!

In the early days of Peek we went with the database approach, albeit using MySQL and hosting it on Amazon EC2 (not a RAID). Well, here’s the gotcha of that approach. Because of Amazon I can only throw so much hardware at the problem, I’m on their Very Large instance now and thats as big as I can get! So I lose the growth of a RAID. And since I chose a database that doesn’t distribute very well, I can’t distribute and grow horizontally easily.

So now we’re in the midst of re-designing our data layer to store a lot more things in distributed file systems like S3.

But in the interim we still have to keep the database running fast and furiously. So I have to start doing something I’ve often ignored in the past, I have to optimize the database!

As a very small company we don’t have an in-house dba. We all have some db knowledge, but its not deep expertise. So we’ve got a db guru who worked at MySQL for many years, Ronald Bradford 42sql.com, who helps us out. Generally he’s been a lifesaver. So armed with some support from Ronald and the trusty High Performance MySQL book written by the Percona guys I set about with the first baby steps in making our database run faster.

The first step is to deeply understand your traffic and data needs. We have very spiky writing patterns (people receive emails in clumps), and pretty constant reads. Our reads outnumber our writes about 3:1 statistically. On top of that we don’t need to be absolute in our ACID requirements of transactionality. We can always re-fetch emails that fail to store. The last note is that we use the MySQL InnoDB engine.

So we made a few big changes:
1. We got more aggressive about cleaning our database and increased our delete script amounts.
2. We changed innodb_trx_flush_commit to level 2, which favours performance over lossiness of writes/consistency of data.
3. We added another 1GB of memory to the innodb buffers to help with the spikiness of our traffic patterns.
4. We decreased the connection pool size on each node. We found we had a lot of OS Waiting due to context switches.

These changes had an extremely sizable impact. We used to have 120 threads running to handle 500+ queries per second. Now we have 20 threads running to handle the same volume of queries.

And we’ve still got a few more changes to make as well, increasing innodb log size to allow longer delays until it writes to physical disk, adding more memory to the buffer and playing with dirty page percentages.