MySQL stored procedure performance monitoring and analysis

The current version of the company's system uses a large number of stored procedures, some complex process, a keynote user may invoke up to dozens of other small business logic and judgment, do not say it is unreasonable, in mainland China, at least 30% of the securities Trading system code is to write business logic with stored procedures, including SQL Server/oracle/mysql, three versions all have, so BS write business in the storage process of the students do not underestimate, it is likely that you are using the stored procedures developed in the world one of the most stable systems.

In MySQL version 5.6, the Performance_schema.events_statements_history and Events_statements_history_ Long stores all the most recently executed stored procedures and the SQL statements in the stored procedure, except that the design of this version does not take into account that the SQL is executed independently and those that are included in the stored procedure. So, this is still very troublesome.

In MySQL 5.7, also in the Performance_schema.events_statements_history and Events_statements_history_long tables, you can see the difference between the two, as shown below:

CREATE PROCEDURE Sp_test

BEGIN
INSERT into T1 (name) VALUES (' Abafewefwefw '), (' abafewefwefw11111 ');
Delete from T1 limit 1;
END

For SQL statements embedded in stored procedures, the object_type and object_name columns indicate the name of the object. For the stored procedure call itself, it is empty. At least in the 5.6 and previous versions, you still have to borrow slow logs.

Because these two tables are FIFO nature, so when set setup_instruments, not all the statement are enabled, so there will be a lot of metadata statements, resulting in this table growth super fast (see Event_Name generally can guess out, However, the abstract must be fully enabled, otherwise statement related events will be disabled, which is a prerequisite.

MySQL stored procedure performance monitoring and analysis