This week I read an article on HackerNews about Ex-facebook employs releasing a new database server called MemSQL.
After viewing there product overview video I got excited and wanted to take this product for a spin.
What to test?
First I needed a project for testing the performance on. At my work we developed a Rails application that needs to validate a permit request with rules, questions, filters, answers depending on a region etc. etc. This process of validating a whole permit request takes some time because it has to perform 6850 queries.
At this moment the application runs on a PostgreSQL database so lets see what the performance is at this point:
1 2 3 4
The average result is that it takes 14.312ms for 6850 queries on PostgreSQL.
Configuring the Rails app to use MemSQL
After installing MemSQL and starting it running on port 3307, I created a new database added the existing data and changed some connection strings for the Rails app.
Because MemSQL is built on top of MySQL you can use all the MySQL client tools to perform operations on MemSQL.
The MemSQL documentation recommended using the memsql2 gem but to do this I had to install MySQL first on my machine.
Failed to connect?
After installing MySQL and configured the Rails application I got some strange error when starting the application.
1 2 3 4
After some googling I found out that on OSX you might need to symlink the dylib manually. NOTE Because I’ve installed a newer version of MySQL I have a
libmysqlclient.18.dylib instead of the required
libmysqlclient.16.dylib. Symlinking this file from libmysqlclient.18.dylib to libmysqlclient.16.dylib seemed to work for me.
Explain not supported for joins
At this point I am able to connect to the database and perform the benchmark, but I got the next error:
Apparently I’ve EXPLAIN queries on in my config and this isn’t supported for JOIN queries as MemSQL tells us.
To resolve this you just have to modify/add this line in your
MemSQL’s first spin!
So after resolving all these issues I was able to run the benchmark, but the first run took 3 min and 05 seconds! First I thought that this can not be right, but I forgot the fact that it generate’s c code from the queries.
On the MemSQL server I got the following output:
1 2 3 4 5 6
As you can see, generating the compiled version for every query took about 7 seconds.
So below again the same table with results. The first line is the time with generating all the compiled queries:
1 2 3 4
The performance boost is quite huge! The average result is 6.635ms for 6850 queries if we are skipping the first result. This means it’s like 7.677ms faster!
Cold boot of MemSQL
I’ve also tried the performance when I restart the database server. I just wanted to know if it keeps the data in memory and would take longer to run after a restart.
1 2 3 4
The first time it took just a bit longer, but with an average of 7.044ms for 6850 queries this is still faster than running it on PostgreSQL!
Pros and Cons
Here’s a list with some pros and cons I noticed while experimenting with MemSQL:
- Blazing fast!
- You can use all the MySQL client tools out there. Also because it uses MySQL, troubleshooting might not be an issue because it isn’t a new product.
- Huge cache folder! After restoring the database (15Mb) MemSQL creates a folder in the
plancachefolder which is 5.5GB. So performance comes with a price I think.
- Unable to create users. When creating the database, I tried to add a new user but this isn’t supported. Maybe this is because I’m using the Developer Edition.