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.
What are the tools? We have various tools which we can use to improve the performance:
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:
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 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.
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
Feb 9, 2019, 6:34:22 PM | Anurag Srivastava
Feb 9, 2019, 12:06:18 PM | Anurag Srivastava
Jan 31, 2018, 6:11:29 AM | Anurag Srivastava
Mar 9, 2019, 8:20:38 AM | Anurag Srivastava
Sep 16, 2019, 5:55:06 AM | Anurag Srivastava
Apr 6, 2019, 8:41:41 PM | Anurag Srivastava
Jan 7, 2020, 7:15:34 PM | Anurag Srivastava
Jan 6, 2020, 7:30:13 PM | Anurag Srivastava
Jan 14, 2020, 10:22:34 AM | Anurag Srivastava
Jun 21, 2018, 4:58:11 AM | Anurag Srivastava
Jun 26, 2018, 4:50:18 PM | Anurag Srivastava
Jun 13, 2018, 3:44:01 PM | Anurag Srivastava
May 12, 2018, 4:47:07 PM | Anurag Srivastava
Aug 10, 2018, 7:14:40 PM | Anurag Srivastava
Mar 9, 2019, 8:20:38 AM | Anurag Srivastava
Apr 24, 2018, 2:52:28 PM | Anurag Srivastava
Jul 31, 2018, 6:16:42 PM | Anurag Srivastava
Jun 2, 2018, 10:49:54 AM | Anurag Srivastava