Exclusive MySQL Performance Tuning Tips For Better Database Optimization
Structured Query Language (SQL) is a special purpose programming language used to store, manipulate and retrieve data from the database. It has found applications in many relational database systems including MySQL, Postgres, Oracle, SQL server and others.
By using SQL statements, developers easily carry out various functional database operations such as creating, updating and deleting data.
As data volumes grow, and technology becomes increasingly complex, it is becoming more important to optimize MySQL databases properly to deliver end-user experience and to lower infrastructure costs. MySQL performance tuning can help database professionals quickly identify bottlenecks, target insufficient operations through review of query execution plans and eliminate any guessing games.
With the added complexity of growing data volumes and ever changing workloads, database performance tuning and MySQL query optimization is now necessary to maximize resource utilization and system performance.
There are number of reasons which make SQL tuning bit complex for developers. Firstly, it requires extensive technical expertise to write and understand different executional plans. While writing clean and complete SQL statements is the responsibility of the one who garners thorough knowledge of it.
Besides its complexity, tuning is very time consuming. Because when you have large number of SQL statements to sort through, it brings a bit of uncertainty to find out which statements must you tune up and which one should you leave. And while every statement is different, their tuning approach also varies according to their respective functionalities.
In this tutorial, I will discuss some very handy MySQL performance tuning tips to optimize MySQL database operations.
The Benefits of MySQL Performance Tuning
The major advantage of identifying performance driving factor for database allows you to avoid over-provisioning and reducing cost by right sizing your servers. It also gives you insights into whether moving data storage or adding server capacity will bring improvement in performance or not, and if so, then how much will it be.
Tuning database for MySQL query performance optimization doesn’t come with pale challenges. However, once tuned properly, the database gives worthwhile performance results with great functionalities. It not only lowers unwanted task load but also optimizes MySQL database for faster data retrieval.
Optimize Queries With MySQL Query Optimization Guidelines
Follow these best practices for your MySQL performance tuning and optimizing database speed.
First of all, ensure indexing of all the predicates in WHERE, JOIN, ORBER BY and GROUP BY clauses. WebSphere Commerce strongly emphasizes on indexing of predicates to augment SQL performance. Because improper indexing of SQL queries can cause table scans, which eventually lead up to locking problems and other issues.
Therefore, I highly recommend to index all predicate columns so that database can experience MySQL query optimization.
Avoid using functions in predicates
The database doesn’t use index if it has some function predefined in the column.
For example:
- SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'Copy
Because of the UPPER() function, the database doesn’t utilize the index on COL1. If there isn’t any way to avoid that function in SQL, you will have to create a new function-based index, or have to generate custom columns in database in order to improve performance.
Avoid using wildcard (%) in the beginning of a predicate
The predicate LIKE
'%abc' causes full table scan. For example:- SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'Copy
In most cases, this wildcard usage brings major performance limitation.
Avoid unnecessary columns in SELECT clause
Instead of using ‘SELECT *’, always specify columns in the SELECT clause to improve MySQL performance. Because unnecessary columns cause additional load on the database, slowing down its performance as well whole systematic process.
Use inner join, instead of outer join if possible
Use outer join only when it is necessary. Using it needlessly not only limits database performance but also limits MySQL query optimization options, resulting in slower execution of SQL statements.
Use DISTINCT and UNION only if it is necessary
Using UNION and DISTINCT operators without any major purpose causes unwanted sorting and slowing down of SQL execution. Instead of UNION, using UNION ALL brings more efficiency in the process and improves MySQL performance more precisely.
The ORDER BY clause is mandatory in SQL if you expect to get a sorted result
The ORDER BY keyword sorts the result-set in predefined statement columns. Though the statement brings advantage for the database admins for getting the sorted data, but it also produces bit performance impact in the SQL execution. Because the query first needs to sort the data for producing final result-set, causing a bit complexed operation in the SQL execution.
Don’t Use MySQL as a Queue
Queues can really affect your database performance right from the core and could enter in your app databases without your knowledge. For instance, if you are setting up a status for particular item so that a ‘relevant process’ can access it, you are unintentionally creating a queue. What it actually does is that it builds up extra load time to access the resource without any major reason.
Queues cause problems for two major reasons. They serialize your workload, preventing completion of tasks in parallel, and they often result in a table that contains work in process as well as historical data from already completed jobs. It not only adds latency to the application but also adds hindrance to the MySQL performance tuning.
Understand the Four Fundamental Resources
You need four fundamental resources to make database function. CPU, disk, memory and network. If any one of these doesn’t function correctly, it ultimately affects the database server and results in poor performance.
To understand the fundamental resources properly, you need to focus on two particular areas i.e choosing right hardware and troubleshooting problems with it.
Always ensure to use all-round performance components when choosing hardware for MySQL database. Not only opt for the best among the stack, but also make sure that there should be the right balance between them. We have often seen that organizations tend to select servers with fast CPUs and large disks, but they get mistaken with starved memory which eventually kills performance.
In some scenarios, adding memory becomes highly substantial for improving performance when it comes to the magnitude. It does look a bit counterintuitive, but in most cases, overutilization of disks affects directly to the database performance. As the deficiency of enough memory to hold server’s data proves costly in derailing database performance.
When it comes to troubleshooting, always keep in check the performance of all four fundamental resources. Validate qualitatively that they are performing as per the needs improvement in the norms. Taking this audit into regular consideration will quickly solve major occurring problems.
Pagination Queries
Applications that paginate tend to bring the server down. In showing you a page of results, with a link to go to the next page, these applications typically group and sort in ways that can’t use indexes, and they employ a LIMIT and offset function that causes the server to do a lot of work generating, then discarding rows.
You can find optimizations within the user interface itself. Instead of showing the exact number of pages in the results and links to individual page, you can just show a link to the next page. You can also prevent people from going to irrelevant pages.
On the query side, instead of using LIMIT with offset, you can select one more row than you need, and when the user clicks the “next page” link, you can designate that final row as the starting point for the next set of results. For example, if the user has viewed a page with rows 101 through 120, you will have to select row 121 as well; to render the next page, you’d query the server for rows greater than or equal to 121, limit 21.
Optimizing MySQL Subqueries
The most important advice I can give you in relation to subqueries is that you must prefer a join where possible, at least in current versions of MySQL.
Subqueries are the subject of intense work by the optimizer team, and upcoming versions of MySQL may have more subquery optimizations. Do keep a check on which of the optimizations will end up in released code, and how much difference they’ll make. My point here is that “prefer a join” is not future-proof advice. The server is getting smarter all the time, and the cases where you have to tell it how to do something instead of what results to return are becoming fewer.
Mysql Query Cache
One of the most important aspects of measuring performance is caching the content. MySQL provides database query caching which caches the SELECT statement text and the retrieved result. Hence, whenever you make a duplicate database, you call MySQL query cache, it will respond to you and show the result from cache, and no call will be parsed repeatedly. In this way, you can maximize the MySQL cache optimization process.
To setup MySQL query cache, you must add few settings to MySQL. First of all, you must check if query cache is available or not with the following command:
- mysql> SHOW VARIABLES LIKE 'have_query_cache';
This will show the result, YES. This means MySQL cache is working fine.
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | have_query_cache | YES |
- +------------------+-------+
Now, you can setup the MySQL query cache size and type. Remember the minimum default size is 40KB. The maximum size can be 32MB. You can setup the MySQL query_cache_size by using the following command:
- mysql> SET GLOBAL query_cache_size = 40000;
Query cache type can determine the behaviour of all connections. You can also disable the Query cache for queries like:
- mysql> SET SESSION query_cache_type = OFF;
You can also set values like 0,1 and 2 for setting up the connection status.
Use Memcached for MySQL Caching
Memcached is a distributed memory caching system. It speeds up websites having large dynamic databases by storing database object in Dynamic Memory to reduce the pressure on a server, whenever an external data source requests a read. A memcached layer reduces the number of times database makes a request.
Memcached stores the values (v) with the key (k), and retrieves the values (v) with the key (k) without even parsing the database queries and stays away from all these hassles.
Wrapping up!
This article provides in detail, the account of the best practices for database optimization and handy MySQL performance tuning tips every developer must know. It’s a complete guide for those backend devs, who are uncertain about their poor database performance and need some handy techniques to optimize MySQL database from the core.
If you want to add your thoughts on the topic or want to ask some questions regarding it, feel free to write your comments in the comments section.
Comments
Post a Comment