A MySQL administrator knows that one of MySQL’s most important features is its scalability. When you start out using MySQL, you are more likely to use it in one box. Then the need for separate servers according to function or business needs call for you to be able to scale your database and MySQL along with it.
How do you deal with it? Did you know that there are several MySQL scaling techniques available to you? These are, read/write splitting, distributed replicated block device, MySQL Cluster and cloud. Are you familiar with each option and when to use each? These are some of the things you need to know as a MySQL administrator.
First things first
First, run the latest version of MySQL. You would need MySQL 5.6, which is the fastest MySQL version. It also gives you a simpler and more worry-free platform to start with.
But if, for some reason, you cannot upgrade to the latest MySQL version, all is not lost. You can add a Memcached Layer, which will give you a cache for fast access to session data and which can give you access to NoSQL for InnoDB. It is also a simple process to do, but you would need to make your applications cache aware to benefit from this.
After this, you will need to find out what is required from your database and MySQL. Is it to get the highest up time possible? Or do your users need fast response times. Or do you need to make sure that multiple users accessing the database would not be a problem?
After you figure it out, you will now know what scalability option is perfect.
1. Read/Write Splitting
Read/write splitting is making use of duplicate databases (you call slaves) to handle the reads for your system. So even with too many users accessing it, you can make sure that the system can cope. An offshoot benefit of this is that you are able to make sure that your master database is not overloaded, which is important because all the write processes are handled by the master.
Read/Write splitting is a popular way to scale your databases and MySQL, but it is not suitable for big data. For one, because all the writes are handled by the master node alone, it creates a bottleneck and this can slow things down. Plus, if you have big databases, you would need to replicate this to your slaves, as a whole. You also get problems with replication lag and missing data. Also, this does not improve query performance. On top of all this, you would need to deal with a more complex environment because you are basically dealing with several databases.
So if you have a lot of data or if you do not want to deal with too many databases, what could you do? Well, lucky for your MySQL administrator, there are a lot of options that you could use when it comes to MySQL scaling. If you need guidance, call Four Cornerstone today and ask about our MySQL administrator courses!
Please stay tuned for the next part of this article tomorrow. We will discuss more about MySQL scaling.
Photo courtesy of MySQL.
