CN115129680A - 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
- CN115129680A CN115129680A CN202210748332.2A CN202210748332A CN115129680A CN 115129680 A CN115129680 A CN 115129680A CN 202210748332 A CN202210748332 A CN 202210748332A CN 115129680 A CN115129680 A CN 115129680A
- Authority
- CN
- China
- Prior art keywords
- log
- group
- log group
- file
- log file
- 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.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 49
- 230000004044 response Effects 0.000 claims abstract description 33
- 230000009467 reduction Effects 0.000 claims abstract description 9
- 238000004590 computer program Methods 0.000 claims description 11
- 238000010586 diagram Methods 0.000 description 7
- 230000006870 function Effects 0.000 description 6
- 230000008569 process Effects 0.000 description 6
- 238000012217 deletion Methods 0.000 description 4
- 230000037430 deletion Effects 0.000 description 4
- 230000003287 optical effect Effects 0.000 description 4
- 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
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 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
- 238000004364 calculation method Methods 0.000 description 1
- 230000015556 catabolic process Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000006731 degradation reaction Methods 0.000 description 1
- 238000005259 measurement Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Images
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 file in one log group of the ORACLE database, switching the log groups, and judging the state of the next log group; responding to the active state of the next log group, automatically adding the log groups in an ORACLE database and increasing the size of a log file in one added log group according to a first preset proportion; responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion; and in response to the state of the next log group being in the inactive state for three consecutive times, deleting one log group in the ORACLE database, wherein the state of the log group is in the inactive state. By using the scheme of the invention, the performance reduction of the database caused by the REDO switching waiting can be avoided, the workload brought by the REDO adjustment is reduced, and the problem of the performance of the database caused by the 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 that at least two log groups are needed, and when one log file group is full, the log file group is switched to another log file. A log switch triggers two actions by the database: triggering a checkpoint event to notify a dbwr process to write data in a fully written LOG file into a data file, wherein the LOG file cannot be used in an overwriting manner (the overwriting manner means that the data is lost because data in the LOG file needs to be written into the data file) before the data in the LOG file is written into the data file, namely the database cannot be written into a new REDO file at the moment, so that the database cannot write the data in a REDO LOG BUFFER into the REDO, the database stops changing operation for a short time, and the database cannot be used normally (can be in a suspended state); triggering to generate an archive file, copying a fully written LOG file by a database and generating the archive file after the archive mode of the database is started, wherein the LOG file cannot be used by covering before the archive file is generated, and the database cannot be written into a new REDO file, so that the database cannot write data in REDO LOG BUFFER into REDO, the database stops changing operation for a short time, and the database cannot be normally used (can be in a suspended state).
When the log file is frequently switched, the problem that the log file is in an ACTIVE state and cannot be switched occurs, at the moment, the database is in a suspended state, and the database waits for completion of checkpoint or archiving, so that the performance of the database is greatly affected, and service interruption can be caused seriously. When this happens, the database administrator typically deals with the increase in log size and 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 log file is, the smaller the log file means the more frequent the log switching is, and the influence of the frequent log switching on the system performance during the peak period of the service is not insignificant. Database traffic may be different in different time periods, 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 groups in the system are difficult to meet the conditions of different traffic, so log switching basically affects the performance of all databases.
Disclosure of Invention
In view of this, embodiments of the present invention provide a method, an apparatus, a device, and a readable medium for improving database performance, which can avoid performance degradation of a database caused by an REDO handover wait, reduce workload caused by an REDO adjustment, and solve the problem of database performance caused by an REDO wait without human intervention.
In view of the above object, an aspect of the embodiments of the present invention provides a method for improving performance of a database, including the steps of:
responding to full log file in a log group of an ORACLE database (large relational data) to switch the log group, and judging the state of the next log group;
responding to the active state of the next log group, automatically adding the log group in an ORACLE database and increasing the size of a log file in the added log group according to a first preset proportion;
responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion;
and in response to the state of the next log group being in the inactive state for three consecutive times, deleting one log group in the ORACLE database, wherein the state of the log group is in the inactive state.
According to an embodiment of the present invention, in response to the status of the next log group being active status, automatically adding a log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio includes:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
According to one embodiment of the invention, the log file is set to no more than 1000 megabits.
According to an embodiment of the present invention, in response to the status of the next log group being inactive, reducing the size of the log file in the next log group according to a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
According to one embodiment of the invention, the reduced log file is no less than 50 megabits.
According to an embodiment of the present invention, further comprising:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
According to an embodiment of the present invention, further comprising:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
In another aspect of an embodiment of the present invention, there is provided an apparatus for improving performance of a database, the apparatus including:
the judging module is configured to respond to the full writing of the log files in one log group of the ORACLE database, perform log group switching and judge the state of the next log group;
the adding module is configured to respond that the state of the next log group is an active state, automatically add the log group in an ORACLE database and increase the size of a log file in the added log group according to a first preset proportion;
the reduction module is configured to respond that the state of the next log group is 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 of the next log group being in the inactive state for three consecutive times, and delete one log group in the ORACLE database, wherein the condition of the log group is in the inactive state.
According to an embodiment of the invention, the adding module is further configured to:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
a log file is set in the added log group, wherein the set log file is 10% larger than the full log file.
According to one embodiment of the invention, the log file is set to no more than 1000 megabits.
According to one embodiment of the invention, the reduction module is further configured to:
responding to the condition that the next log group is in an inactive condition, and acquiring the size of a fully written log file;
and 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 an embodiment of the invention, the system further comprises a computing module configured to:
calculating the average value of the log file writing amount every half hour within the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
According to one embodiment of the invention, the computing module is further configured to:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
In another aspect of an embodiment of the present invention, there is also provided a computer apparatus including:
at least one processor; and
a memory storing computer instructions executable on the processor, the instructions when executed by the processor implementing 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 one of the above-mentioned methods.
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 log group switching is carried out by responding to the full writing of the log file in one log group of the ORACLE database, and the state of the next log group is judged; responding to the active state of the next log group, automatically adding the log group in an ORACLE database and increasing the size of a log file in the added log group according to a first preset proportion; responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion; responding to the technical scheme that the state of the next log group is in the inactive state for three times continuously, deleting the log group in the ORACLE database, wherein the state of the log group is in the inactive state, so that the performance reduction of the database caused by the REDO switching waiting can be avoided, the workload brought by the REDO adjustment can be reduced, and the problem of the performance of the database caused by the REDO waiting can be solved without human intervention.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the embodiments or the prior art descriptions will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other embodiments can be obtained according to the drawings without creative efforts.
FIG. 1 is a schematic flow chart diagram of a method of improving database performance in accordance with one embodiment of the present invention;
FIG. 2 is a schematic diagram of an apparatus for improving database performance according to one embodiment of the present invention;
FIG. 3 is a schematic diagram of a computer device according to one embodiment of the present invention;
fig. 4 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present 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 are described in further detail with reference to the accompanying drawings.
In view of the above objects, a first aspect of embodiments of the present invention proposes an embodiment of a method for improving database performance. Fig. 1 shows a schematic flow diagram of the method.
As shown in fig. 1, the method may include the steps of:
s1 performs log group switching in response to full writing of log files in one log group of the ORACLE database, and determines the status of the next log group.
The REDO file (Log file) is used for recording changes to the database, data in the Redo Log Buffer cache are sequentially and continuously written into the REDO file, one REDO group (Log group) is provided for the REDO file in the database, and each REDO group comprises one or two REDO files. The REDO group comprises a plurality of REDO files, wherein the REDO files are written in parallel and have the same data, namely, the REDO files are mirrored to each other. The REDO mechanism is a vital means for ensuring data security and fault recovery, and is also a key factor that has a great influence on database performance, at least two REDO groups in the database are generally written in a circulating manner, that is, after an REDO file in one REDO group is full, data is written into an REDO file in the next REDO group. The size of the REDO file is fixed, when one REDO group is full, the REDO group is switched to another REDO log group to continue writing, and the switching is REDO switching. REDO has three states, current state (currently being written), active state (checkpoint or archive is not completed), and inactive state (checkpoint and archive has been completed). When the log file is switched to the next log group after being fully written, the state of the next log group needs to be checked.
S2 responds to the status of the next log group being active status, automatically adding the log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio.
If the status of the next log group is active status (i.e. there is no log group with inactive status available), the size of the full log file is obtained, then a log group is added to 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.
S3, responding to the status of the next log group being inactive, reducing the size of the log file in the next log group according to a second preset proportion.
And if the state of the next log group is an inactive state, acquiring the size of the fully written log file, and then reducing the log file in the next log group, wherein the log file in the next log group is 20% smaller than the fully written log file, and the minimum log file is not less than 50M.
S4 deletes a log group in the ORACLE database whose status is inactive, in response to the status of the next log group being inactive three consecutive times. But at least 2 log groups need to be reserved, and if the number of the log groups in the current database is 2, the deletion operation is not performed. By the method, the logs can be switched without waiting for available log files, the sizes of the logs and log groups are kept at relatively small and low levels, manual intervention is not needed in the whole process, the performance of the database is improved, and the workload of personnel is reduced.
The log files are written in sequence, and the contents in the log files are continuously stored 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 if the log files are actually generated, more I/O (input/output) is consumed and time is also consumed. When creating the log file, only creating a file header, and recording the address range of the continuous storage space of the log file on the hard disk and the position of the file tail in the file header. When the log file is deleted, the log file is just marked as a deletion state in the file system, and the log file is not actually cleared and deleted on the hard disk, so that the 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 only a new continuous storage space address range and a file tail position are recorded in the log file header, and only writing is performed. When the existing log file is reduced, the reduced portion is not actually deleted on the hard disk, but the continuous storage space address range and the file end position recorded in the file header are modified.
By the technical scheme of the invention, the performance reduction of the database caused by the REDO switching waiting can be avoided, the workload brought by the REDO adjustment is reduced, and the problem of the performance of the database caused by the REDO waiting can be solved without human intervention.
In a preferred embodiment of the present invention, in response to the status of the next log group being active status, automatically adding a log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio includes:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
a log file is set in the added log group, wherein the set log file is 10% larger than the full log file.
In a preferred embodiment of the present invention, no more than 1000 megabits of log files are set.
In a preferred embodiment of the present invention, in response to that the status of the next log group is inactive, reducing the size of the log file in the next log group according to a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabits.
In a preferred embodiment of the present invention, the method further comprises:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two writes being greater than the average, increasing the number of log groups in the ORACLE database to a maximum of the number of log groups over seven days.
In a preferred embodiment of the present invention, the method further comprises:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database. The above embodiment is a method for pre-adding and pre-deleting log groups during high and low peak data volumes, and obtains Redo size (bytes) information every half hour from the AWR automatic load information base, where this field is in bytes, and then inserts into the following table 1:
TABLE 1
6:00 and 18:00 average values of the REDO measurement per second in two time periods of 6:00-18:00 and 18: 00-next day 6:00 of the last seven days in the table are respectively obtained. And after obtaining the average value, respectively comparing the average value of 6:00-18:00 with the data of 6:00-18:00 in the table, when the values of two times of the average value are greater than the average value, indicating that the period has entered the business peak period, checking the REDO group and the REDO size in the database, comparing the number of the REDO group and the REDO size in the peak period with the values, if the number of the REDO group in the database is less than the number of the REDO group at the peak, adding the REDO group until the number of the REDO group at the peak is equal to the number of the REDO group at the peak, and the REDO size is the REDO size recorded in the table at the peak period. Comparing the average value of the 18: 00-day 6:00 with the data of the 18: 00-day 6:00 time period in the table, when the two continuous numerical values are smaller than the average value, showing that the period has entered the business low peak period, checking the magnitude of the REDO group and the REDO in the database, comparing the number of the REDO group in the low peak period and the magnitude of the REDO subjected to numerical comparison, if the number of the REDO group in the database is higher than the number of the REDO group in the low peak period, deleting the REDO group in the inactive state until the log group is equal to the number of the REDO group in the low peak period, and the REDO size is the REDO size recorded in the table in the low peak period.
The technical scheme of the invention avoids the performance reduction of the database caused by the REDO switching waiting by automatically increasing the size of the REDO group and the REDO in the peak period of the service and automatically reducing the size of the REDO group and the REDO in the low period of the service. When a database engineer processes the performance influence on the database caused by the REDO switching, the number and the size of the required REDO groups are difficult to be accurately judged, and the number and the size of the REDO groups cannot be changed in real time along with different business volumes.
It should be noted that, as will be understood by those skilled in the art, all or part of the processes in the methods of the above embodiments may be implemented by instructing relevant hardware through a computer program, and the above programs may be stored in a computer-readable storage medium, and when executed, the programs may include the processes of the embodiments of the methods as described above. The storage medium may be a magnetic disk, an optical disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), or the like. The embodiments of the computer program may achieve the same or similar effects as any of the above-described method embodiments corresponding thereto.
Furthermore, the method disclosed according to an embodiment of the present invention may also be implemented as a computer program executed by a CPU, and the computer program may be stored in a computer-readable storage medium. The computer program, when executed by the CPU, performs the above-described functions defined in the method disclosed in the embodiments of the present invention.
In view of the above-mentioned objects, a second aspect of the embodiments of the present invention provides an apparatus for improving performance of a database, as shown in fig. 2, the apparatus 200 includes:
and the judging module is configured to respond to the full writing of the log files in one log group of the ORACLE database to carry out log group switching and judge the state of the next log group. The REDO file (Log file) is used for recording changes to the database, data in the Redo Log Buffer cache are sequentially and continuously written into the REDO file, one REDO group (Log group) is provided for the REDO file in the database, and each REDO group comprises one or two REDO files. The REDO group comprises a plurality of REDO files, wherein the REDO files are written in parallel and have the same data, namely, the REDO files are mirrored to each other. The REDO mechanism is a vital means for ensuring data security and fault recovery, and is also a key factor that has a great influence on database performance, at least two REDO groups in the database are generally written in a circulating manner, that is, after an REDO file in one REDO group is full, data is written into an REDO file in the next REDO group. The size of the REDO file is fixed, when one REDO group is full, the REDO file is switched to another REDO log group to continue writing, and the switching is REDO switching. REDO has three states, current state (currently being written), active state (checkpoint or archive is not completed), and inactive state (checkpoint and archive has been completed). When the log file is switched to the next log group after being fully written, the state of the next log group needs to be checked.
The log file adding module is configured to respond to the fact that the state of the next log group is an active state, automatically add the log group in an ORACLE database and increase the size of the log file in an added log group according to a first preset proportion. If the status of the next log group is active status (i.e. there is no log group with inactive status available), the size of the fully written log file is obtained, then a log group is added to the ORACLE database, and the log file is set in the added log group, where the set log file is 10% larger than the fully written log file, and the log file cannot exceed 1000M at most.
The log file size reducing module is configured to respond to the fact that the state of the next log group is an inactive state, and reduce the size of the log file in the next log group according to a second preset proportion. And if the state of the next log group is in an inactive state, acquiring the size of the fully written log file, and then reducing the log file in the next log group, wherein the log file in the next log group is 20% smaller than the fully written log file, and the minimum log file is not less than 50M.
The log group deleting module is configured to respond to the condition of the next log group being in the inactive state for three consecutive times, and delete one log group in the ORACLE database, wherein the condition of the log group is in the inactive state. But at least 2 log groups need to be reserved, and if the number of the log groups in the current database is 2, the deletion operation is not carried out. By the method, the logs can be switched without waiting for available log files, the sizes of the logs and the log groups are kept relatively small and low, manual intervention is not needed in the whole process, the performance of the database is improved, and the workload of personnel is reduced. The log files are written in sequence, and the contents in the log files are continuously stored 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 if the log files are actually generated, more I/O (input/output) is consumed and time is also consumed. When creating the log file, only creating a file header, and recording the address range of the continuous storage space of the log file on the hard disk and the position of the file tail in the file header. When the log file is deleted, the log file is just marked as a deletion state in the file system, and the log file is not actually cleared and deleted on the hard disk, so that the 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 only a new continuous storage space address range and a file tail position are recorded in the log file header, and only writing is performed. When the existing log file is reduced, the reduced portion is not actually deleted on the hard disk, but the continuous storage space address range and the file end position recorded in the file header are modified.
In a preferred embodiment of the invention, the adding module is further configured to:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
In a preferred embodiment of the present invention, no more than 1000 megabits of log files are set.
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 a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
In a preferred embodiment of the present invention, the system further comprises a computing module configured to:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
In a preferred embodiment of the invention, the calculation module is further configured to:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
In view of the above object, a third aspect of the embodiments of the present invention provides 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, the 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 implementing the method of:
responding to the full writing of the log files in one log group of the ORACLE database, performing log group switching, and judging the state of the next log group;
responding to the active state of the next log group, automatically adding the log group in an ORACLE database and increasing the size of a log file in the added log group according to a first preset proportion;
responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion;
and in response to the state of the next log group being in the inactive state for three consecutive times, deleting one log group in the ORACLE database, wherein the state of the log group is in the inactive state.
In a preferred embodiment of the present invention, in response to the status of the next log group being active status, automatically adding a log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio includes:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
In a preferred embodiment of the invention, no more than 1000 megabits of log file are set.
In a preferred embodiment of the present invention, in response to that the status of the next log group is inactive, reducing the size of the log file in the next log group according to a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabits.
In a preferred embodiment of the present invention, the method further comprises:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two writes being greater than the average, increasing the number of log groups in the ORACLE database to a maximum of the number of log groups over seven days.
In a preferred embodiment of the present invention, the method further comprises:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
In view of the above object, a fourth aspect of the embodiments of the present invention proposes a computer-readable storage medium. FIG. 4 illustrates 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 method of:
responding to the full writing of the log file in one log group of the ORACLE database, switching the log groups, and judging the state of the next log group;
responding to the active state of the next log group, automatically adding the log group in an ORACLE database and increasing the size of a log file in the added log group according to a first preset proportion;
responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion;
and in response to the state of the next log group being in the inactive state for three consecutive times, deleting one log group in the ORACLE database, wherein the state of the log group is in the inactive state.
In a preferred embodiment of the present invention, in response to the status of the next log group being active status, automatically adding a log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio includes:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
In a preferred embodiment of the present invention, no more than 1000 megabits of log files are set.
In a preferred embodiment of the present invention, in response to that the status of the next log group is inactive, reducing the size of the log file in the next log group according to a second preset ratio includes:
responding to the state of the next log group as an inactive state, and acquiring the size of a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
In a preferred embodiment of the present invention, the reduced log file is no less than 50 megabits.
In a preferred embodiment of the present invention, the method further comprises:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
In a preferred embodiment of the present invention, the method further comprises:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
Furthermore, the methods disclosed according to embodiments 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. Which when executed by a processor performs the above-described functions defined in the methods disclosed in embodiments of the invention.
Further, the above method steps and system elements may also be implemented using a controller and a computer readable storage medium for storing a computer program for causing the controller to implement the functions of the above steps or elements.
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 place 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 a general-purpose or special-purpose processor. Also, 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 present 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 of the invention 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 numbers of the embodiments disclosed in the embodiments of the present invention are merely for description, and do not represent the merits 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 instructing relevant hardware, and the program may be stored in a computer-readable storage medium, where the above-mentioned storage medium may be a read-only memory, a magnetic disk or an optical disk.
Those of ordinary skill in the art will understand that: the discussion of any embodiment above is meant to be exemplary only, and is not intended to intimate that the scope of the disclosure, including the claims, of embodiments of the invention is limited to these examples; within the idea of an embodiment of the invention, also technical features in the above embodiment or in different embodiments may be combined and there are many other variations of the different aspects of the embodiments of the invention as described above, which are not provided in detail for the sake of brevity. Therefore, any omissions, modifications, substitutions, improvements, and the like that may be made without departing from the spirit and principles of the embodiments of the present invention are intended to be included within the scope of the embodiments of the present invention.
Claims (15)
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, performing log group switching, and judging the state of the next log group;
responding to the active state of the next log group, automatically adding the log group in an ORACLE database and increasing the size of a log file in the added log group according to a first preset proportion;
responding to the condition that the next log group is in the inactive condition, and reducing the size of the log file in the next log group according to a second preset proportion;
and in response to the state of the next log group being in the inactive state for three consecutive times, deleting one log group in the ORACLE database, wherein the state of the log group is in the inactive state.
2. The method as claimed in claim 1, wherein automatically adding a log group in the ORACLE database and increasing the size of the log file in the added log group according to a first preset ratio in response to the status of the next log group being active status comprises:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
3. The method of claim 2, wherein no more than 1000 megabits of log files are set.
4. The method of claim 1, wherein in response to the status of the next log group being inactive, reducing the size of the log file in the next log group according to a second preset ratio comprises:
responding to the state of the next log group as an inactive state, and acquiring the size of a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
5. The method of claim 4, wherein the reduced log file is no less than 50 megabits.
6. The method of claim 1, further comprising:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
7. The method of claim 6, further comprising:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
8. An apparatus for improving performance of a database, 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, perform log group switching and judge the state of the next log group;
the adding module is configured to respond to the active state of the next log group, automatically add the log group in an ORACLE database and increase the size of a log file in the added log group according to a first preset proportion;
the reduction module is configured to respond to the condition that the next log group is in an inactive condition, 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 of the next log group being in the inactive state for three consecutive times, and delete one log group in the ORACLE database, wherein the condition of the log group is in the inactive state.
9. The apparatus of claim 8, wherein the increasing module is further configured to:
responding to the active state of the next log group, and acquiring the size of a fully written log file;
adding a log group in an ORACLE database;
setting a log file in the added log group, wherein the set log file is 10% larger than the fully written log file.
10. The apparatus of claim 9, wherein the set log file does not exceed 1000 megabits.
11. The apparatus of claim 8, 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 a fully written log file;
the log files in the next log group are reduced, wherein the log files in the next log group are 20% smaller than the fully written log files.
12. The apparatus of claim 8, further comprising a computing module configured to:
calculating the average value of the log file writing amount every half an hour in the last seven days;
acquiring the write-in quantity of the log file every half hour, and comparing the acquired write-in quantity with the average value;
in response to the number of consecutive two 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 over seven days.
13. The apparatus of claim 12, wherein the computing module is further configured to:
and in response to the quantity of the two continuous writes being smaller than the average value, deleting the log group with the inactive state in the ORACLE database.
14. A computer device, comprising:
at least one processor; and
a memory storing computer instructions executable on the processor, the instructions when executed by the processor implementing the steps of the method of any one of claims 1 to 7.
15. A computer-readable storage medium, in which a computer program is stored which, when being executed by a processor, carries out the steps of the method according to any one of claims 1 to 7.
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 true CN115129680A (en) | 2022-09-30 |
| CN115129680B 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) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20160314151A1 (en) * | 2015-04-21 | 2016-10-27 | International Business Machines Corporation | Deleted database record reuse |
| 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 |
-
2022
- 2022-06-29 CN CN202210748332.2A patent/CN115129680B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20160314151A1 (en) * | 2015-04-21 | 2016-10-27 | International Business Machines Corporation | Deleted database record reuse |
| 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 |
Non-Patent Citations (1)
| Title |
|---|
| 金蝶云社区: "Oracle redo日志组大小调整及日志频率检查", Retrieved from the Internet <URL:https://vip.kingdee.com/article/206129902313841920?isKnowledge=2&productLineId=8> * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN115129680B (en) | 2024-01-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109725822B (en) | Method, apparatus and computer program product for managing a storage system | |
| US9569458B2 (en) | Preserving a state using snapshots with selective tuple versioning | |
| US9355112B1 (en) | Optimizing compression based on data activity | |
| US6421767B1 (en) | Method and apparatus for managing a storage system using snapshot copy operations with snap groups | |
| US20050071436A1 (en) | System and method for detecting and sharing common blocks in an object storage system | |
| US11232073B2 (en) | Method and apparatus for file compaction in key-value store system | |
| US20070214334A1 (en) | Storage system | |
| CN110442535B (en) | Method and system for improving reliability of distributed solid-state disk key value cache system | |
| US20140075240A1 (en) | Storage apparatus, computer product, and storage control method | |
| JP2004334574A (en) | Operation managing program and method of storage, and managing computer | |
| US7987325B1 (en) | Method and apparatus for implementing a storage lifecycle based on a hierarchy of storage destinations | |
| US20130275696A1 (en) | Storage device | |
| US10628298B1 (en) | Resumable garbage collection | |
| US10725907B2 (en) | Information processing apparatus for specifying data region of garbage collection, information processing system and information processing method | |
| US11128535B2 (en) | Computer system and data management method | |
| 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 | |
| WO2018000191A1 (en) | Method and device for data processing | |
| RU2665272C1 (en) | Method and apparatus for restoring deduplicated data | |
| CN105808451B (en) | Data caching method and related device | |
| CN115129680A (en) | Method, device, equipment and readable medium for improving database performance | |
| CN115454958A (en) | Data processing method, device, equipment, system and medium based on artificial intelligence | |
| US11599290B2 (en) | Data storage method, electronic device, and computer program product | |
| JP4473206B2 (en) | Computer system | |
| CN114003612A (en) | Processing method and processing system for abnormal conditions of database |
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 |