CN116701319A - Watch space fragment monitoring method and device and electronic equipment - Google Patents
Watch space fragment monitoring method and device and electronic equipment Download PDFInfo
- Publication number
- CN116701319A CN116701319A CN202210178848.8A CN202210178848A CN116701319A CN 116701319 A CN116701319 A CN 116701319A CN 202210178848 A CN202210178848 A CN 202210178848A CN 116701319 A CN116701319 A CN 116701319A
- Authority
- CN
- China
- Prior art keywords
- target
- directory
- target table
- data
- name
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 239000012634 fragment Substances 0.000 title claims abstract description 127
- 238000000034 method Methods 0.000 title claims abstract description 60
- 238000012544 monitoring process Methods 0.000 title claims abstract description 54
- 238000004140 cleaning Methods 0.000 claims description 41
- 238000013467 fragmentation Methods 0.000 claims description 15
- 238000006062 fragmentation reaction Methods 0.000 claims description 15
- 238000004590 computer program Methods 0.000 claims description 11
- 230000004044 response Effects 0.000 claims description 10
- 238000012806 monitoring device Methods 0.000 claims description 8
- 238000003860 storage Methods 0.000 claims description 6
- 238000010586 diagram Methods 0.000 description 11
- 230000008569 process Effects 0.000 description 8
- 230000000694 effects Effects 0.000 description 6
- 238000012360 testing method Methods 0.000 description 6
- 230000006870 function Effects 0.000 description 4
- 238000012986 modification Methods 0.000 description 4
- 230000004048 modification Effects 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 230000004075 alteration Effects 0.000 description 2
- 230000000007 visual effect Effects 0.000 description 2
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/17—Details of further file system functions
- G06F16/1724—Details of de-fragmentation performed by the file system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/17—Details of further file system functions
- G06F16/1727—Details of free space management performed by the file system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a method and a device for monitoring table space fragments and electronic equipment, wherein the method comprises the following steps: acquiring a data directory under a database, traversing the data directory, and acquiring a target data directory of a non-system library directory from the data directory; traversing the target data directory to obtain a target table data file under the target data directory; and acquiring attribute information of the target table data file, acquiring table information of the target table from a database instance of the database according to the attribute information, and determining whether the target table has table space fragments according to the table information. The method and the device can not directly obtain the table space fragments through the database system table any more, avoid the problem of serious performance bottleneck of the database under the condition that a large number of tables exist, and improve the efficiency and reliability of the table space fragments in the monitoring process.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a method and an apparatus for monitoring a tablespace fragment, and an electronic device.
Background
In the on-line database system, a scene of using deletion (Delete) to clear a large amount of historical data exists in the daily use process, and the disk space occupied by the data cannot be released in the scene, so that table fragments occupy larger disk space, which is called table space fragments for short in the application. In the related art, in order to recycle the part of the disk space to increase the disk utilization, a system administrator is generally provided with a reference index for space release by automatically collecting a table with a large fragment occupation.
However, in the method for monitoring the tablespace debris in the related art, since some examples of the database of the current platform have a large number of tables, in this case, serious performance bottlenecks may occur in the database, which results in technical problems of low efficiency and poor reliability in the process of monitoring the tablespace debris. Thus, how to improve the efficiency and reliability in the monitoring of the tablespace debris has become a challenge.
Disclosure of Invention
The present application aims to solve at least one of the technical problems in the related art to some extent.
Therefore, a first object of the present application is to provide a method for monitoring tablespace debris, which is used for solving the technical problems of low efficiency and poor reliability existing in the existing method for monitoring tablespace debris.
A second object of the application is to propose a monitoring device for the fragmentation of a tablespace.
A third object of the present application is to propose an electronic device.
A fourth object of the present application is to propose a computer readable storage medium.
To achieve the above object, an embodiment of a first aspect of the present application provides a method for monitoring a tablespace fragment, the method including the steps of: acquiring a data directory under a database, traversing the data directory, and acquiring a target data directory of a non-system library directory from the data 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 a target table; acquiring attribute information of the target table data file, and acquiring table information of the target table from a database instance of the database according to the attribute information; and determining whether the target table has table space fragments according to the table information.
In addition, the method for monitoring the tablespace debris according to the above embodiment of the present application may further have the following additional technical features:
according to an embodiment of the present application, the traversing the target data directory to obtain a target table data file under the target data directory includes: and each time traversing to one table data file, acquiring the size of the table data file, and responding to the fact that the size of the table data file is larger than or equal to a first preset threshold value, taking the table data file with the size larger than or equal to the first preset threshold value as the target table data file.
According to one embodiment of the present application, the obtaining attribute information of the target table data file includes: determining a directory name corresponding to the target table data file based on the directory where the target table data file is located; and acquiring the file name of the target table data file, wherein the attribute information comprises the directory name and the file name.
According to one embodiment of the present application, the obtaining, according to the attribute information, table information of a target table from a database instance of the database includes: according to the directory names and the file names, determining the corresponding library names and table names of the target table; and generating a Structured Query Language (SQL) statement according to the library name and the table name, and executing table information query operation on the database instance to acquire the table information of the target table.
According to one embodiment of the present application, the determining, according to the directory name and the file name, a pool name and a table name corresponding to the target table includes: directly determining the directory name as a library name corresponding to the target table; and removing the suffix character of the file name, and taking the file name with the suffix character removed as the table name corresponding to the target table.
According to one embodiment of the present application, the determining whether the target table has a tablespace fragment according to the table information of the target table includes: determining 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; and if the difference value is larger than a preset value, determining that the target table has table space fragments.
According to one embodiment of the present application, further comprising: responding to the existence of the table space fragments of the target table, acquiring the sizes of the table space fragments, and judging whether the sizes of the table space fragments are larger than or equal to a second preset threshold value; generating a fragment cleaning prompt and sending the fragment cleaning prompt to a terminal device in response to the size of the tablespace fragments being greater than or equal to the second preset threshold; and receiving a cleaning instruction of the terminal equipment, and cleaning the table space fragments.
The embodiment of the application provides a method for monitoring table space fragments, which can obtain a target data directory of a non-system library directory by obtaining a data directory under a database and traversing the data directory, then traversing the target data directory to obtain a target table data file under the target data directory, further obtain attribute information of the target table data file, obtain table information of a target table from a database instance of the database according to the attribute information, determine whether the table space fragments exist in the target table according to the table information, so as to realize the monitoring of the table space fragments, and not obtain the table space fragments directly through a database system table any more, thereby avoiding serious performance bottleneck problem of the database under the condition that a large number of tables exist, and improving the efficiency and reliability in the monitoring process of the table space fragments.
In order to achieve the above object, an embodiment of a second aspect of the present application provides a monitoring apparatus for a tablespace fragment, including: the first acquisition module is used for acquiring a data directory under a database, traversing the data directory and acquiring a target data directory of a non-system library directory from the data directory; the second acquisition module is used for traversing the target data catalog to acquire a target table data file under the target data catalog, wherein the target table data file stores the data content of a target table; the third acquisition module is used for acquiring attribute information of the target table data file, acquiring table information of the target table from a database instance of the database according to the attribute information, and the determination module is used for determining whether table space fragments exist in the target table according to the table information.
In addition, the monitoring device for the tablespace debris according to the above embodiment of the present application may further have the following additional technical features:
according to an embodiment of the present application, the second obtaining module is further configured to: and each time traversing to one table data file, acquiring the size of the table data file, and responding to the fact that the size of the table data file is larger than or equal to a first preset threshold value, taking the table data file with the size larger than or equal to the first preset threshold value as the target table data file.
According to an embodiment of the present application, the third obtaining module is further configured to: determining a directory name corresponding to the target table data file based on the directory where the target table data file is located; and acquiring the file name of the target table data file, wherein the attribute information comprises the directory name and the file name.
According to an embodiment of the present application, the third obtaining module is further configured to: according to the directory names and the file names, determining the corresponding library names and table names of the target table; and generating a Structured Query Language (SQL) statement according to the library name and the table name, and executing table information query operation on the database instance to acquire the table information of the target table.
According to an embodiment of the present application, the third obtaining module is further configured to: directly determining the directory name as a library name corresponding to the target table; and removing the suffix character of the file name, and taking the file name with the suffix character removed as the table name corresponding to the target table.
According to one embodiment of the application, the determining module is further configured to: determining 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; and if the difference value is larger than a preset value, determining that the target table has table space fragments.
According to one embodiment of the application, the cleaning module is further configured to: responding to the existence of the table space fragments of the target table, acquiring the sizes of the table space fragments, and judging whether the sizes of the table space fragments are larger than or equal to a second preset threshold value; generating a fragment cleaning prompt and sending the fragment cleaning prompt to a terminal device in response to the size of the tablespace fragments being greater than or equal to the second preset threshold; and receiving a cleaning instruction of the terminal equipment, and cleaning the table space fragments.
According to the monitoring device for the table space fragments, the data catalogs under the database can be obtained, the data catalogs are traversed, the target data catalogs of the non-system catalogs are obtained, then the target data catalogs are traversed, the target table data files under the target data catalogs are obtained, further the attribute information of the target table data files is obtained, the table information of the target table is obtained from the database instance of the database according to the attribute information, whether the table space fragments exist in the target table is determined according to the table information, so that the monitoring of the table space fragments is achieved, the table space fragments are not obtained directly through the database system table, the problem that serious performance bottlenecks of the database occur under the condition that a large number of tables exist is avoided, and the efficiency and reliability in the table space fragment monitoring process are improved.
To achieve the above object, an embodiment of a third aspect of the present application provides an electronic device, including: the system comprises a memory, a processor and a computer program stored in the memory and capable of running on the processor, wherein the processor realizes the monitoring method of the tablespace fragments according to any one of the embodiments of the first aspect of the application when executing the program.
In order to achieve the above object, a fourth aspect of the present application provides a computer-readable storage medium, which when executed by a processor, implements a method for monitoring a tablespace fragment according to any one of the first aspect of the embodiments of the present application.
Drawings
FIG. 1 is a flow chart of a method for monitoring tablespace fragmentation according to one embodiment of the present application;
FIG. 2 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 3 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 4 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 5 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 6 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 7 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application;
FIG. 8 is a schematic diagram of a monitoring device for tablespace debris according to one embodiment of the application;
FIG. 9 is a schematic diagram of a monitoring device for tablespace debris according to another embodiment of the present application;
fig. 10 is a schematic structural diagram of an electronic device according to an embodiment of the present application.
Detailed Description
In order that the above-described aspects may be better understood, exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be embodied in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.
The following describes a method and a device for monitoring tablespace fragments and electronic equipment according to an embodiment of the application with reference to the accompanying drawings.
FIG. 1 is a flow chart of a method for monitoring tablespace fragmentation according to one embodiment of the present application.
As shown in fig. 1, the method for monitoring the tablespace fragments provided by the embodiment of the application specifically includes the following steps:
s101, acquiring a data directory under a database, traversing the data directory, and acquiring a target data directory of a non-system library directory from the data directory.
Wherein, the data directory includes: system directories and non-system directories.
It should be noted that, at least one system library is usually set by default in the database system. For example, taking the relational database management system mysql5.7 as an example, after the database system is initialized, the following system library may be automatically generated: mySQL, a system library information_schema capable of providing a database metadata access mode, a system library performance_schema for monitoring conditions such as resource consumption, resource waiting and the like of MySQL5.7 in a lower-level operation process, and a system library Sys for quickly knowing database system metadata Information.
However, because the data stored in the system library is usually less, the use frequency is low, and the data cannot be deleted, unnecessary time consumption is increased if the data directories of all the directories under the database are acquired in the process of monitoring the tablespace fragmentation. Therefore, according to the method for monitoring the table space fragments, only the target data catalogs of the non-system library catalogs are acquired.
S103, 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.
It should be noted that, in order to further shorten the time consumption in the table space fragment monitoring process, only the target table data file under the target data directory may be obtained after the target data directory is traversed. The target table data file may be a MySQL table data file, such as abcde, with ibd as a filename suffix, which meets a preset condition.
As a possible implementation manner, the operating system layer may loop through for loops to loop through all the subdirectories under the target data directory, so as to obtain all the table data files under the target data directory. Further, each time a table data file is traversed, the size of the table data file may be obtained, the size of the table data file may be compared with a first preset threshold value set in advance, and if the size of the table data file is greater than or equal to the first preset threshold value, the table data file greater than or equal to the first preset threshold value may be used as the target table data file in response to the size of the table data file being greater than or equal to the first preset threshold value.
The first preset threshold value can be set according to actual conditions. For example, 10G, 30G, 50G, or the like can be set.
For example, the subdirectory under the target data directory includes a MySQL table structured file with frm as the filename suffix and a MySQL table data file with ibd as the filename suffix. In this case, the operating system layer may loop through the for loop through the files under the target data directory that are frm and ibd as the filename suffix, and obtain all table data files that are ibd as the filename suffix. Further, the size of the table data file may be obtained, and in response to the size of the table data being greater than or equal to 50G, the table data file greater than or equal to 50G is taken as the target table data file.
S105, acquiring attribute information of the target table data file, and acquiring table information of the target table from a database instance of the database according to the attribute information.
In the embodiment of the application, after the target table data file is acquired, the attribute information of the target table data file can be acquired, and the table information of the target table is acquired from the database instance of the database according to the attribute information. Wherein the attribute information includes a directory name and a file name.
In the present application, the specific manner of obtaining the table information of the target table according to the attribute information is not limited, and may be selected according to the actual situation. Alternatively, the library name and the table name corresponding to the target table may be determined according to the directory name and the file name in the attribute information, respectively. Further, table information of the target table may be obtained based on the library name and the table name.
S107, determining whether the target table has table space fragments according to the table information.
In the embodiment of the application, after the table information of the target table is obtained, whether the target table has the table space fragments or not can be determined according to the table information.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
according to the method and the device, the data catalogue under the database can be obtained and traversed, the target data catalogue of the non-system library catalogue is obtained, then the target data catalogue is traversed, the target table data file under the target data catalogue is obtained, further the attribute information of the target table data file is obtained, the table information of the target table is obtained from the database instance of the database according to the attribute information, whether the table space fragments exist in the target table is determined according to the table information, so that the monitoring of the table space fragments is realized, the table space fragments are not directly obtained through the database system table any more, the problem that serious performance bottleneck of the database occurs under the condition that a large number of tables exist is avoided, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
In the case of attempting to acquire the attribute information of the target table data file, the directory name and the file name of the target table data file may be processed, respectively.
As a possible implementation manner, as shown in fig. 2, on the basis of the foregoing embodiment, the process of acquiring the attribute information of the target table data file in the foregoing step S105 specifically includes the following steps:
s201, determining the directory name corresponding to the target table data file based on the directory where the target table data file is located.
For example, if the directory where the target table data file is located is the test directory, in this case, it may be determined that the directory name corresponding to the target table data file is test.
S203, obtaining the file name of the target table data file, wherein the attribute information comprises a directory name and a file name.
For example, for the destination table data file emp.ibd, the file name of the destination table data file may be obtained as emp.ibd.
Further, after the attribute information of the target table is acquired, table information of the target table may be acquired from a database instance of the database according to the attribute information.
As a possible implementation manner, as shown in fig. 3, on the basis of the above embodiment, the process of acquiring 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:
s301, determining a library name and a table name corresponding to the target table according to the directory name and the file name.
As a possible implementation manner, as shown in fig. 4, on the basis of the foregoing embodiment, the process of determining, in the step S301, the library name and the table name corresponding to the target table according to the directory name and the file name specifically includes the following steps:
s401, directly determining the directory name as a corresponding library name of the target table.
For example, if the directory name corresponding to the target table data file is test, the library name corresponding to the target table may be determined as test.
S403, removing the suffix character of the file name, and taking the file name with the suffix character removed as a table name corresponding to the target table.
It should be noted that, after the operation of removing the suffix character of the file name is performed, the database example may be connected to obtain the corresponding table information.
For example, if the file name corresponding to the target table data file is emp.ibd, the suffix character of the file name, that is, ibd, may be removed, and the file name emp with the suffix character removed is taken as the table name corresponding to the target table.
In practical application, the visual library name of the database corresponds to the directory name of the operating system layer, and the visual table name of the database corresponds to the file of the operating system. For example, if the directory name is test and the file names are emp. Frm, emp. Ibd, then it may be displayed as test. Emp in MySQL.
S303, generating a structured query language SQL statement according to the library name and the table name, and executing a table information query operation on the database instance to acquire the table information of the target table.
As one possible implementation, a structured query language SQL statement may be generated from a library name and a table name. Further, the database instance may be connected by an SQL statement, and a query operation may be performed on the database instance to obtain table information of the target table.
For example, taking mysql5.7 as an example, the following SQL statement may be generated: SHOW TABLE STATUS LIKE (obtain table information) and the database can then obtain table information for the target table by executing SHOW TABLE STATUS the command.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
according to the method and the device, after the database name and the table name are acquired, the database instance can be connected, and the table information of the corresponding target table is acquired in a SHOW TABLE STATUS mode, so that the influence on the performance of the database can be further reduced while the corresponding requirement can be met, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
Further, after obtaining the table information of the target table, it may be determined whether the target table has a table space fragment according to the table information.
As a possible implementation manner, as shown in fig. 5, the method specifically includes the following steps on the basis of the above embodiment:
s501, determining the data size of the target table according to the table information.
In the embodiment of the application, after the table information of the target table is acquired, the size of the table data file can be acquired every time a target table data file is traversed.
S503, calculating the difference value between the data size of the data file of the target table and the data size of the target table.
It should be noted that, after the table is obtained as the size of the data file, the data size of the data file of the target table may be differenced from the data size of the target table to obtain the difference between the data size of the data file of the target table and the data size of the target table.
S505, if the difference value is larger than a preset value, determining that the target table has table space fragments.
In the embodiment of the application, after the difference value between the data size of the target table data file and the data size of the target table is obtained, the difference value can be compared with a preset value, and if the difference value is larger than the preset value, the table space fragments of the target table are determined; if the difference value is smaller than or equal to the preset value, determining that no table space fragments exist in the target table.
The preset value can be set according to actual conditions. For example, 10G, 30G, 50G, or the like can be set.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
according to the method and the device, the data size of the target table can be determined according to the table information, the difference value between the data size of the data file of the target table and the data size of the target table is calculated, if the difference value is larger than the preset value, the existence of the table space fragments in the target table is determined, the influence on the performance of the database can be further reduced while the corresponding requirements can be met, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
Further, after the tablespace fragment is obtained, tablespace fragment size information may be extracted from the field of SHOW TABLE STATUS LIKE and a matched reminder may be generated based on the tablespace fragment size.
As a possible implementation manner, as shown in fig. 6, on the basis of the foregoing embodiment, the method specifically includes the following steps:
s601, responding to the existence of the tablespace fragments of the target table, acquiring the sizes of the tablespace fragments, and judging whether the sizes of the tablespace fragments are larger than or equal to a second preset threshold value.
The second preset threshold may be set according to actual situations. For example, 10G, 30G, 50G, or the like can be set.
And S603, generating a fragment cleaning prompt and sending the fragment cleaning prompt to the terminal equipment in response to the size of the tablespace fragments being greater than or equal to a second preset threshold.
The cleaning prompt can be various prompts such as text or voice.
It should be noted that, in the present application, a specific manner of sending the debris cleaning reminder to the terminal device is not limited, and may be selected according to actual situations. Optionally, the debris cleaning reminder may be sent to the terminal device by means of mail, short message, weChat, etc.
S605, receiving a cleaning instruction of the terminal equipment, and cleaning the surface space fragments.
Optionally, the administrator may send a cleaning instruction based on a debris cleaning reminder displayed on the terminal device. Accordingly, after receiving the cleaning instruction of the terminal device, the surface space debris can be cleaned.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
according to the method and the device, the size of the tablespace fragments can be obtained, whether the size of the tablespace fragments is larger than or equal to the second preset threshold value or not is judged, then the fragment cleaning prompt is generated and sent to the terminal equipment in response to the fact that the size of the tablespace fragments is larger than or equal to the second preset threshold value, and then the cleaning instruction of the terminal equipment is received, so that the tablespace fragments are cleaned, an administrator is reminded of timely cleaning the tablespace fragments through automatic service discovery of the table with more fragments, the disk utilization rate is optimized, and the continuity of the service is improved while the performance of a database is not influenced.
Fig. 7 is a flow chart of a method for monitoring tablespace fragmentation according to another embodiment of the present application, as shown in fig. 7, specifically including the following steps:
s701, acquiring a data directory under a database.
S703, traversing the data directory, and judging whether the data directory is a system library directory.
Optionally, if the data directory is a system library directory, ignoring the data directory; if the data directory is not a system library directory, step S705 may be performed.
S705, the data directory is set as the target data directory.
S707, circulating all subdirectories under the target data directory to obtain all table data files under the target data directory.
S709, acquiring the size of the table data file, and judging whether the size of the table data file is larger than or equal to a first preset threshold.
Alternatively, if the size of the table data file is greater than or equal to the first preset threshold, step S711 may be performed; if the size of the table data file is smaller than the first preset threshold, the table data file may be ignored.
S711, taking the table data file which is larger than or equal to the first preset threshold value as a target table data file.
S713, determining the directory name corresponding to the target table data file based on the directory where the target table data file is located.
S715, obtaining the file name of the target table data file, wherein the attribute information comprises a directory name and a file name.
S717, directly determining the directory name to the corresponding library name of the target table.
S719, removing the suffix character of the file name, and taking the file name with the suffix character removed as a table name corresponding to the target table.
S721, generating a structured query language SQL statement according to the library name and the table name, and executing a table information query operation on the database instance to acquire the table information of the target table.
S723, determining the data size of the target table according to the table information.
S725, calculating the difference between the data size of the data file of the target table and the data size of the target table.
S727, if the difference value is larger than the preset value, determining that the target table has table space fragments.
Alternatively, if the difference is greater than the preset value, step S729 may be performed.
S729, responding to the existence of the tablespace fragments of the target table, acquiring the sizes of the tablespace fragments, and judging whether the sizes of the tablespace fragments are larger than or equal to a second preset threshold.
Alternatively, if the size of the tablespace fragment is greater than or equal to the second preset threshold, step S731 may be performed.
S731, generating a debris cleaning prompt and sending the debris cleaning prompt to the terminal equipment.
S733, receiving a cleaning instruction of the terminal equipment, and cleaning the surface space fragments.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
1. according to the method and the device, the data catalogue under the database can be obtained and traversed, the target data catalogue of the non-system library catalogue is obtained, then the target data catalogue is traversed, the target table data file under the target data catalogue is obtained, further the attribute information of the target table data file is obtained, the table information of the target table is obtained from the database instance of the database according to the attribute information, whether the table space fragments exist in the target table is determined according to the table information, so that the monitoring of the table space fragments is realized, the table space fragments are not directly obtained through the database system table any more, the problem that serious performance bottleneck of the database occurs under the condition that a large number of tables exist is avoided, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
2. According to the method and the device, after the database name and the table name are acquired, the database instance can be connected, and the table information of the corresponding target table is acquired in a SHOW TABLE STATUS mode, so that the influence on the performance of the database can be further reduced while the corresponding requirement can be met, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
3. According to the method and the device, the size of the tablespace fragments can be obtained, whether the size of the tablespace fragments is larger than or equal to the second preset threshold value or not is judged, then the fragment cleaning prompt is generated and sent to the terminal equipment in response to the fact that the size of the tablespace fragments is larger than or equal to the second preset threshold value, and then the cleaning instruction of the terminal equipment is received, so that the tablespace fragments are cleaned, an administrator is reminded of timely cleaning the tablespace fragments through automatic service discovery of the table with more fragments, the disk utilization rate is optimized, and the continuity of the service is improved while the performance of a database is not influenced.
Based on the same application conception, the embodiment of the application also provides a monitoring device for the tablespace debris.
Fig. 8 to 9 are schematic structural diagrams of a monitoring device for space debris in a watch according to an embodiment of the present application. As shown in fig. 8, the monitoring apparatus 100 for the tablespace debris includes: a first acquisition module 11, a second acquisition module 13, a third acquisition module 15 and a determination module 17.
The first obtaining module 11 is configured to obtain a data directory under the database, and traverse the data directory to obtain a target data directory of a non-system library directory; a second obtaining module 13, configured to traverse the target data directory to obtain a target table data file under the target data directory, where the target table data file stores data contents of a target table; a third obtaining module 15, configured to obtain attribute information of the target table data file, and obtain table information of the target table from a database instance of the database according to the attribute information; a determining module 17, configured to determine whether the target table has a tablespace fragment according to the table information.
According to one embodiment of the application, the second acquisition module 13 is further configured to: and each time traversing to one table data file, acquiring the size of the table data file, and responding to the fact that the size of the table data file is larger than or equal to a first preset threshold value, taking the table data file with the size larger than or equal to the first preset threshold value as the target table data file.
According to one embodiment of the application, the third acquisition module 15 is further configured to: determining a directory name corresponding to the target table data file based on the directory where the target table data file is located; and acquiring the file name of the target table data file, wherein the attribute information comprises the directory name and the file name.
According to one embodiment of the application, the third acquisition module 15 is further configured to: according to the directory names and the file names, determining the corresponding library names and table names of the target table; and generating a Structured Query Language (SQL) statement according to the library name and the table name, and executing table information query operation on the database instance to acquire the table information of the target table.
According to one embodiment of the application, the third acquisition module 15 is further configured to: directly determining the directory name as a library name corresponding to the target table; and removing the suffix character of the file name, and taking the file name with the suffix character removed as the table name corresponding to the target table.
According to one embodiment of the application, the determining module 17 is further configured to: determining whether the target table has table space fragments according to the table information of the target table comprises the following steps: determining 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; and if the difference value is larger than a preset value, determining that the target table has table space fragments.
According to an embodiment of the present application, as shown in fig. 9, the monitoring apparatus 100 for tablespace debris provided by the present application further includes a cleaning module 19, configured to: responding to the existence of the table space fragments of the target table, acquiring the sizes of the table space fragments, and judging whether the sizes of the table space fragments are larger than or equal to a second preset threshold value; generating a fragment cleaning prompt and sending the fragment cleaning prompt to a terminal device in response to the size of the tablespace fragments being greater than or equal to the second preset threshold; and receiving a cleaning instruction of the terminal equipment, and cleaning the table space fragments.
The technical scheme provided by the embodiment of the application at least has the following technical effects or advantages:
1. according to the method and the device, the data catalogue under the database can be obtained and traversed, the target data catalogue of the non-system library catalogue is obtained, then the target data catalogue is traversed, the target table data file under the target data catalogue is obtained, further the attribute information of the target table data file is obtained, the table information of the target table is obtained from the database instance of the database according to the attribute information, whether the table space fragments exist in the target table is determined according to the table information, so that the monitoring of the table space fragments is realized, the table space fragments are not directly obtained through the database system table any more, the problem that serious performance bottleneck of the database occurs under the condition that a large number of tables exist is avoided, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
2. According to the method and the device, after the database name and the table name are acquired, the database instance can be connected, and the table information of the corresponding target table is acquired in a SHOW TABLE STATUS mode, so that the influence on the performance of the database can be further reduced while the corresponding requirement can be met, and the efficiency and the reliability in the monitoring process of the table space fragments are improved.
3. According to the method and the device, the size of the tablespace fragments can be obtained, whether the size of the tablespace fragments is larger than or equal to the second preset threshold value or not is judged, then, in response to the fact that the size of the tablespace fragments is larger than or equal to the second preset threshold value, a tablespace fragment cleaning prompt is generated and sent to the terminal equipment, and further, a cleaning instruction of the terminal equipment is received, the tablespace fragments are cleaned, so that an administrator is reminded of timely cleaning the tablespace fragments through an automatic service discovery table with more fragments, the disk utilization rate is optimized, and service continuity is improved while the performance of a database is not affected.
Based on the same application conception, the embodiment of the application also provides electronic equipment.
Fig. 10 is a schematic structural diagram of an electronic device according to an embodiment of the present application. As shown in fig. 10, 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, and when the processor executes the program, the foregoing method for monitoring the tablespace fragmentation is implemented.
It will be appreciated by those skilled in the art that embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems) and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
It should be noted that in the claims, any reference signs placed between parentheses shall not be construed as limiting the claim. The word "comprising" does not exclude the presence of elements or steps not listed in a claim. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. The application may be implemented by means of hardware comprising several distinct elements, and by means of a suitably programmed computer. In the unit claims 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, third, etc. do not denote any order. These words may be interpreted as names.
While preferred embodiments of the present application have been described, additional variations and modifications in those embodiments may occur to those skilled in the art once they learn of the basic inventive concepts. It is therefore intended that the following claims be interpreted as including the preferred embodiments and all such alterations and modifications as fall within the scope of the application.
It will be apparent to those skilled in the art that various modifications and variations can be made to the present application without departing from the spirit or scope of the application. Thus, it is intended that the present application also include such modifications and alterations insofar as they come within the scope of the appended claims or the equivalents thereof.
Claims (10)
1. A method of monitoring tablespace fragmentation, comprising:
acquiring a data directory under a database, traversing the data directory, and acquiring a target data directory of a non-system library directory from the data 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 a target table;
acquiring attribute information of the target table data file, and acquiring table information of the target table from a database instance of the database according to the attribute information;
and determining whether the target table has table space fragments according to the table information.
2. The method of claim 1, wherein traversing the target data directory to obtain the target table data file under the target data directory comprises:
and each time traversing to one table data file, acquiring the size of the table data file, and responding to the fact that the size of the table data file is larger than or equal to a first preset threshold value, taking the table data file with the size larger than or equal to the first preset threshold value as the target table data file.
3. The method according to claim 1, wherein the obtaining attribute information of the target table data file includes:
determining a directory name corresponding to the target table data file based on the directory where the target table data file is located;
and acquiring the file name of the target table data file, wherein the attribute information comprises the directory name and the file name.
4. A monitoring method according to claim 3, wherein the obtaining table information of the target table from the database instance of the database according to the attribute information includes:
according to the directory names and the file names, determining the corresponding library names and table names of the target table;
and generating a Structured Query Language (SQL) statement according to the library name and the table name, and executing table information query operation on the database instance to acquire the table information of the target table.
5. The method according to claim 4, wherein determining the library name and the table name corresponding to the target table according to the directory name and the file name comprises:
directly determining the directory name as a library name corresponding to the target table;
and removing the suffix character of the file name, and taking the file name with the suffix character removed as the table name corresponding to the target table.
6. The method according to claim 1, wherein determining whether there is a tablespace fragment in the target table based on the table information of the target table, comprises:
determining 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;
and if the difference value is larger than a preset value, determining that the target table has table space fragments.
7. The monitoring method according to any one of claims 1 to 6, further comprising:
responding to the existence of the table space fragments of the target table, acquiring the sizes of the table space fragments, and judging whether the sizes of the table space fragments are larger than or equal to a second preset threshold value;
generating a fragment cleaning prompt and sending the fragment cleaning prompt to a terminal device in response to the size of the tablespace fragments being greater than or equal to the second preset threshold;
and receiving a cleaning instruction of the terminal equipment, and cleaning the table space fragments.
8. A monitoring device for tablespace debris, characterized by implementing a method for monitoring tablespace debris according to any one of claims 1-7.
9. An electronic device, comprising: memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method of monitoring tablespace fragmentation according to any one of claims 1-7 when the program is executed.
10. A computer readable storage medium, on which a computer program is stored, characterized in that the program, when being executed by a processor, implements a method of monitoring tablespace fragmentation according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210178848.8A CN116701319A (en) | 2022-02-25 | 2022-02-25 | Watch space fragment monitoring method and device and electronic equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210178848.8A CN116701319A (en) | 2022-02-25 | 2022-02-25 | Watch space fragment monitoring method and device and electronic equipment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116701319A true CN116701319A (en) | 2023-09-05 |
Family
ID=87824433
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210178848.8A Pending CN116701319A (en) | 2022-02-25 | 2022-02-25 | Watch space fragment monitoring method and device and electronic equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116701319A (en) |
-
2022
- 2022-02-25 CN CN202210178848.8A patent/CN116701319A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10783115B2 (en) | Dividing a dataset into sub-datasets having a subset of values of an attribute of the dataset | |
| CN108694195B (en) | Management method and system of distributed data warehouse | |
| US8161015B2 (en) | Method and system for deferred maintenance of database indexes | |
| CN107608860B (en) | Method, device and equipment for classified storage of error logs | |
| CN106126731B (en) | Method and device for acquiring Elasticissearch paging data | |
| JP6870466B2 (en) | Control programs, control methods, controls, and database servers | |
| CN105573859A (en) | Data recovery method and device of database | |
| US20080307013A1 (en) | Updating an inverted index | |
| CN110580255A (en) | method and system for storing and retrieving data | |
| US20150242284A1 (en) | Two-algorithm sort during backup and recovery | |
| CN104462349A (en) | File processing method and file processing device | |
| US9098603B2 (en) | Index partitioning and scope checking | |
| CN109032940B (en) | Test scene input method, device, equipment and storage medium | |
| CN117112522A (en) | Concurrent process log management method, device, equipment and storage medium | |
| CN108121514B (en) | Meta information updating method and device, computing equipment and computer storage medium | |
| CN114077690A (en) | Vector data processing method, device, equipment and storage medium | |
| CN116701319A (en) | Watch space fragment monitoring method and device and electronic equipment | |
| CN114579368B (en) | Backup management method for continuous data protection, computer equipment and storage medium | |
| US8818990B2 (en) | Method, apparatus and computer program for retrieving data | |
| CN113886342A (en) | File format conversion method and device, storage medium and processor | |
| CN115269519A (en) | Log detection method and device and electronic equipment | |
| US20100228771A1 (en) | Query result iteration | |
| CN113946543A (en) | Data archiving method, device, equipment and storage medium based on artificial intelligence | |
| US20150039598A1 (en) | Data analysis control | |
| CN112835967A (en) | Data processing method, device, equipment and medium based on distributed storage system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |