Table space fragmentation monitoring method, device and electronic equipment

Abstract

The present application discloses a method, device, and electronic device for monitoring tablespace fragmentation. The method comprises: obtaining a data directory under a database, traversing the data directory, and obtaining a target data directory that is not a system library directory; traversing the target data directory, and obtaining a target table data file under the target data directory; obtaining attribute information of the target table data file, and obtaining table information of the target table from a database instance of the database based on the attribute information, and determining whether the target table has tablespace fragmentation based on the table information. The present application no longer directly obtains tablespace fragmentation through a database system table, thus avoiding the problem of a serious performance bottleneck in the database when a large number of tables exist, and improving the efficiency and reliability of the process of monitoring tablespace fragmentation.

Description

Technical Field

[0001] The present application relates to the field of computer technology, and in particular to a method, device, and electronic device for monitoring table space fragmentation. Background Art

[0002] In daily use, online database systems often use delete operations to clean up large amounts of historical data. This doesn't free up the disk space occupied by the data, leading to table fragmentation, which is referred to in this application as tablespace fragmentation. Related technologies typically automatically collect tables with large fragmentation footprints to improve disk utilization, providing system administrators with reference indicators for freeing up space.

[0003] However, in related technologies, monitoring tablespace fragmentation often involves a large number of tables in some database instances. This inevitably leads to severe performance bottlenecks in the database, resulting in low efficiency and reliability in the monitoring process. Therefore, improving the efficiency and reliability of tablespace fragmentation monitoring has become an urgent issue. Summary of the Invention

[0004] The present application aims to solve one of the technical problems in the related art at least to a certain extent.

[0005] To this end, the first purpose of this application is to propose a method for monitoring table space fragmentation, which is used to solve the technical problems of low efficiency and poor reliability in existing methods for monitoring table space fragmentation.

[0006] The second object of the present invention is to provide a device for monitoring table space fragmentation.

[0007] A third object of the present invention is to provide an electronic device.

[0008] A fourth object of the present invention is to provide a computer-readable storage medium.

[0009] In order to achieve the above-mentioned purpose, an embodiment of the first aspect of the present application provides a method for monitoring table space fragmentation, which includes the following steps: obtaining a data directory under a database, and traversing the data directory to obtain a target data directory of a non-system library directory; traversing the target data directory to obtain a target table data file under the target data directory, wherein the target table data file stores the data content of the target table; obtaining attribute information of the target table data file, and obtaining table information of the target table from a database instance of the database based on the attribute information; and determining whether the target table has table space fragmentation based on the table information.

[0010] In addition, the tablespace fragmentation monitoring method according to the above embodiment of the present application may also have the following additional technical features:

[0011] According to one embodiment of the present application, traversing the target data directory to obtain the target table data file under the target data directory includes: whenever traversing to a table data file, obtaining the size of the table data file, and in response to the size of the table data file being greater than or equal to a first preset threshold, using the table data file whose size is greater than or equal to the first preset threshold as the target table data file.

[0012] According to one embodiment of the present application, obtaining the attribute information of the target table data file includes: determining the directory name corresponding to the target table data file based on the directory where the target table data file is located; obtaining the file name of the target table data file, wherein the attribute information includes the directory name and the file name.

[0013] According to one embodiment of the present application, obtaining the table information of the target table from the database instance of the database based on the attribute information includes: determining the library name and table name corresponding to the target table based on the directory name and the file name; generating a structured query language SQL statement based on the library name and the table name, and performing a table information query operation on the database instance to obtain the table information of the target table.

[0014] According to one embodiment of the present application, determining the library name and table name corresponding to the target table based on the directory name and the file name includes: directly determining the directory name as the library name corresponding to the target table; removing the suffix characters of the file name, and using the file name without the suffix characters as the table name corresponding to the target table.

[0015] According to one embodiment of the present application, determining whether a target table has table space fragmentation based on the table information of the target table includes: determining the data size of the target table based on the table information; calculating the difference between the data size of the target table data file and the data size of the target table; if the difference is greater than a preset value, determining that the target table has table space fragmentation.

[0016] According to one embodiment of the present application, the method further includes: in response to the existence of table space fragments in the target table, obtaining the size of the table space fragments, and determining whether the size of the table space fragments is greater than or equal to a second preset threshold; in response to the size of the table space fragments being greater than or equal to the second preset threshold, generating a fragment cleanup reminder and sending it to a terminal device; receiving a cleanup instruction from the terminal device, and cleans the table space fragments.

[0017] The first embodiment of the present application provides a method for monitoring table space fragmentation, which can obtain a data directory under a database and traverse the data directory to obtain a target data directory of a non-system library directory, and then traverse the target data directory to obtain a target table data file under the target data directory, and then obtain attribute information of the target table data file, and obtain table information of the target table from a database instance of the database based on the attribute information, and determine whether the target table has table space fragmentation based on the table information to realize table space fragmentation monitoring, and no longer directly obtain table space fragmentation through the database system table, thereby avoiding the problem of serious performance bottlenecks in the database when a large number of tables exist, and improving the efficiency and reliability of the table space fragmentation monitoring process.

[0018] In order to achieve the above-mentioned purpose, the second aspect embodiment of the present application provides a monitoring device for table space fragmentation, and the monitoring device for table space fragmentation includes: a first acquisition module, used to obtain a data directory under a database, and traverse the data directory to obtain a target data directory of a non-system library directory; a second acquisition module, used to traverse the target data directory to obtain a target table data file under the target data directory, wherein the target table data file stores the data content of the target table; a third acquisition module, used to obtain attribute information of the target table data file, and obtain table information of the target table from the database instance of the database according to the attribute information; a determination module, used to determine whether the target table has table space fragmentation according to the table information.

[0019] In addition, the tablespace fragmentation monitoring device according to the above embodiment of the present application may also have the following additional technical features:

[0020] According to an embodiment of the present application, the second acquisition module is further configured to: whenever traversing to one of the table data files, obtain the size of the table data file; in response to the size of the table data file being greater than or equal to a first preset threshold, use the table data file whose size is greater than or equal to the first preset threshold as the target table data file.

[0021] According to one embodiment of the present application, the third acquisition module is further used to: determine the directory name corresponding to the target table data file based on the directory where the target table data file is located; and obtain the file name of the target table data file, wherein the attribute information includes the directory name and the file name.

[0022] According to one embodiment of the present application, the third acquisition module is also used to: determine the library name and table name corresponding to the target table based on the directory name and the file name; generate a structured query language SQL statement based on the library name and the table name, and perform a table information query operation on the database instance to obtain the table information of the target table.

[0023] According to one embodiment of the present application, the third acquisition module is also used to: directly determine the directory name as the library name corresponding to the target table; remove the suffix characters of the file name, and use the file name without the suffix characters as the table name corresponding to the target table.

[0024] According to one embodiment of the present application, the determination module is further used to: determine the data size of the target table based on the table information; calculate the difference between the data size of the target table data file and the data size of the target table; if the difference is greater than a preset value, determine that table space fragmentation exists in the target table.

[0025] According to one embodiment of the present application, it also includes a cleaning module, which is used to: in response to the existence of table space fragments in the target table, obtain the size of the table space fragments, and determine whether the size of the table space fragments is greater than or equal to a second preset threshold; in response to the size of the table space fragments being greater than or equal to the second preset threshold, generate a fragment cleaning reminder and send it to a terminal device; receive a cleaning instruction from the terminal device, and clean up the table space fragments.

[0026] The second aspect of the present application provides a monitoring device for table space fragmentation, which can obtain a data directory under a database and traverse the data directory to obtain a target data directory of a non-system library directory, and then traverse the target data directory to obtain a target table data file under the target data directory, and then obtain attribute information of the target table data file, and obtain table information of the target table from a database instance of the database based on the attribute information, and determine whether the target table has table space fragmentation based on the table information to realize table space fragmentation monitoring, and no longer directly obtain table space fragments through the database system table, thereby avoiding the problem of serious performance bottlenecks in the database when a large number of tables exist, and improving the efficiency and reliability of the table space fragmentation monitoring process.

[0027] In order to achieve the above-mentioned purpose, the third aspect embodiment of the present application provides an electronic device, including: a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, it implements a table space fragmentation monitoring method as described in any one of the first aspect embodiments of the present application.

[0028] In order to achieve the above-mentioned purpose, the fourth embodiment of the present application provides a computer-readable storage medium, which, when executed by a processor, implements the table space fragmentation monitoring method as described in any one of the first embodiment of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 A flowchart of a method for monitoring table space fragmentation disclosed in one embodiment of the present application;

[0030] Figure 2 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0031] Figure 3 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0032] Figure 4 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0033] Figure 5 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0034] Figure 6 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0035] Figure 7 A flowchart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application;

[0036] Figure 8 A schematic diagram of the structure of a table space fragmentation monitoring device disclosed in one embodiment of the present application;

[0037] Figure 9 A schematic diagram of the structure of a table space fragmentation monitoring device disclosed in another embodiment of the present application;

[0038] Figure 10 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0039] To better understand the above technical solutions, exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments described herein. Instead, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0040] The following describes the table space fragmentation monitoring method, device and electronic device proposed in accordance with the embodiments of the present application with reference to the accompanying drawings.

[0041] Figure 1 A flowchart of a method for monitoring table space fragmentation disclosed in an embodiment of the present application is provided.

[0042] like Figure 1 As shown, the table space fragmentation monitoring method proposed in the embodiment of the present application specifically includes the following steps:

[0043] S101. Obtain a data directory under a database, and traverse the data directory to obtain a target data directory that is not a system library directory.

[0044] Among them, data directories include: system directories and non-system directories.

[0045] It's important to note that database systems typically have at least one system library configured by default. For example, in the relational database management system MySQL 5.7, the following system libraries are automatically generated after system initialization: MySQL; the system library Information_Schema, which provides access to database metadata; the system library Performance_Schema, which monitors resource consumption and resource waits during MySQL 5.7's lower-level operations; and the system library Sys, which provides quick access to database system metadata.

[0046] However, because the data stored in the system database is usually small, rarely used, and cannot be deleted, obtaining the data directories of all directories in the database during tablespace fragmentation monitoring will inevitably increase unnecessary time consumption. Therefore, according to the tablespace fragmentation monitoring method proposed in this application, only the target data directories of non-system database directories are obtained.

[0047] S103: Traverse the target data directory to obtain the target table data file under the target data directory, wherein the target table data file stores the data content of the target table.

[0048] It should be noted that to further shorten the time spent on tablespace fragmentation monitoring, after traversing the target data directory, only the target table data files in the target data directory can be obtained. The target table data files can be MySQL table data files with a .ibd file name suffix that meet preset conditions, such as abcde.ibd.

[0049] As a possible implementation, the operating system layer can loop through all subdirectories under the target data directory using a for loop to obtain all table data files under the target data directory. Furthermore, each time a table data file is traversed, the size of the table data file can be obtained and compared with a pre-set first threshold. If the size of the table data file is greater than or equal to the first preset threshold, then, in response to the table data size being greater than or equal to the first preset threshold, the table data file greater than or equal to the first preset threshold can be selected as the target table data file.

[0050] The first preset threshold can be set according to actual conditions, for example, 10G, 30G, or 50G.

[0051] For example, a subdirectory under the target data directory contains MySQL table structure files with a .frm filename suffix and MySQL table data files with a .ibd filename suffix. In this case, the operating system layer can use a for loop to loop through the files with a .frm and .ibd filename suffixes in the target data directory to obtain all table data files with a .ibd filename suffix. Furthermore, the size of the table data file can be obtained. If the table data size is greater than or equal to 50GB, the table data file greater than or equal to 50GB is selected as the target table data file.

[0052] S105: Acquire attribute information of the target table data file, and acquire table information of the target table from the database instance of the database according to the attribute information.

[0053] In the embodiment of the present application, after obtaining the target table data file, the attribute information of the target table data file can be obtained, and based on the attribute information, the table information of the target table can be obtained from the database instance of the database. The attribute information includes the directory name and the file name.

[0054] It should be noted that, in this application, the specific method for obtaining the table information of the target table based on the attribute information is not limited and can be selected based on the actual situation. Alternatively, the library name and table name corresponding to the target table can be determined based on the directory name and file name in the attribute information, respectively. Furthermore, the table information of the target table can be obtained based on the library name and table name.

[0055] S107: Determine whether the target table has table space fragments based on the table information.

[0056] In the embodiment of the present application, after obtaining the table information of the target table, it can be determined whether the target table has table space fragmentation based on the table information.

[0057] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0058] Since the present application can obtain the data directory under the database and traverse the data directory to obtain the target data directory of the non-system library directory, and then traverse the target data directory to obtain the target table data file under the target data directory, and then obtain the attribute information of the target table data file, and obtain the table information of the target table from the database instance of the database according to the attribute information, and determine whether the target table has table space fragmentation according to the table information to realize the monitoring of table space fragmentation, and no longer directly obtain the table space fragmentation through the database system table, thereby avoiding the problem of serious performance bottlenecks in the database when a large number of tables exist, and improving the efficiency and reliability of the table space fragmentation monitoring process.

[0059] It should be noted that when attempting to obtain the attribute information of the target table data file, the directory name and file name of the target table data file may be processed separately.

[0060] As a possible implementation, Figure 2 As shown, based on the above embodiment, the process of obtaining the attribute information of the target table data file in the above step S105 specifically includes the following steps:

[0061] S201 : Determine the directory name corresponding to the target table data file based on the directory where the target table data file is located.

[0062] For example, if the directory where the target table data file is located is a test directory, in this case, the directory name corresponding to the target table data file can be determined to be test.

[0063] S203: Obtain the file name of the target table data file, wherein the attribute information includes the directory name and the file name.

[0064] For example, for the target table data file emp.ibd, the file name of the target table data file can be obtained as emp.ibd.

[0065] Furthermore, after the attribute information of the target table is obtained, the table information of the target table can be obtained from the database instance of the database according to the attribute information.

[0066] As a possible implementation, Figure 3 As shown, based on the above embodiment, the process of obtaining the table information of the target table from the database instance of the database according to the attribute information in the above step S105 specifically includes the following steps:

[0067] S301. Determine the library name and table name corresponding to the target table according to the directory name and file name.

[0068] As a possible implementation, Figure 4 As shown, based on the above embodiment, the process of determining the library name and table name corresponding to the target table according to the directory name and file name in the above step S301 specifically includes the following steps:

[0069] S401. Directly determine the directory name as the library name corresponding to the target table.

[0070] For example, if the directory name corresponding to the target table data file is test, the library name corresponding to the target table can be determined as test.

[0071] S403: Remove the suffix characters from the file name, and use the file name without the suffix characters as the table name corresponding to the target table.

[0072] It should be noted that after removing the suffix characters from the file name, you can connect to the database example to obtain the corresponding table information.

[0073] For example, if the file name corresponding to the target table data file is emp.ibd, you can remove the suffix characters of the file name, that is, .ibd, and use the file name emp without the suffix characters as the table name corresponding to the target table.

[0074] It should be noted that in actual applications, database names correspond to directory names at the operating system level, and table names correspond to operating system files. For example, if the directory name is test and the file names are emp.frm and emp.ibd, they will be displayed as test.emp in MySQL.

[0075] S303: Generate a structured query language SQL statement according to the library name and the table name, and execute a table information query operation on the database instance to obtain table information of the target table.

[0076] As a possible implementation, a structured query language (SQL) statement can be generated based on the database name and table name. Further, a database instance can be connected through the SQL statement to perform a query operation on the database instance to obtain table information of the target table.

[0077] For example, taking MySQL 5.7 as an example, the following SQL statement can be generated: SHOW TABLE STATUS LIKE (get table information), and then the database can obtain the table information of the target table by executing the SHOW TABLE STATUS command.

[0078] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0079] Since this application can connect to the database instance after obtaining the library name and table name, and obtain the table information of the corresponding target table through SHOWTABLE STATUS, it can further reduce the impact on database performance while ensuring that the corresponding requirements can be met, thereby improving the efficiency and reliability of the table space fragmentation monitoring process.

[0080] Furthermore, after obtaining the table information of the target table, it can be determined whether the target table has table space fragmentation according to the table information.

[0081] As a possible way to achieve this, Figure 5 As shown, based on the above embodiment, the following steps are specifically included:

[0082] S501: Determine the data size of the target table according to the table information.

[0083] In the embodiment of the present application, after the table information of the target table is obtained, the size of the table data file can be obtained each time a target table data file is traversed.

[0084] S503: Calculate the difference between the data size of the target table data file and the data size of the target table.

[0085] It should be noted that after obtaining the size of the table data file, the data size of the target table data file can be subtracted from the data size of the target table to obtain the difference between the data size of the target table data file and the data size of the target table.

[0086] S505: If the difference is greater than a preset value, it is determined that table space fragmentation exists in the target table.

[0087] In an embodiment of the present application, after obtaining the difference between the data size of the target table data file and the data size of the target table, the difference can be compared with a pre-set preset value. If the difference is greater than the preset value, it is determined that table space fragmentation exists in the target table; if the difference is less than or equal to or greater than the preset value, it is determined that table space fragmentation does not exist in the target table.

[0088] The preset value can be set according to actual conditions, for example, 10G, 30G, or 50G.

[0089] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0090] Since the present application can determine the data size of the target table based on the table information and calculate the difference between the data size of the target table data file and the data size of the target table, if the difference is greater than the preset value, it is determined that table space fragmentation exists in the target table. While ensuring that the corresponding requirements can be met, the impact on database performance can be further reduced, thereby improving the efficiency and reliability of the table space fragmentation monitoring process.

[0091] Furthermore, after obtaining the tablespace fragmentation, the tablespace fragmentation size information can be extracted from the SHOW TABLE STATUS LIKE field, and a matching reminder can be generated based on the tablespace fragmentation size.

[0092] As a possible implementation, Figure 6 As shown, based on the above embodiment, the following steps are specifically included:

[0093] S601: In response to the existence of table space fragments in a target table, obtain the size of the table space fragments, and determine whether the size of the table space fragments is greater than or equal to a second preset threshold.

[0094] The second preset threshold can be set according to actual conditions, for example, 10G, 30G, or 50G.

[0095] S603: In response to the size of the table space fragment being greater than or equal to a second preset threshold, generate a fragment cleanup reminder and send it to the terminal device.

[0096] Among them, the cleaning reminder can be a reminder in various forms such as text or voice.

[0097] It should be noted that the specific method for sending the debris cleaning reminder to the terminal device is not limited in this application and can be selected according to actual conditions. Optionally, the debris cleaning reminder can be sent to the terminal device via email, text message, WeChat, etc.

[0098] S605: Receive a cleanup instruction from the terminal device and clean up table space fragments.

[0099] Optionally, the administrator can send a cleanup instruction based on the debris cleanup reminder displayed on the terminal device. Accordingly, after receiving the cleanup instruction from the terminal device, the table space fragments can be cleaned up.

[0100] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0101] Since the present application can obtain the size of table space fragments and determine whether the size of table space fragments is greater than or equal to a second preset threshold, and then generate a fragment cleaning reminder and send it to the terminal device in response to the size of the table space fragments being greater than or equal to the second preset threshold, and then receive the cleaning instruction of the terminal device, the table space fragments are cleaned up, so as to discover tables with more fragments through automated services, remind the administrator to clean up the table space fragments in time, thereby optimizing disk utilization and improving business continuity without affecting database performance.

[0102] Figure 7 This is a flow chart of a method for monitoring table space fragmentation disclosed in another embodiment of the present application, such as Figure 7 As shown, the specific steps include:

[0103] S701. Obtain the data directory under the database.

[0104] S703: Traverse the data directory and determine whether the data directory is a system library directory.

[0105] Optionally, if the data directory is a system library directory, the data directory is ignored; if the data directory is not a system library directory, step S705 can be executed.

[0106] S705: Use the data directory as the target data directory.

[0107] S707: Loop through all subdirectories under the target data directory to obtain all table data files under the target data directory.

[0108] S709: Obtain the size of the table data file, and determine whether the size of the table data file is greater than or equal to a first preset threshold.

[0109] Optionally, if the size of the table data file is greater than or equal to a first preset threshold, step S711 may be executed; if the size of the table data file is less than the first preset threshold, the table data file may be ignored.

[0110] S711 : Use a table data file whose value is greater than or equal to a first preset threshold as a target table data file.

[0111] S713. Determine the directory name corresponding to the target table data file based on the directory where the target table data file is located.

[0112] S715. Obtain the file name of the target table data file, where the attribute information includes the directory name and the file name.

[0113] S717. Directly determine the library name corresponding to the target table using the directory name.

[0114] S719: Remove the suffix characters from the file name, and use the file name without the suffix characters as the table name corresponding to the target table.

[0115] S721. Generate a structured query language SQL statement based on the library name and the table name, and execute a table information query operation on the database instance to obtain table information of the target table.

[0116] S723. Determine the data size of the target table according to the table information.

[0117] S725: Calculate the difference between the data size of the target table data file and the data size of the target table.

[0118] S727. If the difference is greater than the preset value, it is determined that table space fragmentation exists in the target table.

[0119] Optionally, if the difference is greater than a preset value, step S729 may be executed.

[0120] S729: In response to the existence of table space fragments in the target table, obtain the size of the table space fragments, and determine whether the size of the table space fragments is greater than or equal to a second preset threshold.

[0121] Optionally, if the size of the table space fragment is greater than or equal to a second preset threshold, step S731 may be executed.

[0122] S731. Generate a fragment cleaning reminder and send it to the terminal device.

[0123] S733. Receive a cleanup instruction from the terminal device and clean up table space fragments.

[0124] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0125] 1. Since the present application can obtain the data directory under the database and traverse the data directory to obtain the target data directory of the non-system library directory, and then traverse the target data directory to obtain the target table data file under the target data directory, and then obtain the attribute information of the target table data file, and obtain the table information of the target table from the database instance of the database according to the attribute information, and determine whether the target table has table space fragmentation according to the table information to realize the monitoring of table space fragmentation, it no longer directly obtains table space fragmentation through the database system table, avoids the problem of serious performance bottleneck of the database when a large number of tables exist, and improves the efficiency and reliability of the monitoring process of table space fragmentation.

[0126] 2. Since this application can connect to the database instance after obtaining the library name and table name, and obtain the table information of the corresponding target table through SHOW TABLE STATUS, it can further reduce the impact on database performance while ensuring that the corresponding requirements can be met, and improve the efficiency and reliability of the table space fragmentation monitoring process.

[0127] 3. Since the present application can obtain the size of table space fragments and determine whether the size of table space fragments is greater than or equal to a second preset threshold, and then generate a fragment cleaning reminder and send it to the terminal device in response to the size of the table space fragments being greater than or equal to the second preset threshold, and then receive the cleaning instruction of the terminal device, clean up the table space fragments, so as to discover tables with more fragments through automated services, remind administrators to clean up table space fragments in time, thereby optimizing disk utilization and improving business continuity without affecting database performance.

[0128] Based on the same application concept, an embodiment of the present application also provides a monitoring device for table space fragmentation.

[0129] Figures 8-9 This is a schematic diagram of the structure of the monitoring device for table space fragmentation provided in the embodiment of the present application. Figure 8 As shown, the table space fragmentation monitoring device 100 includes: a first acquisition module 11, a second acquisition module 13, a third acquisition module 15 and a determination module 17.

[0130] Among them, the first acquisition module 11 is used to obtain the data directory under the database, and traverse the data directory to obtain the target data directory of the non-system library directory; the second acquisition module 13 is used to traverse the target data directory to obtain the target table data file under the target data directory, wherein the target table data file stores the data content of the target table; the third acquisition module 15 is used to obtain attribute information of the target table data file, and according to the attribute information, obtain the table information of the target table from the database instance of the database; the determination module 17 is used to determine whether the target table has table space fragmentation according to the table information.

[0131] According to one embodiment of the present application, the second acquisition module 13 is further configured to: whenever traversing to a table data file, obtain the size of the table data file; in response to the size of the table data file being greater than or equal to a first preset threshold, use the table data file whose size is greater than or equal to the first preset threshold as the target table data file.

[0132] According to one embodiment of the present application, the third acquisition module 15 is further used to: determine the directory name corresponding to the target table data file based on the directory where the target table data file is located; and obtain the file name of the target table data file, wherein the attribute information includes the directory name and the file name.

[0133] According to one embodiment of the present application, the third acquisition module 15 is also used to: determine the library name and table name corresponding to the target table based on the directory name and the file name; generate a structured query language SQL statement based on the library name and the table name, and perform a table information query operation on the database instance to obtain the table information of the target table.

[0134] According to one embodiment of the present application, the third acquisition module 15 is further used to: directly determine the directory name as the library name corresponding to the target table; remove the suffix characters of the file name, and use the file name without the suffix characters as the table name corresponding to the target table.

[0135] According to one embodiment of the present application, the determination module 17 is further used to: determine whether the target table has table space fragmentation based on the table information of the target table, including: determining the data size of the target table based on the table information; calculating the difference between the data size of the target table data file and the data size of the target table; if the difference is greater than a preset value, determining that the target table has table space fragmentation.

[0136] According to one embodiment of the present application, Figure 9 As shown, the table space fragmentation monitoring device 100 provided by the present application further includes a cleaning module 19, which is used to: in response to the existence of table space fragments in the target table, obtain the size of the table space fragments, and determine whether the size of the table space fragments is greater than or equal to a second preset threshold; in response to the size of the table space fragments being greater than or equal to the second preset threshold, generate a fragment cleaning reminder and send it to the terminal device; receive a cleaning instruction from the terminal device, and clean up the table space fragments.

[0137] The technical solutions in the above embodiments of the present application have at least the following technical effects or advantages:

[0138] 1. Since the present application can obtain the data directory under the database and traverse the data directory to obtain the target data directory of the non-system library directory, and then traverse the target data directory to obtain the target table data file under the target data directory, and then obtain the attribute information of the target table data file, and obtain the table information of the target table from the database instance of the database according to the attribute information, and determine whether the target table has table space fragmentation according to the table information to realize the monitoring of table space fragmentation, it no longer directly obtains table space fragmentation through the database system table, avoids the problem of serious performance bottleneck of the database when a large number of tables exist, and improves the efficiency and reliability of the monitoring process of table space fragmentation.

[0139] 2. Since this application can connect to the database instance after obtaining the library name and table name, and obtain the table information of the corresponding target table through SHOW TABLE STATUS, it can further reduce the impact on database performance while ensuring that the corresponding requirements can be met, and improve the efficiency and reliability of the table space fragmentation monitoring process.

[0140] 3. Since the present application can obtain the size of table space fragments and determine whether the size of table space fragments is greater than or equal to a second preset threshold, and then generate a table space fragment cleaning reminder in response to the size of the table space fragments being greater than or equal to the second preset threshold, and send it to the terminal device, and then receive the cleaning instruction of the terminal device, clean up the table space fragments, so as to discover tables with more fragments through automated services, remind administrators to clean up table space fragments in time, thereby optimizing disk utilization and improving business continuity without affecting database performance.

[0141] Based on the same application concept, an embodiment of the present application also provides an electronic device.

[0142] Figure 10 This is a schematic diagram of the structure of the electronic device provided in the embodiment of the present application. Figure 10 As shown, the electronic device 2000 includes a memory 210, a processor 220, and a computer program stored in the memory 210 and executable on the processor 220. When the processor executes the program, the aforementioned table space fragmentation monitoring method is implemented.

[0143] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0144] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0145] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0146] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0147] It should be noted that in the claims, any reference signs placed between brackets shall not be construed as limiting the claims. The word "comprising" does not exclude the presence of components or steps not listed in the claim. The word "a" or "an" preceding a component does not exclude the presence of a plurality of such components. The present application may be implemented by means of hardware comprising several different components and by means of a suitably programmed computer. In a unit claim enumerating several means, several of these means may be embodied by one and the same item of hardware. The use of the words first, second, and third etc. does not indicate any order. These words may be interpreted as names.

[0148] Although the preferred embodiments of the present application have been described, those skilled in the art may make additional changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the present application.

[0149] Obviously, those skilled in the art may make various changes and modifications to the present application without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present application fall within the scope of the claims and their equivalents, the present application is intended to include such modifications and variations.

Claims

1. A method for monitoring table space fragmentation, characterized in that: include: Obtain a data directory under the database, and traverse the data directory to obtain a target data directory that is not a system library directory; Traversing the target data directory to obtain a target table data file under the target data directory, wherein the target table data file stores data content of the target table; Acquire attribute information of the target table data file, and acquire table information of the target table from the database instance of the database according to the attribute information; Determine whether table space fragmentation exists in the target table according to the table information.

2. The method according to claim 1, characterized in that The traversing the target data directory to obtain the target table data file under the target data directory includes: Whenever a table data file is traversed, the size of the table data file is obtained. In response to the size of the table data file being greater than or equal to a first preset threshold, the table data file whose size is greater than or equal to the first preset threshold is used as the target table data file.

3. The monitoring method according to claim 1, wherein: The acquiring of the attribute information of the target table data file includes: Determine the directory name corresponding to the target table data file based on the directory where the target table data file is located; The file name of the target table data file is obtained, wherein the attribute information includes the directory name and the file name.

4. The monitoring method according to claim 3, characterized in that: The acquiring the table information of the target table from the database instance of the database according to the attribute information includes: Determine the library name and table name corresponding to the target table according to the directory name and the file name; A structured query language SQL statement is generated according to the library name and the table name, and a table information query operation is performed on the database instance to obtain table information of the target table.

5. The monitoring method according to claim 4, characterized in that: Determining the library name and table name corresponding to the target table according to the directory name and the file name includes: Directly determine the directory name as the library name corresponding to the target table; The suffix characters of the file name are removed, and the file name with the suffix characters removed is used as the table name corresponding to the target table.

6. The monitoring method according to claim 1, characterized in that: The determining whether the target table has table space fragmentation according to the table information of the target table includes: Determine the data size of the target table according to the table information; Calculating a difference between the data size of the target table data file and the data size of the target table; If the difference is greater than a preset value, it is determined that table space fragmentation exists in the target table.

7. The monitoring method according to any one of claims 1 to 6, characterized in that: Also includes: In response to the presence of table space fragments in the target table, obtaining the size of the table space fragments, and determining whether the size of the table space fragments is greater than or equal to a second preset threshold; In response to the size of the table space fragment being greater than or equal to the second preset threshold, generating a fragment cleanup reminder and sending it to a terminal device; A cleaning instruction is received from a terminal device, and the table space fragments are cleaned.

8. A device for monitoring tablespace fragmentation, characterized in that: include: A first acquisition module is used to acquire a data directory under the database, and traverse the data directory to acquire a target data directory that is not a system library directory; A second acquisition module is configured to traverse the target data directory and acquire a target table data file under the target data directory, wherein the target table data file stores data content of the target table; a third acquisition module, configured to acquire attribute information of the target table data file, and acquire table information of the target table from a database instance of the database according to the attribute information; The determination module is used to determine whether the target table has table space fragments according to the table information.

9. An electronic device, characterized in that: include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method for monitoring table space fragmentation according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method for monitoring table space fragmentation according to any one of claims 1 to 7 is implemented.

Images