MySQL Performance Tuning

Many databases (and particularly many relational databases) rely on Structured Query Language (SQL), for handling data storage, data manipulation, and data retrieval. If developers want to create, update or delete data then they have always been able to do so easily with SQL statements. That said, we live in an age where the sheer amount of data being shunted around has grown and is still growing at alarming and overwhelming rates, and to compound this, workloads are always changing too, so while SQL statements are useful, there is an ongoing and pressing need for MySQL performance tuning. The swift and efficient movement and processing of data is crucial if we hope to deliver an excellent end-user experience while keeping costs as low as possible.

So, developers wanting to seek out and eliminate hold-ups and inadequate operations must turn to MySQL performance tuning tools which can help them with execution plans and remove any guesswork. MySQL performance may be important, but it isn’t necessarily an easy thing to do. In fact, there are a few aspects of the process which make it a difficult undertaking for developers. MySQL optimization requires sufficient technical prowess, i.e. enough knowledge and skill to comprehend and create a variety of execution plans, and that can be quite off-putting.

As if the fact that it’s tricky wasn’t enough, MySQL optimization takes time and energy. When you’re faced with a whole array of SQL statements to wade through, you face a problem with a built-in degree of uncertainty. Each example needs to be carefully considered during MySQL performance tuning. First, you need to decide which ones to amend and which to leave alone, then you need to work out what approach to take to MySQL tuning with each one that you do select because they’ll all need different approaches depending on what their function is. That’s why we are going to discuss several tips and techniques that will help you approach MySQL performance without getting distracted snowed under by the sheer weight of them.

The Benefits of MySQL Optimization

MySQL performance tuning is essential for keeping costs down. If you can use the right-sized server for the job, then you won’t be paying for more than you need, and if you can understand whether moving data storage or adding extra server capacity will lead to MySQL performance improvements then that helps efficiency too. MySQL tuning can be challenging, but it’s worth the time that it takes because an optimized database has greater responsiveness, delivers better performance, and offers better functionality.

MySQL Query Optimization Guidelines

Here are some useful tips for MySQL tuning. They are a great addition to your collection of best practices.

Make sure that the predicates in WHERE, JOIN, ORDER BY and GROUP BY clauses are all indexed. WebSphere Commerce points out that SQL performance can be improved significantly by predicate indexing because not doing so can result in table scans that culminate in locking and other difficulties. Which is why we highly recommend that you index all predicate columns for better MySQL optimization.

Keep functions out of predicates

The database won’t use an index if there’s a predefined function.

For instance:

  1. SELECT * FROM TABLEONE WHERE UPPER(COLONE)=’ABC’

The UPPER() function means that the database won’t look to the index on COLONE. If that function can’t be avoided in SQL, you’ll need to make a new function-based index or create custom columns in the database in order to experience improved MySQL performance.

Remove non-essential columns with the SELECT clause

Rather than use ‘SELECT *’, always specify columns for the SELECT clause, because unneeded columns add extra load on the database, hindering its performance and causing knock-on effects to the whole system.

Try not to use a wildcard (%) at the start of a predicate

The predicate LIKE ‘%abc’ will cause a full table scan, i.e.:

  1. SELECT * FROM TABLEONE WHERE COLONE LIKE ‘%ABC’

This kind of wildcard use can slow down performance significantly.

Use INNER JOIN, instead of OUTER JOIN where you can

Only use outer join where you absolutely need to. If you use it when you don’t need to then you’ll be putting the brakes on database performance due to slower execution of SQL statements and negative effects on MySQL optimization.

Use UNION AND DISTINCT only where needed

If you use the UNION and DISTINCT operators when other options are available you’ll be needlessly adding excessive sorting which slows down SQL performance. Try using UNION ALL instead for better MySQL performance.

You need to use ORDER BY in SQL for better sorting results

ORDER BY sorts the result-set into pre-determined statement columns. Although this is advantageous for database admins who want data to be sorted, it’s detrimental to MySQL performance. The reason for this is that in order to produce the final result set the query needs to sort the data first, which requires quite a convoluted and resource intensive SQL operation.

Don’t Use MySQL as a Queue

Queues can sneak up on you and slow down your database performance. For example, any time you set up a status for a specific item so that a ‘relevant process’ can gain access to it, you will be creating a queue without knowing it. This just adds pointless extra loading time to use the resource.

Queues are a problem because they cause your workload to be treated in an inefficient serial fashion rather than more efficient parallel and because they frequently lead to a table that contains work in progress along with data from jobs that have already been completed. This slows down the app and also hinders MySQL performance tuning.

The Four Fundamental Resources

A properly functioning database requires four fundamental resources. A CPU, hard drive, memory, and a network. Problems with any one of them will negatively affect the database, so it’s important to choose the right hardware and make sure that it’s all functioning properly. In practical terms, this means that if you’re going to invest in a powerful CPU then don’t try to cut corners with less memory or slower storage. A set up is only as good as its slowest component, and if all of them aren’t at parity then the result will be MySQL performance bottlenecks. Investing in more memory is probably the most cost-effective way of improving performance as it is inherently faster than disk-based storage. If all operations can be held in memory without resorting to disk usage, then processes will speed up considerably.

Pagination Queries

Applications that paginate tend to slow the server. By giving you a results page with a link to the next one these apps usually approach grouping and sorting in ways that don’t use indexes, using a LIMIT and offset function that places an extra burden on the server and then discards rows.

Adjusting the user interface itself will assist with optimization.  Instead of listing all pages in the results and linking to each page, you can just include a link to the next page. This also stops users wasting time on incorrect pages.

In terms of queries, rather than using LIMIT with offset, you can select one more row than you require, and when someone clicks the ‘next page’ link, you can set that last row as the start of the next set of results. For example, if the user looked at a page with rows 201 to 220, select row 221 as well; for the next page to be rendered, you would query the server for rows greater than or equal to 221, limit 21.

MySQL Optimization—Subqueries

In terms of subqueries, it’s better to use a join where you can, at least in current versions of MySQL.

The optimizer team is doing a lot of work on subqueries, so it may be that subsequent iterations of MySQL may come with extra subquery optimizations. It’s best to keep an eye on which MySQL optimizations end up in the each version, and what their effects will be. What I’m saying here is that my advice to err towards a join may not hold forever. Servers are getting more and more intelligent, and the instances where you will need to explain to them how to do something instead of what results to return are reducing.

Use Memcached for MySQL Caching

Memcached is a system that enables distributed memory caching, improving the speediness of websites that use big dynamic databases. It manages to do this by keeping database objects in Dynamic Memory to cut server load any time an outside data source asks for a read. A Memcached layer reduces the number of times a database issues a request.

Memcached stores each value (v) with a key (k), and then retrieves them without the need to parse the database queries for a much more streamlined process.

Conclusion

MySQL tuning  ( as well as tuning of MariaDB ) may be time-consuming and thought-provoking but it’s one of the hurdles that you need to take in your stride if you want to ensure that your users receive the best possible experience. Poor database performance could benefit from investing in the best hardware and making sure that it’s balanced, but even with the best CPUs, and the fastest memory and SSDs on the market, there is still an additional performance improvement to be had from taking the time to implement proper MySQL optimization. It can be a laborious task for developers, but the performance and enhancements and efficiency savings are well worth it. Keep these tips close to hand and refer to them often. They’re not all-encompassing, but they are a handy starting point for your journey into MySQL tuning.

MySQL Performance: Identifying Long Queries

MySQL performance

An optimized database server is key to ensuring that apps which rely on MySQL run efficiently. Sometimes all that’s needed to see website and application performance improvements are a few small adjustments, so in this article, we look at some of the tweaks that can help boost performance the most.

What Does it Cover?

Our advice here applies to the majority of MySQL servers running Linux and covers (among others) both dedicated and cloud VPS servers that run on various Linux distros and these system types:

  • Core-managed CentOS 6x/7x
  • Core-managed Ubuntu 14.04/16.04
  • Fully-managed CentOS 6/7 cPanel
  • Fully-managed CentOS 7 Plesk Onyx/Obsidian
  • Self-managed Linux servers

If you’ve chosen not to take advantage of direct support, you can still use these techniques, but we’re assuming that you’re familiar with these basic system admin principles:

  • SSH connections and how to navigate the standard Linux command-line shell environment.
  • Opening, Editing and Saving files in Vim or your preferred system editor.
  • MySQL interactive mode and general MySQL query syntax.

What is MySQL Optimization?

MySQL Optimization doesn’t have an exact meaning, but the general idea is pretty self-evident. It’s just about making your database function as efficiently and effectively as possible, and what that means in practical terms will obviously vary depending on the needs of your organization. Here, we’re taking it to mean a server which has been set up to avoid the kind of typical bottlenecks that can arise.

What’s a bottleneck?

A wine bottle narrows at the neck, so there’s a tighter space for your tipple to squeeze through, so it serves as the perfect metaphor for a road that narrows from two lanes to one and slows traffic or a computer system that hinders data transit for some technical reason.

E.g. the server could cope with 10 simultaneous connections, but as it is, it can only manage 5. If no more than 5 connections are established then the system will hum along happily, but as soon as it hits 6 then the limits will become all too clear.

When Should a MySQL database be optimized?

The best time to tune your database is when doing so will have the lowest impact on whatever services rely on it. As for frequency, you should audit it every week or month to make sure that poor performance isn’t slowing down apps. It’s easy to see when this is happening because you’ll notice a few tell-tale signs:

  • queries back up and never complete in the MySQL process table.
  • websites or websites that rely on it become sluggish.
  • more connection timeout errors, particularly during peak hours.

It’s fine to have a number of concurrently running queries at the same time on a system, but that becomes a burden when they regularly take more time than usual to complete. Even though the threshold for what constitutes a burden will vary from system to system and from app to app, average query times beyond several seconds will result in slower performance for websites and apps that depend on them. You may not notice that anything is amiss until a spike in traffic reveals that there is indeed a bottleneck. That’s why it pays to be proactive and optimize before this happens.

Tracking Down Performance Issues

To diagnose a particular bottleneck, you’ll need to know how to do an examination of the MySQL process table. You can view it in a few different ways, although this will vary according to your setup. To keep it short, we’ll just look at the most frequently used Secure Shell (SSH) access methods:

The MySQL Process Table: Method 1

The ‘mysqladmin’ command-line tool can be used with the flag ‘processlist’ or ‘proc’ for short. (You  can add the flag ‘statistics’ or ‘stat’ for short to show running statistics for queries since MySQL’s most recent restart.)

Command:

mysqladmin proc stat

Output:

+-------+------+-----------+-----------+---------+------+-------+

| Id | User | Host | db | Command | Time | State | Info | Progress |

+-------+------+-----------+-----------+---------+------+-------+--------------------+----------+

| 77255 | root | localhost | employees | Query | 150 | | call While_Loop2() | 0.000 |

| 77285 | root | localhost | | Query | 0 | init | show processlist | 0.000 |

+-------+------+-----------+-----------+---------+------+-------+--------------------+----------+

Uptime: 861755 Threads: 2 Questions: 20961045 Slow queries: 0 Opens: 2976 Flush tables: 1 Open tables: 1011 Queries per second avg: 24.323

For: can be used on the shell interface, so feeding output to other scripts and tools is no problem.

Against: The info column of the process table is always truncated so you won’t be able to view the full query if it’s longer.

The MySQL Process Table: Method 2

Run the query ‘show processlist;’ from the MySQL interactive mode prompt. (Add the ‘full’ modifier to the command to stop the Info column being truncated. You’ll be glad of it when long queries would otherwise get cut off.)

Command:

show processlist;

Output:

MariaDB [(none)]> show full processlist;

+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

| Id | User | Host | db | Command | Time | State | Info | Progress |

+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

| 77006 | root | localhost | employees | Query | 151 | NULL | call While_Loop2() | 0.000 |

| 77021 | root | localhost | NULL | Query | 0 | init | show full processlist | 0.000 |

+-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

 

For: Using the full modifier allows for seeing the full query on longer queries.

Against: MySQL Interactive mode can’t access the scripts and tools of the shell interface.

Using the slow query log

Another useful tool in MySQL is the included slow query logging feature, which is the preferred way of regularly finding long-running queries. You can adjust this feature using several directives, but the most commonly needed settings are:

slow_query_log – enable/disable the slow query log

slow_query_log_file – name and path of the slow query log file

long_query_time – time in seconds/microseconds defining a slow query

You can set these from the [mysqld] section of the MySQL configuration file that you’ll find at /etc/my.cnf. You’ll need to do a MySQL service restart to make them work. Take a look at this example for formatting:

Caution

The slow query log file requires a lot of disk space, and it needs continual attention while it’s running. Remember that the lower your long_query_time directive the quicker the slow query log will fill a disk partition.

[mysqld]

log-error=/var/lib/mysql/mysql.err

innodb_file_per_table=1

default-storage-engine=innodb

innodb_buffer_pool_size=128M

innodb_log_file_size=128M

max_connections=300

key_buffer_size = 8M

slow_query_log=1

slow_query_log_file=/var/lib/mysql/slowquery.log

long_query_time=5

After the slow query log has been enabled you will need to follow up from time to time to evaluate troublesome queries that require your attention. To review the contents of the slow query log file, you can parse it directly. This example shows statistics for a sample query that ran over the allotted 5 seconds:

Caution

If you enable the slow query log feature, then be advised that performance will take a hit. That’s because extra routines will be analyzing every query and writing them to the log file which will increase the input/output burden. This additional overhead explains why it’s considered to be best practice to disable the slow query log on production systems. In fact, you should only enable it for a fixed period: when you’re actively hunting for queries that might be slowing down a website or app.

# Time: 180717 0:23:28

# [email protected]: root[root] @ localhost [] # Thread_id: 32 Schema: employees QC_hit: No

# Query_time: 627.163085 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 0

# Rows_affected: 0

use employees;

SET timestamp=1531801408;

call While_Loop2();

 

Alternatively, there’s also the mysqldumpslow command line tool, which parses the slow query log file and puts like queries together but excludes the values of number and string data.