Database sub-table management method and device, electronic equipment and storage medium

Abstract

The invention provides a management method and device for sub-database and sub-table of a database, electronic equipment and a storage medium, and can be used for the distributed field. The method comprises the steps that a monitoring script is applied to collect the operation condition of database sub-tables in the distributed system, and the monitoring script comprises an application program corresponding to a monitoring function; generating a monitoring report for the distributed system according to the operation condition of the database sub-database sub-table in the preset duration; and calling an interface of the internal instant messaging software, and notifying a database administrator of the monitoring report. By using the scheme provided by the invention, the effects of performing state monitoring on the operation condition of the database sub-library and sub-table in the distributed system, avoiding performance consumption caused by calling an external monitoring program and improving the maintenance efficiency of the database sub-library and sub-table can be achieved.

Description

Technical Field

[0001] The present application relates to the field of distributed systems, and in particular to a method, device, electronic device and storage medium for managing database sub-libraries and sub-tables. Background Art

[0002] In the field of financial technology, core systems such as the bank card core system face the reality of high system concurrency and heavy database pressure. The bank card core system involves real-time transaction entry, authorization, installment, general ledger and other related accounting functions, involving billions of data volumes, and the account transaction flow information and account balance information are complex and diverse. The database is under great pressure and has obvious performance bottlenecks. Moreover, the data in the database is not only used for the bank card core system. When the transaction involves multiple systems, multiple systems will subscribe to the data of the bank card core system for processing. Based on the complex and changeable current situation, it is necessary to use the sub-library and sub-table function to reduce the database pressure, and use the configuration of hundreds of libraries and thousands of tables to store and manage data to improve the database read and write performance.

[0003] In the related art, monitoring the operation status of the database after the database and table are divided by writing shell scripts has the problem of high performance overhead. Summary of the invention

[0004] The embodiments of the present application provide a management method, device, electronic device and storage medium for database sub-libraries and sub-tables, so as to avoid the performance consumption caused by calling external monitoring programs and improve the maintenance efficiency of database sub-libraries and sub-tables.

[0005] In a first aspect, an embodiment of the present application provides a method for managing database sub-libraries and sub-tables, including: applying a monitoring script to collect the operation status of database sub-libraries and sub-tables in a distributed system, wherein the monitoring script itself includes an application program corresponding to the monitoring function;

[0006] Generate a monitoring report for the distributed system based on the operation status of the database sub-libraries and sub-tables within a preset time period;

[0007] Call the interface of the internal instant messaging software to notify the database administrator of the monitoring report.

[0008] In one possible implementation, the monitoring script is written in a combination of shell language and lua language, wherein the monitoring script written in shell language includes an application that calls the monitoring script written in lua language, and the monitoring script written in lua language includes an application corresponding to disk balance monitoring, processor occupancy monitoring, and SQL statement monitoring with access duration greater than a duration threshold.

[0009] In a possible implementation, the operation status includes at least: disk margin, processor occupancy rate, and SQL statements whose access time is longer than a time threshold. According to the operation status of the database sub-library and sub-table within a preset time, a monitoring report for the distributed system is generated, including:

[0010] Read the disk space and processor occupancy rate of the server where the database sub-library and sub-table are located within a preset time period, and obtain SQL statements whose query time for the database sub-library and sub-table is greater than the SQL statement query time threshold;

[0011] Determine the average processor occupancy rate within the preset time period according to the processor occupancy rate of the server where the database sub-library and sub-table are located within the preset time period;

[0012] If the read disk remaining capacity is less than the remaining capacity threshold, first information is recorded, where the first information includes the disk remaining capacity and first warning information for the disk remaining capacity;

[0013] If the average occupancy rate of the processor within the preset time period is greater than the occupancy rate threshold, record second information, the second information including the average occupancy rate of the processor within the preset time period and second warning information of the average occupancy rate of the processor within the preset time period;

[0014] If a SQL statement whose query time is longer than the SQL statement query time threshold is obtained, third information is recorded, where the third information includes the SQL statement whose query time is longer than the SQL statement query time threshold and third warning information of the SQL statement whose query time is longer than the SQL statement query time threshold;

[0015] A monitoring report for the distributed system is generated according to the recorded information, where the recorded information includes at least one of the first information, the second information, and the third information.

[0016] In a possible implementation, obtaining an SQL statement whose query duration for a database sub-library or sub-table is greater than an SQL statement query duration threshold includes:

[0017] Based on the SQL statement query time threshold, apply the monitoring script to analyze the slow query log and obtain the SQL statements whose query time exceeds the SQL statement query time threshold;

[0018] A slow SQL query list is generated according to the SQL statements whose query time exceeds the SQL statement query time threshold. The slow SQL query list includes the SQL statements whose query time exceeds the SQL statement query time threshold.

[0019] In a possible implementation, the management method further includes:

[0020] If the disk remaining capacity read is less than the remaining capacity threshold, a first process is executed, wherein the first process includes clearing the cache to release the cache space;

[0021] If the average processor occupancy rate within the preset time period is greater than the occupancy rate threshold, a second process is executed, wherein the second process includes analyzing the processes being executed, suspending non-urgent processes therein or releasing unnecessary threads;

[0022] If there is an SQL statement whose query duration is longer than the SQL statement query duration threshold, a third process is performed, wherein the third process includes analyzing the cause of the slow query and automatically optimizing the SQL statement based on the analyzed cause;

[0023] Correspondingly, the monitoring report also includes at least one of the first processing, the second processing and the third processing.

[0024] In a possible implementation, the operation status further includes: whether optimistic lock and deadlock are generated, and the management method further includes:

[0025] If optimistic lock and / or deadlock exist, fourth information is recorded, where the fourth information includes a database where optimistic lock and / or deadlock exist, and fourth warning information of the database where optimistic lock and / or deadlock exist;

[0026] Accordingly, the monitoring report also includes fourth information.

[0027] In a second aspect, an embodiment of the present application provides a management device for database sub-libraries and sub-tables, including:

[0028] The acquisition module is used to apply the monitoring script to collect the operation status of the database sub-libraries and sub-tables in the distributed system. The monitoring script itself contains the application program corresponding to the monitoring function;

[0029] A processing module, used to generate a monitoring report for the distributed system according to the operation status of the database sub-libraries and sub-tables within a preset time period and the preset thresholds corresponding to different operation statuses;

[0030] The communication module is used to call the interface of the internal instant messaging software to notify the database administrator of the monitoring report.

[0031] In a third aspect, an embodiment of the present application provides an electronic device, including: a memory, a processor;

[0032] The memory stores computer-executable instructions;

[0033] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the above first aspect and / or various possible implementations of the first aspect.

[0034] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0035] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0036] The monitoring method, device, electronic device, storage medium and program product for database sub-libraries and sub-tables provided in the embodiments of the present application use monitoring scripts to monitor the operation of the database after the database sub-libraries and sub-tables in the distributed system, generate a monitoring report for the distributed system based on the operation of the database sub-libraries and sub-tables within a preset time period, and notify the database administrator of the report through instant messaging software, so as to avoid the performance consumption caused by calling external monitoring programs and improve the maintenance efficiency of the database sub-libraries and sub-tables. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0038] Figure 1 Schematic diagram of the scenario of database sharding and table sharding provided for this application;

[0039] Figure 2 Schematic diagram of the monitoring method for database sub-library and sub-table provided in this application Figure 1 ;

[0040] Figure 3 A schematic diagram of the script architecture provided for this application;

[0041] Figure 4 Schematic diagram of the monitoring method for database sub-library and sub-table provided in this application Figure 2 ;

[0042] Figure 5 Schematic diagram of the monitoring method for database sub-library and sub-table provided in this application Figure 3 ;

[0043] Figure 6 Schematic diagram of the monitoring method for database sub-library and sub-table provided in this application Figure 4 ;

[0044] Figure 7 A schematic diagram of the structure of the monitoring device for database sub-library and sub-table provided in this application;

[0045] Figure 8 A schematic diagram of the structure of the electronic device provided in this application.

[0046] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0047] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0048] The database system is an important infrastructure for enterprises. The digital operation of enterprises cannot be separated from the support of data. These data are stored in the database system in the form of data tables. In some scenarios with busy digital business, such as banking scenarios, the amount of data involved in digital business in real time is in the billions. The database system bears a lot of reading and writing pressure, and multiple businesses concurrently reading and writing databases can easily cause row locks or table locks for a single database, resulting in reduced reading and writing efficiency. The above problems can be effectively alleviated by sharding the database and the table. Figure 1 This is a schematic diagram of the database partitioning scenario. Figure 1 As shown in the figure, in the single database mode, all data tables are stored in one database, and the amount of data in a single data table is also huge. After the database and table are divided, the data tables are split and different data tables are placed in different databases for storage. By splitting the database, the disk usage rate can be reduced, and by dividing the table, the amount of data in a single table can be reduced, which speeds up the SQL query speed.

[0049] Sub-libraries and tables can ease the reading and writing pressure of the database system, but it will also bring some new problems. For example, sub-libraries and tables cause the database to be distributed and run on different devices. Compared with the single-library and single-table mode, the monitoring and maintenance of the database is more difficult. In related technologies, the monitoring of the database operation status after sub-libraries and tables by writing shell scripts has the problem of high performance overhead. Therefore, efficient monitoring and management of the operation status of sub-libraries and tables has become a technical problem that needs to be solved urgently.

[0050] The present application provides a management method for database sub-libraries and sub-tables, which uses a monitoring script to collect the operation status of database sub-libraries and sub-tables in a distributed system. The script is written in a combination of shell language and lua language. The script content includes a program for monitoring the sub-libraries and sub-tables. The monitoring program can at least monitor the disk balance, processor occupancy rate and SQL statements with access time greater than a threshold. Finally, a monitoring report is generated according to the monitoring results, thereby providing a clear and reliable basis for database administrators to maintain the database system while avoiding the performance overhead caused by calling an external monitoring program.

[0051] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0052] Figure 2 Schematic diagram of the process of managing the database sub-library and sub-table provided in this application Figure 1 ,like Figure 2 As shown, the method includes:

[0053] S201. Apply monitoring scripts to collect the operation status of database sub-libraries and sub-tables in the distributed system. The monitoring scripts themselves include application programs corresponding to the monitoring functions.

[0054] Among them, using monitoring scripts to monitor the operation of sub-libraries and sub-tables can avoid the performance overhead caused by calling external monitoring programs, automatically collect the operation status of database sub-libraries and sub-tables, reduce the workload of database administrators, and improve work efficiency.

[0055] S202: Generate a monitoring report for the distributed system based on the operation status of the database sub-libraries and sub-tables within a preset time period.

[0056] For example, a script is used to obtain the operating status of one hundred databases obtained after sharding within three hours. The operating status may include disk remaining capacity, processor occupancy, and SQL read and write status, etc. A monitoring report for the distributed system is generated based on the operating status.

[0057] S203: Call the interface of the internal instant messaging software to notify the database administrator of the monitoring report.

[0058] After the monitoring report is generated, the content of the monitoring report is quickly delivered to the database administrator by calling the interface of the internal instant messaging software, providing a basis for the database administrator to maintain the database.

[0059] The management method of database sub-libraries and sub-tables provided in the embodiment of the present application uses scripts to monitor the operation status of database sub-libraries and sub-tables in a distributed system, realizes the automation of obtaining database operation status, adopts lightweight scripts to avoid the performance overhead caused by calling external monitoring levels, summarizes the database operation status into an intuitive monitoring report and provides it to database administrators, so as to facilitate database administrators to maintain and manage the database after sub-libraries and sub-tables.

[0060] In one embodiment, the monitoring script is written in a combination of shell language and lua language, wherein the monitoring script written in shell language includes an application that calls the monitoring script written in lua language, and the monitoring script written in lua language includes an application corresponding to disk balance monitoring, processor occupancy monitoring, and SQL statement monitoring with access duration greater than a duration threshold.

[0061] Among them, the script written in shell language is called shell script. Shell script can realize automated tasks, such as batch processing of files, monitoring system status and scheduled backup. The development difficulty of shell script is relatively low, but correspondingly, its execution efficiency is not high. This is because when using shell script, shell script usually acts as an interface role. For example, when executing monitoring system status, shell script needs to call other processes with monitoring system status, combine them, and thus form a full range of detection of system status. For example, when the shell script calls the awk text processing tool or the sed process to process files, a new process needs to be generated, and pipes need to be used for IPC process communication, which will lead to increased performance overhead.

[0062] The database sub-library and sub-table management method provided in the embodiment of the present application uses the lua language to write the monitoring program based on the advantage of retaining the easy development of shell scripts, and overcomes the defect of low efficiency caused by the shell script calling process. Lua is a lightweight and compact scripting language, written in standard C language and open in source code form. It is designed to be embedded in applications, thereby providing flexible expansion and customization functions for applications. It has the following characteristics: lightweight, it is written in standard C language and open in source code form, and after compilation, it is only more than 100K, which can be easily embedded in other programs; extensible, lua provides a very easy-to-use expansion interface and mechanism.

[0063] Figure 3 This is a schematic diagram of the script architecture provided in the embodiment of this application. Figure 3As shown in the figure, the functions implemented by the Lua language are written. Multiple functions correspond to multiple functions, and each function is implemented by a Lua script. The Lua script is called in the shell script, and the shell script reports the value returned by the Lua function.

[0064] The database sub-library and sub-table management method provided in the embodiment of the present application uses the shell language combined with the Lua language to write database monitoring scripts. On the basis of retaining the advantage of the shell language being easy to develop, the lightweight Lua language is used to write functions for shell calls, thereby overcoming the defect of low efficiency caused by the shell language calling file processing processes provided by other applications, and improving the execution efficiency of the monitoring program.

[0065] In one implementation, the operation status includes at least: disk margin, processor occupancy rate, and SQL statements whose access time is longer than a time threshold. According to the operation status of the database sub-library and sub-table within a preset time, a monitoring report for the distributed system is generated, including the following steps:

[0066] S401, reading the disk space and processor occupancy rate of the server where the database sub-library and sub-table are located within a preset time period, and obtaining SQL statements whose query time period for the database sub-library and sub-table is greater than the SQL statement query time period threshold.

[0067] Disk headroom refers to the amount of unused space on the hard disk, which is usually used to store new data or files. Maintaining sufficient disk headroom is critical to the normal operation and performance of the system. Sufficient disk headroom ensures that the system has enough space for temporary file storage and caching, thereby improving the system's responsiveness and operating efficiency. When the hard disk is close to full, the system may become unstable, increasing the risk of data loss or corruption. Reserve sufficient disk headroom to facilitate future system maintenance and upgrades, such as installing new software or performing system updates.

[0068] Processor utilization refers to the proportion of time the processor spends executing tasks, usually expressed as a percentage. A processor utilization of 100% means that the processor has reached its limit, which may cause the computer to run slowly or become unresponsive. There are many factors that affect processor utilization, such as running multiple programs or background processes at the same time, which will take up a lot of system resources, causing the processor to be overloaded. Viruses or malware will run in the background and take up a lot of processor resources, causing the processor utilization to be too high. Some system updates may cause compatibility issues, causing the processor resources to be occupied too much. Some programs may have memory leaks, causing them to occupy more and more system resources after running for a period of time. Outdated or incompatible hardware drivers can also cause abnormal occupation of system resources. Tasks that handle high resolutions or large files, such as video editing, large graphics processing, etc., can also cause the processor utilization to reach 100%.

[0069] When the processor usage is high, it will affect the normal operation of the database. In this case, the database will not be able to process all requests, resulting in data loss, slower queries, increased response time, etc. In addition, high processor usage will also lead to increased disk I / O.

[0070] SQL statements whose query duration is longer than the SQL statement query duration threshold can be called slow SQL statements. When slow SQL statements are executed, they will occupy a large amount of CPU, memory, or I / O resources. These resources are limited in the database system, so the execution of slow SQL will result in a reduction in the resources available to other database operations (including write operations). Slow SQL may occupy table locks or row locks for a long time during execution, especially when performing complex queries or update operations. This lock waiting phenomenon will block other write operations that need to access the same data, resulting in increased write latency. If the slow SQL statement is included in a transaction, the processing time of the entire transaction will be extended. The long running of a transaction will not only affect the write performance of the current transaction, but may also affect the concurrent execution of other transactions because the lock resources are occupied for a long time.

[0071] S402: Determine an average processor occupancy rate within a preset time period according to the processor occupancy rate of the server where the database sub-library and sub-table are located within a preset time period.

[0072] S403: If the read disk remaining capacity is less than the remaining capacity threshold, record first information, where the first information includes the disk remaining capacity and first warning information regarding the disk remaining capacity.

[0073] Among them, the first alarm information shows that the disk space is insufficient, reminding the database administrator to take corresponding measures to ensure the normal operation of the database system.

[0074] S404: If the average processor occupancy within the preset time period is greater than the occupancy threshold, record second information, the second information including the average processor occupancy within the preset time period and second alarm information for the average processor occupancy within the preset time period.

[0075] Exemplarily, the preset duration is thirty minutes, and the occupancy threshold is set to 80%. If the average processor occupancy within thirty minutes is monitored to be greater than 80%, the average processor occupancy within these thirty minutes is recorded, as well as the second alarm information. The second alarm information indicates that the current processor occupancy is high, prompting the server administrator to take corresponding measures to ensure the normal operation of the database system.

[0076] S405. If a SQL statement whose query time is longer than the SQL statement query time threshold is obtained, third information is recorded, where the third information includes the SQL statement whose query time is longer than the SQL statement query time threshold and the third alarm information of the SQL statement whose query time is longer than the SQL statement query time threshold.

[0077] In one implementation, SQL statements whose query duration for database sub-libraries and sub-tables is greater than a SQL statement query duration threshold may be obtained from the slow query log.

[0078] It should be noted that the execution order of the above steps S403, S404 and S405 can be changed arbitrarily, and this application does not limit the execution order of the above steps.

[0079] S406. Generate a monitoring report for the distributed system according to the recorded information, where the recorded information includes at least one of the first information, the second information, and the third information.

[0080] The management method of database sharding provided in the embodiment of the present application monitors the disk balance, processor occupancy rate and SQL statement execution speed during the operation of data sharding in a distributed system. When the disk balance is insufficient or the processor occupancy rate is too high, the information is recorded and an alarm message is generated to prompt the database administrator to maintain the system in a timely manner. At the same time, a slow SQL list is obtained from the slow query log for reference by the database administrator when optimizing database reading and writing.

[0081] Figure 5 Schematic diagram of the management method of database sub-library and sub-table provided in the embodiment of the present application Figure 3 .like Figure 5 As shown, in one embodiment, obtaining a SQL statement whose query duration for a database sub-library or sub-table is greater than a SQL statement query duration threshold includes the following steps:

[0082] S501. Based on the SQL statement query time threshold, apply a monitoring script to analyze the slow query log to obtain SQL statements whose query time exceeds the SQL statement query time threshold.

[0083] In MySQL, under the default configuration, if the execution time of slow SQL exceeds a certain time, it will be recorded in the slow query log. For example, the threshold of slow SQL can be set to 0.1 to 0.2 seconds.

[0084] S502. Generate a slow SQL query list according to the SQL statements whose query duration exceeds the SQL statement query duration threshold, where the slow SQL query list includes SQL statements whose query duration exceeds the SQL statement query duration threshold.

[0085] After obtaining the slow SQL query list, the database administrator can optimize the slow SQL queries according to the list and optimize the queries. The specific optimization operations are described in the following embodiments.

[0086] The management method of database sub-libraries and sub-tables provided in the embodiment of the present application uses a script to analyze the slow query log based on the SQL statement query time threshold, obtains a slow SQL query list, and automatically, clearly and intuitively displays the slow SQL to the database administrator, indicating the direction for optimizing database queries. In addition to using scripts to obtain the operating status of database sub-libraries and sub-tables in a distributed system, the management method provided by the present application can also perform corresponding processing based on the operating status to optimize system performance, which will be described in detail in conjunction with the accompanying drawings.

[0087] Figure 6 Schematic diagram of the management method of database sub-library and sub-table provided in the embodiment of the present application Figure 4 .like Figure 6 As shown, in a possible implementation manner, the management method further includes:

[0088] S601: If the disk space read is less than the space threshold, a first process is executed, wherein the first process includes clearing a cache to release cache space.

[0089] There are several ways to clear the database cache: restart the database service, use specific SQL commands, adjust database configuration parameters, and use cache management tools. Using specific SQL commands is the most direct and commonly used method. For example, in Oracle, you can use the ALTER SYSTEM FLUSH BUFFER_CACHE command to clear the cache. The advantage of this method is that it does not require interrupting the database service, which is very important for environments that need to maintain high system availability. Clearing the cache can release occupied memory resources and improve database performance, especially when performing large-scale data updates or query optimization.

[0090] S602: If the average processor occupancy rate within the preset time period is greater than the occupancy rate threshold, execute a second process, which includes analyzing the processes being executed, suspending non-urgent processes therein, or releasing unnecessary threads.

[0091] Among them, pausing non-urgent processes in the processor or releasing unnecessary threads can reduce the processor occupancy rate and reduce the query failure or error risk caused by excessive processor occupancy rate.

[0092] S603: If there is an SQL statement whose query duration is longer than the SQL statement query duration threshold, execute a third process, which includes analyzing the cause of the slow query and automatically optimizing the SQL statement based on the analyzed cause.

[0093] There are many ways to optimize slow queries. For example, you can optimize the index to ensure that there are appropriate indexes (such as B-tree indexes and hash indexes) on the fields involved in the query. Avoid using functions or operations on index columns, which will cause index failure. Reconstruct the query to simplify complex SQL queries, break down a large query into multiple small queries, use EXPLAIN to view the query execution plan, and find performance bottlenecks. Reduce the amount of data scanned and use LIMIT to limit the number of rows returned. Select only the required columns and avoid using SELECT * statements.

[0094] Correspondingly, the monitoring report also includes at least one of the first processing, the second processing and the third processing.

[0095] It should be noted that the execution order of the above steps S602 and S603 can be arbitrarily changed, and this application does not limit the execution order of the above steps.

[0096] The management method of database sub-libraries and sub-tables provided in the embodiment of the present application includes an optimization method for disk margin, processor occupancy and slow SQL, and provides the processing content in the monitoring report for reference by database administrators, thereby improving the efficiency of database maintenance and management.

[0097] In one implementation, the operation status also includes: whether optimistic lock and deadlock are generated, and the management method also includes:

[0098] If optimistic lock and / or deadlock exist, fourth information is recorded, where the fourth information includes a database where optimistic lock and / or deadlock exist, and fourth warning information of the database where optimistic lock and / or deadlock exist;

[0099] Accordingly, the monitoring report also includes fourth information.

[0100] Among them, optimistic locking is a concurrency control mechanism used to solve the data consistency problem that may arise when multiple users access and modify the same data at the same time. Its basic idea is to assume that the modification operations of multiple users on the same data will not conflict, so there is no need to add lock restrictions. A version check will be performed for each operation to ensure data consistency. Optimistic locking may cause the following problems: long-term spinning may cause high overhead. If the compare and swap (CAS) operation fails for a long time, the thread will keep spinning and waiting, which will cause a large consumption of processor resources and increase system overhead; ABA problem. The ABA problem refers to the situation in which a variable is modified by other threads between two checks in a CAS operation and then restored to its original value. This will cause the CAS operation to mistakenly believe that the variable has not changed, thereby allowing incorrect updates to the variable, which will affect the correctness of the calculation result; applicable scenario restrictions. Optimistic locking is suitable for scenarios with more reads and less writes, because in this scenario, the use of locks can be reduced and the system throughput can be improved. However, in scenarios with more writes and less reads, frequent conflicts will lead to performance degradation.

[0101] Deadlock refers to a phenomenon in which two or more processes are blocked during execution due to competition for resources or communication with each other. Without external force, they will not be able to move forward. At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes.

[0102] The management method of database sub-libraries and sub-tables provided in the embodiment of the present application includes monitoring of optimistic locks and / or deadlocks to ensure the consistency of data in the database and the smooth operation of processes during database query, reading and writing.

[0103] Figure 7 The schematic diagram of the structure of the management device for database sub-library and sub-table provided in this application is as follows: Figure 7 As shown, the database sub-library and sub-table management device 70 provided in this embodiment includes:

[0104] The acquisition module 701 is used to apply the monitoring script to collect the operation status of the database sub-library and sub-table in the distributed system, and the monitoring script itself includes the application program corresponding to the monitoring function;

[0105] Processing module 702, used to generate a monitoring report for the distributed system according to the operation status of the database sub-library and sub-table within a preset time period and the preset thresholds corresponding to different operation statuses;

[0106] The communication module 703 is used to call the interface of the internal instant messaging software to notify the database administrator of the monitoring report.

[0107] In a possible implementation, the operation status includes at least: disk margin, processor occupancy rate, and SQL statements whose access time is greater than a time threshold. A monitoring report for the distributed system is generated according to the operation status of the database sub-library and sub-table within a preset time. The processing module 702 is further used to:

[0108] Read the disk space and processor occupancy rate of the server where the database sub-library and sub-table are located within a preset time period, and obtain SQL statements whose query time for the database sub-library and sub-table is greater than the SQL statement query time threshold;

[0109] Determine the average processor occupancy rate within the preset time period according to the processor occupancy rate of the server where the database sub-library and sub-table are located within the preset time period;

[0110] If the read disk remaining capacity is less than the remaining capacity threshold, first information is recorded, where the first information includes the disk remaining capacity and first warning information for the disk remaining capacity;

[0111] If the average occupancy rate of the processor within the preset time period is greater than the occupancy rate threshold, record second information, the second information including the average occupancy rate of the processor within the preset time period and second warning information of the average occupancy rate of the processor within the preset time period;

[0112] If a SQL statement whose query time is longer than the SQL statement query time threshold is obtained, third information is recorded, where the third information includes the SQL statement whose query time is longer than the SQL statement query time threshold and third warning information of the SQL statement whose query time is longer than the SQL statement query time threshold;

[0113] A monitoring report for the distributed system is generated according to the recorded information, where the recorded information includes at least one of the first information, the second information, and the third information.

[0114] In a possible implementation, an SQL statement whose query duration for a database sub-library or sub-table is greater than a SQL statement query duration threshold is obtained, and the processing module 702 is further used to:

[0115] Based on the SQL statement query time threshold, apply the monitoring script to analyze the slow query log and obtain the SQL statements whose query time exceeds the SQL statement query time threshold;

[0116] A slow SQL query list is generated according to the SQL statements whose query time exceeds the SQL statement query time threshold. The slow SQL query list includes the SQL statements whose query time exceeds the SQL statement query time threshold.

[0117] In a possible implementation, the processing module 702 is further configured to:

[0118] If the disk remaining capacity read is less than the remaining capacity threshold, a first process is executed, wherein the first process includes clearing the cache to release the cache space;

[0119] If the average processor occupancy rate within the preset time period is greater than the occupancy rate threshold, a second process is executed, wherein the second process includes analyzing the processes being executed, suspending non-urgent processes therein or releasing unnecessary threads;

[0120] If there is an SQL statement whose query duration is longer than the SQL statement query duration threshold, a third process is performed, wherein the third process includes analyzing the cause of the slow query and automatically optimizing the SQL statement based on the analyzed cause;

[0121] Correspondingly, the monitoring report also includes at least one of the first processing, the second processing and the third processing.

[0122] In a possible implementation, the operation status also includes: whether optimistic lock and deadlock are generated, and the management method processing module 702 is further used to:

[0123] If optimistic lock and / or deadlock exist, fourth information is recorded, where the fourth information includes a database where optimistic lock and / or deadlock exist, and fourth warning information of the database where optimistic lock and / or deadlock exist;

[0124] Accordingly, the monitoring report also includes fourth information.

[0125] The management device for database sub-libraries and sub-tables provided in this embodiment can execute the method provided in the above method embodiment. Its implementation principle and technical effect are similar, and this embodiment will not be described in detail here.

[0126] Figure 8 This is a schematic diagram of the structure of the electronic device provided in this application. Figure 8 As shown, the electronic device 80 provided in this embodiment includes: at least one processor 801 and a memory 802. Optionally, the device 80 also includes a communication component 803. The processor 801, the memory 802 and the communication component 803 are connected via a bus 804.

[0127] In a specific implementation process, at least one processor 801 executes the computer-executable instructions stored in the memory 802, so that at least one processor 801 executes the above method.

[0128] The specific implementation process of the processor 801 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0129] In the above embodiments, it should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. The general-purpose processor may be a microprocessor or the processor may be any conventional processor, etc. The steps of the method disclosed in the invention may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0130] The memory may include a high-speed memory (Random Access Memory, RAM), and may also include a non-volatile memory (Non-volatile Memory, NVM), such as at least one disk memory.

[0131] The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.

[0132] The present application also provides a computer program product, including a computer program, which implements the above method when executed by a processor.

[0133] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0134] The above-mentioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The readable storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0135] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0136] The division of units is only a logical function division, and there may be other divisions in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interface, device or unit, which can be electrical, mechanical or other forms.

[0137] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple 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.

[0138] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0139] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc., various media that can store program codes.

[0140] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0141] Finally, it should be noted that those skilled in the art will readily conceive of other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present invention, which follow the general principles of the present invention and include common knowledge or customary technical means in the art not disclosed by the present invention, are not limited to the precise structure described above and shown in the drawings, and may be modified and changed in various ways without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

Claims

1. A method for managing database sub-libraries and sub-tables, characterized in that: include: The monitoring script is used to collect the operation status of the database sub-libraries and sub-tables in the distributed system, and the monitoring script itself contains the application program corresponding to the monitoring function; Generate a monitoring report for the distributed system based on the operation status of the database sub-libraries and sub-tables within a preset time period; The interface of the internal instant messaging software is called to notify the database administrator of the monitoring report.

2. The management method according to claim 1, characterized in that: The monitoring script is written in a combination of shell language and lua language, wherein the monitoring script written in shell language includes an application that calls the monitoring script written in lua language, and the monitoring script written in lua language includes an application that corresponds to disk balance monitoring, processor occupancy monitoring, and structured query language (SQL) statement monitoring when access time is greater than a time threshold.

3. The management method according to claim 1 or 2, characterized in that: The operation status at least includes: disk margin, processor occupancy rate, and SQL statements whose access time is longer than a time threshold. The monitoring report for the distributed system is generated according to the operation status of the database sub-library and sub-table within a preset time, including: Read the disk space and processor occupancy rate of the server where the database sub-library and sub-table are located within the preset time length, and obtain the SQL statement whose query time length for the database sub-library and sub-table is greater than the SQL statement query time length threshold; Determine the average processor occupancy rate within the preset time period according to the processor occupancy rate of the server where the database sub-library and sub-table are located within the preset time period; If the read disk remaining capacity is less than the remaining capacity threshold, first information is recorded, wherein the first information includes the disk remaining capacity and first warning information for the disk remaining capacity; If the average occupancy rate of the processor within the preset time period is greater than the occupancy rate threshold, recording second information, wherein the second information includes the average occupancy rate of the processor within the preset time period and second warning information of the average occupancy rate of the processor within the preset time period; If a SQL statement whose query time is longer than the SQL statement query time threshold is obtained, third information is recorded, wherein the third information includes the SQL statement whose query time is longer than the SQL statement query time threshold and the third warning information of the SQL statement whose query time is longer than the SQL statement query time threshold; A monitoring report for the distributed system is generated according to the recorded information, wherein the recorded information includes at least one of the first information, the second information, and the third information.

4. The management method according to claim 3, characterized in that: The step of obtaining the SQL statement whose query duration for the sub-library and sub-table of the database is greater than the SQL statement query duration threshold comprises: Based on the SQL statement query time threshold, the monitoring script is applied to analyze the slow query log to obtain SQL statements whose query time exceeds the SQL statement query time threshold; A slow SQL query list is generated according to the SQL statements whose query duration exceeds the SQL statement query duration threshold, wherein the slow SQL query list includes the SQL statements whose query duration exceeds the SQL statement query duration threshold.

5. The management method according to claim 3, characterized in that: The management method also includes: If the disk remaining capacity read is less than the remaining capacity threshold, a first process is executed, wherein the first process includes clearing the cache to release the cache space; If the average processor occupancy rate within the preset time period is greater than the occupancy rate threshold, performing a second process, wherein the second process includes analyzing the processes being executed, suspending non-urgent processes therein or releasing unnecessary threads; If there is an SQL statement whose query duration is longer than the SQL statement query duration threshold, a third process is performed, wherein the third process includes analyzing the cause of the slow query and automatically optimizing the SQL statement based on the analyzed cause; Correspondingly, the monitoring report also includes at least one of the first processing, the second processing and the third processing.

6. The management method according to claim 3, characterized in that: The operation status also includes: whether optimistic lock and deadlock are generated, and the management method also includes: If there is an optimistic lock and / or a deadlock, fourth information is recorded, wherein the fourth information includes a database where the optimistic lock and / or the deadlock exists, and fourth warning information of the database where the optimistic lock and / or the deadlock exists; Correspondingly, the monitoring report also includes the fourth information.

7. A management device for database sub-libraries and sub-tables, characterized in that: include: An acquisition module is used to collect the operation status of database sub-libraries and sub-tables in a distributed system by applying a monitoring script, wherein the monitoring script itself contains an application program corresponding to the monitoring function; A processing module, used to generate a monitoring report for the distributed system according to the operation status of the database sub-library and sub-table within a preset time period and the preset thresholds corresponding to different operation statuses; The communication module is used to call the interface of the internal instant messaging software to notify the database administrator of the monitoring report.

8. An electronic device, characterized in that: include: Memory, processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed.

Images