Dynamic adjustment method for configuration parameters of database, storage medium and program product

Abstract

The invention provides a dynamic adjustment method for configuration parameters of a database, a storage medium and a program product. The method comprises the following steps: acquiring a target configuration parameter, and acquiring a tuning trigger condition of the target configuration parameter; obtaining performance data of the database, and judging whether the database triggers the tuning trigger condition or not according to the performance data; and if yes, obtaining a suggested value of the target configuration parameter, and adjusting the value of the target configuration parameter according to the suggested value. According to the technical scheme, the configuration parameters of the database can be automatically adjusted and optimized, so that the purpose of improving the performance of the database is achieved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method for dynamically adjusting configuration parameters of a database, a storage medium and a program product. Background Art

[0002] A database management system is a software system used to store, retrieve, and manage large amounts of data. It is a core component of database technology and can process and organize data efficiently and securely to support the needs of various businesses and applications. There are many parameters in a database, which involve many aspects of the database system, such as memory allocation and CPU usage, and can greatly affect the database's throughput, latency, and other performance. Therefore, in order to ensure the reliability of database operation, the database management system needs to monitor the database parameters to detect database problems and correct them immediately.

[0003] The database management system in the prior art is provided with a snapshot mechanism, that is, a snapshot of the performance parameters of the database is obtained at a certain interval (one hour by default). After each snapshot, the database management system staff can analyze the data in the performance snapshot to find the cause of the performance problem of the database, and give modification suggestions for the configuration parameters of the database based on the cause. According to the modification suggestions, the configuration parameters of the data disk are adjusted to improve the reliability of database operation.

[0004] In the prior art, it is necessary to manually find the cause of the performance problem and modify the configuration parameters of the database. Therefore, the timeliness of the database configuration parameters cannot be guaranteed, which affects the performance of the database. Summary of the invention

[0005] The purpose of the present invention is to provide a method for dynamically adjusting the configuration parameters of a database, a storage medium and a program product, which are used to automatically tune the configuration parameters of the database to achieve the purpose of improving the performance of the database.

[0006] In a first aspect, the present invention provides a method for dynamically adjusting configuration parameters of a database, comprising:

[0007] Obtain target configuration parameters, and obtain tuning trigger conditions of the target configuration parameters;

[0008] Acquire performance data of the database, and determine whether the database triggers the tuning trigger condition according to the performance data;

[0009] If so, a recommended value of the target configuration parameter is obtained, and the value of the target configuration parameter is adjusted according to the recommended value.

[0010] Furthermore, the step of obtaining the tuning trigger condition of the target configuration parameter includes:

[0011] If the target configuration parameter includes work_mem, the performance data includes the proportion of temporary block IO of the database, and the buffer file read wait event and buffer file write wait event of the database foreground, and the tuning trigger condition includes:

[0012] The proportion of the temporary block IO is greater than the first IO proportion threshold, and the proportion of the file read wait event or the buffer file write wait event is greater than the first set threshold;

[0013] Alternatively, a proportion of the buffer file read wait event or the buffer file write wait event is greater than a second set threshold, wherein the first set threshold is less than the second set threshold.

[0014] Furthermore, the step of obtaining the tuning trigger condition of the target configuration parameter includes:

[0015] If the target configuration parameter includes temp_buffers, the performance data includes the proportion of local block IO of the database, the hit rate of local cache, the proportion of shared IO, and the buffer file read wait event and buffer file write wait event of the database foreground, and the tuning trigger condition includes:

[0016] The proportion of the local block IO is greater than the second IO proportion threshold, and the hit rate of the local cache is less than the preset hit rate threshold;

[0017] Alternatively, the proportion of the local block IO and the proportion of the shared IO meet a preset condition, and the proportion of the buffer file read wait event or the buffer file write wait event is greater than a third set threshold.

[0018] Furthermore, the step of adjusting the value of the target configuration parameter according to the recommended value includes:

[0019] Obtaining a current value of the target configuration parameter, and determining whether a ratio between the current value and the recommended value is greater than a set ratio;

[0020] If yes, setting the value of the target configuration parameter to the recommended value, and determining whether the performance parameter corresponding to the target configuration parameter satisfies a preset critical condition;

[0021] If satisfied, stop further adjustment of the target configuration parameters;

[0022] If not satisfied, the recommended value is updated, and then the process returns to the step of obtaining the current value of the target configuration parameter.

[0023] Furthermore, the step of updating the recommended value includes:

[0024] A set multiple of the recommended value is used as a new recommended value of the target configuration parameter, wherein the value of the set multiple is greater than 1.

[0025] Furthermore, before the step of updating the recommended value, the method further includes:

[0026] Determining whether the recommended value is a maximum recommended value of the target configuration parameter;

[0027] If yes, return to the step of obtaining the current value of the target configuration parameter;

[0028] If not, the step of updating the recommended value is performed.

[0029] Furthermore, the step of obtaining the tuning trigger condition of the target configuration parameter includes:

[0030] If the target configuration parameter includes bindcpulist, the performance data includes the CPU usage and the number of active sessions of the database, and the tuning trigger condition includes:

[0031] The CPU usage is greater than a preset usage threshold, and the number of active sessions is greater than a set percentage of the database CPU core.

[0032] Furthermore, the step of obtaining the recommended value of the target configuration parameter includes:

[0033] The number of CPU cores and the CPU usage of the database are obtained, and the recommended value is calculated according to the number of CPU cores and the CPU usage.

[0034] In a second aspect, the present invention further provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of any of the above-mentioned methods for dynamically adjusting configuration parameters.

[0035] In a third aspect, the present invention further provides a computer program product, comprising a computer program, which, when executed by a processor, implements the steps of any of the above-mentioned methods for dynamically adjusting configuration parameters.

[0036] The technical solution of the present invention can adjust the value of the target configuration parameter according to the recommended value of the target configuration parameter when the database triggers the tuning trigger condition of the target configuration parameter, thereby realizing automatic tuning of the target configuration parameter of the database, ensuring the timeliness and reliability of the adjustment of the target configuration parameter, and achieving the purpose of improving the performance of the database.

[0037] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0039] Figure 1 is a schematic flow chart of a method for dynamically adjusting configuration parameters of a database according to an embodiment of the present invention;

[0040] Figure 2 is a schematic flow chart of adjusting target configuration parameters in a method for dynamically adjusting configuration parameters according to an embodiment of the present invention;

[0041] Figure 3 is a schematic flow chart of a method for dynamically adjusting configuration parameters of a database according to an embodiment of the present invention;

[0042] Figure 4 is a schematic flow chart of a method for dynamically adjusting configuration parameters of a database according to an embodiment of the present invention;

[0043] Figure 5 is a schematic flow chart of a method for dynamically adjusting configuration parameters of a database according to an embodiment of the present invention;

[0044] Figure 6 is a schematic diagram of a computer program product according to an embodiment of the present invention; and

[0045] Figure 7 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention. DETAILED DESCRIPTION

[0046] Refer to the following Figures 1 to 7 The method, storage medium and program product for dynamically adjusting configuration parameters of a database in an embodiment of the present invention are described. In the description of this embodiment, when a feature "includes or contains" one or some of the features it covers, unless otherwise specifically described, this indicates that other features are not excluded and other features may be further included.

[0047] See also Figure 1 , Figure 1 What is shown is a schematic flow chart of a method for dynamically adjusting configuration parameters of a database according to an embodiment of the present invention. The method can automatically adjust the configuration parameters of the database according to the performance of the database to achieve the purpose of improving the performance of the database.

[0048] exist Figure 1 In the process shown, the method for dynamically adjusting the configuration parameters of a database of the present invention may generally include:

[0049] Step S102: obtaining target configuration parameters of the database, and obtaining tuning trigger conditions of the target configuration parameters;

[0050] Step S104: detecting the performance data of the database, and judging whether the database has triggered the tuning trigger condition of the target configuration parameter according to the performance data;

[0051] If yes, execute step S106;

[0052] Step S106: Obtain a recommended value of the target configuration parameter, and adjust the value of the target configuration parameter according to the recommended value.

[0053] In the above step S102, the target configuration parameter obtained is a configuration parameter that can affect the performance of the database, and the number of the target configuration parameters can be one or more. Since different configuration parameters of the database can affect different performances of the database, the tuning trigger condition of the target configuration parameter can be determined according to the performance index of the database involved in the target configuration parameter.

[0054] In this embodiment, the database runs a performance snapshot mechanism, that is, a performance snapshot of the database is obtained once every set time interval. In the above step S104, after each performance snapshot is executed, a performance diagnosis and recommendation report corresponding to the snapshot number can be obtained, and the performance data involved in the target configuration parameters can be read from the performance diagnosis and recommendation report, and then it can be determined whether the database has triggered the tuning trigger condition of the target configuration parameters based on the performance data.

[0055] For example, if the database is the Renmin University Kingbase database, the obtained performance diagnosis and advice report is a KDDM report (KingbaseES performance automatic Diagnosis and advice report), and then the performance data involved in the target configuration parameters are read from the KDDM report.

[0056] In the above step S106, the recommended value of the target configuration parameter can be obtained according to the tuning suggestion given in the KDDM report, and then the value of the target configuration parameter can be adjusted according to the recommended value to improve the performance of the database.

[0057] According to the above content, the technical solution provided in this embodiment can adjust the value of the target configuration parameter according to the recommended value of the target configuration parameter when the database triggers the tuning trigger condition of the target configuration parameter, thereby realizing automatic tuning of the target configuration parameter of the database to achieve the purpose of improving the performance of the database.

[0058] In some embodiments of the present invention, the method for obtaining the tuning trigger condition of the target configuration parameter in step S102 includes:

[0059] If the target configuration parameter is work_mem, the performance data of the database corresponding to the target configuration parameter includes the proportion of temporary block IO in the database, as well as the buffer file read wait event and buffer file write wait event in the database foreground, and the tuning trigger conditions of the target configuration parameter include:

[0060] The proportion of temporary block IO of the database is greater than the first IO proportion threshold, and the proportion of buffer file read wait events or buffer file write wait events of the database foreground is greater than the first set threshold;

[0061] Alternatively, the proportion of buffer file read wait events or buffer file write wait events in the database foreground is greater than a second set threshold, wherein the first set threshold is less than the second set threshold.

[0062] In this embodiment, the value range of the first IO ratio threshold is 20% to 30%, and preferably 25%; the value range of the first set threshold is 0.2%-0.3%, and preferably 0.25%; the value range of the second set threshold is 0.1%-0.9%, and preferably 0.5%.

[0063] work_mem is an important configuration parameter in a database. This configuration parameter is used to configure the maximum amount of memory to be used before writing temporary files to disk during query operations such as sorting and hashing.

[0064] Temporary block IO of a database refers to the input and output operations generated by the use of temporary blocks (such as temporary tables or temporary storage space) when the database executes queries or operations. The proportion of temporary blocks in the database can reflect whether there are problems with the performance of the database. If the proportion of temporary blocks is too large, it means that the query or operation requires a large amount of temporary storage, which usually leads to a decrease in database performance. The reasons for the excessive proportion of temporary blocks in the database are that the query is not optimized, the memory configuration is insufficient, or the amount of data is too large. If the temporary blocks of the database are stored on the disk instead of in memory, the increase in the proportion of temporary blocks will increase the disk I / O, which will affect the query performance of the database, while a reasonable memory configuration can reduce the demand for temporary blocks. Since temporary blocks require disk space, the use of a large number of temporary blocks will lead to disk space shortage, especially when large data operations are performed, which will affect the stability of database performance.

[0065] If the proportion of database temporary block IO is too high, it indicates that the database operation and configuration need to be optimized. By adjusting work_mem to increase the memory available for temporary operations, the dependence on temporary blocks can be reduced, and the use of temporary blocks can be kept within a reasonable range to improve the stability and efficiency of database performance.

[0066] In some embodiments of the present invention, the method for obtaining the tuning trigger condition of the target configuration parameter in step S102 includes:

[0067] If the target configuration parameter is temp_buffers, the performance data of the database corresponding to the target configuration parameter includes the proportion of local block IO, the hit rate of local cache, the proportion of shared IO, and the buffer file read wait event and buffer file write wait event of the database foreground. The tuning trigger conditions of the target configuration parameter include:

[0068] The proportion of local block IO is greater than the second IO proportion threshold, and the hit rate of the local cache is less than the preset hit rate threshold; or

[0069] The proportion of local block IO and the proportion of shared IO meet the preset conditions, and the proportion of buffer file read wait events or buffer file write wait events in the database foreground is greater than the third set threshold.

[0070] In this embodiment, the value range of the second IO ratio threshold is 1%-10%, and preferably 5%; the value range of the preset hit rate threshold is 80%-90%, and preferably 90%; the value range of the third set threshold is 0.5%-1%, and preferably 1%. The preset conditions include: local IO ratio / (local IO ratio+shared IO ratio)>set value, and the value range of the set value is 20%-30%, and preferably 25%.

[0071] In the database, temp_buffers is the size of the memory buffer used to store session-level temporary table data. Each session of the database can independently allocate and use this memory buffer area to store temporary tables or temporary data generated when executing queries. By adjusting the size of the database's temp_buffers, you can improve the database's performance in processing temporary table data and reduce the proportion of disk I / O operations, thereby speeding up queries. In the process of executing complex queries or processing large amounts of temporary data, increasing the database's temp_buffers can effectively improve database performance.

[0072] The local block IO ratio usually refers to the ratio of IO operations used by local storage devices (such as local disks or SSDs) when the database system processes requests. This ratio can be used to evaluate the storage performance and efficiency of the system and understand whether database operations are limited by disk IO. If the local IO ratio is high, it means that the database relies on disk rather than memory when processing requests. This phenomenon will cause the performance of the database to deteriorate, especially when the database requires high throughput and low latency. The database's temp_buffers need to be adjusted to improve the performance of the database.

[0073] In some embodiments of the present invention, the method for adjusting the target configuration parameter according to the recommended value of the target configuration parameter in step S106 is as follows: Figure 2 As shown, the following steps are included:

[0074] Step S202: obtaining the current value of the target configuration parameter;

[0075] Step S204: determining whether the ratio between the current value and the recommended value of the target configuration parameter is greater than a set ratio, wherein the set ratio is preferably 75%;

[0076] If yes, then execute step S206; if no, then stop adjusting the target configuration parameters;

[0077] Step S206: setting the value of the target configuration parameter to the recommended value of the target configuration parameter;

[0078] Step S208: determining whether the performance data corresponding to the target configuration parameter meets the critical condition of the target configuration parameter;

[0079] If yes, then execute step S210; if no, then stop adjusting the target configuration parameters;

[0080] Step S210: Update the recommended value of the target configuration parameter, and then return to step S202.

[0081] For example, when the target configuration parameter is work_mem, the default value of work_mem is 4MB, the performance data corresponding to work_mem is the proportion of temporary block IO, and the critical condition of work_mem is that the proportion of temporary block IO of the database has an inflection point. After detecting that the proportion of temporary block IO of the database and the foreground wait event meet the tuning trigger condition of work_mem, the current value and recommended value of work_mem are obtained, and the ratio between the current value and the recommended value is calculated; if the ratio is greater than 75%, the value of work_mem is set to the recommended value of work_mem. Then, it is determined whether the proportion of temporary block IO has an inflection point. If so, the value of work_mem adjusted last time is used as the value of work_mem to complete the adjustment of the value of work_mem. For example, if the value of work_mem after the last adjustment is 64MB, the value of work_mem after this adjustment is 128MB, and the proportion of temporary block IO of the database has an inflection point when the value of work_mem is 128MB, the value of work_mem is set to 64MB, and the adjustment of work_mem is stopped. If the proportion of database temporary block IO does not show an inflection point, it means that the performance parameter corresponding to work_mem does not meet the corresponding critical condition. Therefore, it is necessary to adjust the recommended value of work_mem and adjust the value of work_mem again.

[0082] For another example, when the target configuration parameter is temp_buffers, the default value of temp_buffers is 8 MB, the performance data corresponding to temp_buffers is the proportion of local block IO and the local cache hit rate, and the critical condition of work_mem is that the proportion of database local block IO and local cache hit rate reaches an inflection point.

[0083] After detecting that the proportion of local block IO and local cache hit rate of the database meet the tuning trigger conditions of temp_buffers, obtain the current value and recommended value of temp_buffers, and calculate the ratio between the current value and the recommended value; if the ratio is greater than 75%, set the value of temp_buffers to the recommended value of temp_buffers. Then determine whether the proportion of local block IO and local cache hit rate has an inflection point. If so, use the value of temp_buffersm after the last adjustment as the value of temp_buffers to adjust the value of temp_buffers; if the proportion of local block IO and local cache hit rate of the database does not have an inflection point, it means that the performance parameters corresponding to temp_buffers do not meet the critical conditions of temp_buffers, so it is necessary to adjust the recommended value of temp_buffers and adjust the value of temp_buffers again.

[0084] Through the technical solution of this embodiment, the value of the target configuration parameter can be adjusted according to the recommended value of the target configuration parameter until the performance data corresponding to the target configuration parameter is optimal. Therefore, the technical solution of this embodiment can improve the reliability and accuracy of adjusting the target configuration parameter to achieve the purpose of improving the performance of the database.

[0085] In some embodiments of the present invention, the method for updating the recommended value of the target configuration parameter in step S210 includes:

[0086] The set multiple of the recommended value of the target configuration parameter is used as the new recommended value of the target configuration parameter.

[0087] In this embodiment, the value of the multiple is set to be greater than 1, and preferably 2, that is, twice the recommended value of the target configuration parameter is used as the new recommended value of the target configuration parameter. For example, if the target configuration parameter is work_mem, and the recommended value of the target configuration parameter is 16M, then after the recommended value of the target configuration parameter is updated, the new recommended value of the target configuration parameter is 32M. If the target configuration parameter is temp_buffers, and the recommended value of the target configuration parameter is 16M, then after the recommended value of the target configuration parameter is updated, the new recommended value of the target configuration parameter is 32M.

[0088] Through the technical solution of this embodiment, the set multiple of the recommended value of the target configuration parameter can be used as the new recommended value of the target configuration parameter to update the recommended value of the target configuration parameter, thereby improving the convenience and reliability of updating the recommended value of the target configuration parameter.

[0089] In some embodiments of the present invention, before the target configuration parameters are updated in step S210, the following steps are further included:

[0090] Step S212: determining whether the recommended value of the target configuration parameter is the maximum recommended value of the target configuration parameter;

[0091] If yes, stop adjusting the target configuration parameters; if no, execute step S210.

[0092] In this embodiment, if the target configuration parameter is work_mem, the maximum recommended value of the target configuration parameter is 128M; if the target configuration parameter is temp_buffers, the maximum recommended value of the target configuration parameter is 256M.

[0093] Through the technical solution of this embodiment, it is possible to prevent the recommended value of the target configuration parameter from increasing without limit, so as to improve the stability and reliability of the database.

[0094] In some embodiments of the present invention, the method for obtaining the tuning trigger condition of the target configuration parameter in step S102 includes:

[0095] If the target configuration parameter is bindcpulist, the performance data of the database corresponding to the target configuration parameter includes the CPU usage and number of active sessions of the database, and the tuning trigger conditions of the target configuration parameter include:

[0096] The CPU usage of the database is greater than the preset usage threshold, and the number of active sessions is greater than the set percentage of the database CPU cores.

[0097] In this embodiment, the value range of the preset usage rate threshold is 10%-20%, preferably 15%, and the preferred value of the set percentage is 80%.

[0098] In the database, the configuration parameter bindcpulist involves binding a specific CPU list to a process, thread, or operation to achieve resource management and performance optimization. In many operating systems and programming environments, there are similar mechanisms or methods to manage the allocation of CPU resources.

[0099] Accordingly, methods for adjusting bindcpulist according to its recommended value include:

[0100] Calculate the recommended value of bindcpulist and set the value of bindcpulist to its recommended value. Then execute the business and obtain the CPU usage of the database.

[0101] Determine whether the CPU usage of the database has dropped to the corresponding critical point;

[0102] If so, stop adjusting bindcpulist;

[0103] If not, return to the step of calculating the recommended value of bindcpulist.

[0104] Through the technical solution of this embodiment, it is possible to determine whether to adjust the configuration parameter bindcpulist according to the CPU usage of the database, so as to improve the data processing capability of the database by setting the database CPU to bind the core, thereby achieving the purpose of improving the performance of the database.

[0105] In some embodiments of the present invention, when the target configuration parameter is bindcpulist, the method for obtaining the recommended value of the target configuration parameter in step S106 includes:

[0106] Obtain the number of CPU cores and CPU usage of the database, and calculate the recommended values ​​of the target configuration parameters based on the number of CPU cores and CPU usage of the database.

[0107] In this embodiment, assuming that the number of CPU cores of the database is n and the CPU usage is ω, the recommended value p of the target configuration parameter can be calculated by the following formula:

[0108] p=2×n×ω

[0109] In this embodiment, the maximum recommended value of bindcpulist is the total number of database CPUs, and the minimum is one quarter of the total number of database CPUs. That is, if the recommended value of bindcpulist calculated by the above calculation formula is less than one quarter of the total number of database CPUs, the recommended value of bindcpulist is set to one quarter of the total number of database CPUs; if the recommended value of bindcpulist calculated by the above calculation formula is greater than the total number of database CPUs, the recommended value of bindcpulist is set to the total number of database CPUs.

[0110] Through the technical solution of this embodiment, the recommended value of the configuration parameter bindcpulist can be calculated according to the number of CPU cores and the CPU usage of the database, so as to improve the reliability of adjusting the configuration parameter bindcpulist.

[0111] In some embodiments of the present invention, the process of the method for dynamically adjusting the configuration parameters of a database of the present invention is as follows: Figure 3 As shown, the following steps are included:

[0112] Step S302: determine whether the database meets the tuning trigger condition of the configuration parameter work_mem;

[0113] If yes, execute step S304;

[0114] Step S304: obtaining a recommended value and a current value of the configuration parameter work_mem, and determining whether a ratio between the current value and the recommended value is greater than a set ratio;

[0115] If yes, execute step S306;

[0116] Step S306: adjusting the value of the configuration parameter work_mem according to the recommended value of the configuration parameter work_mem;

[0117] Step S308: Detect whether the proportion of temporary block IO of the database meets the corresponding critical condition;

[0118] If not, execute step S310; if yes, execute step S314;

[0119] Step S310: determining whether the recommended value of the configuration parameter work_mem is the maximum recommended value of the configuration parameter work_mem;

[0120] If yes, the configuration parameter work_mem is no longer adjusted; if no, step S312 is executed;

[0121] Step S312: Update the recommended value of the configuration parameter work_mem, and then return to step S304;

[0122] Step S314: Set the value of the configuration parameter work_mem to its last adjusted value.

[0123] Take an application scenario as an example. In this application scenario, the tuning trigger conditions for configuring the parameter work_mem include:

[0124] The proportion of temporary block IO of the database is greater than 25%, and the proportion of buffer file read wait events or buffer file write wait events in the database foreground is greater than 0.25%; or the proportion of buffer file read wait events or buffer file write wait events in the database foreground is greater than 0.5%.

[0125] The above setting ratio is 75%, the maximum recommended value of work_mem is 128MB, and the default value of work_mem is 4MB.

[0126] Then, a query statement is constructed to perform a large amount of data query. The query statement constructed in this embodiment is:

[0127] create table t1(a int); / / Create a table named t1 with an integer field a

[0128] insert into t1 select generate_series(1,50000000); / / Insert 50,000,000 records into table t1

[0129] Select*from perf.create_snapshot(); / / Call the function create_snapshot, which is in the collection perf

[0130] select to_char(systimestamp,'yyyymmdd hh24:mi:ss.ff'); / / Get the current system timestamp and format it as a string in the format of yyyymmdd hh24:mi:ss.ff

[0131] select count(*)from(select*from t1 order by a desc); / / Execute a nested query. The inner query select*from t1 order by a desc selects all records from the t1 table and sorts them in descending order of field a. Then, the outer query calculates the total number of records after sorting.

[0132] select to_char(systimestamp,'yyyymmdd hh24:mi:ss.ff'); / / Get the current system timestamp and format it as a string in the format of yyyymmdd hh24:mi:ss.ff

[0133] Select*from perf.create_snapshot(); / / Call the function create_snapshot, which is in the collection perf

[0134] After executing the above query statement, the experimental data obtained is shown in Table 1, where BufferFileRead is buffer file reading and BufferFileWrite is buffer file writing. For example, when the value of work_mem is 4MB, the proportion of database temporary block IO is 55.14%, the speed of database temporary block IO is 10.44MB / s, the proportion of BufferFileRead waiting events in the database foreground is 0.39%, and the proportion of BufferFileWrite waiting events in the database foreground is 0.97%.

[0135]

[0136] Table 1

[0137] From the experimental data in Table 1, we can see that when the value of the configuration parameter work_mem is adjusted from 4MB to 128MB, the proportion of database temporary block IO gradually decreases, and the operation speed of temporary block IO is improved. Therefore, by adjusting the size of the configuration parameter work_mem, the performance of the database can be effectively improved.

[0138] In some embodiments of the present invention, the process of the method for dynamically adjusting the configuration parameters of a database of the present invention is as follows: Figure 4 As shown, the following steps are included:

[0139] Step S402: determining whether the database meets the tuning trigger condition of the configuration parameter temp_buffers;

[0140] If yes, execute step S404;

[0141] Step S404: obtaining a recommended value of the configuration parameter temp_buffers, and determining whether a ratio between the current value and the recommended value is greater than a set ratio;

[0142] If yes, execute step S406;

[0143] Step S406: adjusting the value of temp_buffers according to the recommended value of the configuration parameter temp_buffers;

[0144] Step S408: Detect whether the proportion of the local block IO and the local cache hit rate of the database meets the corresponding critical conditions;

[0145] If not, execute step S410; if yes, execute step S414;

[0146] Step S410: determining whether the recommended value of the configuration parameter temp_buffers is the maximum recommended value of the configuration parameter temp_buffers;

[0147] If yes, the configuration parameter temp_buffers is no longer adjusted; if no, step S412 is executed;

[0148] Step S412: Update the recommended value of the configuration parameter temp_buffers, and then return to step S404;

[0149] Step S414: Set the value of the configuration parameter temp_buffers to its last adjusted value.

[0150] Take an application scenario as an example. Assume that the tuning trigger conditions for configuring the parameter temp_buffers in this scenario include:

[0151] The proportion of local block IO is greater than 5%, and the hit rate of local cache is less than 90%; or the local IO proportion / (local IO proportion + shared IO proportion) is greater than 25%, and the proportion of buffer file read wait events or buffer file write wait events in the database foreground is greater than 1%.

[0152] The above setting ratio is 75%, the maximum recommended value of temp_buffers is 256MB, and the default value of temp_buffers is 4MB.

[0153] Then, a query statement is constructed to perform a large amount of data query. The query statement constructed in this embodiment is:

[0154] create temporary table t2(id int); / / Create a temporary table named t2, which has an integer field id

[0155] select * from perf.create_snapshot(); / / Call the function create_snapshot, which is in the collection perf

[0156] select to_char(systimestamp,'yyyymmdd hh24:mi:ss.ff'); / / Format the current system timestamp (systimestamp) into a string in the format of yyyymmdd hh24:mi:ss.ff and return the time string

[0157] insert into t2 select generate_series(1,10000000); / / Insert 10,000,000 records into table t2

[0158] insert into t2 select generate_series(1,10000000); / / Insert 10,000,000 records into table t2

[0159] insert into t2 select generate_series(1,10000000); / / Insert 10,000,000 records into table t2

[0160] select count(*)from t2; / / Calculate and return the total number of records in table t2

[0161] select to_char(systimestamp,'yyyymmdd hh24:mi:ss.ff'); / / Get and format the current system timestamp

[0162] select * from perf.create_snapshot(); / / Call the function create_snapshot, which is in the collection perf

[0163] Test 1:

[0164] When the value of the configuration parameter temp_buffers is 4MB, you can get:

[0165] The proportion of local block IO is 96.16%, which is greater than 5%; and the proportion of local IO / (local IO + shared IO)>25, and the proportion of buffer file write wait events in the database frontend is 1.08%, which is greater than 1%. Therefore, it is determined that the tuning trigger conditions of the configuration parameter temp_buffers are met, and it is recommended to adjust the value of the configuration parameter Temp_buffers to its recommended value of 32MB. The duration of this process is 32.142316s-15.862865s=16.279451s.

[0166] Test 2:

[0167] When the value of the configuration parameter temp_buffers is 16MB, you can get:

[0168] The proportion of local block IO is 95.88%, which is greater than 5%; and the proportion of local IO / (local IO + shared IO)>25, and the proportion of buffer file write wait events in the database frontend is 1.12%, which is greater than 1%. Therefore, it is determined that the tuning trigger conditions of the configuration parameter temp_buffers are met, and it is recommended to adjust the value of the configuration parameter Temp_buffers to its recommended value of 32MB. The duration of this process is 58.087971s-41.759015s=16.328956s.

[0169] Test 3:

[0170] When the value of the configuration parameter temp_buffers is 32MB, you can get:

[0171] The proportion of local block IO is 95.48%, which is greater than 5%; and the proportion of local IO / (local IO + shared IO)>25, and the proportion of buffer file write wait events in the database frontend is 1.01%, which is greater than 1%. Therefore, it is determined that the tuning trigger conditions of the configuration parameter temp_buffers are met, and it is recommended to adjust the value of the configuration parameter Temp_buffers to its recommended value of 64MB. The duration of this process is 35.242947s-18.964669s=16.278278s.

[0172] Test 4:

[0173] When the value of the configuration parameter temp_buffers is 64MB, you can get:

[0174] The proportion of local block IO is 93.43%, which is greater than 5%; and the proportion of local IO / (local IO + shared IO)>25, the proportion of buffer file write wait events and buffer file read wait events in the database front-end are both less than 1%, which does not meet the tuning trigger conditions of the configuration parameter temp_buffers, so there is no need to adjust the value of temp_buffers. The duration of this process is 58.137140s-41.880469s=16.256671s.

[0175] According to the above content, when the value of the configuration parameter temp_buffers is adjusted from 32MB to 256MB, the proportion of database local block IO gradually decreases. Therefore, by adjusting the value of the configuration parameter temp_buffers, the performance of the database can be effectively improved.

[0176] In some embodiments of the present invention, the process of the method for dynamically adjusting the configuration parameters of a database of the present invention is as follows: Figure 5 As shown, the following steps are included:

[0177] Step S502: determine whether the database meets the tuning trigger condition of the configuration parameter bind_cpu;

[0178] If yes, execute step S504;

[0179] Step S504: Obtain the total number of CPU cores and the CPU usage of the database, and calculate the recommended value of the configuration parameter bind_cpu according to the total number of CPU cores and the CPU usage of the database;

[0180] Step S506: adjusting the value of the configuration parameter bind_cpu according to the recommended value of the configuration parameter bind_cpu;

[0181] Step S508: Detect whether the CPU usage of the database drops to a critical point;

[0182] If not, return to step S504; if so, stop adjusting the configuration parameter bind_cpu.

[0183] Taking an application scenario as an example, it is assumed that the configuration parameter bindcpulist is empty by default in the application scenario, and the tuning trigger conditions of the configuration parameter bind_cpu include: the usage rate of the database CPU is greater than 60%, and the number of active sessions is greater than 80% of the total number of CPU cores.

[0184] Test 1:

[0185] When the configuration parameter bindcpulist is empty by default, the database is tested with TPCC (Transaction Processing Performance Council-C Benchmark) and the test performance value is

[0186] tpmC(NewOrders)=139465.4

[0187] Through detection, it is found that the database CPU usage is greater than 60%, and the number of active sessions is greater than 80% of the total number of CPU cores, which meets the tuning trigger conditions of the configuration parameter bind_cpu. Since the configuration parameter bind_cpu is empty, the value of the configuration parameter bind_cpu is set to its recommended value 0-95.

[0188] Test 2:

[0189] When the bindcpulist parameter is set to '0-95', the database is tested with TPCC. The test performance value is

[0190] tpmC(NewOrders)=154078.2

[0191] The detection shows that the database CPU usage is 4.56%, which is less than 60%, and the number of active sessions is 4.61%, which is less than 80%. Therefore, there is no need to adjust the configuration parameter bind_cpu.

[0192] According to the above test examples, by binding the CPU of the database, the data processing energy of the database is significantly improved. Therefore, by adjusting the configuration parameter bindcpulist, the purpose of improving the performance of the database can be achieved.

[0193] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical ideas provided by the present embodiment method, additional changes may be made to the above method.

[0194] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0195] This embodiment also provides a computer program product 10 and a computer readable storage medium 20 . Figure 6 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 7 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned methods for dynamically adjusting the configuration parameters of a database when the computer program 11 is executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned methods for dynamically adjusting the configuration parameters of a database when the computer program 11 is executed by the processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0196] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0197] In the description of this embodiment, the computer program product 10 is a related product including the computer program 11 .

[0198] For the purpose of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which can be any device that can contain, store, communicate, propagate or use the computer program 11 for an instruction execution system, device or apparatus or in conjunction with these instruction execution systems, devices or apparatuses. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the above.

[0199] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for dynamically adjusting configuration parameters of a database, characterized in that: include: Obtain target configuration parameters, and obtain tuning trigger conditions of the target configuration parameters; Acquire performance data of the database, and determine whether the database triggers the tuning trigger condition according to the performance data; If so, a recommended value of the target configuration parameter is obtained, and the value of the target configuration parameter is adjusted according to the recommended value.

2. The method for dynamically adjusting configuration parameters according to claim 1, characterized in that: The step of obtaining the tuning trigger condition of the target configuration parameter includes: If the target configuration parameter includes work_mem, the performance data includes the proportion of temporary block IO of the database, and the buffer file read wait event and buffer file write wait event of the database foreground, and the tuning trigger condition includes: The proportion of the temporary block IO is greater than the first IO proportion threshold, and the proportion of the file read wait event or the buffer file write wait event is greater than the first set threshold; Alternatively, a proportion of the buffer file read wait event or the buffer file write wait event is greater than a second set threshold, wherein the first set threshold is less than the second set threshold.

3. The method for dynamically adjusting configuration parameters according to claim 1, characterized in that: The step of obtaining the tuning trigger condition of the target configuration parameter includes: If the target configuration parameter includes temp_buffers, the performance data includes the proportion of local block IO of the database, the hit rate of local cache, the proportion of shared IO, and the buffer file read wait event and buffer file write wait event of the database foreground, and the tuning trigger condition includes: The proportion of the local block IO is greater than the second IO proportion threshold, and the hit rate of the local cache is less than the preset hit rate threshold; Alternatively, the proportion of the local block IO and the proportion of the shared IO meet a preset condition, and the proportion of the buffer file read wait event or the buffer file write wait event is greater than a third set threshold.

4. The method for dynamically adjusting configuration parameters according to claim 2 or 3, characterized in that: The step of adjusting the value of the target configuration parameter according to the recommended value comprises: Obtaining a current value of the target configuration parameter, and determining whether a ratio between the current value and the recommended value is greater than a set ratio; If yes, setting the value of the target configuration parameter to the recommended value, and determining whether the performance parameter corresponding to the target configuration parameter satisfies a preset critical condition; If satisfied, stop further adjustment of the target configuration parameters; If not satisfied, the recommended value is updated, and then the process returns to the step of obtaining the current value of the target configuration parameter.

5. The method for dynamically adjusting configuration parameters according to claim 4, characterized in that: The step of updating the recommended value comprises: A set multiple of the recommended value is used as a new recommended value of the target configuration parameter, wherein the value of the set multiple is greater than 1.

6. The method for dynamically adjusting configuration parameters according to claim 4, characterized in that: Before the step of updating the recommended value, the method further includes: Determining whether the recommended value is a maximum recommended value of the target configuration parameter; If yes, return to the step of obtaining the current value of the target configuration parameter; If not, the step of updating the recommended value is performed.

7. The method for dynamically adjusting configuration parameters according to claim 1, characterized in that: The step of obtaining the tuning trigger condition of the target configuration parameter includes: If the target configuration parameter includes bindcpulist, the performance data includes the CPU usage and the number of active sessions of the database, and the tuning trigger condition includes: The CPU usage is greater than a preset usage threshold, and the number of active sessions is greater than a set percentage of the database CPU core.

8. The method for dynamically adjusting configuration parameters according to claim 7, characterized in that: The step of obtaining the recommended value of the target configuration parameter includes: The number of CPU cores and the CPU usage of the database are obtained, and the recommended value is calculated according to the number of CPU cores and the CPU usage.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method for dynamically adjusting configuration parameters according to any one of claims 1 to 8 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method for dynamically adjusting configuration parameters according to any one of claims 1 to 8 are implemented.

Images