MySQL stored procedure performance monitoring and analysis

Source: Internet
Author: User
Tags mysql version

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.