CN117873841A - MySQL temporary table space early warning method based on threshold triggering and event statement monitoring - Google Patents

MySQL temporary table space early warning method based on threshold triggering and event statement monitoring Download PDF

Info

Publication number
CN117873841A
CN117873841A CN202311702997.0A CN202311702997A CN117873841A CN 117873841 A CN117873841 A CN 117873841A CN 202311702997 A CN202311702997 A CN 202311702997A CN 117873841 A CN117873841 A CN 117873841A
Authority
CN
China
Prior art keywords
mysql
temporary
threshold
sql
schema
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202311702997.0A
Other languages
Chinese (zh)
Inventor
冯信洁
汤志凤
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Tianyi Cloud Technology Co Ltd
Original Assignee
Tianyi Cloud Technology Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Tianyi Cloud Technology Co Ltd filed Critical Tianyi Cloud Technology Co Ltd
Priority to CN202311702997.0A priority Critical patent/CN117873841A/en
Publication of CN117873841A publication Critical patent/CN117873841A/en
Pending legal-status Critical Current

Links

Classifications

    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention relates to the technical field of database management, in particular to a MySQL temporary table space early warning method based on threshold triggering and event statement monitoring, which comprises the following steps: confirming that the performance_schema is in an on state; collecting the service condition of a temporary table space in a MySQL database in real time; dynamically calculating a threshold value of the MySQL temporary table space according to the history monitoring data; judging a dynamic threshold value and triggering early warning information; a key SQL digest text report is generated. The method and the device find out the problem of overlarge occupation of the temporary table space in time by dynamically calculating the threshold value, avoid the problem of database abnormality caused by the problems of disk capacity and the like, provide key SQL information for frequently using the temporary table and facilitate the optimization and management of an administrator.

Description

MySQL temporary table space early warning method based on threshold triggering and event statement monitoring
Technical Field
The invention relates to the technical field of database management, in particular to a MySQL temporary table space early warning method based on threshold triggering and event statement monitoring.
Background
MySQL is a common relational database management system, in practical applications, mySQL sometimes needs to create a temporary table to solve some queries, and in order to successfully obtain all query results, the size of the MySQL temporary table space is not set to an upper limit, which may cause a shortage of disk space if the temporary table space is used too much, thereby causing a series of problems such as data loss, performance degradation, system crash, and the like. The common temporary table space monitoring method monitors the use condition of the temporary table space by configuring a static threshold value through a timing script, the method relies on experience of operation and maintenance personnel, the configured threshold values of different operation and maintenance personnel have differences, the static threshold value can be adjusted at any time along with continuous change of business, otherwise, alarm inaccuracy can be caused, and meanwhile, the static threshold value is inaccurate for capturing the trend that the use amount of the temporary table space is abnormally increased. Therefore, the use size of the temporary tablespace can be monitored by adopting a dynamic threshold monitoring method, and meanwhile, key SQL text using the temporary tablespace is acquired by utilizing the monitoring of an event statement (events statements) of MySQLperformance schema, so that the situation of abnormal use of the temporary tablespace is early warned.
In the prior art, as disclosed in the patent with application publication number CN114153858A, a method and a device for automatically updating a tenant under MySQL table space are disclosed, which comprise: determining the maximum update sequence number of MySQL script of each service; for each service, scanning a tenant table space associated with the service in a MySQL table space pool, and executing MySQL script update on the tenant table space associated with the service; judging whether the current execution sequence number of the MySQL script of each service is the same as the maximum update sequence number; if the MySQL script is the same, the MySQL script upgrading of the service is confirmed to be completed;
the above-mentioned patent relates to interaction and management with MySQL databases, requiring reasonable configuration and maintenance of database servers to support script updating and checking operations. In addition, the management tasks such as the tablespace and script version in the above patent also require special resources and work, and a great deal of manpower work is consumed by the administrator.
Disclosure of Invention
This section is intended to outline some aspects of embodiments of the invention and to briefly introduce some preferred embodiments. Some simplifications or omissions may be made in this section as well as in the description summary and in the title of the application, to avoid obscuring the purpose of this section, the description summary and the title of the invention, which should not be used to limit the scope of the invention.
According to the invention, the problem of overlarge occupation of temporary table space is found in time through dynamic calculation of the threshold value, the problem of database abnormality caused by the problems of disk capacity and the like is avoided, key SQL information of frequently used temporary tables is provided, optimization and management are facilitated for an administrator, and a MySQL temporary table space early warning method based on threshold value triggering and event statement monitoring is provided.
In order to achieve the purpose, the technical scheme of the MySQL temporary table space early warning method based on threshold triggering and event statement monitoring comprises the following steps:
s1: confirming that the performance_schema is in an on state;
s2: collecting the service condition of a temporary table space in a MySQL database in real time;
s3: dynamically calculating a threshold value of the MySQL temporary table space according to the history monitoring data;
s4: judging a dynamic threshold value and triggering early warning information;
s5: a key SQL digest text report is generated.
Specifically, S1 includes the following specific steps:
s101: judging the use state of the performance_schema;
s102: modifying the performance_schemes table, the collector configuration item switch containing event statements (events_states) is opened.
Specifically, the performance_schema is a MySQL self-contained performance monitoring tool, and is used for recording the resource consumption condition of MySQL.
Specifically, in S101, when the performance_schema is in the off state, the performance_schema is turned ON by modifying the performance_schema attribute in the my.cnf configuration file, setting it to be performance_schema=on, and restarting MySQL.
Specifically, in S2, the usage of the temporary table space includes: the size of the current MySQL temporary table space is obtained from the monitoring data.
Specifically, S3 includes the following specific steps:
s301: defining a fixed size time window;
s302: sampling the temporary table space once every other fixed time, and recording the current size of the temporary table space;
s303: calculating the average value X and standard deviation S of all sampling values in a time window;
s304: the defined threshold is calculated according to a defined threshold formula.
Specifically, in S304, the defined threshold formula is as follows:
θ＝X±k×S；
wherein θ is a defined threshold; k is a monitoring custom coefficient.
Specifically, S4 includes the following specific steps:
s401: performing dynamic threshold judgment, triggering early warning information and executing S402 when the values of n continuous monitoring indexes exceed the threshold range;
s402: and (3) moving towards the time development direction with a fixed time window, repeating the step (S3), and continuously updating the threshold value.
Specifically, in the step S5, the generating of the key sqldiget text report includes: by querying the events_statements_summary_by_digest table in the performance_schema library, the SQL digest format text frequently used by the temporary table in the MySQL database is obtained, the SQL statements are optimized, and these SQL texts are consolidated into reports for the administrator.
Specifically, in S5, the key sqldiget text report includes the following key parameters: schema_NAME, DIGEST_TEXT, SUM_CREATE_TMP_TABLES, SUM_CREATE_TMP_DISK_TABLES, FIRST_SEEN, LAST_SEEN; wherein the schema_name is used for executing the SQL database NAME; the DIGEST_TEXT is used for executing a DIGEST TEXT of the SQL by carrying out hash calculation on the execution statement; the SUM_CREATED_TMP_TABLES is used for executing the SUM of the number of the memory temporary TABLES CREATED by SQL; the SUM_CREATED_TMP_DISK_TABLES is used for executing the SUM of the number of the DISK temporary TABLES CREATED by SQL; the first_seen is used to execute a timestamp of the FIRST observed statement; the LAST_SEEN is used for executing the timestamp that the statement was LAST observed.
Compared with the prior art, the invention has the following technical effects:
1. the invention adopts dynamic threshold calculation, can dynamically calculate the threshold of the temporary table space according to the historical monitoring data so as to ensure the accuracy and the effectiveness of the alarm, and can perform accurate early warning on the temporary table space to avoid false alarm and missing alarm.
2. According to the method, the sliding window algorithm is adopted to calculate the dynamic threshold, so that the calculation complexity can be effectively reduced, the calculation time is shortened, the early warning efficiency is improved, and meanwhile, the performance of the database is not excessively influenced.
3. The method and the system can trigger early warning in time when the temporary table space suddenly grows, an administrator can receive early warning information in time, synchronously acquire a key SQL digest text report and process the report in time, and avoid the risk of performance degradation or breakdown of the database caused by excessive occupation of the temporary table space.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are needed in the description of the embodiments will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
Wherein:
FIG. 1 is a flow chart of a MySQL temporary table space early warning method based on threshold triggering and event statement monitoring;
FIG. 2 is a schematic diagram of a sliding window calculation dynamic threshold according to the present invention.
Detailed Description
In order that the above-recited objects, features and advantages of the present invention will become more readily apparent, a more particular description of the invention will be rendered by reference to specific embodiments thereof which are illustrated in the appended drawings.
In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present invention, but the present invention may be practiced in other ways other than those described herein, and persons skilled in the art will readily appreciate that the present invention is not limited to the specific embodiments disclosed below.
Further, reference herein to "one embodiment" or "an embodiment" means that a particular feature, structure, or characteristic can be included in at least one implementation of the invention. The appearances of the phrase "in one embodiment" in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments.
Examples:
as shown in fig. 1 and 2, the MySQL temporary table space early warning method based on threshold triggering and event statement monitoring according to the embodiment of the invention, as shown in fig. 1, comprises the following specific steps:
s1: confirming that the performance_schema is in an on state;
s1 comprises the following specific steps:
s101: judging the use state of the performance_schema;
s102: modifying the performance_schemes table, the collector configuration item switch containing event statements (events_states) is opened.
The performance_schema is a performance monitoring tool of MySQL and is used for recording the resource consumption condition of MySQL.
In S101, when the performance_schema is in the closed state, the performance_schema is started by modifying the performance_schema attribute in the my.cnf configuration file, and is set to be performance_schema=on, and MySQL is restarted.
S2: collecting the service condition of a temporary table space in a MySQL database in real time;
in S2, the usage of the temporary table space includes: the size of the current MySQL temporary table space is obtained from the monitoring data.
S3: dynamically calculating a threshold value of the MySQL temporary table space according to the history monitoring data;
s3 comprises the following specific steps:
s301: defining a fixed size time window;
s302: sampling the temporary table space once every other fixed time, and recording the current size of the temporary table space;
s303: calculating the average value X and standard deviation S of all sampling values in a time window;
s304: the defined threshold is calculated according to a defined threshold formula.
In S304, the defined threshold formula is as follows:
θ＝X±k×S；
wherein θ is a defined threshold; k is a monitoring custom coefficient.
S4: judging a dynamic threshold value and triggering early warning information;
s4 comprises the following specific steps:
s401: performing dynamic threshold judgment, triggering early warning information and executing S402 when the values of n continuous monitoring indexes exceed the threshold range;
s402: and (3) moving towards the time development direction with a fixed time window, repeating the step (S3), and continuously updating the threshold value.
S5: a key SQL digest text report is generated.
In the step S5, the generating of the key SQL digest text report includes: SQL text in the SQL table format frequently used by the temporary table is obtained by querying the events_statements_summary_by_digest table in the performance_schema library, the SQL statement is optimized, and the SQL text is arranged into a report and provided to an administrator.
In the step S5, the key SQL digest text report comprises the following key parameters: schema_NAME, DIGEST_TEXT, SUM_CREATE_TMP_TABLES, SUM_CREATE_TMP_DISK_TABLES, FIRST_SEEN, LAST_SEEN; wherein the schema_name is used for executing the SQL database NAME; the DIGEST_TEXT is used for executing a DIGEST TEXT of the SQL by carrying out hash calculation on the execution statement; the SUM_CREATED_TMP_TABLES is used for executing the SUM of the number of the memory temporary TABLES CREATED by SQL; the said
SUM_CREATED_TMP_DISK_TABLES is used for executing the SUM of the number of temporary TABLES of the DISK CREATED by SQL; the first_seen is used to execute a timestamp of the FIRST observed statement; the LAST_SEEN is used for executing the timestamp that the statement was LAST observed.
It should be understood that, in various embodiments of the present application, the sequence numbers of the foregoing processes do not mean the order of execution, and the order of execution of the processes should be determined by the functions and internal logic thereof, and should not constitute any limitation on the implementation process of the embodiments of the present application.
It should be understood that determining B from a does not mean determining B from a alone, but can also determine B from a and/or other information.
The above embodiments may be implemented in whole or in part by software, hardware, firmware, or any other combination. When implemented in software, the above-described embodiments may be implemented in whole or in part in the form of a computer program product. The computer program product comprises one or more computer instructions or computer programs. When the computer instructions or computer program are loaded or executed on a computer, the processes or functions in accordance with embodiments of the present invention are produced in whole or in part. The computer may be a general purpose computer, a special purpose computer, a computer network, or other programmable apparatus. The computer instructions may be stored in or transmitted from one computer-readable storage medium to another, for example, by way of wired or/and wireless networks from one website site, computer, server, or data center to another. Computer readable storage media can be any available media that can be accessed by a computer or data storage devices, such as servers, data centers, etc. that contain one or more collections of available media. The usable medium may be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium. The semiconductor medium may be a solid state disk.
Those of ordinary skill in the art will appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
It will be clear to those skilled in the art that, for convenience and brevity of description, specific working procedures of the above-described systems, apparatuses and units may refer to corresponding procedures in the foregoing method embodiments, and are not repeated herein.
In the several embodiments provided by the present invention, it should be understood that the disclosed systems, devices, and methods may be implemented in other manners. For example, the apparatus embodiments described above are merely illustrative, e.g., the partitioning of units is merely one, and there may be additional partitioning in actual implementation, e.g., multiple units or components may be combined or integrated into another system, or some features may be omitted, or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.
The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed over a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional unit in the embodiments of the present invention may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit.
In the description of the present specification, the descriptions of the terms "one embodiment," "example," "specific example," and the like, mean that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiments or examples. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.
In summary, compared with the prior art, the technical effects of the invention are as follows:
1. the invention adopts dynamic threshold calculation, can dynamically calculate the threshold of the temporary table space according to the historical monitoring data so as to ensure the accuracy and the effectiveness of the alarm, and can perform accurate early warning on the temporary table space to avoid false alarm and missing alarm.
2. According to the method, the sliding window algorithm is adopted to calculate the dynamic threshold, so that the calculation complexity can be effectively reduced, the calculation time is shortened, the early warning efficiency is improved, and meanwhile, the performance of the database is not excessively influenced.
3. The method and the system can trigger early warning in time when the temporary table space suddenly grows, an administrator can receive early warning information in time, synchronously acquire a key SQL digest text report and process the report in time, and avoid the risk of performance degradation or breakdown of the database caused by excessive occupation of the temporary table space.
The foregoing has shown and described the basic principles and main features of the present invention and the advantages of the present invention. It will be understood by those skilled in the art that the present invention is not limited to the embodiments described above, and that the above embodiments and descriptions are merely illustrative of the principles of the present invention, and various changes and modifications may be made without departing from the spirit and scope of the invention, which is defined in the appended claims. The scope of the invention is defined by the appended claims and equivalents thereof.

Claims (10)

1. The MySQL temporary table space early warning method based on threshold triggering and event statement monitoring is characterized by comprising the following steps of: the method comprises the following specific steps:
s1: confirming that the performance_schema is in an on state;
s2: collecting the service condition of a temporary table space in a MySQL database in real time;
s3: dynamically calculating a threshold value of the MySQL temporary table space according to the history monitoring data;
s4: judging a dynamic threshold value and triggering early warning information;
s5: a key SQL digest text report is generated.
2. The MySQL temporary tablespace pre-warning method based on threshold triggering and event statement monitoring according to claim 1, wherein S1 comprises the following specific steps:
s101: judging the use state of the performance_schema;
s102: modifying the performance_schemes table, the collector configuration item switch containing event statements (events_states) is opened.
3. The MySQL temporary tablespace early warning method based on threshold triggering and event statement monitoring according to claim 1, wherein the performance_schema is a MySQL self-contained performance monitoring tool for recording the resource consumption condition of MySQL.
4. The MySQL temporary tablespace warning method based ON threshold triggering and event statement monitoring according to claim 1, wherein in S101, when the performance_schema is in a closed state, the performance_schema is turned ON by modifying the performance_schema attribute in the my.cnf configuration file, and the performance_schema is set to be performance_schema=on, and MySQL is restarted.
5. The MySQL temporary tablespace early warning method based on threshold triggering and event statement monitoring according to claim 1, wherein in S2, the usage situation of the temporary tablespace comprises: the size of the current MySQL temporary table space is obtained from the monitoring data.
6. The MySQL temporary tablespace pre-warning method based on threshold triggering and event statement monitoring according to claim 1, wherein S3 comprises the following specific steps:
s301: defining a fixed size time window;
s302: sampling the temporary table space once every other fixed time, and recording the current size of the temporary table space;
s303: calculating the average value X and standard deviation S of all sampling values in a time window;
s304: the defined threshold is calculated according to a defined threshold formula.
7. The MySQL temporary tablespace pre-warning method based on threshold triggers and event statement monitoring of claim 6, wherein in S304, the defined threshold formula is as follows:
θ＝X±k×S ；
wherein the method comprises the steps of ， θ is a defined threshold ； k is a monitoring custom coefficient 。
8. The MySQL temporary tablespace pre-warning method based on threshold triggering and event statement monitoring of claim 8, wherein S4 comprises the following specific steps:
s401: performing dynamic threshold judgment, triggering early warning information and executing S402 when the values of n continuous monitoring indexes exceed the threshold range;
s402: and (3) moving towards the time development direction with a fixed time window, repeating the step (S3), and continuously updating the threshold value.
9. The MySQL temporary tablespace pre-warning method based on threshold triggers and event statement monitoring according to claim 1, wherein in S5, the generating of the key SQL digest text report comprises: SQL text in the SQL table format frequently used by the temporary table is obtained by querying the events_statements_summary_by_digest table in the performance_schema library, the SQL statement is optimized, and the SQL text is arranged into a report and provided to an administrator.
10. The MySQL temporary tablespace pre-warning method based on threshold triggers and event statement monitoring according to claim 1, wherein in S5, the key SQL digest text report includes the following key parameters: SCHEMA_NAME, DIGEST_TEXT, SUM_CREATED_TMP_TABLES,
SUM_CREATED_TMP_DISK_TABLES, first_SEEN, LAST_SEEN; wherein the schema_name is used for executing the SQL database NAME; the DIGEST_TEXT is used for executing a DIGEST TEXT of the SQL by carrying out hash calculation on the execution statement; the SUM_CREATED_TMP_TABLES is used for executing the SUM of the number of the memory temporary TABLES CREATED by SQL; the said
SUM_CREATED_TMP_DISK_TABLES is used for executing the SUM of the number of temporary TABLES of the DISK CREATED by SQL; the first_seen is used to execute a timestamp of the FIRST observed statement; the LAST_SEEN is used for executing the timestamp that the statement was LAST observed.
CN202311702997.0A 2023-12-12 2023-12-12 MySQL temporary table space early warning method based on threshold triggering and event statement monitoring Pending CN117873841A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202311702997.0A CN117873841A (en) 2023-12-12 2023-12-12 MySQL temporary table space early warning method based on threshold triggering and event statement monitoring

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202311702997.0A CN117873841A (en) 2023-12-12 2023-12-12 MySQL temporary table space early warning method based on threshold triggering and event statement monitoring

Publications (1)

Publication Number Publication Date
CN117873841A true CN117873841A (en) 2024-04-12

Family

ID=90590820

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202311702997.0A Pending CN117873841A (en) 2023-12-12 2023-12-12 MySQL temporary table space early warning method based on threshold triggering and event statement monitoring

Country Status (1)

Country Link
CN (1) CN117873841A (en)

Similar Documents

Publication Publication Date Title
US6944630B2 (en) Database management system and method which monitors activity levels and determines appropriate schedule times
US20220083529A1 (en) Tracking database partition change log dependencies
US11329869B2 (en) Self-monitoring
US8131796B2 (en) Recoverable return code tracking and notification for autonomic systems
CN109144862A (en) Statistical method, device, computer equipment and the storage medium of test data
CN107491558B (en) Metadata updating method and device
US8229884B1 (en) Systems and methods for monitoring multiple heterogeneous software applications
US20170213142A1 (en) System and method for incident root cause analysis
US7562094B1 (en) Object-level database performance management
CN106844497A (en) The check device and method of a kind of database code
JP4911061B2 (en) Management system, history information storage method, and data structure of history information database
CN117873841A (en) MySQL temporary table space early warning method based on threshold triggering and event statement monitoring
US10614095B2 (en) Method and systems for monitoring changes for a server system
CN110493326B (en) Zookeeper-based cluster configuration file management system and method
EP3255563A1 (en) Method and systems for monitoring changes for a server system
JP2019057195A (en) Management system, management method, and management program
EP3951600B1 (en) Method and system for managing performance for use cases in software applications
CN116226171A (en) Method and device for automatically monitoring and killing metadata lock of MySQL database
Strate et al. Index Analysis
WO2021102743A1 (en) Method for acquring man-hours, apparatus for acquring man-hours, terminal device and server
CN115576926A (en) Distributed database supervisory systems
CN115374087A (en) Database change risk analysis method, device, equipment and readable storage medium
CN115718690A (en) Data accuracy monitoring system and method
CN114077510A (en) Method and device for fault root cause positioning and fault root cause display
CN117573680A (en) Positioning data transmission management system and method based on big data

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination