Blog Detail

Covid-19 Global Tracker
preview image DevOps
by Anurag Srivastava, Jan 31, 2018, 6:15:34 AM | 5 minutes |

MySQL Performance Tuning

When we talk about performance tuning the first thing which comes in our mind is "When should we start ?". During Development to catch bad queries early or on production to catch good queries starting to go bad. So what I suggest is both, because it is not a task to fix once rather than a process which involves a lot of things, which I am going to discuss here.

So what are the main reasons for the bad performance of the database? There are many reasons for that but I am mentioning some of them like workload changes, data size, changing query plans, changing MySQL settings and hardware changes.

How can we get optimum performance by tweaking the queries? We should use the IN clause as it is fast. Never use select * as this will decrease the query performance. Keep column alone on left side of condition, we should use the function on value rather than column name to increase the performance. Avoid % at the start of value in LIKE queries as this will avoid the index. If columns used in the ORDER BY clause are indexed they help with performance.

When to use indexes?  We should avoid using indexes before we really need them. Only large tables need indexes as they multiply the record count by the field's length. We can monitor the slow query logs to decide the indexing process.

How to use multi-column efficient sorting with index? Suppose we have an index on columns A and B as KEY(A, B). Now there are two scenarios, one in which index will work while in other, the index will not work.

Will use Index for sorting:

    ORDER BY A - Sorting by leading column
    A=5 ORDER BY B - EQ filtering by 1st and sorting by 2nd
    ORDER BY A DESC, B DESC - Sorting by 2 columns in same order
    A>5 ORDER BY A - Range and sorting on the 1st column

    Will NOT use Index for sorting:

    ORDER BY B - Sorting by second column in the index
    A>5 ORDER BY B - Range on first column, sorting by second
    A IN (1,2) ORDER BY B - In-Range on first column
    ORDER BY A ASC, B DESC - Sorting in the different order

    When to use the covering index? We can use the covering index where the index is typically smaller than data, like "SELECT status from orders where customer_id = 123" so here we can create an index as KEY(customer_id, status) which will serve the results from index itself rather than searching the actual table.


    Available Tools:

    What are the tools? We have various tools which we can use to improve the performance:


    Explain

    EXPLAIN is one of the most powerful tools for understanding and optimizing troublesome MySQL queries. We can type explain before a query to get the insight of the query.

    EXPLAIN SELECT c.name, y.name, y.population, l.language from country as c, city as y, countrylanguage as l where y.name = c.name and l.countrycode = y.countrycode and c.name = 'India'


    Performance_Schema (MySQL 5.5+)

    Performance Schema provides the user with an insight into the scene picture of MySQL. It was introduced in MySQL version 5.5 with the introduction of a new storage engine 'Performance Schema' and a new database named 'Performance Schema'. It provides us a SQL user interface to get the insight.

    Performance Schema has many instruments to monitor the activities. It has a tree-like structure separated by '/' where when we move from left to right it moves from more generic o more specific:


    statement/sql/select
    statement/sql/create_table

    There are more than 1000 instruments in MySQL 5.7 which are stored in performance_schema.setup_instruments table.  For example, if we want to know the slow queries which are taking too long to load, we can run the following query:

    SELECT digest_text , count_star, avg_timer_wait    FROM events_statements_summary_by_digest  ORDER BY avg_timer_wait DESC  LIMIT 1\G;


    MySQL sys Schema (MySQL 5.7.7+)

    MySQL sys schema has a set of objects that helps us to interpret data collected by the Performance Schema. It converts the performance schema results in a more meaningful way which is easy to understand.

    Like if I want to find out all unused indexes:

    select * from sys.schema_unused_indexes;


    Here we can get queries with errors or warnings:

    select * from sys.statements_with_errors_or_warnings;


    pt-query-digest (Percona Toolkit)

    pt-query-digest is a command-line tool from Percona Toolkit. It is used to analyze MySQL queries from logs, process-list, and tcpdump. It has a powerful filtering capability and can take input from multiple sources like slow query log, general query log, tcpdump, binary log, and process list, etc and can create the report through which we can take decisions.

    pt-index-usage (Percona Toolkit)

    It read queries from a log file and analyze how they use indexes. This tool can connect to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. Once this process is done it prints out a report on indexes and lists those queries which are not using indexes.

    sudo pt-index-usage --ask-pass  ~/Downloads/SlaveDB-slow.log


    mysqltuner

    MySQLTuner is a script file that is written in Perl and allows us to review a MySQL installation. It makes adjustments to increase the performance and stability of the MySQL server. Configuration variables and status data is fetched through MySQLTuner script. After accessing these details it presents a brief detail about the setup along with some basic performance suggestions. These suggestions are quite handy and can help us to quickly fix some of the issues in practically no time.

    This blog provides a basic introduction to the tools which I have discussed here but I will try to write on each of them in detail.

    About Author

    Anurag Srivastava

    Author of “Mastering Kibana 6.x”, “Kibana 7 Quick Start Guide”, “Learning Kibana 7”, &“Elasticsearch 7 Quick Start Guide” books & AWS Certified Solutions Architect.

    View Profile

    Comments (3)

    • user image
      jitender yadav
      Feb 12, 2018, 6:24:56 PM

      good blog , really helpful

    • user image
      jitender yadav
      Feb 12, 2018, 6:27:13 PM

      good blog , really helpful

    • user image
      Anurag Srivastava
      Jan 1, 2020, 6:07:47 PM

      Thanks Jeetu.

    Leave a comment

    Related Blogs

    Loading CSV Data into Elasticsearch

    Feb 9, 2019, 6:34:22 PM | Anurag Srivastava

    Execute Commands on Remote Machines using sshpass

    Jul 16, 2018, 5:00:02 PM | Anurag Srivastava

    Configuring Logstash to push MySQL data into Elasticsearch

    Feb 9, 2019, 12:06:18 PM | Anurag Srivastava

    Log analysis with Elastic stack

    Jan 31, 2018, 6:11:29 AM | Anurag Srivastava

    Configuring Logstash to send MongoDB data into Elasticsearch

    Mar 9, 2019, 8:20:38 AM | Anurag Srivastava

    Snapshot and Restore Elasticsearch Indices

    Sep 16, 2019, 5:55:06 AM | Anurag Srivastava

    How to create Elasticsearch Cluster

    Apr 6, 2019, 8:41:41 PM | Anurag Srivastava

    Introduction to Elastic APM

    Jan 7, 2020, 7:15:34 PM | Anurag Srivastava

    Why monitoring is important?

    Jan 6, 2020, 7:30:13 PM | Anurag Srivastava

    Configuring Django application with Elastic APM

    Jan 14, 2020, 10:22:34 AM | Anurag Srivastava

    Top Blogs

    Configure SonarQube Scanner with Jenkins

    Jun 21, 2018, 4:58:11 AM | Anurag Srivastava

    Deploying Angular code using Python script

    Jun 26, 2018, 4:50:18 PM | Anurag Srivastava

    Execute Commands on Remote Machines using sshpass

    Jul 16, 2018, 5:00:02 PM | Anurag Srivastava

    Configure Jenkins for Automated Code Deployment

    Jun 13, 2018, 3:44:01 PM | Anurag Srivastava

    SonarQube installation on Ubuntu

    May 12, 2018, 4:47:07 PM | Anurag Srivastava

    Wildcard and Boolean Search in Elasticsearch

    Aug 10, 2018, 7:14:40 PM | Anurag Srivastava

    Configuring Logstash to send MongoDB data into Elasticsearch

    Mar 9, 2019, 8:20:38 AM | Anurag Srivastava

    Why SonarQube is important for IT projects ?

    Apr 24, 2018, 2:52:28 PM | Anurag Srivastava

    Elasticsearch Rest API

    Jul 31, 2018, 6:16:42 PM | Anurag Srivastava

    Analyze your project with SonarQube

    Jun 2, 2018, 10:49:54 AM | Anurag Srivastava