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.

AI and Machine Learning

Artificial Intelligence

We hear AI term more and more these days, but it’s easy to misunderstand it just following the mass media hype. It has nothing to do with conscious machines – no one knows how to build one of those yet.

What is AI?

AI is the simulation of human intelligence processes by computer systems. Such simulation includes:

  • Reasoning – using rules to reach solid or approximate conclusions
  • Learning­ – acquiring information and rules for using information
  • Self-correction

Expert systems, speech recognition and machine vision, in particular, have been early and prominent beneficiaries of AI.

AI is typically described as being either weak or strong. Weak (or narrow) AI refers to a system that’s been built to focus on one task alone. A virtual assistant like Alexa is one type of weak AI. Strong AI, also referred to as artificial general intelligence, tries to generally mimic human cognitive abilities. So, if you give it a task that hasn’t seen before, a strong AI system can work its way through it without needing any further human assistance.

Paying for the people you need to run AI and the hardware itself can cost a lot, so companies are now offering Artificial Intelligence as a Service ( AIaaS ) platforms. AI as a Service is appealing from a cost point of view because it lets businesses audition different operators and discover whether AI is worth it for them, for less than if they had to commit fully.

Services like IBM Watson AssistantAmazon AI services, Microsoft Cognitive Services and Google AI services have all found favour in this growing sector. Many companies have been quick to seize on the business potential of using autonomous systems to aid their processes, but this new frontier brings some ethical questions with it. Many of the most advanced AI tools use deep learning algorithms which analyse existing data. Unfortunately, they can only be as clever (or moral) as the information that they are given. AI systems used in American healthcare to help manage treatment were found to systematically discriminate against black people, and there are many other examples besides.

Some of the top minds in the industry think that artificial intelligence might be the wrong term to use for this particular technology. It’s hard to dispel the image of created by numerous science fiction stories of rogue robots turning on their masters or stealing everyone’s jobs, and along with these unfounded fears it’s also difficult to separate the term from unrealistic expectations. AI systems aren’t alive and they don’t have feelings, so perhaps a term like ‘augmented intelligence’, which sounds more neutral, might be better at helping people to accept and understand these tools.

Types of Artificial Intelligence

Arend Hintze is an assistant professor of integrative biology and computer science and engineering at Michigan State University. He divides AI into four categories:

Reactive Machines

The chess computer Deep Blue (which beat Garry Kasparov in the 1990s) Deep Blue understands the game and can make predictions, although it doesn’t have any memory and can’t make predictions based on past experiences. It simply works out all the possible moves that are available to itself and its opponent and selects the one that is most strategically beneficial. Deep Blue and Google’s AlphaGO were both built to fulfil these tasks alone, so you can’t expect them to suggest your workout playlist or the movies you might like on Netflix.

Restricted Memory

These AI systems are able to use previous experiences to inform their decisions, which is why you’ll find some of these decision-making functions in self-driving cars. They can use what they see to inform future actions, like when a car might be about to change lanes. These observations are only held temporarily.

Theory of Mind

This term from psychology refers to the acknowledgement that others have their own beliefs, wants and goals that influence their decisions. This type of AI doesn’t exist yet.

Self-awareness

This kind of machine can say “Cogito ergo sum” (“I think, therefore I am”) and mean it. There are no self-aware machines (that we know of!) as yet.

Examples of AI Technology

Here are seven examples of where AI has been incorporated into various types of technology.

Automation

Robotic process automation (RPA) is ideal for automating repeatable tasks. It can do it with more intelligence than IT automation can manage, adapting to changing circumstances without the need for further human intervention.

Machine Learning

This is the science of having a computer act without anyone needing to program it. Deep learning is a subset of machine learning that, in crude terms, can be described as the automation of predictive analytics. There are three kinds of machine learning algorithms:

  • Supervised learning: where data sets are labeled so that patterns can be detected and used to label new data sets
  • Unsupervised learning: data sets aren’t labeled and are arranged according to their similarities or differences
  • Reinforcement learning: data sets aren’t labelled, but after an action or a number of actions have been performed, the AI system gets feedback

Machine Vision

A camera gives visual information to the computer, which analyses it using sophisticated algorithms. It can see far beyond the range of human sight and can be used for anything from identifying signatures to analysing medical images. Computer vision, which focuses on image processing, is a term that is often used interchangeably with machine vision, although they are distinct areas.

Natural language processing (NLP)

NLP is using human computers to understand language. The best-known use for NLP is probably spam detection. It looks at email text using a machine learning approach and makes a good job of deciding whether it’s spam. NLP tasks include translating text, analysis of sentiment and recognising speech.

Robotics

Robots are most often used for repetitive tasks that are too difficult or dangerous for humans to perform either well enough or consistently enough. Many robots are used on car assembly lines, performing assembly tasks that include very precise welding. There are also efforts being made to build robots that can interact socially.

Self-driving cars

Self-driving cars combine computer vision, image recognition and deep learning to react to other traffic, keep the car in its lane, avoid collisions and so on.

AI Applications

There are six areas where artificial intelligence has found use:

Healthcare

Companies are hoping to improve patient care and cut costs by using machine learning to diagnose illnesses more accurately and more quickly than humans can. IBM Watson is one of the best-known examples of this technology. It understands language well enough to answer questions. It trawls through patient data and other sources to generate a hypothesis and will give a score to indicate how confident it is with its conclusion.

Other AI Applications Include Chatbots

They can simulate a human assistant and can help with making follow-up appointments or helping patients through the billing process, along with more general healthcare advice.

Business

Robotic process automation helps speed up repetitive tasks that people normally perform, and machine learning algorithms can analyse the interactions in CRM platforms to find better ways of serving customers. Chatbots can speed up customer service for some tasks that don’t require human assistance and there is speculation that in the future some jobs could be replaced by AI.

Education

AI can grade work and free up educators to get on with teaching. It can assess students and adapt to their needs so that they can work at their own pace. AI tutors can give extra student support, leading to more consistent progress. AI is changing how students learn and may even replace teachers in some areas.

Finance

AI applications like Mint or TurboTax take personal data and use it to offer financial advice and assistance. Programs like IBM Watson have even assisted with home buying, and AI has long been used to assist with Wall Street trading.

Law

The discovery process involves poring over piles of documents; so many that it can easily become overwhelming for the people doing it, which makes it a process that is ripe for automation. Start-ups have been creating question-and-answer computer assistants that can sift through programmed-to-answer questions by examining the taxonomy and ontology associated with a database.

Manufacturing

Industrial robots have been around for a long time, helping to manufacture cars since the 1950s, and they’ve grown more sophisticated as technology has advanced.

Web Hosting

In the present time custom AI solutions can handle:

  • scaling and predicting traffic spikes to ensure smooth user experience
  • managing workloads and ensuring uptime
  • adjusting automatically dedicated resources as memory, CPU and I/O to improve server performance
  • protecting the servers from brute-force attacks

Ethics and Security Concerns

Self-driving cars have pushed AI usage into the news. How ethical is it to allow such a potentially dangerous device as a car to drive itself, when it could be hacked and used to deliberately harm people? And who bears for the responsibility when it’s involved in an accident? What if the AI cannot avoid harming others while thinking it’s making the best decision?

Deep learning has found its way into the news as well, as it’s been used to generate deepfakes. These videos are realistic fabrications that show people saying and doing things that have never happened. At the very least, repressive regimes could use this technology to discredit people who they wish to suppress.

Regulating AI Technology

Even though AI presents so many potential risks, its speed of progression has outstripped the abilities of legislators to match it with appropriate safeguards.

Laws do exist but they tend to be quite specific, as in the case of federal Fair Lending regulations. These require financial institutions to explain credit decisions to would-be customers, which limits how much lenders can rely on deep learning algorithms to inform their decisions because they are opaque by their nature. Europe’s GDPR puts strict limits on how enterprises can use consumer data, which holds back the training and functionality of many consumer-facing AI applications.