Method and system for acquiring SQL performance data of database

A collection method and collection system technology, applied in the field of databases, can solve problems such as limited performance, uncertain duration of MySQL performance jitter, and inability to obtain the first on-site data analysis, so as to achieve the effect of improving efficiency

Active Publication Date: 2019-11-08
杭州有赞科技有限公司
View PDF13 Cites 2 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0005] However, the duration of MySQL performance jitter is uncertain, and the granularity of monitoring tools is usually at the minute level. Therefore, when the duration of MySQL performance jitter is short, the information provided by the slow check and other logs is relatively limited. When the application uses MySQL abnormally, log in to the server for analysis, and the database has returned to normal, which makes it impossible to obtain the first site data for effective analysis

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Method and system for acquiring SQL performance data of database
  • Method and system for acquiring SQL performance data of database
  • Method and system for acquiring SQL performance data of database

Examples

Experimental program
Comparison scheme
Effect test

Embodiment 1

[0052] Embodiment 1, a kind of collection method of database SQL performance data, such as figure 1 shown, including the following steps:

[0053] S100. Detecting whether performance jitter occurs in the MySQL instance and the host, respectively, and generates a detection result when performance jitter occurs in the MySQL instance and / or the host;

[0054] S200. Collect host performance data and corresponding MySQL performance data based on the detection results until the detected MySQL instance and / or the host that is jittering returns to normal. At this time, the collection of host and corresponding MySQL performance data is suspended.

[0055] Note: Host performance data includes at least CPU data, IO data, and network data. The above CPU data includes at least CPU usage, and IO data includes at least IO usage.

[0056] MySQL performance data includes at least the engine status of the MySQL instance, the active transaction list, the executed SQL list, and lock waiting info...

Embodiment 2

[0109] Embodiment 2, based on the step S200 in embodiment 1, collect the host performance data and the corresponding MySQL performance data based on the detection results and then add the step of analyzing and diagnosing the performance data, and the rest are the same as in embodiment 1. The specific steps for analyzing and diagnosing the performance data are:

[0110] When performance jitter occurs, match the host performance data and corresponding MySQL performance data with the preset analysis rules in the rule base, and obtain the slow query log, kill log and / or information_schema database according to the matching analysis rules, and combine the host performance Data and corresponding MySQL performance data are analyzed, and diagnostic results are output.

[0111] The preset analysis rules are the rules set by the staff themselves according to actual needs when analyzing MySQL performance problems. Those skilled in the relevant technical fields can set the analysis rules t...

Embodiment 3

[0122] Embodiment 3, a collection system of database SQL performance data, including detection module 1 and collection module 2;

[0123] The detection module 1 is used to respectively detect whether the MySQL instance and the host have performance jitter, and generate a detection result when the MySQL instance and / or the host has performance jitter;

[0124] The collection module 2 is used to collect host performance data and MySQLMySQL performance data based on the detection results, until the MySQL instance and / or the host that is detected to be jittering returns to normal, and at this time, the collection of the performance data of the host and corresponding MySQL is suspended.

[0125]Further, the detection module 1 includes a detection thread creation unit 11, a host detection unit 12 and an instance detection unit 13;

[0126] The detection thread creation unit 11 is used to create a mainframe detection thread and several MySQL detection threads, wherein the mainframe d...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

Abstract

The invention discloses a method for acquiring SQL performance data of a database, which comprises the following steps: respectively detecting whether a MySQL instance and a host have performance jitter or not, and generating a detection result when the MySQL instance and / or the host have / has the performance jitter; and acquiring host performance data and corresponding MySQL performance data basedon the detection result until detecting that the jittered MySQL instance and / or host returns to normal, and suspending acquisition of the performance data of the host and the corresponding MySQL at the moment. According to the method, the performance data of the MySQL instance / host when the performance problem occurs can be obtained in time, and the situation that in the prior art, the problem ofperformance jitter cannot be solved due to the fact that the site where the problem occurs when operation and maintenance personnel log in a database does not exist is effectively avoided.

Description

technical field [0001] The invention relates to the field of databases, in particular to a method and system for collecting SQL performance data of a database. Background technique [0002] SQL, Structured Query Language (Structured Query Language); [0003] MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System, relational database management system) application software. [0004] In the era of big data, the data types and scale of database systems are constantly expanding, which brings certain challenges to database management. Nowadays, when MySQL performance fluctuates, staff often need to log in to the database server or log platform to obtain MySQL slow check, killed SQL logs, error logs and other information, combined with monitoring tools to obtain MySQL historical performance data for manual analysis. [0005] However, the duration of MySQL perform...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
Patent Type & Authority Applications(China)
IPC IPC(8): G06F11/36G06F16/28
CPCG06F11/3688G06F11/3692G06F16/284Y02D10/00
Inventor 王航威杨奇龙
Owner 杭州有赞科技有限公司
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap