Answers

• 

  Anurag Srivastava

  Oct 3, 2020, 10:15:12 AM | Share

  • Facebook
  • Twitter
  • WhatsApp
  • Linkedin
  • Copy Link

  We have various tools which we can use to improve the MySQL performance, some of them are as follows:

  Explain Query
  

  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 to 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.

  If you want to learn more then please refer my two blogs on MySQL performance:

  • MySQL Performance Tuning
  • How MySQLTuner can increase MySQL performance

  If you have any further queries then please let me know.

  0 | 0