Database parameter adjustment method and device, equipment, medium and program product

Abstract

The invention provides a database parameter adjusting method which can be applied to the technical field of computers, and the database parameter adjusting method comprises the steps that N groups of operation performance parameters pre-configured for a plurality of database instances are read from a configuration center, the N operation performance parameter groups are suitable for N different service scenes respectively, and N is a positive integer greater than or equal to 1; n is a positive integer greater than or equal to 2; obtaining performance index data related to the database instance; determining a target performance parameter group from the N operation performance parameter groups according to the performance index data; and adjusting the target performance parameters in the target performance parameter group according to a predetermined parameter adjustment strategy. The invention further provides a database parameter adjusting device and equipment, a storage medium and a program product.

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and more specifically, to a database parameter adjustment method, device, equipment, medium and program product. Background Art

[0002] A database instance is an operating environment that contains specific memory structures and background processes and is used to manage and operate a database. Currently, many database management systems (DBMS) use single-level parameter group management, that is, each database instance is associated with only one parameter group, and all parameter configurations are managed in this parameter group. This method is simple and easy to use, and configuration and management are relatively intuitive.

[0003] In the process of implementing the concept of the present disclosure, the inventors found that there are at least the following problems in the related art: in different application scenarios and time periods, only one set of fixed parameter settings can be used. However, different environments or time periods may require different parameter configurations, and a single-level parameter group cannot flexibly adjust parameters to meet the business needs of multiple scenarios. Summary of the invention

[0004] In view of this, the present disclosure provides a database parameter adjustment method, apparatus, device, medium and program product.

[0005] One aspect of the present disclosure provides a database parameter adjustment method, including: reading N groups of operating performance parameters pre-configured for multiple database instances from a configuration center, wherein the N operating performance parameter groups are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; obtaining performance indicator data related to the database instance; determining a target performance parameter group from the N operating performance parameter groups based on the performance indicator data; and adjusting a target performance parameter in the target performance parameter group according to a predetermined parameter adjustment strategy.

[0006] According to an embodiment of the present disclosure, the N operating performance parameter groups include M common parameter groups and NM specific parameter groups. The specific parameter groups and the common parameter groups have different operating performance parameter values. Multiple database instances share the M common parameter groups, where M is a positive integer greater than or equal to 1; and multiple database instances correspond one-to-one to the NM specific parameter groups.

[0007] According to an embodiment of the present disclosure, the target performance parameters in the target performance parameter group are adjusted according to a predetermined parameter adjustment strategy, including: when the target performance parameter group is a common parameter group, modifying the first initial performance parameter in the common parameter group so that the first initial performance parameters corresponding to multiple database instances are simultaneously updated to the first target performance parameters; when the target performance parameter group is a specific parameter group, modifying the second initial performance parameter in the specific parameter group so that the second initial performance parameters corresponding to each database instance are respectively updated to the second target performance parameters.

[0008] According to an embodiment of the present disclosure, the specific parameter group is one or more of an instance parameter group, a master-slave parameter group, or a temporary parameter group.

[0009] According to an embodiment of the present disclosure, the instance parameter group and the temporary parameter group have the same operating performance parameter fields and different operating performance parameter values ​​as the common parameter group to be suitable for different business scenarios, wherein the temporary parameter group is configured to be applied to the database instance within a predetermined time period.

[0010] According to an embodiment of the present disclosure, based on performance indicator data, a target performance parameter group is determined from N operating performance parameter groups according to a predetermined parameter adjustment strategy, including: based on the performance indicator data, determining a candidate performance parameter group to be adjusted from a common parameter group, an instance parameter group, a master-slave parameter group, and a temporary parameter group; in the case where there are multiple candidate performance parameter groups, determining a target performance parameter group and target performance parameters from multiple candidate performance parameter groups according to preset priorities.

[0011] According to an embodiment of the present disclosure, the preset priorities are, from high to low, a temporary parameter group, a master-slave parameter group, an instance parameter group, and a common parameter group.

[0012] According to an embodiment of the present disclosure, the method further includes: determining target configuration parameters from database configuration parameters related to the database instance according to the performance indicator data; and adjusting the target configuration parameters according to a predetermined parameter adjustment strategy.

[0013] Another aspect of the present disclosure provides a database parameter adjustment device, including: a reading module, used to read N groups of operating performance parameters pre-configured for multiple database instances from a configuration center, wherein the N operating performance parameter groups are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; an acquisition module, used to obtain performance indicator data related to the database instance; a first determination module, used to determine a target performance parameter group from the N operating performance parameter groups based on the performance indicator data and a predetermined parameter adjustment strategy; and a first adjustment module, used to adjust the target performance parameters in the target performance parameter group.

[0014] Another aspect of the present disclosure provides an electronic device, including: one or more processors; and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the above method.

[0015] Another aspect of the present disclosure provides a computer-readable storage medium storing computer-executable instructions, which are used to implement the above method when executed.

[0016] Another aspect of the present disclosure provides a computer program product, which includes computer executable instructions, and the instructions are used to implement the above method when executed.

[0017] According to an embodiment of the present disclosure, since a database instance needs to meet the requirements of multiple scenarios, a single single array cannot cope with parameter changes and balances in multiple business scenarios. For example, in a high-concurrency scenario, a large number of concurrent requests need to be responded to quickly, so the buffer pool should be set relatively large to cache as many data pages as possible, reduce disk I / O operations, and increase data access speed. Data analysis usually involves scanning and processing a large amount of data, and a larger buffer pool is required to cache data to increase data reading speed. At the same time, since data analysis tasks may take up a lot of memory, it is necessary to balance the buffer pool size with other memory requirements. Therefore, the embodiment of the present disclosure can achieve different configurations or adjustments for the same parameter by configuring multiple parameter groups suitable for different business scenarios. Thus, when the business environment changes, the parameter group can be directly switched to apply to the database instance. Compared with a single-level parameter group, it can adapt to the requirements for parameters in multiple business environments and different time periods, and improve database performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The above and other objects, features and advantages of the present disclosure will become more apparent through the following description of the embodiments of the present disclosure with reference to the accompanying drawings, in which:

[0019] Figure 1 An exemplary system architecture 100 to which a database parameter adjustment method according to an embodiment of the present disclosure can be applied is schematically shown;

[0020] Figure 2 A flowchart of a method for adjusting database parameters according to an embodiment of the present disclosure is schematically shown;

[0021] Figure 3 The flowchart of the target performance parameter adjustment method according to the embodiment of the present disclosure is schematically shown;

[0022] Figure 4 The flowchart of the method for adjusting database parameters according to another embodiment of the present disclosure is schematically shown;

[0023] Figure 5 A block diagram schematically shows a database parameter adjustment device according to an embodiment of the present disclosure; and

[0024] Figure 6 A block diagram of an electronic device suitable for implementing the database parameter adjustment method described above according to an embodiment of the present disclosure is schematically shown. DETAILED DESCRIPTION

[0025] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present disclosure. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present disclosure. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concepts of the present disclosure.

[0026] The terms used herein are only for describing specific embodiments and are not intended to limit the present disclosure. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0027] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0028] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0029] In the embodiments of the present disclosure, the collection, updating, analysis, processing, use, transmission, provision, disclosure, storage, etc. of the data involved (for example, including but not limited to user personal information) are in compliance with the provisions of relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. In particular, necessary measures are taken for user personal information to prevent illegal access to user personal information data and maintain the security of user personal information and network security.

[0030] In the embodiments of the present disclosure, the user's authorization or consent is obtained before obtaining or collecting the user's personal information.

[0031] When a database instance is configured with only one parameter group, it means that only this fixed set of parameter settings can be used in all situations. This may be sufficient in some simple application scenarios, but in complex scenarios, this lack of flexibility will cause many problems. For example, during the database development phase, frequent testing and debugging may be required. At this time, some parameters may need to be adjusted to optimize development efficiency, such as increasing the log level for better troubleshooting, or reducing the cache size to reduce memory usage so that more attention can be paid to the verification of code logic. The test environment needs to simulate the production environment as much as possible, but it must be convenient for various stress tests and abnormal situation tests. Different connection number settings, query timeouts, and other parameters may be required to meet testing requirements. The production environment usually requires high stability and high performance. Depending on the actual user load and data volume, it may be necessary to fine-tune the cache size, concurrency control parameters, etc. to ensure that the system can efficiently process business requests.

[0032] For example, during busy business hours, the database load will increase significantly. You may need to adjust parameters to improve concurrent processing capabilities, such as increasing the connection pool size, optimizing query execution plans, etc., to ensure that the system can quickly respond to customer requests and avoid performance bottlenecks. During low business hours, the system load is low, and you can consider adjusting some parameters to save resources, such as reducing the cache size, reducing the activity of background processes, etc., to reduce the system's energy consumption and resource usage.

[0033] Therefore, the method of configuring a parameter group for a database instance is difficult to adjust quickly and effectively when facing changes in requirements in different environments and time periods in complex scenarios, and thus cannot meet the requirements for database parameter flexibility in these scenarios.

[0034] In view of this, an embodiment of the present disclosure provides a database parameter adjustment method, including: reading N groups of operating performance parameters pre-configured for multiple database instances from a configuration center, wherein the N operating performance parameter groups are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; obtaining performance indicator data related to the database instance; determining a target performance parameter group from the N operating performance parameter groups based on the performance indicator data; and adjusting the target performance parameters in the target performance parameter group according to a predetermined parameter adjustment strategy.

[0035] Figure 1 The exemplary system architecture 100 to which the database parameter adjustment method according to the embodiment of the present disclosure can be applied is schematically shown. It should be noted that: Figure 1What is shown is merely an example of a system architecture to which the embodiments of the present disclosure can be applied, in order to help those skilled in the art understand the technical content of the present disclosure, but it does not mean that the embodiments of the present disclosure cannot be used in other devices, systems, environments or scenarios.

[0036] like Figure 1 As shown, the system architecture 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 is used to provide a medium for communication links between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired and / or wireless communication links, etc.

[0037] The user may use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 through the network 104 to receive or send messages, etc. Various communication client applications may be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, and / or social platform software, etc. (only for example).

[0038] The first terminal device 101, the second terminal device 102, and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0039] The server 105 may be a server that provides various services, such as a background management server (only as an example) that provides support for websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103. The background management server may analyze and process the received data such as user requests, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal device.

[0040] It should be noted that the database parameter adjustment method provided in the embodiment of the present disclosure can generally be executed by the server 105. Accordingly, the database parameter adjustment system provided in the embodiment of the present disclosure can generally be set in the server 105. The database parameter adjustment method provided in the embodiment of the present disclosure can also be executed by a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105. Correspondingly, the database parameter adjustment system provided in the embodiment of the present disclosure can also be set in a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105. Alternatively, the database parameter adjustment method provided in the embodiment of the present disclosure can also be executed by the first terminal device 101, the second terminal device 102 or the third terminal device 103, or can also be executed by other terminal devices that are different from the first terminal device 101, the second terminal device 102 or the third terminal device 103. Accordingly, the database parameter adjustment system provided in the embodiment of the present disclosure may also be arranged in the first terminal device 101, the second terminal device 102 or the third terminal device 103, or in other terminal devices different from the first terminal device 101, the second terminal device 102 or the third terminal device 103.

[0041] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0042] Figure 2 The flowchart of the database parameter adjustment method according to the embodiment of the present disclosure is schematically shown.

[0043] like Figure 2 As shown, the database parameter adjustment method includes operations S210 to S240.

[0044] In operation S210, N groups of operation performance parameters pre-configured for multiple database instances are read from a configuration center, where the N groups of operation performance parameters are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2.

[0045] According to an embodiment of the present disclosure, before operation S210, N operating performance parameter groups are pre-configured for each database instance in the configuration center.

[0046] One configuration method may be: determining at least one group of operating performance parameters based on historical operating performance parameters; based on the already configured operating performance parameters, copying N-1 operating performance parameter groups, and resetting the parameter values ​​in the N-1 operating performance parameter groups to 0.

[0047] In this case, the parameter configurations in each running performance parameter group are the same. The first parameter group is uniformly configured with parameter values ​​applicable to each database instance. The parameters in other parameter groups are configured to 0. When running the database instance, the parameter values ​​in other parameter groups can be adjusted according to the performance indicator data.

[0048] Another configuration method may be: obtaining multiple groups of historical performance indicator data corresponding to multiple business scenarios; and configuring N operating performance parameter groups according to historical operating performance parameters corresponding to the multiple groups of historical performance indicator data.

[0049] In this case, according to the historical experience, N operation performance parameter groups corresponding to N business scenarios are configured based on the operation performance parameters corresponding to multiple business scenarios and different performance indicator data. The parameter configurations in each operation performance parameter group are different. Multiple groups of basic parameter groups are pre-configured based on historical experience. When the application scenario or time period of the database instance changes, it is directly switched to other parameter groups. Compared with the case where the parameter configurations in each operation performance parameter group are the same, configuring different parameter configurations in each operation performance parameter group further improves the selectivity of parameters in the database instance, thereby improving database performance.

[0050] According to the embodiments of the present disclosure, in addition to the cases listed in the above embodiments, the pre-configured N groups of operating performance parameters can also be obtained based on historical experience. Exemplarily, according to the operating environment and performance indicator data of the historical database instance, the respective parameter fields and initial parameter values ​​are configured for different performance parameter groups.

[0051] According to an embodiment of the present disclosure, in operation S210, the preconfigured N groups of operating performance parameters may be read at any time, or may be read at a preset fixed time (eg, a set period), and so on.

[0052] According to an embodiment of the present disclosure, the operating performance parameters may include: buffer pool size, sort buffer size, number of connection threads, maximum number of connections, etc., which are not listed here one by one.

[0053] In operation S220, performance indicator data related to the database instance is obtained.

[0054] According to an embodiment of the present disclosure, performance indicator data may include: database response time, throughput, number of concurrent connections, resource utilization (such as CPU utilization, memory utilization, disk I / O, etc.) and specific business-related indicators (such as the number of transactions processed per second, execution time of a specific query, etc. Database monitoring tools, system performance monitoring software, etc. may be used to collect performance indicator data.

[0055] In operation S230, a target performance parameter group is determined from the N operating performance parameter groups according to the performance indicator data.

[0056] According to an embodiment of the present disclosure, based on the performance indicator data, according to the current application scenario and the collected performance indicator data, a target performance parameter group is determined from N operating performance parameter groups.

[0057] For example, for high-concurrency application scenarios, such as product query operations on e-commerce platforms, long response time will lead to a decline in user experience and affect sales. The time from user request to system response should be as short as possible. According to the amount of data analyzed and the access mode, the buffer pool size can be adjusted to increase the data reading speed. For large-scale data analysis, the buffer pool can be appropriately increased. For another example, the number of requests that the system can handle per unit time should be high, such as thousands or even tens of thousands of requests per second. By appropriately increasing this parameter, ensure that the system can accept more client connections at the same time. Alternatively, the connection timeout can be appropriately shortened to quickly release connection resources that have not responded for a long time, so that new connections can be established faster. And so on.

[0058] According to the embodiments of the present disclosure, since different operating performance parameter groups are respectively applicable to different business scenarios, after determining the current application scenario, one group of parameters can be determined as the target performance parameter group based on the current application scenario and historical experience, as well as in combination with the operating performance parameters.

[0059] In operation S240, the target performance parameters in the target performance parameter group are adjusted.

[0060] According to an embodiment of the present disclosure, based on the above analysis results and a predetermined parameter adjustment strategy, a target parameter to be adjusted in the target performance parameter group is determined, and an initial parameter value of the target parameter is modified to a target parameter value.

[0061] According to an embodiment of the present disclosure, since a database instance needs to meet the requirements of multiple scenarios, a single single array cannot cope with parameter changes and balances in multiple business scenarios. For example, in a high-concurrency scenario, a large number of concurrent requests need to be responded to quickly, so the buffer pool should be set relatively large to cache as many data pages as possible, reduce disk I / O operations, and increase data access speed. Data analysis usually involves scanning and processing a large amount of data, and a larger buffer pool is required to cache data to increase data reading speed. At the same time, since data analysis tasks may take up a lot of memory, it is necessary to balance the buffer pool size with other memory requirements. Therefore, the embodiment of the present disclosure can achieve different configurations or adjustments for the same parameter by configuring multiple parameter groups suitable for different business scenarios. Thus, when the business environment changes, the parameter group can be directly switched to apply to the database instance. Compared with a single-level parameter group, it can adapt to the requirements for parameters in multiple business environments and different time periods, and improve database performance.

[0062] According to an embodiment of the present disclosure, the N operating performance parameter groups include M common parameter groups and NM specific parameter groups. The specific parameter groups and the common parameter groups have different operating performance parameter values. Multiple database instances share the M common parameter groups, where M is a positive integer greater than or equal to 1; and multiple database instances correspond one-to-one to the NM specific parameter groups.

[0063] According to an embodiment of the present disclosure, a specific parameter group and a common parameter group may have the same operating performance parameters, or may have different operating performance parameters, but the specific parameter value configurations are different. The common parameter group is used to characterize that multiple database instances share this set of parameters. For example, multiple database instances share a common parameter group, and multiple database instances have the same parameter settings. In addition to the common parameter group, the operating performance parameter group may also include a specific parameter group, which is used to characterize a parameter group unique to each database instance. In the case of multiple database instances, it is only necessary to modify the specific parameter group corresponding to one of the database instances. The parameters of other database instances will not be affected, and other parameter values ​​will not be affected. The database performance is improved by adjusting the parameters within the smallest possible range.

[0064] Figure 3 The flowchart of the target performance parameter adjustment method according to the embodiment of the present disclosure is schematically shown.

[0065] like Figure 3 As shown, adjusting the target performance parameters in the target performance parameter group includes operations S310 to S320.

[0066] In operation S310, when the target performance parameter group is a common parameter group, the first initial performance parameter in the common parameter group is modified so that the first initial performance parameters corresponding to the plurality of database instances are simultaneously updated to the first target performance parameter.

[0067] According to the embodiments of the present disclosure, since multiple database instances share a common parameter group, that is, the parameter values ​​of the common parameter group in multiple database instances are the same, when the parameters in the common parameter group are modified, the relevant parameter values ​​of all database instances sharing the common parameter group are modified.

[0068] For example, when business needs change and the connection timeout of all instances needs to be adjusted to adapt to new performance requirements, users do not need to log in to each database instance separately to make individual modifications. They only need to perform one operation on the common parameter group in the configuration center. This greatly improves the efficiency of configuration management and reduces tedious repetitive work and possible errors.

[0069] In operation S320, when the target performance parameter group is a specific parameter group, the second initial performance parameters in the specific parameter group are modified so that the second initial performance parameters corresponding to each database instance are respectively updated to the second target performance parameters.

[0070] According to an embodiment of the present disclosure, since each database instance corresponds to a specific parameter group, that is, the parameter values ​​of the specific parameter group of each database instance can be different to adapt to different business scenarios or time periods. Each database instance may require unique parameter settings due to the specific applications, load characteristics or data characteristics of its service. With separate parameter groups, each instance can be fine-tuned to achieve optimal performance and stability. If one instance has a performance problem or error, other instances are not affected. The isolation of specific parameter groups makes troubleshooting and resolution easier, and problems will not spread due to shared parameters. As the business develops, the needs of database instances may change. Specific parameter groups can be easily adjusted to adapt to new loads, functional requirements or hardware environments without affecting other instances.

[0071] According to an embodiment of the present disclosure, the specific parameter group is one or more of an instance parameter group, a master-slave parameter group, or a temporary parameter group.

[0072] According to an embodiment of the present disclosure, the instance parameter group and the temporary parameter group have the same operating performance parameter fields and different operating performance parameter values ​​as the common parameter group to be suitable for different business scenarios, wherein the temporary parameter group is configured to be applied to the database instance within a predetermined time period.

[0073] According to an embodiment of the present disclosure, the parameters in the instance parameter group are the same as the parameters in the public parameter group but have different parameter values. They are set according to the characteristics of each database instance. The parameters in the master-slave parameter group are different from those in the public parameter group. The master-slave database is used to achieve data synchronization and backup, load balancing, provide data security and recovery mechanisms, and support system expansion to ensure stable and efficient operation of the database system. The parameters in the temporary parameter group are the same as the parameters in the public parameter group but have different parameter values, and the application of the temporary parameter group is configured with time conditions. Based on historical experience, the parameter values ​​are set within a preset time period. For example, in an e-commerce platform, due to discounts or price cuts, a larger maximum number of connections is set to cope with high concurrent connections caused by a large number of users accessing at the same time during an event.

[0074] Taking MySQL database as an example, the master-slave parameter group includes the sync_binlog parameter, which is an important parameter used in MySQL database to control the frequency of binary log synchronization to disk, and control data consistency, recovery, and master-slave replication reliability. When setting this parameter, you need to make a trade-off based on the specific business scenario and performance requirements.

[0075] If sync_binlog is set to 0, the MySQL server will not synchronize the binary log to disk, but rely on the operating system's cache mechanism to refresh the log. This setting can achieve higher performance, but some binary log content may be lost when the server crashes.

[0076] If sync_binlog is set to 1, the MySQL server will synchronize the binary log to disk each time a transaction is committed. This is the safest setting, but it may have some impact on performance, especially in a high-concurrency environment.

[0077] You can also set sync_binlog to a value greater than 1, which means that the binary log is synchronized to disk after every N transactions are committed. This setting strikes a balance between performance and data security.

[0078] According to an embodiment of the present disclosure, the master-slave parameter group may further include: innodb_flush_log_at_trx_commit, log_slave_updates, slave_compressed_protocol, slave_net_timeout, slave_heartbeat_period, slave_parallel_workers, etc., which are not listed here one by one.

[0079] According to an embodiment of the present disclosure, based on the performance indicator data, a target performance parameter group is determined from N operating performance parameter groups according to a predetermined parameter adjustment strategy, including steps 11 and 12.

[0080] In step 11, according to the performance indicator data, a candidate performance parameter group to be adjusted is determined from the common parameter group, the instance parameter group, the master-slave parameter group, and the temporary parameter group.

[0081] In step 12, when there are multiple candidate performance parameter groups, a target performance parameter group and target performance parameters are determined from the multiple candidate performance parameter groups according to preset priorities.

[0082] According to the embodiments of the present disclosure, the business scenario of the current database instance is determined, and the operating performance parameters corresponding to the business scenario are determined based on historical experience and a predetermined parameter adjustment strategy. Since the parameter fields in the instance parameter group and the master-slave parameter group are different, for the same business scenario, adjusting the instance parameter group or adjusting the master-slave parameter group can achieve optimization of database performance. At this time, a set of operating performance parameter groups is selected according to a pre-set priority mechanism to be applied to the database instance.

[0083] According to the embodiments of the present disclosure, the preset priorities are from high to low: temporary parameter group, master-slave parameter group, instance parameter group and common parameter group. Since the temporary parameter group has a time characteristic, it has the highest priority and is used to deal with emergencies or temporary needs. By setting a priority mechanism, when there are multiple candidate performance parameter groups, the preferred parameters can be selected and applied to the database. This ensures that the system can automatically select the optimal parameter configuration under different levels of requirements and optimization strategies.

[0084] According to the embodiments of the present disclosure, in addition to adjusting the operating performance parameters configured by the user in the configuration center, the database performance can also be further improved by adjusting the database configuration parameters provided by the cloud service provider.

[0085] Figure 4 A flowchart of a method for adjusting database parameters according to another embodiment of the present disclosure is schematically shown.

[0086] like Figure 4 As shown, the database parameter adjustment method includes operations S410 to S420.

[0087] In operation S410, target configuration parameters are determined from database configuration parameters related to the database instance according to the performance indicator data.

[0088] In operation S420, the target configuration parameters are adjusted according to a predetermined parameter adjustment strategy.

[0089] According to an embodiment of the present disclosure, the real-time monitoring service provided by the cloud service is utilized to monitor the performance indicator data of the database instance. When the performance indicator data reaches or exceeds the set threshold, the target configuration parameters to be modified are automatically determined, and the adjustment of the database configuration parameters is triggered.

[0090] Among them, performance indicators include CPU usage, memory usage, disk I / O, number of connections, etc. Set reasonable thresholds based on the actual usage and performance requirements of the database. For example, set the CPU usage exceeding 80% as the threshold to trigger adjustment. When the performance indicator data reaches or exceeds the set threshold, the target configuration parameters are automatically modified. For example, when the CPU usage exceeds the threshold, increase the database cache size; when the number of connections is too large, adjust the maximum number of connections parameter, etc. The appropriate adjustment strategy can be determined based on the specific database type and performance indicators.

[0091] Taking MySQL database as an example, the database configuration parameters include: basedir, datadir, port, socket, character-set-server, pid-file, log-error, tmpdir, key_buffer_size, table_open_cache, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, max_heap_table_size, tmp_table_size, log_bin, log_bin_trust_function_creators, slow_query_log, long_query_time, etc.

[0092] For example, when the time it takes for the database to execute a query statement exceeds a preset threshold, it is considered a slow query. Slow queries may cause system performance to degrade, affect user experience, and even paralyze the database server in high concurrency situations. For slow query processing: If there are more than 10 slow queries within 1 minute, perform the following steps: Check whether the CPU usage has reached 90%; Check whether the memory usage has reached 90%; Check whether the IOPS has reached the preset upper limit; Adjust the target configuration parameters based on the inspection results.

[0093] For example, for CPU usage, if the CPU usage does not exceed 90% for a continuous period of time (for example, 5 minutes), increase innodb_thread_concurrency. innodb_thread_concurrency = innodb_thread_concurrency + 2, which cannot exceed about twice the number of CPU cores.

[0094] For example, for memory usage optimization, if the memory usage does not exceed 90% in a continuous period of time (for example, 5 minutes), increase innodb_buffer_pool_size appropriately. innodb_buffer_pool_size = innodb_buffer_pool_size × 1.1, which cannot exceed 80% of the total memory.

[0095] For example, IOPS reflects the storage system's ability to handle read and write requests. For example, when a database reads and writes data, it sends I / O requests to the storage device, and the speed at which the storage device responds to these requests is measured in IOPS. For the number of read and write (I / O) operations, if the IOPS reaches the preset upper threshold, the system will prompt the user to add more disk space or select a higher-performance storage device.

[0096] For example, for the number of database connections, when the number of connections is full and the user connection fails, max_connections can be appropriately increased. max_connections = max_connections + 50, and innodb_buffer_pool_size needs to be adjusted to prevent the memory from being full. For example, in a high-concurrency online shopping system, if a large number of users access the system at the same time, and each user's operation requires a connection to the database, the number of database connections may quickly reach the upper limit and be full.

[0097] Exemplarily, for master-slave latency optimization, when the slave latency exceeds 300 seconds, set the innodb_flush_log_at_trx_commit parameter to 2, allowing the InnoDB engine to write logs only to the operating system's log buffer instead of writing them immediately to disk. Set the sync_binlog parameter to 0, allowing MySQL to rely on the operating system's buffering mechanism when writing binary logs, rather than synchronizing to disk every time it is written. When the slave latency drops to less than 60 seconds, change the innodb_flush_log_at_trx_commit parameter back to 1 to ensure that the logs are written to disk after each transaction is committed. The sync_binlog parameter remains at 0 for better performance.

[0098] The cloud platform's monitoring service tracks database performance indicator data in real time; when performance indicators exceed the predetermined range, an alarm is triggered and the target configuration parameters are adjusted using historical experience and predetermined parameter adjustment strategies. The cloud management platform automatically adjusts the parameters related to the database instance through the pre-set parameter adjustment strategy, without the need for users to make judgments and manual modifications. This ensures the stability and security of the system while reducing the risks caused by human intervention.

[0099] Figure 5 A block diagram of a database parameter adjustment device according to an embodiment of the present disclosure is schematically shown.

[0100] like Figure 5 As shown, the database parameter adjustment device 500 includes a reading module 510 , an acquisition module 520 , a first determination module 530 , and a first adjustment module 540 .

[0101] The reading module 510 is used to read N groups of operating performance parameters pre-configured for multiple database instances from the configuration center, where the N operating performance parameter groups are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; the acquisition module 520 is used to obtain performance indicator data related to the database instance; the first determination module 530 is used to determine the target performance parameter group from the N operating performance parameter groups based on the performance indicator data; the first adjustment module 540 is used to adjust the target performance parameters in the target performance parameter group according to a predetermined parameter adjustment strategy.

[0102] According to the embodiments of the present disclosure, the embodiments of the present disclosure can realize different configurations or adjustments for the same parameter by reading multiple parameter groups pre-configured in the configuration center and applicable to different business scenarios through a reading module. Therefore, when the business environment changes, the parameter group can be directly switched to apply to the database instance. Compared with a single-level parameter group, it can adapt to the requirements of parameters in multiple business environments and different time periods, and improve database performance.

[0103] According to an embodiment of the present disclosure, the N operation performance parameter groups include M common parameter groups and NM specific parameter groups, and the specific parameter groups and the common parameter groups have different operation performance parameter values.

[0104] Multiple database instances share M common parameter groups, where M is a positive integer greater than or equal to 1; and multiple database instances correspond one-to-one to NM specific parameter groups.

[0105] According to an embodiment of the present disclosure, the first adjustment module 540 includes a first modification submodule and a second modification submodule.

[0106] The first modification submodule is used to modify the first initial performance parameter in the common parameter group when the target performance parameter group is a common parameter group, so that the first initial performance parameters corresponding to multiple database instances are simultaneously updated to the first target performance parameters; the second modification submodule is used to modify the second initial performance parameter in the specific parameter group when the target performance parameter group is a specific parameter group, so that the second initial performance parameters corresponding to each database instance are respectively updated to the second target performance parameters.

[0107] According to an embodiment of the present disclosure, the specific parameter group is one or more of an instance parameter group, a master-slave parameter group, or a temporary parameter group.

[0108] According to an embodiment of the present disclosure, the instance parameter group and the temporary parameter group have the same operating performance parameter fields and different operating performance parameter values ​​as the common parameter group to be suitable for different business scenarios, wherein the temporary parameter group is configured to be applied to the database instance within a predetermined time period.

[0109] According to an embodiment of the present disclosure, the first determination module 530 includes a first determination submodule and a second determination submodule.

[0110] The first determination submodule is used to determine the candidate performance parameter group to be adjusted from the common parameter group, the instance parameter group, the master-slave parameter group, and the temporary parameter group according to the performance indicator data; the second determination submodule is used to determine the target performance parameter group and the target performance parameter from the multiple candidate performance parameter groups according to the preset priority when there are multiple candidate performance parameter groups.

[0111] According to an embodiment of the present disclosure, the preset priorities are, from high to low, a temporary parameter group, a master-slave parameter group, an instance parameter group, and a common parameter group.

[0112] According to an embodiment of the present disclosure, the device also includes: a second determination module, used to determine the target configuration parameters from the database configuration parameters related to the database instance according to the performance indicator data; a second adjustment module, used to adjust the target configuration parameters according to a predetermined parameter adjustment strategy.

[0113] According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits, or at least part of the functions of any one of them can be implemented in one module. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be split into multiple modules for implementation. According to the embodiments of the present invention, any one or more of the modules, submodules, units, and subunits can be at least partially implemented as hardware circuits, such as field programmable gate arrays (FPGAs), programmable logic arrays (PLAs), systems on chips, systems on substrates, systems on packages, application specific integrated circuits (ASICs), or can be implemented by hardware or firmware in any other reasonable way of integrating or packaging circuits, or can be implemented in any one of the three implementation methods of software, hardware, and firmware, or in any appropriate combination of any of them. Alternatively, according to the embodiments of the present invention, one or more of the modules, submodules, units, and subunits can be at least partially implemented as computer program modules, and when the computer program modules are run, the corresponding functions can be performed.

[0114] For example, any of the reading module 510, the reading module 510, the acquisition module 520, the first determination module 530, and the first adjustment module 540 can be combined in one module / unit / sub-unit for implementation, or any of the modules / units / sub-units can be split into multiple modules / units / sub-units. Alternatively, at least part of the functions of one or more of these modules / units / sub-units can be combined with at least part of the functions of other modules / units / sub-units and implemented in one module / unit / sub-unit. According to an embodiment of the present disclosure, at least one of the reading module 510, the acquisition module 520, the first determination module 530, and the first adjustment module 540 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or by any one of the three implementation methods of software, hardware, and firmware, or by a suitable combination of any of them. Alternatively, at least one of the reading module 510, the acquiring module 520, the first determining module 530, and the first adjusting module 540 may be at least partially implemented as a computer program module, and when the computer program module is executed, a corresponding function may be executed.

[0115] Figure 6 A block diagram of an electronic device suitable for implementing the database parameter adjustment method described above according to an embodiment of the present disclosure is schematically shown.

[0116] Figure 6 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

[0117] like Figure 6 As shown, the electronic device 600 according to an embodiment of the present disclosure includes a processor 601, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 602 or a program loaded from a storage part 608 to a random access memory (RAM) 603. The processor 601 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 601 may also include an onboard memory for caching purposes. The processor 601 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present disclosure.

[0118] In RAM 603, various programs and data required for the operation of electronic device 600 are stored. Processor 601, ROM 602 and RAM 603 are connected to each other through bus 609. Processor 601 performs various operations of the method flow according to the embodiment of the present disclosure by executing the program in ROM 602 and / or RAM 603. It should be noted that the program can also be stored in one or more memories other than ROM 602 and RAM 603. Processor 601 can also perform various operations of the method flow according to the embodiment of the present disclosure by executing the program stored in one or more memories.

[0119] According to an embodiment of the present disclosure, the electronic device 600 may further include an input / output (I / O) interface 605, which is also connected to the bus 609. The electronic device 600 may further include one or more of the following components connected to the input / output (I / O) interface 605: an input portion 606 including a keyboard, a mouse, etc.; an output portion 607 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 608 including a hard disk, etc.; and a communication portion 609 including a network interface card such as a LAN card, a modem, etc. The communication portion 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the input / output (I / O) interface 605 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed, so that a computer program read therefrom is installed into the storage portion 608 as needed.

[0120] According to an embodiment of the present disclosure, the method flow according to an embodiment of the present disclosure can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a computer-readable storage medium, and the computer program contains a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 609, and / or installed from the removable medium 611. When the computer program is executed by the processor 601, the above-mentioned functions defined in the system of the embodiment of the present disclosure are executed. According to an embodiment of the present disclosure, the system, equipment, device, module, unit, etc. described above can be implemented by a computer program module.

[0121] The present disclosure also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present disclosure is implemented.

[0122] According to an embodiment of the present disclosure, the computer-readable storage medium may be a non-volatile computer-readable storage medium. For example, it may include, but is not limited to: 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 portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in combination with an instruction execution system, apparatus, or device.

[0123] For example, according to an embodiment of the present disclosure, the computer-readable storage medium may include the ROM 602 and / or the RAM 603 described above and / or one or more memories other than the ROM 602 and the RAM 603 .

[0124] An embodiment of the present disclosure also includes a computer program product, which includes a computer program, and the computer program contains a program code for executing the method provided by the embodiment of the present disclosure. When the computer program product runs on an electronic device, the program code is used to enable the electronic device to implement the database parameter adjustment method provided by the embodiment of the present disclosure.

[0125] When the computer program is executed by the processor 601, the above functions defined in the system / device of the embodiment of the present disclosure are executed. According to the embodiment of the present disclosure, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0126] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 609, and / or installed from a removable medium 611. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0127] According to an embodiment of the present disclosure, the program code for executing the computer program provided by the embodiment of the present disclosure can be written in any combination of one or more programming languages. Specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect through the Internet).

[0128] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each box in the flowchart or block diagram may represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of the code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box may also occur in an order different from that marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions. It can be understood by those skilled in the art that the features recorded in the various embodiments of the present disclosure can be combined and / or combined in a variety of ways, even if such a combination or combination is not explicitly recorded in the present disclosure. In particular, without departing from the spirit and teaching of the present disclosure, the features described in the various embodiments of the present disclosure may be combined and / or combined in a variety of ways. All of these combinations and / or combinations fall within the scope of the present disclosure.

[0129] The embodiments of the present disclosure are described above. However, these embodiments are only for illustrative purposes and are not intended to limit the scope of the present disclosure. Although the embodiments are described above, this does not mean that the measures in the various embodiments cannot be used in combination to advantage. Without departing from the scope of the present disclosure, those skilled in the art may make a variety of substitutions and modifications, which should all fall within the scope of the present disclosure.

Claims

1. A method for adjusting database parameters, comprising: Read N groups of pre-configured operating performance parameters for multiple database instances from the configuration center, where the N groups of operating performance parameters are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; Obtaining performance indicator data related to the database instance; Based on the performance indicator data, determining a target performance parameter group from the N operating performance parameter groups according to a predetermined parameter adjustment strategy; The target performance parameters in the target performance parameter group are adjusted.

2. The method according to claim 1, wherein: The N operation performance parameter groups include M common parameter groups and NM specific parameter groups, and the specific parameter groups have different operation performance parameter values ​​from the common parameter groups. A plurality of the database instances share M common parameter groups, where M is a positive integer greater than or equal to 1; The multiple database instances correspond one-to-one to the NM specific parameter groups.

3. The method according to claim 2, wherein: Adjusting the target performance parameters in the target performance parameter group includes: When the target performance parameter group is the common parameter group, modifying the first initial performance parameter in the common parameter group so that the first initial performance parameters corresponding to the plurality of database instances are simultaneously updated to the first target performance parameter; When the target performance parameter group is the specific parameter group, the second initial performance parameters in the specific parameter group are modified so that the second initial performance parameters corresponding to each of the database instances are respectively updated to second target performance parameters.

4. The method according to claim 2, wherein: The specific parameter group is one or more of an instance parameter group, a master-slave parameter group or a temporary parameter group.

5. The method according to claim 4, wherein: The instance parameter group and the temporary parameter group have the same operating performance parameter fields and different operating performance parameter values ​​as the common parameter group to be applicable to different business scenarios, wherein the temporary parameter group is configured to be applied to the database instance within a predetermined time period.

6. The method according to claim 4, wherein: Based on the performance indicator data, determining a target performance parameter group from the N operating performance parameter groups according to a predetermined parameter adjustment strategy includes: Determine, according to the performance indicator data, a candidate performance parameter group to be adjusted from the common parameter group, the instance parameter group, the master-slave parameter group, and the temporary parameter group; In the case that there are multiple candidate performance parameter groups, the target performance parameter group and the target performance parameters are determined from the multiple candidate performance parameter groups according to preset priorities.

7. The method according to claim 6, wherein: The preset priorities are, from high to low, a temporary parameter group, a master-slave parameter group, an instance parameter group, and a common parameter group.

8. The method according to claim 1, further comprising: Determining target configuration parameters from database configuration parameters related to the database instance according to the performance indicator data; The target configuration parameters are adjusted according to the predetermined parameter adjustment strategy.

9. A database parameter adjustment device, comprising: A reading module is used to read N groups of operation performance parameters pre-configured for multiple database instances from a configuration center, where the N groups of operation performance parameters are respectively applicable to N different business scenarios, and N is a positive integer greater than or equal to 2; An acquisition module, used to acquire performance indicator data related to the database instance; A first determination module, configured to determine a target performance parameter group from the N operating performance parameter groups based on the performance indicator data and according to a predetermined parameter adjustment strategy; The first adjustment module is used to adjust the target performance parameters in the target performance parameter group.

10. An electronic device comprising: one or more processors; a memory for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 8.

11. A computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, enables the processor to implement the method according to any one of claims 1 to 8.

12. A computer program product comprising a computer program, which, when executed by a processor, implements the method according to any one of claims 1 to 8.

Images