• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

What’s a good process to find and eliminate slow queries in MySQL

by admin

There are a few approaches you may take to find slow queries. The best one with which to start is to enable the slow query log by adding slow_query_log server option to your server start-up or options files. You also need to set the long_query_time to filter which queries are slow. This will log slow queries together with the time it took to execute them to [hostname]-slow.log in your MySQL server data directory. If you have a large number of slow queries, you might start with higher values which will show the slowest queries and gradually reduce your settings until you’re satisfied with the results.

Configuring mysqld to log slow queries

You could also enable the log_queries_not_using_indexes option, which will log queries that are performing full table scans. Some of such queries are normal. However, you might wish to review these as they are frequently the reason for performance problems. You may also use mysqldumpslow utility to parse your slow query log and to summarize all similar queries. This utility is especially helpful if you have many slow queries.

Besides the slow query log, you might want to look at the processlist using the “SHOW PROCESSLIST SQL” statement. Sometimes you can see a query which is so sub-optimal, one which never finishes. Such queries that never finish will never register in the slow query log and will sometimes run for weeks until the server is restarted. You also should also check the processlist and look for any queries appearing frequently. These might be fast enough not to register in the slow query log, but also frequent enough to drain server resources.

Once you’ve discovered candidates for optimization, use the “EXPLAIN SQL” statement to see if particular queries are executing optimally.

References

https://dev.mysql.com/doc/en/server-system-variables.html#sysvar_log_queries_not_using_indexes
https://dev.mysql.com/doc/en/slow-query-log.html
https://dev.mysql.com/doc/en/server-system-variables.html#sysvar_long_query_time
https://dev.mysql.com/doc/en/show-processlist.html
https://dev.mysql.com/doc/en/explain.html
https://dev.mysql.com/doc/en/server-system-variables.html#sysvar_slow_query_log

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  2. Understanding MySQL Pluggable Authentication
  3. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  4. How To Start And Stop MySQL Cluster
  5. Granting All Privileges On All databases Except One Specific Table in MySQL
  6. How to Restrict MySQL User Creation with Blank Password
  7. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL
  8. System Tuning for MySQL Server
  9. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  10. Beginners Guide to MySQL User Management

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright