CN115129680B - Method, device, equipment and readable medium for improving database performance - Google Patents

Method, device, equipment and readable medium for improving database performance Download PDF

Info

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
Application number
CN202210748332.2A
Other languages
Chinese (zh)
Other versions
CN115129680A (en
Inventor
马国良
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Suzhou Inspur Intelligent Technology Co Ltd
Original Assignee
Suzhou Inspur Intelligent Technology Co Ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Suzhou Inspur Intelligent Technology Co Ltd filed Critical Suzhou Inspur Intelligent Technology Co Ltd
Priority to CN202210748332.2A priority Critical patent/CN115129680B/en
Publication of CN115129680A publication Critical patent/CN115129680A/en
Application granted granted Critical
Publication of CN115129680B publication Critical patent/CN115129680B/en
Active legal-status Critical Current
Anticipated expiration legal-status Critical

Links

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

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

Method, device, equipment and readable medium for improving database performance
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.
CN202210748332.2A 2022-06-29 2022-06-29 Method, device, equipment and readable medium for improving database performance Active CN115129680B (en)

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)

Citations (3)

* Cited by examiner, † Cited by third party
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)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9928259B2 (en) * 2015-04-21 2018-03-27 International Business Machines Corporation Deleted database record reuse

Patent Citations (3)

* Cited by examiner, † Cited by third party
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
CN107943867B (en) High-performance hierarchical storage system supporting heterogeneous storage
US9355112B1 (en) Optimizing compression based on data activity
US7174438B2 (en) Disk array apparatus
US9569458B2 (en) Preserving a state using snapshots with selective tuple versioning
US5704031A (en) Method of performing self-diagnosing hardware, software and firmware at a client node in a client/server system
US9645892B1 (en) Recording file events in change logs while incrementally backing up file systems
JP5037811B2 (en) Remote copy method and remote copy system
US8548948B2 (en) Methods and apparatus for a fine grained file data storage system
US8627026B2 (en) Storage apparatus and additional data writing method
US20030220929A1 (en) Managing finite data storage utilizing preservation weights
US20070208918A1 (en) Method and apparatus for providing virtual machine backup
JP2004334574A (en) Operation managing program and method of storage, and managing computer
CN102033786B (en) Method for repairing consistency of copies in object storage system
US20060047926A1 (en) Managing multiple snapshot copies of data
US20130275696A1 (en) Storage device
GB2350454A (en) A method of updating and/or restoring an operating system
US20110295809A1 (en) Computer system and restoration method
CN103605585A (en) Intelligent backup method based on data discovery
US20120324185A1 (en) Storage system and control method for the same
US6192376B1 (en) Method and apparatus for shadowing a hierarchical file system index structure to enable error recovery
US8275937B2 (en) Storage system and processing efficiency improving method of storage system
CN105224891A (en) Magnetic disc optic disc fused data method for secure storing, system and device
CN109684338A (en) A kind of data-updating method of storage system
CN110597779A (en) Data reading and writing method in distributed file system and related device
JPH0991099A (en) Disk update log recording system

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
GR01 Patent grant
GR01 Patent grant