SQL Server slow log real-time acquisition and storage system and method

Abstract

The invention belongs to the technical field of slow log acquisition, and discloses an SQL Server slow log real-time acquisition and storage system, which comprises an SQL Server, a python and MySQL, the python is used for reading data for analysis; the input end of the python is connected with the SQL Server, and the python comprises a real-time analysis report service; the input end of the MySQL is connected with the python, and the MySQL is used for collecting and storing the analyzed data; and the real-time analysis and reporting service realizes reading of an SQL Server extended event file, analyzes a read slow log field, reads full-amount or incremental data and stores the full-amount or incremental data in MySQL (Structured Query Language). According to the method, after the SQL Server slow logs are collected and stored in real time, the Python script is written to read the latest slow log file name, the slow log data are analyzed, and the slow log data are stored in the MySQL in a full-amount and incremental mode, so that efficiency is improved, and resource occupation of online instances caused by direct query of slow log file analysis is reduced.

Description

Technical Field

[0001] The present invention belongs to the technical field of slow log collection, and specifically relates to a system and method for real-time collection and preservation of SQL Server slow logs. Background Art

[0002] A slow log is a log file that records operations in a database that take a long time to execute. In a database system, a slow query usually refers to a query operation whose execution time exceeds a preset threshold.

[0003] SQL Server slow log collection technology is a commonly used database performance analysis method that can help DBAs or developers discover slow query operations and bottleneck problems in SQL Server databases.

[0004] The existing SQL Server slow log collection technology has the following shortcomings: the existing SQL Server slow log collection technology includes Redgate SQL Monitor. The back-end data collection, storage and analysis of this collection technology may adopt a specific technical architecture, which may not support user-defined statistics and analysis logic. In addition, in order to ensure the stability and performance of the software, the software provider may also strictly control and optimize the back-end data processing process, resulting in opacity in the back-end data collection, storage and analysis, and the inability to perform personalized statistics and analysis based on the back-end data, which greatly affects the user experience. Therefore, it needs to be improved. Summary of the invention

[0005] The object of the present invention is to provide a system and method for real-time collection and preservation of SQL Server slow logs to solve the problems raised in the above background technology.

[0006] In order to achieve the above object, the present invention provides the following technical solutions: a SQL Server slow log real-time collection and storage system, comprising: SQL Server, Python and MySQL, Python is used to read data for parsing;

[0007] The Python input terminal is connected to SQL Server, including real-time analysis and reporting services;

[0008] The input end of MySQL is connected to Python to collect and save the parsed data;

[0009] The real-time analysis and reporting service implements SQL Server extended event file reading, parses the slow log fields being read, reads the full or incremental data, and saves it to MySQL.

[0010] Preferably, the SQL Server includes: an extended event component and a slow log file, and the extended event component stores the content of the SQL Server through the slow log file.

[0011] Preferably, when the real-time analysis and reporting service reads records, it reads the log file in full and then performs analysis, saves the analyzed data to MySQL, and reads the latest slow log file to make the last collected file equal to the latest file.

[0012] Preferably, Python analyzes the collected files, and when the last collected file is not equal to the latest file, the latest log file is read in full and the log position is parsed, and the parsed data is saved to MySQL.

[0013] Preferably, when the real-time analysis and reporting service reads no records, the log file is incrementally read and then analyzed, the parsed data is saved to MySQL, and the log file name and offset position are recorded.

[0014] Preferably, Python reads files and parses slow log data through real-time parsing and reporting services to achieve full or incremental data collection.

[0015] Preferably, MySQL analyzes and counts the collected data, and Python parses the slow logs by writing scripts and saves them in MySQL.

[0016] The beneficial effects of the present invention are as follows:

[0017] 1. After collecting and saving the SQL Server slow log in real time, the present invention writes a Python script to read the latest slow log file name, parses the slow log data, and saves the slow log data in full and incrementally to MySQL, thereby improving efficiency and reducing the resource occupation of online instances by directly querying slow log files for analysis.

[0018] 2. The present invention stores the data in the relational database MySQL, which makes it easier to analyze and count the slow log data, and also facilitates the development of related system products based on this.

[0019] 3. The present invention facilitates centralized storage and comparative analysis of slow logs of multiple instances by writing Python scripts. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 This is the overall system architecture diagram of the present invention;

[0021] Figure 2 The system of the present invention collects logic flow charts in real time. DETAILED DESCRIPTION

[0022] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0023] like Figure 1 to Figure 2 As shown, an embodiment of the present invention provides a SQL Server slow log real-time collection and storage system, including: SQL Server, Python and MySQL, Python is used to read data for parsing;

[0024] The Python input terminal is connected to SQL Server, including real-time analysis and reporting services;

[0025] The input end of MySQL is connected to Python to collect and save the parsed data;

[0026] The real-time analysis and reporting service implements SQL Server extended event file reading, parses the slow log fields being read, reads the full or incremental data, and saves it to MySQL.

[0027] This part implements SQL Server extended event file reading, parses the slow log fields in the reading, reads the full or incremental data, and saves it to MySQL;

[0028] By reading files and parsing slow log data, full or incremental data collection is achieved

[0029] Fully read and parse the key code of the file:

[0030]

[0031]

[0032]

[0033]

[0034]

[0035]

[0036]

[0037]

[0038]

[0039] like Figure 1 to Figure 2 As shown, SQL Server includes: an extended event component and a slow log file. The extended event component stores the content of SQL Server through the slow log file.

[0040] When you need to collect slow log data, first create an Extended Events session

[0041] CREATE EVENT SESSION EE_SlowQueryLog ON SERVER

[0042] ADD EVENT sqlserver.sp_statement_completed -- Stored procedure execution completed event (

[0044] ACTION(

[0045] sqlserver.session_id

[0046] ,sqlserver.client_app_name

[0047] ,sqlserver.client_hostname

[0048] ,sqlserver.database_id

[0049] ,sqlserver.database_name

[0050] ,sqlserver.plan_handle

[0051] ,sqlserver.sql_text

[0052] ,sqlserver.username )

[0054] WHERE duration>500000--500milliseconds in microseconds

[0055] AND sql_text NOT LIKE'WAITFOR(RECEIVE message_body FROM WMIEventProviderNotificationQueue)%'--Exclude WMI waits

[0056] AND sql_text NOT LIKE '%fn_xe_file_target_read_file%' -- Exclude traffic from stored procedure to read data

[0057] AND sql_text NOT LIKE '%sys.%'

[0058] AND username <> 'NT AUTHORITY\SYSTEM'

[0059] AND username <> 'NT AUTHORITY\NETWORK SERVICE'

[0060] AND username <> 'sa'

[0061] AND username <> 'replication_user'

[0062] AND username <> 'monitor_user'

[0063] ),

[0064] ADD EVENT sqlserver.sql_statement_completed -- SQL execution completed event(

[0065] ACTION(

[0066] sqlserver.session_id

[0067] ,sqlserver.client_app_name

[0068] ,sqlserver.client_hostname

[0069] ,sqlserver.database_id

[0070] ,sqlserver.database_name

[0071] ,sqlserver.plan_handle

[0072] ,sqlserver.sql_text

[0073] ,sqlserver.username )

[0075] WHERE duration>500000--500milliseconds in microseconds

[0076] AND sql_text NOT LIKE'WAITFOR(RECEIVE message_body FROM WMIEventProviderNotificationQueue)%'--Exclude WMI waits

[0077] AND sql_text NOT LIKE'%fn_xe_file_target_read_file%'--Excludetraffic from stored procedure to read data )

[0079] ADD TARGET package0.event_counter,

[0080] ADD TARGET package0.asynchronous_file_target (

[0082] SET FILENAME = N' / data / sql_backup / EE_SlowQueryLog.xel', -- file name, if it is a Windows machine, store it in D:\SQL_BACKUP or E:\SQL_BACKUP

[0083] METADATAFILE = N' / data / sql_backup / EE_SlowQueryLog.xem', -- file name, if it is a Windows machine, store it in D:\SQL_BACKUP or E:\SQL_BACKUP

[0084] max_file_size=100,--Maximum file size 100M

[0085] max_rollover_files=50 - retain up to 50 files

[0086] )WITH(

[0087] max_dispatch_latency=30seconds,

[0088] EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, -- Allow multiple events to be lost, maximum performance

[0089] STARTUP_STATE = ON - Start when the service starts )

[0091] Extended Events starts a session to collect slow log data

[0092] --Start event collection

[0093] ALTER EVENT SESSION EE_SlowQueryLog ON SERVER STATE=START

[0094] Write a Python script to parse slow logs and save them to MySQL

[0095] 1. Read the latest slow log file name

[0096] 2. Parsing slow log data

[0097] 3. Save slow log data in full and incremental form to MySQL

[0098] like Figure 2 As shown, when the real-time analysis and reporting service reads records, it reads the log file in full and then performs analysis, saves the analyzed data to MySQL, and reads the latest slow log file to make the last collected file equal to the latest file.

[0099] The real-time parsing and reporting service of Python is used to read the name and offset of the last collected file and determine whether there is a record before. If there is a record, the log file is incrementally read for the last collected file, and the file is parsed after reading. The parsed data is then saved in MySQL, and the latest slow log file is read.

[0100] like Figure 2 As shown, Python analyzes the collected files. When the last collected file is not equal to the latest file, it reads the latest log file in full and parses the log position, and saves the parsed data to MySQL.

[0101] When there is a record that the latest slow log is different from the last collected file, the latest log file is read in full, the file is parsed after reading, the parsed data is saved in MySQL, and the log file name and offset position are recorded.

[0102] like Figure 2As shown, when the real-time analysis and reporting service reads no records, it incrementally reads the log file and performs analysis, saves the analyzed data to MySQL, and records the log file name and offset position.

[0103] After reading the last collected file name and offset position, the analysis is performed. If there is no record, the latest log file is fully read. After reading, the file is parsed, and the parsed data is saved in MySQL, and the log file name and offset position are recorded;

[0104] like Figure 2 As shown in the figure, Python reads files and parses slow log data through the real-time parsing and reporting service to achieve full or incremental data collection.

[0105] This part implements SQL Server extended event file reading, parses the slow log fields in the reading, reads the full or incremental data, and saves it to MySQL.

[0106] like Figure 1 As shown in the figure, MySQL analyzes and counts the collected data, and Python parses the slow logs by writing scripts and saves them to MySQL.

[0107] The steps include:

[0108] S1. First, create an Extended Events session. The Extended Events session starts to collect slow log data and collect and save SQL Server slow logs in real time.

[0109] S2. Read the name and offset of the last collected file through Python's real-time parsing and reporting service, and determine whether there is a record before;

[0110] S3. When there are records, incrementally read the log file for the last acquisition file, parse the file after reading, save the parsed data to MySQL, and read the latest slow log file;

[0111] S4. When there is no record, read the latest log file in full, parse the file after reading, save the parsed data in MySQL, and record the log file name and offset position;

[0112] S5. When there is a record of reading the latest slow log that is different from the last collected file, read the latest log file in full, parse the file after reading, save the parsed data in MySQL, and record the log file name and offset position.

[0113] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device.

[0114] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A SQL Server slow log real-time collection and storage system, characterized in that: The system includes: SQL Server, Python and MySQL, wherein Python is used to read data for parsing; The input end of the python is connected to the SQL Server, including a real-time analysis and reporting service; The input end of the MySQL is connected to Python to collect and save the parsed data; The real-time analysis and reporting service implements SQL Server extended event file reading, and parses the slow log fields being read, performs full or incremental data reading and saves it to MySQL.

2. A SQL Server slow log real-time collection and storage system according to claim 1, characterized in that: The SQL Server includes: an extended event component and a slow log file, and the extended event component stores the content of the SQL Server through the slow log file.

3. A SQL Server slow log real-time collection and storage system according to claim 2, characterized in that: When the real-time analysis and reporting service reads records, it reads the log file in full and then performs analysis, saves the analyzed data to MySQL, and reads the latest slow log file to make the last collected file equal to the latest file.

4. A SQL Server slow log real-time collection and storage system according to claim 3, characterized in that: The python analyzes the collected files. When the last collected file is not equal to the latest file, the latest log file is read in full and the log position is parsed, and the parsed data is saved to MySQL.

5. A SQL Server slow log real-time collection and storage system according to claim 4, characterized in that: When the real-time analysis and reporting service reads no records, it incrementally reads the log file and then performs analysis, saves the analyzed data to MySQL, and records the log file name and offset position.

6. A SQL Server slow log real-time collection and storage system according to claim 5, characterized in that: The python reads the file and parses the slow log data through the real-time parsing and reporting service to achieve full or incremental data collection.

7. A SQL Server slow log real-time collection and storage system according to claim 6, characterized in that: The MySQL analyzes and counts the collected data, and the Python parses the slow log by writing a script and saves it in MySQL.

8. A method for the SQL Server slow log real-time collection and storage system based on any one of claims 1 to 7, characterized in that: The method comprises the following steps: S1. First, create an Extended Events session. The Extended Events session starts to collect slow log data and collect and save SQL Server slow logs in real time. S2. Read the name and offset of the last collected file through Python's real-time parsing and reporting service, and determine whether there is a record before; S3. When there are records, incrementally read the log file for the last acquisition file, parse the file after reading, save the parsed data to MySQL, and read the latest slow log file; S4. When there is no record, read the latest log file in full, parse the file after reading, save the parsed data in MySQL, and record the log file name and offset position; S5. When there is a record of reading the latest slow log that is different from the last collected file, read the latest log file in full, parse the file after reading, save the parsed data in MySQL, and record the log file name and offset position.

Images