This week, building on the DBaaS theme, I am taking a look at the Google Cloud SQL service. Last week I took a look at Microsoft's DBaaS offering via Azure, and this week we are looking at Google's offering which his MySQL compatible. The last article had a pretty decent flow I think with the different subtopics and I will try to keep with that same pattern this time too:
- How to Create
- How to Connect
- How to Backup and Restore
- (new addition) How it preforms
Let's dive in.
How to Create a Google Cloud SQL instance
Google's Cloud is one that I did not yet have an account with... no real reason why not, just hadn't played with it yet. But it only took about 30 seconds to get my credit card info in, collect my $300 dollar service discount (just for signing up and playing with the service), from there all I needed to do was find the SQL services area and click create.
The entire process for getting an instance online is contained on a single page. However its a pretty long page so I had to split it up into two images .
Here in part one you give your instance a name... For some reason I was thinking I was creating the actual database like I did in Azure, but here what we are actually doing is provisioning a MySQL instance which can contain many databases. After you name it, you select a size (which can easily be changed later)... select a region and pick how you want to be billed.
Depending on your use case you may want to investigate the two different billing methods. I haven't seen this method before, but basically you can have the database always ready to go and waiting for queries; or you can have Google pause the service after a predetermined time with no usage. If you are an 8-5 shop with no need for it to run after hours, the "Per Use" billing method may be cheaper. Then down a little further make sure you select "OnDemand" activation policy... this is the policy that will actually turn down the instance when its not needed.
On the bottom part of image one you can also see that they are going to charge you 24 cents a day if you want ipv4 addressing... If you are using a server with an ipv6 address you can leave this unchecked and just use ipv6. Over a month it will save you about 7 bucks. I think this method is actually pretty neat... it will help drive ipv6 adoption.
On the second part of creation page you are essentially just setting up the firewall rules so that you can restrict access to the database instance. Just like Azure, Google allows allows access to the instance from anywhere INSIDE of Google Compute by default, but if you want anything outside to have access you must explicitly allow it.
It only took a few seconds and my instance was created and I could access the details page. On this page you find pretty much everything you want to know about your instance. It also has some fairly realtime performance monitoring graphs which are pretty cool.
That's it. You now have a MySQL compatible instance running on Google's Cloud. You do not however have a database , to do that you can use your favorite MySQL client (Linux CLI, phpmyadmin, Toad, etc)
How to connect to your new Database DB Instance
I opted for using the Linux CLI client for MySQL because I have a lot of experience with it and already had it installed on serveral VM's. I was very easily able to connect to the database instance with the client and do a "show databases" command, the typical default MySQL databases were returned. I also created a database with the usual "create database <database name>" command.
While I was messing around the graphs on the details page were happily updating in near real time as well!
Since I had local MySQL instances installed I thought I would do some comparisons between a local Linux VM and a DBaaS instance with the mysqlslap utility. I followed a tutorial online that also used the sample employee data available from mysql.org.I ran this test on both my blog's local MySQL instance as well as the DBaaS instance.
How does it perform ?
Local MySQL instance (3 vCPU Xeon E5504 with 8GB of RAM and storage on an EMC CX3 FC San)
Same test on the DBaaS instance with the D16 instance setting. I also had to use a Google Compute VM to run the commands from, as I tried to run it from a linux instance at my Colo and things were hammering the WAN connection on my side like crazy.
When I say like crazy I mean about 70-80Mbps constantly, so I decided that to get a realistic number i would need something a little more local to the DB instance
I also tried out the HammerDB test tools and was able to get almost 80k tpm.
Note: One thing that I did notice when doing the performance testing was when I changed between instance sizes all connections to the instance would drop and take about 20-30 seconds to reconnect. So word of advise is to not change instance sizes unless you are willing to drop your connections for a bit of time.
How to do Backups and Restores
I saved this part for last because I really don't have much to talk about here, other than this part is a lot less user friendly than the Azure backup and restore. In fact other than selecting your 4 hour backup window when creating the database instance, none of the other settings are in the GUI. In fact throughout the Google documentation they recommend installing their SDK tools. After you have those utilities installed you can reference the documentaiton here Backups and Recovery - Cloud SQL — Google Cloud Platform to create backups and restore them.
I know... this section is pretty disappointing, but aside from retyping the Google documentation there really isnt too much to see here.
MySQL has to be my favorite database, probably because I've been using it since college. It's free, super easy to use, and more than enough for anything I have thrown at it thus far (personally). Google has created a crazy fast service with it too, I have never seen my bandwidth usage as high as it was when I kicked off the mysqlslap test, plus with the flexability to only bill you for when queries are actually being ran... that is awesome.
Overall I think that once Google gets backup and restore integrated into the GUI it will make it much more user friendly for the non-dba / coder to feel comfortable using.