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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
  2. How to obtain MySQL metadata (metadata access methods)
  3. Beginners Guide to MySQL User Management
  4. Understanding MySQL Pluggable Authentication
  5. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  6. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
  7. How to Set Space limits for MySQL for database/schema/table
  8. Multi-Versioning in MySQL Database
  9. MySQL Grants – Setting User Permissions On Different Tables
  10. How to Backup a MySQL Cluster

You May Also Like

Primary Sidebar

Recent Posts

  • Basics of client connectivity in Oracle Data Guard configuration
  • ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  • How to relocate the redo log files to a different location on disk
  • Oracle Database: Redo log operations (Add/Drop/Change Location)
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary