CN115129680B - Method, device, equipment and readable medium for improving database performance - Google Patents
Method, device, equipment and readable medium for improving database performance Download PDFInfo
- Publication number
- CN115129680B CN115129680B CN202210748332.2A CN202210748332A CN115129680B CN 115129680 B CN115129680 B CN 115129680B CN 202210748332 A CN202210748332 A CN 202210748332A CN 115129680 B CN115129680 B CN 115129680B
- Authority
- CN
- China
- Prior art keywords
- log
- group
- state
- file
- log group
- 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.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 44
- 230000004044 response Effects 0.000 claims abstract description 38
- 238000004590 computer program Methods 0.000 claims description 11
- 238000004364 calculation method Methods 0.000 claims description 3
- 230000009467 reduction Effects 0.000 claims description 3
- 230000003416 augmentation Effects 0.000 claims description 2
- 230000007423 decrease Effects 0.000 abstract description 3
- 230000006870 function Effects 0.000 description 6
- 238000010586 diagram Methods 0.000 description 5
- 230000003287 optical effect Effects 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 230000009471 action Effects 0.000 description 2
- 230000008901 benefit Effects 0.000 description 2
- 230000015556 catabolic process Effects 0.000 description 2
- 230000008859 change Effects 0.000 description 2
- 238000006731 degradation reaction Methods 0.000 description 2
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 239000000835 fiber Substances 0.000 description 2
- 230000007246 mechanism Effects 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 238000011084 recovery Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004422 calculation algorithm Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 238000012546 transfer Methods 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/18—File system types
- G06F16/1805—Append-only file systems, e.g. using logs or journals to store data
- G06F16/1815—Journaling file systems
-
- 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/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method, a device, equipment and a readable medium for improving database performance, wherein the method comprises the following steps: responding to the full writing of the log files in one log group of the ORACLE database to switch the log groups, and judging the state of the next log group; responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion; responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion; and deleting the log group with one state being the inactive state in the ORACLE database in response to the state of the next log group being the inactive state three times in succession. By using the scheme of the invention, the decline of the database performance caused by REDO switching waiting can be avoided, the workload caused by REDO adjustment can be reduced, and the problem of the database performance caused by REDO waiting can be solved without human intervention.
Description
Technical Field
The present invention relates to the field of computers, and more particularly, to a method, apparatus, device, and readable medium for improving database performance.
Background
The log files of the ORACLE database are recycled, so at least two log groups are needed, and when one log file group is full, the log files are switched to the other log files. Log switch triggers two actions of the database: 1. triggering a checkpoint (checkpoint) event, informing the dbwr process to write data in the full LOG file into the data file, wherein the LOG file cannot be overwritten (because the data in the LOG file needs to be written into the data file, the overwriting means that the data is lost), that is, the database cannot write to a new REDO file at the moment, so that the database cannot write the data in the REDO LOG BUFFER into the REDO, and the database temporarily stops changing operation, and cannot provide normal use (and is in a suspended state); 2. triggering the generation of an archive file, after the database opens the archive mode, the LOG file which is already written up is copied by the database and the archive file is generated, and before the generation of the archive file is completed, the LOG file cannot be used in an overwriting manner, and the database cannot write to a new REDO file, so that the database cannot write data in the REDO LOG BUFFER into the REDO, and the database temporarily stops changing operation, so that the database cannot provide normal use (can be in a suspended state).
When log files are frequently switched, the problem that the log files are in an ACTIVE state and cannot be switched occurs, the database is in a suspended state and waits for a checkpoint or archiving to be completed, the problem greatly affects the performance of the database, and service interruption is seriously caused. When this occurs, the database manager typically handles this by increasing the log size and increasing the log group. The log file stores data which is not written into the data file, and the larger the log file is, the more data is lost when the log file is damaged. The smaller the log file is, the better the smaller the log file is, which means that the more frequent the log switch is, the frequent log switch has a non-trivial impact on system performance during peak traffic. The different time periods of the database traffic can be different, larger log files are needed when the traffic is large, smaller log files are needed when the traffic is idle, and the fixed log size and log group in the system are difficult to meet the conditions under different traffic, so that the log switching basically affects the performance of all databases.
Disclosure of Invention
In view of the above, an object of the embodiments of the present invention is to provide a method, an apparatus, a device and a readable medium for improving database performance, which can avoid the decrease of database performance caused by REDO switching waiting, reduce workload caused by REDO adjustment, and solve the problem of database performance caused by REDO waiting without human intervention.
Based on the above objects, an aspect of an embodiment of the present invention provides a method for improving database performance, including the steps of:
responding to the full writing of a log file in one log group of an ORACLE database (a large-scale relational data) to switch the log groups, and judging the state of the next log group;
responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion;
responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion;
and deleting the log group with one state being the inactive state in the ORACLE database in response to the state of the next log group being the inactive state three times in succession.
According to one embodiment of the present invention, in response to the state of the next log group being an active state, automatically adding one log group to the ORACLE database and increasing the size of the log file in the added log group by a first preset ratio comprises:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
According to one embodiment of the invention, the log file is set to not more than 1000 megabytes.
According to one embodiment of the present invention, in response to the state of the next log group being an inactive state, reducing the size of the log file in the next log group by a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
According to one embodiment of the invention, the scaled-down log file is no less than 50 megabytes.
According to one embodiment of the present invention, further comprising:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
According to one embodiment of the present invention, further comprising:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
In another aspect of the embodiment of the present invention, there is also provided an apparatus for improving database performance, including:
the judging module is configured to respond to the full writing of the log files in one log group of the ORACLE database to switch the log groups and judge the state of the next log group;
the adding module is configured to automatically add the log groups in the ORACLE database and increase the sizes of the log files in the added log groups according to a first preset proportion in response to the state of the next log group being an active state;
the reducing module is configured to respond to the state of the next log group as an inactive state, and reduce the size of the log file in the next log group according to a second preset proportion;
and the deleting module is configured to respond to the condition that the next log group is in an inactive state three times in succession, and delete the log group in the ORACLE database, one of which is in the inactive state.
According to one embodiment of the invention, the add-on module is further configured to:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
According to one embodiment of the invention, the log file is set to not more than 1000 megabytes.
According to one embodiment of the invention, the reduction module is further configured to:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
According to one embodiment of the invention, the method further comprises a calculation module configured to:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
According to one embodiment of the invention, the computing module is further configured to:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
In another aspect of the embodiments of the present invention, there is also provided a computer apparatus including:
at least one processor; and
and a memory storing computer instructions executable on the processor, the instructions when executed by the processor performing the steps of any of the methods described above.
In another aspect of the embodiments of the present invention, there is also provided a computer-readable storage medium storing a computer program which, when executed by a processor, implements the steps of any of the methods described above.
The invention has the following beneficial technical effects: according to the method for improving the performance of the database, provided by the embodiment of the invention, the state of the next log group is judged by switching the log groups in response to the full writing of the log files in one log group of the ORACLE database; responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion; responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion; in response to the technical scheme that the state of the next log group is the inactive state three times continuously, deleting the log group with one state being the inactive state in the ORACLE database can avoid the decline of the database performance caused by REDO switching waiting, reduce the workload caused by REDO adjustment and solve the problem of the database performance caused by REDO waiting under the condition of no need of human intervention.
Drawings
In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are necessary for the description of the embodiments or the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention and that other embodiments may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic flow chart of a method of improving database performance according to one embodiment of the invention;
FIG. 2 is a schematic diagram of an apparatus for improving database performance according to one embodiment of the invention;
FIG. 3 is a schematic diagram of a computer device according to one embodiment of the invention;
fig. 4 is a schematic diagram of a computer-readable storage medium according to one embodiment of the invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the following embodiments of the present invention will be described in further detail with reference to the accompanying drawings.
With the above object in view, in a first aspect, an embodiment of a method for improving database performance is provided. Fig. 1 shows a schematic flow chart of the method.
As shown in fig. 1, the method may include the steps of:
s1, responding to the full writing of the log files in one log group of the ORACLE database to switch the log groups, and judging the state of the next log group.
The REDO file (Log file) is used for recording the change to the database, the data in the Redo Log Buffer is sequentially written into the REDO file, and a REDO group (Log group) is provided for the REDO file in the database, and each REDO group contains one or two REDO files. The REDO group contains a plurality of REDO files which are written in parallel and whose data are identical, i.e. mirror images of each other. The REDO mechanism is a crucial means for guaranteeing data security and fault recovery, and is also a key factor greatly influencing the performance of a database, and generally at least two REDO groups in the database are written circularly, namely after the REDO file in one REDO group is fully written, the data is written into the REDO file in the next REDO group. The REDO file is fixed in size, and when one REDO group is full, it is switched to another REDO log group for writing, and this switch is REDO switch. REDO has three states, namely current (currently writing), active (outstanding or archive) and inactive (outstanding and archive). When switching to the next log group after the log file is full, the state of the next log group needs to be checked.
S2, responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of the log file in the added log group according to a first preset proportion.
If the next log group is in active state (i.e. no active state log group is available), the size of the full log file is obtained, then one log group is added in the ORACLE database, and the log file is set in the added log group, wherein the set log file is 10% larger than the full log file, and the maximum log file cannot exceed 1000M.
And S3, responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion.
If the state of the next log group is in the inactive state, the size of the full log file is obtained, and then the log files in the next log group are reduced, wherein the log files in the next log group are smaller than the full log files by 20%, and the minimum log files are not smaller than 50M.
S4, in response to the state of the next log group being an inactive state three times in succession, deleting the log group with one state being the inactive state in the ORACLE database. But a minimum of 2 log groups need to be maintained, and if the number of log groups in the current database is 2, no deletion operation is performed. By the method, available log files are not waited when the logs are switched, the log size and the log group are kept relatively small and at a low level, manual intervention is not needed in the whole process, the database performance is improved, and the workload of personnel is reduced.
The log files are written sequentially, and the contents in the log files are stored continuously on the hard disk, so that the log files with corresponding sizes do not need to be actually generated on the hard disk when the REDO files are created, and more I/O is consumed and time is also spent if the log files are actually generated. When the log file is created, only a file header is created, and the address range of the continuous storage space of the log file on the hard disk and the position of the file tail are recorded in the file header. When deleting the log file, the log file is marked as a deleted state in the file system, and the log file cannot be cleaned and deleted on the hard disk actually, so that I/O consumption is avoided. When the existing log file is increased, the newly added part is not actually generated on the hard disk, and the new continuous storage space address range and the file tail position are recorded in the log file head only when writing. When an existing log file is reduced, the reduced portion is not actually deleted on the hard disk, but the contiguous memory space address range and tail position recorded in the file header are modified.
By the technical scheme, the method and the device can avoid the performance degradation of the database caused by REDO switching waiting, reduce the workload caused by REDO adjustment, and solve the problem of the performance of the database caused by REDO waiting without human intervention.
In a preferred embodiment of the present invention, automatically adding a log group to the ORACLE database and increasing the size of the log file in the added log group by a first preset ratio in response to the state of the next log group being an active state comprises:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
In a preferred embodiment of the invention, the log file is set to not more than 1000 megabytes.
In a preferred embodiment of the present invention, in response to the state of the next log group being an inactive state, reducing the size of the log file in the next log group by a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabytes.
In a preferred embodiment of the present invention, further comprising:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
In a preferred embodiment of the present invention, further comprising:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value. The above embodiment is a method of pre-adding and pre-deleting log groups in the data peak period and the low peak period, and acquires the redox size (bytes) information from the AWR automatic load information base every half an hour, wherein the field is a bytes byte unit, and then inserts the byte unit into the following table 1:
TABLE 1
6 per day: 00 and 18:00 are averaged per second over two time periods of 6:00-18:00 and 18:00-6:00 of the next day, respectively, for the last seven days in the table above. After the average value is obtained, the average value of 6:00-18:00 is compared with the data of the time period of 6:00-18:00 in the table, when the continuous two times of numerical values are larger than the average value, the time period is indicated to enter a service peak period, the REDO group and the REDO size in the current database are checked, the REDO group quantity and the REDO size in the peak period for numerical comparison are compared, if the current REDO group quantity in the database is smaller than the REDO group quantity in the peak period, the REDO group is added until the REDO group quantity in the peak period is equal, and the REDO size is the REDO size recorded in the table in the peak period. Comparing the average value of 18:00-next day 6:00 with the data of the time period of 18:00-next day 6:00 in the table, when the continuous two times of values are smaller than the average value, indicating that the time period has entered a service low peak period, checking the REDO group and the REDO size in the current database, comparing the REDO group number and the REDO size of the low peak period compared with the values, and if the current REDO group number in the database is higher than the REDO group number at the low peak period, deleting the REDO group in the inactive state until the log group is equal to the REDO group number at the low peak period, wherein the REDO size is the REDO size recorded in the table at the low peak period.
The technical proposal of the invention avoids the performance degradation of the database caused by REDO switching waiting by automatically increasing the REDO group and the REDO size in the service peak period and automatically reducing the REDO group and the REDO size in the service low peak period. When a database engineer processes the performance influence caused by REDO switching to the database, the number and the size of the required REDO groups are difficult to judge accurately, and the number and the size of the REDO groups cannot be changed in real time according to different traffic.
It should be noted that, it will be understood by those skilled in the art that all or part of the procedures in implementing the methods of the above embodiments may be implemented by a computer program to instruct related hardware, and the above program may be stored in a computer readable storage medium, and the program may include the procedures of the embodiments of the above methods when executed. Wherein the storage medium may be a magnetic disk, an optical disk, a Read-Only Memory (ROM), a random access Memory (Random Access Memory, RAM), or the like. The computer program embodiments described above may achieve the same or similar effects as any of the method embodiments described above.
Furthermore, the method disclosed according to the embodiment of the present invention may also be implemented as a computer program executed by a CPU, which may be stored in a computer-readable storage medium. When executed by a CPU, performs the functions defined above in the methods disclosed in the embodiments of the present invention.
With the above object in mind, in a second aspect of the embodiments of the present invention, there is provided an apparatus for improving database performance, as shown in fig. 2, an apparatus 200 includes:
the judging module is configured to respond to the full writing of the log files in one log group of the ORACLE database to switch the log groups and judge the state of the next log group. The REDO file (Log file) is used for recording the change to the database, the data in the Redo Log Buffer is sequentially written into the REDO file, and a REDO group (Log group) is provided for the REDO file in the database, and each REDO group contains one or two REDO files. The REDO group contains a plurality of REDO files which are written in parallel and whose data are identical, i.e. mirror images of each other. The REDO mechanism is a crucial means for guaranteeing data security and fault recovery, and is also a key factor greatly influencing the performance of a database, and generally at least two REDO groups in the database are written circularly, namely after the REDO file in one REDO group is fully written, the data is written into the REDO file in the next REDO group. The REDO file is fixed in size, and when one REDO group is full, it is switched to another REDO log group for writing, and this switch is REDO switch. REDO has three states, namely current (currently writing), active (outstanding or archive) and inactive (outstanding and archive). When switching to the next log group after the log file is full, the state of the next log group needs to be checked.
The system further comprises an adding module, wherein the adding module is configured to automatically add the log groups in the ORACLE database and increase the sizes of the log files in the added one log group according to a first preset proportion in response to the state of the next log group being an active state. If the next log group is in active state (i.e. no active state log group is available), the size of the full log file is obtained, then one log group is added in the ORACLE database, and the log file is set in the added log group, wherein the set log file is 10% larger than the full log file, and the maximum log file cannot exceed 1000M.
The system further comprises a reducing module configured to reduce the size of the log file in the next log group according to a second preset proportion in response to the state of the next log group being an inactive state. If the state of the next log group is in the inactive state, the size of the full log file is obtained, and then the log files in the next log group are reduced, wherein the log files in the next log group are smaller than the full log files by 20%, and the minimum log files are not smaller than 50M.
The system further comprises a deleting module, wherein the deleting module is configured to respond to the state of the next log group to be the inactive state three times in succession, and delete the log group with one state being the inactive state in the ORACLE database. But a minimum of 2 log groups need to be maintained, and if the number of log groups in the current database is 2, no deletion operation is performed. By the method, available log files are not waited when the logs are switched, the log size and the log group are kept relatively small and at a low level, manual intervention is not needed in the whole process, the database performance is improved, and the workload of personnel is reduced. The log files are written sequentially, and the contents in the log files are stored continuously on the hard disk, so that the log files with corresponding sizes do not need to be actually generated on the hard disk when the REDO files are created, and more I/O is consumed and time is also spent if the log files are actually generated. When the log file is created, only a file header is created, and the address range of the continuous storage space of the log file on the hard disk and the position of the file tail are recorded in the file header. When deleting the log file, the log file is marked as a deleted state in the file system, and the log file cannot be cleaned and deleted on the hard disk actually, so that I/O consumption is avoided. When the existing log file is increased, the newly added part is not actually generated on the hard disk, and the new continuous storage space address range and the file tail position are recorded in the log file head only when writing. When an existing log file is reduced, the reduced portion is not actually deleted on the hard disk, but the contiguous memory space address range and tail position recorded in the file header are modified.
In a preferred embodiment of the invention, the augmentation module is further configured to:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
In a preferred embodiment of the invention, the log file is set to not more than 1000 megabytes.
In a preferred embodiment of the invention, the reduction module is further configured to:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
In a preferred embodiment of the present invention, further comprising a calculation module configured to:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
In a preferred embodiment of the invention, the computing module is further configured to:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
Based on the above object, a third aspect of the embodiments of the present invention proposes a computer device. Fig. 3 is a schematic diagram of an embodiment of a computer device provided by the present invention. As shown in fig. 3, an embodiment of the present invention includes the following means: at least one processor 21; and a memory 22, the memory 22 storing computer instructions 23 executable on the processor, the instructions when executed by the processor performing the method of:
responding to the full writing of the log files in one log group of the ORACLE database to switch the log groups, and judging the state of the next log group;
responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion;
responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion;
and deleting the log group with one state being the inactive state in the ORACLE database in response to the state of the next log group being the inactive state three times in succession.
In a preferred embodiment of the present invention, automatically adding a log group to the ORACLE database and increasing the size of the log file in the added log group by a first preset ratio in response to the state of the next log group being an active state comprises:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
In a preferred embodiment of the invention, the log file is set to not more than 1000 megabytes.
In a preferred embodiment of the present invention, in response to the state of the next log group being an inactive state, reducing the size of the log file in the next log group by a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabytes.
In a preferred embodiment of the present invention, further comprising:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
In a preferred embodiment of the present invention, further comprising:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
Based on the above object, a fourth aspect of the embodiments of the present invention proposes a computer-readable storage medium. Fig. 4 is a schematic diagram of an embodiment of a computer-readable storage medium provided by the present invention. As shown in fig. 4, the computer-readable storage medium 31 stores a computer program 32 that, when executed by a processor, performs the following method:
responding to the full writing of the log files in one log group of the ORACLE database to switch the log groups, and judging the state of the next log group;
responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion;
responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion;
and deleting the log group with one state being the inactive state in the ORACLE database in response to the state of the next log group being the inactive state three times in succession.
In a preferred embodiment of the present invention, automatically adding a log group to the ORACLE database and increasing the size of the log file in the added log group by a first preset ratio in response to the state of the next log group being an active state comprises:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
In a preferred embodiment of the invention, the log file is set to not more than 1000 megabytes.
In a preferred embodiment of the present invention, in response to the state of the next log group being an inactive state, reducing the size of the log file in the next log group by a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabytes.
In a preferred embodiment of the present invention, further comprising:
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
In a preferred embodiment of the present invention, further comprising:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
Furthermore, the method disclosed according to the embodiment of the present invention may also be implemented as a computer program executed by a processor, which may be stored in a computer-readable storage medium. The above-described functions defined in the methods disclosed in the embodiments of the present invention are performed when the computer program is executed by a processor.
Furthermore, the above-described method steps and system units may also be implemented using a controller and a computer-readable storage medium storing a computer program for causing the controller to implement the above-described steps or unit functions.
Those of skill would further appreciate that the various illustrative logical blocks, modules, circuits, and algorithm steps described in connection with the disclosure herein may be implemented as electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software, various illustrative components, blocks, modules, circuits, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as software or hardware depends upon the particular application and design constraints imposed on the overall system. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present disclosure.
In one or more exemplary designs, the functions may be implemented in hardware, software, firmware, or any combination thereof. If implemented in software, the functions may be stored on or transmitted over as one or more instructions or code on a computer-readable medium. Computer-readable media includes both computer storage media and communication media including any medium that facilitates transfer of a computer program from one location to another. A storage media may be any available media that can be accessed by a general purpose or special purpose computer. By way of example, and not limitation, such computer-readable media can comprise RAM, ROM, EEPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a general purpose or special purpose computer or general purpose or special purpose processor. Further, any connection is properly termed a computer-readable medium. For example, if the software is transmitted from a website, server, or other remote source using a coaxial cable, fiber optic cable, twisted pair, digital Subscriber Line (DSL), or wireless technologies such as infrared, radio, and microwave, then the coaxial cable, fiber optic cable, twisted pair, DSL, or wireless technologies such as infrared, radio, and microwave are included in the definition of medium. Disk and disc, as used herein, includes Compact Disc (CD), laser disc, optical disc, digital Versatile Disc (DVD), floppy disk, blu-ray disc where disks usually reproduce data magnetically, while discs reproduce data optically with lasers. Combinations of the above should also be included within the scope of computer-readable media.
The foregoing is an exemplary embodiment of the present disclosure, but it should be noted that various changes and modifications could be made herein without departing from the scope of the disclosure as defined by the appended claims. The functions, steps and/or actions of the method claims in accordance with the disclosed embodiments described herein need not be performed in any particular order. Furthermore, although elements of the disclosed embodiments may be described or claimed in the singular, the plural is contemplated unless limitation to the singular is explicitly stated.
It should be understood that as used herein, the singular forms "a", "an", and "the" are intended to include the plural forms as well, unless the context clearly supports the exception. It should also be understood that "and/or" as used herein is meant to include any and all possible combinations of one or more of the associated listed items.
The foregoing embodiment of the present invention has been disclosed with reference to the number of embodiments for the purpose of description only, and does not represent the advantages or disadvantages of the embodiments.
It will be understood by those skilled in the art that all or part of the steps for implementing the above embodiments may be implemented by hardware, or may be implemented by a program for instructing relevant hardware, and the program may be stored in a computer readable storage medium, where the storage medium may be a read-only memory, a magnetic disk or an optical disk, etc.
Those of ordinary skill in the art will appreciate that: the above discussion of any embodiment is merely exemplary and is not intended to imply that the scope of the disclosure of embodiments of the invention, including the claims, is limited to such examples; combinations of features of the above embodiments or in different embodiments are also possible within the idea of an embodiment of the invention, and many other variations of the different aspects of the embodiments of the invention as described above exist, which are not provided in detail for the sake of brevity. Therefore, any omission, modification, equivalent replacement, improvement, etc. of the embodiments should be included in the protection scope of the embodiments of the present invention.
Claims (13)
1. A method for improving database performance, comprising the steps of:
responding to the full writing of the log files in one log group of the ORACLE database to switch the log groups, and judging the state of the next log group;
responding to the state of the next log group as an active state, automatically adding the log group in an ORACLE database and adding the size of a log file in one added log group according to a first preset proportion;
responding to the state of the next log group as an inactive state, and reducing the size of the log file in the next log group according to a second preset proportion;
in response to the state of the next log group being an inactive state three times in succession, deleting the log group with one state being an inactive state in the ORACLE database;
calculating an average value of the log file writing quantity every half an hour in the last seven days;
acquiring the writing quantity of the log file every half an hour, and comparing the acquired writing quantity with an average value;
in response to the two consecutive writes being greater than the average, the number of log groups in the ORACLE database is increased to a maximum of the number of log groups in seven days.
2. The method of claim 1, wherein automatically adding one log group to the ORACLE database and increasing the size of the log file in the added log group by a first predetermined ratio in response to the status of the next log group being an active status comprises:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
3. The method of claim 2, wherein the log file is set to not more than 1000 megabytes.
4. The method of claim 1, wherein reducing the size of the log file in the next log group by a second predetermined ratio in response to the state of the next log group being an inactive state comprises:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
5. The method of claim 4, wherein the scaled-down log file is no less than 50 megabytes.
6. The method as recited in claim 4, further comprising:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
7. An apparatus for improving database performance, the apparatus comprising:
the judging module is configured to respond to the full writing of the log files in one log group of the ORACLE database to switch the log groups and judge the state of the next log group;
the adding module is configured to automatically add the log groups in the ORACLE database and increase the sizes of the log files in the added log groups according to a first preset proportion in response to the state of the next log group being an active state;
the reducing module is configured to respond to the state of the next log group as an inactive state, and reduce the size of the log file in the next log group according to a second preset proportion;
the deleting module is configured to respond to the condition that the state of the next log group is the inactive state three times in succession, and delete the log group with one state being the inactive state in the ORACLE database;
a calculation module configured to calculate an average of log file write amounts every half hour of each interval in the last seven days, acquire the write amounts of the log files every half hour of each interval, and compare the acquired write amounts with the average, and in response to the two consecutive write amounts being greater than the average, increase the number of log groups in the ORACLE database to a maximum of the number of log groups in the seven days.
8. The apparatus of claim 7, wherein the augmentation module is further configured to:
responding to the state of the next log group as an active state, and acquiring the size of the full log file;
adding a log group in an ORACLE database;
the log files are set in the added log group, wherein the set log files are 10% larger than the full log files.
9. The apparatus of claim 8, wherein the log file is set to not more than 1000 megabytes.
10. The apparatus of claim 7, wherein the reduction module is further configured to:
responding to the state of the next log group as an inactive state, and acquiring the size of the full log file;
and (3) reducing the log files in the next log group, wherein the log files in the next log group are 20% smaller than the full log files.
11. The apparatus of claim 7, wherein the computing module is further configured to:
and deleting the log group with the inactive state in the ORACLE database in response to the two continuous writing quantities being smaller than the average value.
12. A computer device, comprising:
at least one processor; and
a memory storing computer instructions executable on the processor, which when executed by the processor, perform the steps of the method of any one of claims 1-6.
13. A computer readable storage medium storing a computer program, characterized in that the computer program when executed by a processor implements the steps of the method of any one of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210748332.2A CN115129680B (en) | 2022-06-29 | 2022-06-29 | Method, device, equipment and readable medium for improving database performance |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210748332.2A CN115129680B (en) | 2022-06-29 | 2022-06-29 | Method, device, equipment and readable medium for improving database performance |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN115129680A CN115129680A (en) | 2022-09-30 |
| CN115129680B true CN115129680B (en) | 2024-01-16 |
Family
ID=83379672
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210748332.2A Active CN115129680B (en) | 2022-06-29 | 2022-06-29 | Method, device, equipment and readable medium for improving database performance |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115129680B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113407503B (en) * | 2020-03-16 | 2024-12-03 | 北京金山云网络技术有限公司 | Log space management method, device, electronic equipment and medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110765088A (en) * | 2019-10-22 | 2020-02-07 | 北京天融信网络安全技术有限公司 | Log processing method and electronic equipment |
| CN112231288A (en) * | 2020-09-30 | 2021-01-15 | 北京金山云网络技术有限公司 | Log storage method and device and medium |
| WO2021223468A1 (en) * | 2020-05-08 | 2021-11-11 | 苏州浪潮智能科技有限公司 | Ssd-based log data storage method and apparatus, device and medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9928259B2 (en) * | 2015-04-21 | 2018-03-27 | International Business Machines Corporation | Deleted database record reuse |
-
2022
- 2022-06-29 CN CN202210748332.2A patent/CN115129680B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110765088A (en) * | 2019-10-22 | 2020-02-07 | 北京天融信网络安全技术有限公司 | Log processing method and electronic equipment |
| WO2021223468A1 (en) * | 2020-05-08 | 2021-11-11 | 苏州浪潮智能科技有限公司 | Ssd-based log data storage method and apparatus, device and medium |
| CN112231288A (en) * | 2020-09-30 | 2021-01-15 | 北京金山云网络技术有限公司 | Log storage method and device and medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN115129680A (en) | 2022-09-30 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9355112B1 (en) | Optimizing compression based on data activity | |
| CN107943867B (en) | High-performance hierarchical storage system supporting heterogeneous storage | |
| US7174438B2 (en) | Disk array apparatus | |
| US5704031A (en) | Method of performing self-diagnosing hardware, software and firmware at a client node in a client/server system | |
| US20070214334A1 (en) | Storage system | |
| US8627026B2 (en) | Storage apparatus and additional data writing method | |
| JP2004334574A (en) | Operation managing program and method of storage, and managing computer | |
| US20030220948A1 (en) | Managing snapshot/backup collections in finite data storage | |
| CN102033786B (en) | Method for repairing consistency of copies in object storage system | |
| US20070239806A1 (en) | Methods and apparatus for a fine grained file data storage system | |
| US20130275696A1 (en) | Storage device | |
| CN103605585A (en) | Intelligent backup method based on data discovery | |
| GB2350454A (en) | A method of updating and/or restoring an operating system | |
| WO2024148856A1 (en) | Data writing method and system, and storage hard disk, electronic device and storage medium | |
| CN109684338A (en) | A kind of data-updating method of storage system | |
| US8275937B2 (en) | Storage system and processing efficiency improving method of storage system | |
| WO2010080781A1 (en) | Systems and methods for monitoring archive storage condition and preventing the loss of archived data | |
| CN115129680B (en) | Method, device, equipment and readable medium for improving database performance | |
| JPH0991099A (en) | Disk update log recording system | |
| JP2003242010A (en) | Optimizing log usage for temporary object | |
| CN110727733A (en) | Main and standby server system and data synchronization method | |
| CN114153395A (en) | Object storage data life cycle management method, device and equipment | |
| CN109344011B (en) | Data backup method and device | |
| CN116610636A (en) | Data processing method and device of file system, electronic equipment and storage medium | |
| JPH09244933A (en) | Method and device for backing up data base |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |