1. Query Response Time

Query Metrics

In order to identify each query, it hast to be digested like below:

-- SQL Sample
SELECT col FROM tbl WHERE id = 1;


-- Digest Query
SELECT `col` FROM `tbl` WHERE `id` = ?

-- Digest Hash (SHA-256)
3d590d7a91357f8b1e2f8458f8289111321b6bbad6ff3eb785e4fc18a79ca06c

Lock time

Metrics

• Performance Schema
  • doesn't contain row lock wait time
  • only contains metadata lock and table lock
• Slow Query Log
  • contains all of them (row lock + metadata lock + table lock)

Reads

• Non-locking reads
  • doesn't acquire row locks but does acquire (shared) metadata lock and table lock
  • doesn't mean non-blocking
    • in that shared metadata lock blocks exclusive metadata locks acquired by ALTER TABLE statement
• Locking reads
  • SELECT ... FOR UPDATE
  • SELECT ... FOR SHARE

Query Optimization

• Direct
  • Change query itself
  • Add an appropriate index
• Indirect
  • Change data
  • Change access pattern