About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world –
BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including
his blog over at lukasvileikis.com.
When Would You Need to Scale MySQL?
In general, the challenge of scaling MySQL would frequently appear in a situation when you start seeting your MySQL-backed application’s traffic increase by tens, hundreds or even thousands of times and you start seeing query timeouts more and more often.
What can You Do to Scale MySQL?
As far as the actual scaling of MySQL is concerned, you have a few options you can choose from. You can apply application level optimizations and, of course, you can apply some optimizations on the MySQL level and you can also scale through infrastructure. As we’re talking about the challenges of scaling MySQL in this blog post, we will go through some of the things that you can do to optimize the performance at the MySQL level yourself and tell you how can dbWatch help you to scale your database instances too.
If you want to optimize your database instances at the MySQL level, here’s some basic things you have to keep in mind:
– Your queries should only fetch the data that is absolutely needed meaning that you should avoid using queries like SELECT * if it’s not absolutely needed. A query working with too much data is one of the most basic reasons a query might not perform well – optimize the data your queries access and optimize your queries such that they access less data. The less data your queries access, the faster they will be – do not ask the database for data you don’t need.
– The EXPLAIN clause can be very useful if you want to optimize your query performance – the EXPLAIN clause is able to give you an access type that MySQL is using to find results (the access types range from a full table scan to index scans, range scans, unique index lookups and constants) – understanding the general concepts of scanning a table, scanning an index, range accesses and single value accesses could prove to be very valueable if you want to optimize your database instances at the MySQL level. The type ALL means that MySQL has ran a full table scan while ref means that MySQL used the ref access type on some sort of an index.
– Keep in mind that there are some basic things related to query execution. First, the client sends the SQL statement to the server, then the server checks the query cache. If there’s a hit in the query cache, it returns the stored result from the cache, otherwise the server parses, preprocesses and optimizes the SQL into a query execution plan. Afterwards, the query execution engine executes the plan by making calls to the storage engine API and the server sends the result to the client. Keeping these things in mind could put you on a good path when scaling your MySQL instances.
– If you are using wildcard searches (for example if you use LIKE queries) do not start your search with a wildcard. Only use a wildcard at the end. If a wildcard is used MySQL won’t know what the search term begins with and thus an index might not be used even if it exists on the column you are querying.
The advice above should set you on a good path regarding fixing query timeouts, deadlocks and the like, but if you need to scale MySQL seriously, chances are that you are going to need certain tools suitable for the task.
Solving MySQL Scaling Challenges with dbWatch
If you want to be able to solve your MySQL scaling challenges using tools, dbWatch can help you. dbWatch is a highly scalable software solution that helps enterprises monitor and manage your database server instances efficiently while also providing you with total control over all aspects of operation, performance and resource usage. dbWatch can help you scale all kinds of database management systems – it does matter if you are using MSSQL, Oracle, PostgreSQL, Sybase, MySQL or Azure SQL. Here’s how the user interface of dbWatch looks like:
dbWatch offers a few options to scale your MySQL (or any kind of database management system) instances:
1. You can (and should) make use of the dbWatch jobs that are available – the dbWatch jobs are split accross three categories (Availability, Capacity and Performance) and each of those categories contain certain jobs that perform specific tasks. For example, the Availability category for MySQL consists of database monitoring jobs that check the uptime of your DBMS and give you alerts, the Capacity category provides you with aggregated and detailed database growth rates and the Performance category consists of database jobs that check the effectiveness of the InnoDB buffer pool, the MyISAM key buffer, it can provide you with some database lock statistics, it can analyze the memory setup of your database server, it can also provide you with some information regarding your session load and the query cache hitrate and so on. Keep in mind that jobs can be configured (or even disabled if you so desire) and they also have a details section meaning that you can see some more information. Simply right click on a job and click on “Details”, “Configure” or “Set Schedule”:
For example, here’s the Details section on one of MySQL jobs in all of its beauty:
2. Make use of job scheduling – if you have a lot of database instances (and you can do that with dbWatch), job scheduling can be an invalueable resource. To schedule your jobs with dbWatch, simply click Set Schedule after you right click a job. Then you will be able to set a schedule for the job to run every minute, hour, day of a week or week of a year:
3. When you’re using dbWatch, you can also connect to or shutdown all servers at once – that might help with scaling your database instances too.
4. You can also make use of FDL (Farm Data Language) – now this one deserves an entire book alone (take a look into the documentation), but in short, FDL can help you with a multitude of different things ranging from filtering instances by database name to sorting the results of a column by an ascending or descending order. For example if you use use #sort your instances would be sorted in a certain order (use asc for an ascending order and desc for a descending order)
FDL can be really useful if you want to scale MySQL further and push your MySQL instances to its limits.
If you’re a MySQL DBA, chances are that sometime in the future you will face issues in relation to scaling MySQL. Your queries might become slower and slower, your traffic might increase more and more etc. – when using dbWatch you can make sure these kinds of problems will be solved quickly, securely and effectively no matter what kind of database instance you use, so be sure to try dbWatch today – if you encounter any kinds of problems, be sure to give the team behind dbWatch a quote – they will be more than glad to help you out and solve your challenges of scaling MySQL or other kinds of database instances in no-time.