A database performance monitoring method and system

Abstract

This invention proposes a database performance monitoring method and system. The performance monitoring method includes: acquiring performance parameter information through multiple hook functions set in the database kernel during database runtime; updating corresponding performance statistics in shared memory based on the acquired performance parameter information; generating sampling snapshots of the performance statistics in shared memory at preset intervals and storing the sampling snapshots as physical files on disk; retrieving the physical files from disk using a performance reporting tool and generating a performance report based on the physical files. This invention implements performance information collection within the database kernel, thus minimizing the impact on database performance. It also obtains more comprehensive and richer information and uses a separate tool to generate performance analysis reports, enabling report generation for database problem analysis even when the database fails to start.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a database performance monitoring method and system. BACKGROUND

[0002] The performance monitoring of a database is a must for a DBA to pay attention to. At present, the performance monitoring means is mostly to obtain and count relevant information through a timed SQL query on the database. Such a problem has two aspects:

[0003] Firstly, the timed SQL query will affect the performance of the database. The database has to consume some resources to respond to the query request, which increases the burden of the database. Secondly, the database running information that can be obtained through the SQL query is not rich enough. Because many performance-related monitoring information is not collected and saved in the database, the information cannot be obtained through the SQL query. SUMMARY

[0004] The technical problem to be solved by the present application is how to comprehensively monitor the database without affecting the running performance of the database. The present application provides a database performance monitoring method and system.

[0005] The database performance monitoring method according to an embodiment of the present application comprises the following steps.

[0006] During the running of the database, a plurality of hook functions set in the database kernel are used to obtain performance parameter information.

[0007] Based on the obtained performance parameter information, corresponding performance statistical information in shared memory is updated.

[0008] The performance statistical information in the shared memory is generated into a sampling snapshot according to a preset period, and the sampling snapshot is stored as a physical file in a disk.

[0009] A performance report tool is used to obtain the physical file from the disk, and a performance report is generated based on the physical file.

[0010] According to some embodiments of the present application, the performance monitoring method further comprises the following steps.

[0011] The physical files in the disk that exceed a preset time length or have generated a performance report are deleted.

[0012] In some embodiments of the present application, the performance report tool is independent of the database.

[0013] According to some embodiments of the present application, the method for generating the performance report by the performance report tool comprises the following steps.

[0014] Preset the reporting cycle and reporting targets;

[0015] Based on the preset period and the reporting object, the corresponding performance information is obtained from the physical file, and the statistical results are calculated.

[0016] Fill the statistical results into the report template file to generate the performance report.

[0017] In some embodiments of the present invention, the shared memory is pre-configured with a data structure for storing the statistical information.

[0018] A database performance monitoring system according to an embodiment of the present invention includes:

[0019] The data acquisition module is used to obtain performance parameter information by using multiple hook functions set in the database kernel during database runtime.

[0020] The update module is used to update the corresponding performance statistics in the shared memory based on the acquired performance parameter information;

[0021] The transfer module is used to generate sampling snapshots of the performance statistics information in the shared memory according to a preset period, and store the sampling snapshots as physical files on the disk;

[0022] A performance reporting tool is used to retrieve the physical files from the disk and generate a performance report based on the physical files.

[0023] According to some embodiments of the present invention, the performance monitoring system further includes:

[0024] The clearing module is used to delete physical files on the disk that have exceeded a preset time or physical files for which performance reports have already been generated.

[0025] In some embodiments of the present invention, the performance reporting tool used is a performance reporting tool independent of the database.

[0026] According to some embodiments of the present invention, the performance reporting tool is specifically used for:

[0027] Preset the reporting cycle and reporting targets;

[0028] Based on the preset period and the reporting object, the corresponding performance information is obtained from the physical file, and the statistical results are calculated.

[0029] Fill the statistical results into the report template file to generate the performance report.

[0030] In some embodiments of the present invention, the shared memory is pre-configured with a data structure for storing the statistical information.

[0031] The database performance monitoring method and system proposed in this invention have the following beneficial effects:

[0032] This invention collects performance information within the database kernel, thus minimizing the impact on database performance. It also provides more comprehensive and richer information and uses a separate tool to generate performance analysis reports. Even when the database fails to start, reports can still be generated to analyze database problems. Attached Figure Description

[0033] Figure 1 This is a flowchart of a database performance monitoring method according to an embodiment of the present invention;

[0034] Figure 2 This is a flowchart of a database performance monitoring method according to an embodiment of the present invention;

[0035] Figure 3 This is a schematic diagram of a data structure in shared memory according to an embodiment of the present invention;

[0036] Figure 4 This is a flowchart of a performance report generation method according to an embodiment of the present invention;

[0037] Figure 5 This is a schematic diagram of the composition of a database performance monitoring system according to an embodiment of the present invention.

[0038] Figure label:

[0039] Performance monitoring system 100,

[0040] Data collection module 10, update module 20, transfer module 30, performance reporting tool 40. Detailed Implementation

[0041] To further illustrate the technical means and effects of the present invention in achieving its intended purpose, the present invention will be described in detail below with reference to the accompanying drawings and preferred embodiments.

[0042] The steps described in the specification and the flowcharts in the accompanying drawings of this invention are not necessarily to be strictly followed according to the step numbers; the execution order of the steps can be changed. Furthermore, certain steps can be omitted, multiple steps can be combined into one step, and / or one step can be broken down into multiple steps.

[0043] There are many database monitoring systems on the market, and their basic technology involves using a client to connect to the database and then using SQL queries to obtain and collect database information. This can impact database performance, and much low-level information cannot be collected.

[0044] The database performance monitoring method and system proposed in this invention can achieve efficient data collection within the database, minimizing the impact on database operation. Furthermore, it collects richer and more fundamental data, enabling comprehensive monitoring of the database.

[0045] like Figure 1 and Figure 2 As shown, the database performance monitoring method according to an embodiment of the present invention includes:

[0046] S100 obtains performance parameter information by setting multiple hook functions in the database kernel during database runtime;

[0047] S200 updates the corresponding performance statistics in the shared memory based on the acquired performance parameter information;

[0048] For example, when the database calls the disk read / write function to perform I / O access, a hook function is added to this function. This hook function will update the statistics in the shared memory, such as incrementing the I / O access count by 1.

[0049] In some embodiments of the present invention, a data structure for storing statistical information is pre-set in the shared memory. The data structure is as follows: Figure 3 As shown, the leftmost element of this data structure corresponds to the databases. There are three databases: db1, db2, and db3. This structure changes accordingly when a user creates or deletes a database to provide real-time database information. Each database cell (db1, db2, db3) corresponds to a structure containing statistical information for multiple dimensions of a database. The statistical information for each dimension is organized and linked together into a linked list in smaller units.

[0050] S300 generates sampling snapshots of performance statistics in shared memory according to a preset period and stores the sampling snapshots as physical files on the disk;

[0051] In some embodiments of the present invention, the preset period is on the order of hours. For example, the preset period can be 1 hour, 2 hours, 3 hours or other time periods.

[0052] The S400 retrieves physical files from the disk using a performance reporting tool and generates performance reports based on those physical files.

[0053] In some embodiments of the present invention, the performance reporting tool used is a database-independent performance reporting tool.

[0054] It should be noted that the "performance reporting tool" mentioned here is a database-independent performance reporting tool. This can be understood as the performance reporting tool's operation not depending on the database. In other words, the performance reporting tool can still generate performance reports even if the database is not running.

[0055] According to some embodiments of the present invention, a method for generating performance reports using a performance reporting tool includes:

[0056] S410, preset the reporting cycle and reporting targets;

[0057] S420 retrieves the corresponding performance information from the physical file according to the preset cycle and the reporting object, and calculates the statistical results;

[0058] S430: Fill the statistical results into the report template file to generate a performance report.

[0059] According to some embodiments of the present invention, the performance monitoring method further includes:

[0060] The S500 deletes physical files on the disk that have exceeded a preset time limit or for which performance reports have already been generated. This prevents physical files from occupying excessive disk memory.

[0061] like Figure 5 As shown, the database performance monitoring system 100 according to an embodiment of the present invention includes: a data acquisition module 10, an update module 20, a data transfer module 30, and a performance reporting tool 40.

[0062] Among them, the acquisition module 10 is used to obtain performance parameter information by setting multiple HOOK functions in the database kernel during database operation;

[0063] The update module 20 is used to update the corresponding performance statistics in the shared memory based on the acquired performance parameter information;

[0064] For example, when the database calls the disk read / write function for I / O access, a HOOK function is added to this function. The update module 20 will update the statistics in the shared memory based on the performance parameter information obtained by the HOOK function, such as incrementing the I / O access count by 1.

[0065] In some embodiments of the present invention, a data structure for storing statistical information is pre-set in the shared memory. The data structure is as follows: Figure 3As shown, the leftmost element of this data structure corresponds to the databases. There are three databases: db1, db2, and db3. This structure changes accordingly when a user creates or deletes a database to provide real-time database information. Each database cell (db1, db2, db3) corresponds to a structure containing statistical information for multiple dimensions of a database. The statistical information for each dimension is organized and linked together into a linked list in smaller units.

[0066] The transfer module 30 is used to generate sampling snapshots of the performance statistics information in the shared memory according to a preset period, and store the sampling snapshots as physical files on the disk;

[0067] In some embodiments of the present invention, the preset period is on the order of hours. For example, the preset period can be 1 hour, 2 hours, 3 hours or other time periods.

[0068] The performance reporting tool 40 is used to retrieve physical files from the disk and generate performance reports based on the physical files.

[0069] In some embodiments of the present invention, the performance reporting tool 40 used is a database-independent performance reporting tool 40.

[0070] It should be noted that the "performance reporting tool" mentioned here is independent of the database performance reporting tool, which means that the performance reporting tool 40 does not depend on the database for operation. In other words, the performance reporting tool 40 can still generate performance reports even if the database is not running.

[0071] According to some embodiments of the present invention, the performance reporting tool 40 is specifically used for:

[0072] Preset the reporting cycle and reporting targets;

[0073] Based on the preset cycle and the reporting object, the corresponding performance information is obtained from the physical file, and the statistical results are calculated.

[0074] Fill the statistical results into the report template file to generate a performance report.

[0075] According to some embodiments of the present invention, the performance monitoring system 100 further includes a clearing module.

[0076] The cleanup module is used to delete physical files on the disk that have exceeded a preset time limit, or physical files for which performance reports have already been generated.

[0077] The database performance monitoring method and system proposed in this invention have the following beneficial effects:

[0078] This invention collects performance information within the database kernel, thus minimizing the impact on database performance. It also provides more comprehensive and richer information and uses a separate tool to generate performance analysis reports. Even when the database fails to start, reports can still be generated to analyze database problems.

[0079] The database performance monitoring method and system according to the present invention will now be described in detail with reference to the accompanying drawings and a specific embodiment. It is to be understood that the following description is merely exemplary and should not be construed as a specific limitation of the present invention.

[0080] This invention places several hook functions in the database kernel. These hook functions are used to send performance-related information to a shared memory statistics data structure during database runtime. For example, when the database calls disk read / write functions for I / O access, a hook function is added to this function. This hook function updates the statistics in shared memory, such as incrementing the I / O access count by 1.

[0081] Statistics in shared memory are saved at preset sampling periods (one hour by default). The data collected within each sampling period is called a sampling snapshot. Therefore, by default, each sampling snapshot contains one hour of database statistics. Sampling snapshots are periodically saved as physical files on disk, so by default, 24 sampling files are generated per day, and 168 sampling files per week. The retention period (i.e., the number of sampling files) can be configured; expired sampling files are deleted to save disk space.

[0082] When a database performance report needs to be generated, a separate performance reporting tool is required. This tool is independent of the database. It reads and calculates the sampled file according to the user-specified reporting period and reporting object, fills the results into the report template file, and generates the final performance report.

[0083] Combination Figure 1 and Figure 2 As shown, the database performance monitoring method of the present invention is as follows:

[0084] Step 1:

[0085] Design a data structure to be placed in shared memory to collect statistical information. This structure would look like this: Figure 3 As shown.

[0086] The leftmost element of this structure corresponds to the databases. There are three databases here: db1, db2, and db3. This structure changes accordingly when a user creates or deletes a database, allowing for real-time database information statistics. Each database cell (db1, db2, db3) corresponds to a structure containing statistical information for a database across multiple dimensions. The statistical information for each dimension is further organized and linked together into a linked list using smaller units.

[0087] For example:

[0088] Suppose db3 contains two tables, tb1 and tb2, and info1 represents I / O information. In the corresponding data structure of db3, info1 points to a linked list with two elements: one for tb1's I / O information and the other for tb2's I / O information. The I / O information for tb1 and tb2 is further subdivided, storing more detailed I / O information. For example, info1-1 represents the number of disk reads, info1-2 represents the number of disk writes, info1-3 represents the number of memory reads, and info1-4 represents the number of cache hits, etc.

[0089] This structure can be dynamically expanded or shrunk to adapt to changes in the database and save memory overhead.

[0090] Step Two:

[0091] Design a hook function. During the database startup phase, initialize a dedicated shared memory block for performance monitoring information. The address of this shared memory block remains fixed throughout the entire database operation. All hook functions of the database can access this memory block and output statistical information to it.

[0092] In the database kernel, a hook function is added to any performance-related kernel function. (A hook function is a callback function; if it is defined, it will be executed; if it is not defined, nothing will happen.) These hook functions are added to all relevant functions involving I / O, CPU calculation, locks, memory, signals, waiting, etc. Once the relevant function is called by the database, the relevant statistics will be sent to shared memory through the hook function in this function.

[0093] Step 3:

[0094] Shared memory protection. Because multiple hook functions might simultaneously modify the same part of shared memory, protection is needed to prevent concurrent conflicts. A mutex lock is added to the lowest-level structure (i.e., structures info1-1, info1-2, info1-3, and info1-4). This mutex lock is a spin lock, allowing only one function to modify this structure at a time. If there are concurrent write requests, later requests must wait until the previous request has finished writing before acquiring the mutex lock and modifying the structure.

[0095] Step Four:

[0096] Saving of sampling snapshots. At the end of each sampling period (default one hour), the sampling information stored in shared memory is saved as a separate physical file. The memory structure is saved in binary form in the physical file, and then useless information in the memory structure is cleared. Each physical file is named according to: [current date] + [0~1439]. 0~1439 represents the time when the physical file was generated, corresponding to 1440 minutes in a day. For example, a file generated at 1:30 AM on April 1, 2022, would be named 202204010090.snap, indicating that it was a snapshot generated at the 90th minute of that day.

[0097] Step 5:

[0098] Generate a performance report. Combined with... Figure 5 As shown, the performance report generation process is as follows:

[0099] After the tool runs, the user needs to specify which database and time period to generate the performance report for. After reading the preset report template file and checking that the user's input parameters are correct, the tool will read the snapshot file for the corresponding time period into memory, then collect and statistically analyze the data, and finally populate the template to generate the final performance analysis report.

[0100] In summary, this invention collects performance information within the database kernel, thus minimizing the impact on database performance. It also provides more comprehensive and richer information and uses a separate tool to generate performance analysis reports, enabling the generation of reports to analyze database problems even when the database fails to start.

[0101] Through the description of specific embodiments, a more in-depth and specific understanding should be gained of the technical means and effects adopted by the present invention to achieve the intended purpose. However, the accompanying drawings are only provided for reference and illustration and are not intended to limit the present invention.

Claims

1. A method of performance monitoring of a database, characterized by, The method comprises the following steps: During the running of the database, performance parameter information is acquired through a plurality of hook functions set in the database kernel; Based on the acquired performance parameter information, corresponding performance statistical information in shared memory is updated; the shared memory is pre-provided with a data structure for storing the statistical information, including a plurality of database lattices, when a user newly creates or deletes a database, the database lattices change correspondingly to statistically count real-time information of the database, each database lattice corresponds to a data structure, the data structure contains statistical information of a plurality of dimensions of a database, statistical information of each dimension is concatenated into a linked list, and the data structure supports dynamic expansion and contraction; a spin lock is arranged on the lowest layer structure in the entire data structure, and only one hook function is allowed to rewrite the corresponding bottommost structure each time, and if there are concurrent write requests, the late write request waits until the previous write request is completed before rewriting the corresponding structure; The performance statistical information in the shared memory is generated into a sampling snapshot according to a preset period, and the sampling snapshot is stored as a physical file in a disk; The performance report tool acquires the physical file from the disk, and generates a performance report based on the physical file.

2. The method of performance monitoring of a database according to claim 1, wherein, The performance monitoring method further comprises the following steps: The physical file in the disk that exceeds a preset time length or the physical file that has generated a performance report is deleted.

3. The method of claim 1, wherein, The performance report tool is independent of the database.

4. The method of claim 1, wherein, The method for generating the performance report by the performance report tool comprises the following steps: A report period and a report object are preset; According to the preset period and the report object, corresponding performance information is acquired from the physical file, and statistical results are calculated; The statistical results are filled into a report template file to generate the performance report.

5. A performance monitoring system for a database, characterized by The method comprises the following steps: A collection module is configured to acquire performance parameter information through a plurality of hook functions set in the database kernel during the running of the database; An update module is configured to update corresponding performance statistical information in shared memory based on the acquired performance parameter information; the shared memory is pre-provided with a data structure for storing the statistical information, including a plurality of database lattices, when a user newly creates or deletes a database, the database lattices change correspondingly to statistically count real-time information of the database, each database lattice corresponds to a data structure, the data structure contains statistical information of a plurality of dimensions of a database, statistical information of each dimension is concatenated into a linked list, and the data structure supports dynamic expansion and contraction; a spin lock is arranged on the lowest layer structure in the entire data structure, and only one hook function is allowed to rewrite the corresponding bottommost structure each time, and if there are concurrent write requests, the late write request waits until the previous write request is completed before rewriting the corresponding structure; A transfer module is configured to generate a sampling snapshot from the performance statistical information in the shared memory according to a preset period, and store the sampling snapshot as a physical file in a disk; and A performance report tool acquires the physical file from the disk, and generates a performance report based on the physical file. The performance report tool is used to obtain the physical files from the disk and generate a performance report based on the physical files.

6. The performance monitoring system of a database according to claim 5, wherein, The performance monitoring system further comprises: A clearing module is used to delete the physical files in the disk that exceed a preset time length or have generated a performance report.

7. The performance monitoring system of a database according to claim 5, wherein, The performance report tool is independent of the database.

8. The performance monitoring system of a database according to claim 5, wherein, The performance report tool is specifically used to: A preset report period and a report object; According to the preset period and the report object, corresponding performance information is obtained from the physical files and statistical results are calculated; The statistical results are filled into a report template file to generate the performance report.

Images