How to find and analyze a SQL bottlenecks of incoming traffic from performance plan / profiling perspectives on a production system?
This question is simple if you have a similar pre-production environment with name “load-test” and you check all new changes / features before future release. From other side , there is a “performance_schema” in MySQL database with all needed fresher metrics and thresholds to make a decision about what’s wrong with database… So, this is an ideal world.
So, if your production system is under heavy loading then there is a recommendation to disable “performance_schema” because probably whole database will be impacted. Regarding pre-production environment with a similar production storage and vCPUs / RAM, so you know it’s not a free.
Regina decided to investigate MySQL General Log (which can enabled during a few minutes on production system) and split read and write SQL traffic, gather statistics about table usage, EXPLAIN and PROFILE SQL traffic to find some not good metrics like “temporary table creation” or “sequential scan of a table”.