Introduction
If you've ever worked with databases, you have probably encountered some queries that seem to take longer to execute than they should. Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance. In this blog, we will figure out how to avoid that.
What is a Query?
To understand what makes queries slow and improve their performance, we have to start from the very bottom. First, we have to understand what a query fundamentally is–a simple question, huh? However, many developers and even very experienced database administrators could fail to answer.
MySQL Query Execution Sequence
Queries are tasks that are composed of smaller tasks. If we want to improve their performance, we make those smaller tasks shorter or eliminate them. Here’s how MySQL executes its queries:
- First and foremost, MySQL sends the SQL statement to the server for execution.
- MySQL checks the query cache. If the results exist there, they are returned. If not, MySQL proceeds to the next step.
- The server parses a given SQL query, and a query optimization plan is made.
- The plan is executed, calls to storage engines are made.
- Storage engines return rows – we see results!
However, to observe how MySQL executes its queries on a deeper level, we should probably profile them instead of simply looking at the shiny outside of them. That’s where the real fun begins.
Profiling Queries
By now, we have touched the outside of what makes queries run. Still, to optimize our slow queries, we must first understand what they do and what stages of execution they go through before trying to optimize anything – in other words, profile them.
Thankfully, if we want to make use of the power offered by profiling in MySQL, we won’t have a hassle – it’s really, really easy to do.
Using SHOW PROFILES in MySQL
Enable profiling by running SET profiling = 1;, run your query, then issue a SHOW PROFILES; on MySQL. You should now be able to see through what stages your MySQL query went through and how long each of those stages took to execute (the table below represents states and durations returned when a SELECT query was profiled – the entire table is actually quite a bit larger, but since we are only interested in the states and durations, we are going to be observing that):
| State | Duration |
|---|---|
| Starting | 0.000017 |
| Waiting for query cache lock | 0.000004 |
| Waiting on query cache mutex | 0.000006 |
| Checking query cache for query | 0.000027 |
| Checking permissions | 0.000005 |
| Opening tables | 0.000276 |
| System lock | 0.000010 |
| Waiting for query cache lock | 0.000022 |
| Waiting on query cache mutex | 0.000021 |
| Init | 0.000007 |
| Optimizing | 0.000005 |
| Statistics | 0.000004 |
| Preparing | 0.000055 |
| Executing | 0.000042 |
| Sending data | 0.000004 |
| End | 0.000002 |
| Query end | 0.000067 |
| Closing tables | 0.000005 |
| Freeing items | 0.000056 |
| Logging slow query | 0.000001 |
| Cleaning up | 0.000002 |
See what we meant by saying that “queries are tasks that are composed of smaller tasks”? These smaller tasks are listed above. Now that we know what tasks our query goes through before the results reach our screens, it should be easier to optimize.
Optimizing Your Queries
To optimize your query performance, make the execution time of those tasks shorter or make MySQL not execute them at all. “How do I do that?” – you ask? There are a couple of ways to navigate around this complex jungle:
| Action | Explanation |
|---|---|
| Make sure to use appropriate permissions | Checking permissions is one of the first things that happen when a query is executed. By ensuring that the user you run queries with has proper permissions, you can shorten the task. |
| Keep the query cache big enough | Make use of the query_cache_size variable if you are running MySQL <= 5.7.20. Though note that this variable was removed in MySQL 8. |
| Properly use indexes and partitions | While both of them would slow down the performance of INSERTs, UPDATEs, and DELETEs, they would speed up read (SELECT) queries, sometimes by an order of magnitude. With indexes, MySQL will be able to execute SELECT queries faster because it will scan through less data – as a result, your queries will be faster. If you want to, you can also log all of the queries not using indexes by using the log_queries_not_using_indexes parameter, but for that, you would need to enable the slow_query_log parameter (set it to a value of ON). |
| Don’t ask MySQL for data you don’t need | Instead of using SELECT *, select only the columns that are necessary for your query to execute successfully. In other words, don’t make MySQL examine data that is not necessary. |
| If you want to, enable the slow query log. | As you can see, if a query is slow, it’s logged in the slow query log (it’s one of the last tasks that gets executed) – keep your slow_query_log variable ON and perhaps adjust how MySQL understands what a “slow query” is: by default, MySQL logs any query that runs for longer than 10 seconds, but this value can be adjusted by adjusting the long_query_time parameter. |
What to Do After Profiling
Once you have profiled your queries, you should be set on a good path. You should be set on a good path because profiling helps you look at the past of your queries to make them performant in the future. However, there are a couple of additional things you would need to keep a watchful eye on:
| What to Keep an Eye On? | Why? |
|---|---|
| Schema and Data Types | Properly optimizing your schema and data types is crucial for high performance; do not ever neglect them – you will make your own life as a DBA harder. If you neglect to keep an eye on those things, chances are you will waste hard drive space and potentially degrade the performance of your database instances in the future. |
| Character Sets and Collations | MySQL supports many different character sets and collations. Each of them must be used carefully both to save space and to avoid negatively impacting performance. In this case, avoid using character sets and collations of unknown descent to you; consult the MySQL manual or blogs like this one, learn and then apply your knowledge. |
| Indexes | MySQL supports many kinds of indexes that could be twisted in such a way that helps accomplish a variety of goals: of course, they have their own upsides and downsides. However, in most of the use cases, they can dramatically improve performance–we have dug into indexing in an older blog post, so for further explanation, have a read. |
| Partitions | Partitions, of course, are good friends of indexes, and they can also help improve query performance–however, only when used properly. For example, they can allow MySQL to back up or recover data relevant to one partition and not all data at once. We have dug into partitions earlier on, so have a read if you want to figure out how it works on a deeper level. |
| Server Settings | Server settings are a cornerstone of your MySQL instance–always keep an eye out for them. We will have a blog post focused on this specific topic, but for now, learning how to optimize InnoDB for big data sets should do the trick. |
| Hardware | Hardware sits underneath everything you do in MySQL–by having proper hardware, you can properly optimize your server settings for high performance. Therefore, always use as much hardware as necessary and allocate enough resources to MySQL to make it run at the very best of its ability. |
| Blogs and Documentation | Of course, blogs like the Arctype blog are also a perfect place to expand your knowledge. They can tell you how you should work with your database instances to improve your database performance on the go. Always keep an eye on documentation, too– it’s vital for your database both now and in the future. |
Take care of all of the aforementioned things, and once you have, make sure to use a proper SQL client!
Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com
Top comments (0)