MySQL Query Profiler

This blog article shows you how to trace the SQL Statement in MySQL. In order to reproduce what I have done using the guide, the URL at the bottom, you need to install the sakila sample database to your MySQL server.

Below are the SQL commands that I issued to MySQL server.

The first line is for you to connect to your MySQL using the command prompt.

mysql -u root -p

SELECT * FROM performance_schema.setup_actors;

UPDATE performance_schema.setup_actors SET ENABLED = ‘NO’, HISTORY = ‘NO’ WHERE HOST = ‘%’ AND USER = ‘%’;

I have also created a user named test_user before excuse the line below.

Also: Create User in MySQL and Assign Privilege

INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES(‘localhost’,‘test_user’,’%’,’YES’,’YES’);

UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’, TIMED = ‘YES’ WHERE NAME LIKE ‘%statement/%’;

UPDATE performance_schema.setup_instruments SET ENABLED = ‘YES’, TIMED = ‘YES’ WHERE NAME LIKE ‘%stage/%’;

UPDATE performance_schema.setup_consumers SET ENABLED = ‘YES’ WHERE NAME LIKE ‘%events_statements_%’;

UPDATE performance_schema.setup_consumers SET ENABLED = ‘YES’ WHERE NAME LIKE ‘%events_stages_%’;

SELECT
* FROM actor
WHERE
actor_id
=
1

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like ‘%1%’;

You should be about to find the SQL Statement that you want to trace like the one above.

Source code download: https://github.com/chanmmn/mysql/tree/main/2024/MySQLProfiling/?WT.mc_id=DP-MVP-36769

Reference: https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html?WT.mc_id=DP-MVP-36769