How do you find the slowest queries on a database server?

What the interviewer wants to hear

• Enable slow query logging with a threshold
• Use pg_stat_statements or MySQL performance_schema
• Look at total time, not only single slow runs
• Use EXPLAIN ANALYZE on the worst queries
• Check monitoring for CPU, IO and locks

Model answer

First I turn on slow query logging: in PostgreSQL log_min_duration_statement, in MySQL the slow query log with long_query_time. Then I use aggregated statistics, which are more useful than single log lines. In PostgreSQL that is the pg_stat_statements extension; in MySQL it is performance_schema or the sys schema. I sort by total execution time, because a 5 ms query running a million times can cost more than one 10 second report. For the top queries I run EXPLAIN ANALYZE to see if there is a sequential scan, a bad join or a wrong row estimate. I also check monitoring for CPU, disk IO and lock waits, because sometimes queries are slow only because they are blocked by something else.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.