CN116594981A - Database system parameter optimization method and device and electronic equipment - Google Patents
Database system parameter optimization method and device and electronic equipment Download PDFInfo
- Publication number
- CN116594981A CN116594981A CN202310565219.5A CN202310565219A CN116594981A CN 116594981 A CN116594981 A CN 116594981A CN 202310565219 A CN202310565219 A CN 202310565219A CN 116594981 A CN116594981 A CN 116594981A
- Authority
- CN
- China
- Prior art keywords
- parameter
- target
- parameter value
- database system
- name
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 48
- 238000005457 optimization Methods 0.000 title claims abstract description 25
- 238000012360 testing method Methods 0.000 claims abstract description 71
- 238000010998 test method Methods 0.000 claims abstract description 21
- 230000015654 memory Effects 0.000 claims description 58
- 238000009530 blood pressure measurement Methods 0.000 claims description 29
- 238000004590 computer program Methods 0.000 claims description 9
- 238000003672 processing method Methods 0.000 claims description 8
- 238000012545 processing Methods 0.000 claims description 7
- 238000011056 performance test Methods 0.000 claims description 4
- 230000008676 import Effects 0.000 claims description 3
- 239000000758 substrate Substances 0.000 claims 1
- 238000010801 machine learning Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 3
- 238000012986 modification Methods 0.000 description 3
- 230000004048 modification Effects 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 239000007787 solid Substances 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 238000005259 measurement Methods 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 238000003491 array Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 125000004122 cyclic group Chemical group 0.000 description 1
- 230000001419 dependent effect Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000010295 mobile communication Methods 0.000 description 1
- 230000003252 repetitive effect Effects 0.000 description 1
- 238000012163 sequencing technique Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/36—Preventing errors by testing or debugging software
- G06F11/3668—Software testing
- G06F11/3672—Test management
- G06F11/3688—Test management for test execution, e.g. scheduling of test suites
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Computer Hardware Design (AREA)
- Quality & Reliability (AREA)
- Stored Programmes (AREA)
Abstract
The invention discloses a database system parameter optimization method, a device and electronic equipment, wherein each parameter value set in a plurality of parameter value sets corresponding to a first parameter name set of an obtained database system to be tested is arranged and combined to obtain a target parameter value set, further, a target two-dimensional table generated by the target parameter value set is utilized to optimize a parameter value corresponding to each parameter name in the first parameter name set of the database system to be tested, meanwhile, an automatic test method is utilized to test the database system to be tested, and the target database parameter value of the database system to be tested is determined according to a test result. Therefore, by implementing the invention, the parameter tuning of the database system to be tested is performed based on the configuration data of the server, thereby realizing automatic test, reducing human intervention and reducing DBA workload.
Description
Technical Field
The present invention relates to the field of database systems, and in particular, to a method and an apparatus for optimizing parameters of a database system, and an electronic device.
Background
In the information age, the use of database systems is ubiquitous. The data structure is diversified, the business is complicated, and the data structure and the business all put higher requirements on the database system, and the good configuration parameters ensure the basis of the operation of the database system. Existing database parameter optimization methods either employ machine learning based algorithms for tuning or manually by an experienced database administrator (DBA). Wherein, the algorithm tuning based on machine learning has the problem of over-reliance on the measured parameter data set and the machine learning algorithm model. Whereas manual tuning often requires a database administrator with very high experience. Meanwhile, the method is time-consuming, labor-consuming and redundant work repeated for DBA, and the quality of configuration parameters is greatly different due to uneven DBA experience and technology.
Disclosure of Invention
In view of the above, the embodiments of the present invention provide a method, an apparatus, and an electronic device for optimizing database system parameters, so as to solve the technical problems in the prior art that, when database parameters are optimized, the database parameters are excessively dependent on a measured parameter data set and a machine learning algorithm model, the DBA workload is large, and the quality of configuration parameters has a huge difference.
The technical scheme provided by the invention is as follows:
in a first aspect, an embodiment of the present invention provides a method for optimizing parameters of a database system, where the database system is disposed in a server; the database system parameter optimization method comprises the following steps: acquiring a parameter name set of a database system to be tested and a server parameter set of the server; determining a plurality of parameter value sets corresponding to the parameter name sets based on the server parameter sets; each parameter value set is arranged and combined to obtain a target parameter value set, and a target two-dimensional table is generated based on the target parameter value set; and optimizing by an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested.
With reference to the first aspect, in a possible implementation manner of the first aspect, the server includes a central processor and a memory; the parameter name set comprises a first parameter name related to the central processor, a second parameter name related to the memory and a database connection number name in the database system to be tested, and the server parameter set comprises the core number of the central processor and the memory size of the memory.
With reference to the first aspect, in another possible implementation manner of the first aspect, determining, based on the server parameter set, a plurality of parameter value sets corresponding to the parameter name set includes: determining a first parameter value set corresponding to the first parameter name based on the number of cores; determining a second parameter value set corresponding to the second parameter name based on the memory size; determining a database connection value set based on the database connection number name; the plurality of parameter value sets is determined based on the first parameter value set, the second parameter value set, and the database connection value set.
With reference to the first aspect, in a further possible implementation manner of the first aspect, permutation and combination are performed on each of the parameter value sets to obtain a target parameter value set, including: and arranging and combining the first parameter value set, the second parameter value set and the database connection value set to obtain the target parameter value set, wherein the target parameter value set comprises a plurality of groups of target parameter values, and each group of target parameter values comprises a first parameter value, a second parameter value and a database connection value.
With reference to the first aspect, in a further possible implementation manner of the first aspect, optimizing by an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested, including: based on the target two-dimensional table and the parameter name set, performing pressure measurement on the database system to be tested by using an automatic test method to obtain at least one pressure measurement result; determining a target pressure measurement result based on each pressure measurement result; and determining the target database parameter value of the database system to be tested based on the target pressure measurement result.
With reference to the first aspect, in a further possible implementation manner of the first aspect, based on the target two-dimensional table and the parameter name set, performing a pressure measurement on the database system to be tested by using an automatic test method to obtain at least one pressure measurement result, where the pressure measurement result includes: acquiring a tool to be pressed, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set; replacing the initial parameter value corresponding to each parameter name in the parameter name set with each target parameter value in a first group of target parameter values to obtain a preset parameter value corresponding to each parameter name in the parameter name set; generating a test instruction by using the tool to be pressed based on a preset parameter value of each parameter; under the control of the test instruction, the automatic test method is utilized to carry out pressure test on the database system to be tested; and repeating the steps of acquiring a tool to be tested, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set to the step of testing the database system to be tested by using the automatic testing method under the control of the testing instruction until each group of target parameter values in the target two-dimensional table are acquired, and obtaining at least one testing result.
With reference to the first aspect, in a further possible implementation manner of the first aspect, the test instruction includes a library building instruction, a table building instruction, a data import instruction, a create index instruction, and a performance test instruction.
In a second aspect, an embodiment of the present invention provides a database system parameter optimization apparatus, where the database system is disposed in a server; the database system parameter optimizing device comprises: the acquisition module is used for acquiring a parameter name set of the database system to be tested and a server parameter set of the server; the determining module is used for determining a plurality of parameter value sets corresponding to the parameter name sets based on the server parameter sets; the arrangement and generation module is used for carrying out arrangement and combination on each parameter value set to obtain a target parameter value set, and generating a target two-dimensional table based on the target parameter value set; and the optimization module is used for optimizing through an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested.
In a third aspect, an embodiment of the present invention provides a computer readable storage medium, where a computer program is stored, where the computer program is configured to cause the computer to perform the method for optimizing parameters of a database system according to any one of the first aspect and the first aspect of the embodiment of the present invention.
In a fourth aspect, an embodiment of the present invention provides an electronic device, including: the system comprises a memory and a processor, wherein the memory and the processor are in communication connection, the memory stores a computer program, and the processor executes the computer program to execute the database system parameter optimization method according to any one of the first aspect and the first aspect of the embodiment of the invention.
The technical scheme provided by the invention has the following effects:
according to the database system parameter optimization method provided by the embodiment of the invention, each parameter value set in a plurality of parameter value sets corresponding to the first parameter name set of the obtained database system to be tested is arranged and combined to obtain the target parameter value set, further, the parameter value corresponding to each parameter name in the first parameter name set of the database system to be tested is optimized by utilizing the target two-dimensional table generated by the target parameter value set, meanwhile, the database system to be tested is tested by utilizing an automatic test method, and the target database parameter value of the database system to be tested is determined according to the test result. Therefore, by implementing the invention, the parameter tuning of the database system to be tested is performed based on the configuration data of the server, thereby realizing automatic test, reducing human intervention and reducing DBA workload.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings that are needed in the description of the embodiments or the prior art will be briefly described, and it is obvious that the drawings in the description below are some embodiments of the present invention, and other drawings can be obtained according to the drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flow chart of a database system parameter optimization method provided in accordance with an embodiment of the present invention;
FIG. 2 is a flowchart of a database system parameter optimization method based on server configuration according to an embodiment of the present invention;
FIG. 3 is a block diagram of a database system parameter optimizing apparatus according to an embodiment of the present invention;
FIG. 4 is a schematic diagram of a computer-readable storage medium provided according to an embodiment of the present invention;
fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present invention.
Detailed Description
For the purpose of making the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention, and it is apparent that the described embodiments are some embodiments of the present invention, but not all embodiments of the present invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
It should be noted that the terms "first," "second," and the like in the description and the claims of the present invention and the above figures are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate such that the embodiments of the invention described herein may be implemented in sequences other than those illustrated or otherwise described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
The embodiment of the invention provides a database system parameter optimization method, wherein a database system is arranged in a server; as shown in fig. 1, the method comprises the steps of:
step 101: and acquiring a parameter name set of the database system to be tested and a server parameter set of the server.
Wherein the parameter name set reflects database parameter names of the database system to be tested.
Step 102: and determining a plurality of parameter value sets corresponding to the parameter name sets based on the server parameter sets.
Specifically, based on configuration data of the server, namely the server parameter set, the parameter value corresponding to each parameter name in the parameter name set of the database system to be tested is optimized.
Step 103: and arranging and combining each parameter value set to obtain a target parameter value set, and generating a target two-dimensional table based on the target parameter value set.
Specifically, each parameter value set is arranged and combined to obtain a plurality of groups of parameter values in the target parameter value set, and a target two-dimensional table is generated according to each group of parameter values.
Step 104: and optimizing by an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested.
Specifically, each group of parameter values in the target two-dimensional table is utilized to replace an initial parameter value corresponding to each parameter name in the parameter name set, and the automatic testing method is utilized to conduct pressure testing on the database system to be tested until the target database parameter value of the database system to be tested is obtained.
According to the database system parameter optimization method provided by the embodiment of the invention, each parameter value set in a plurality of parameter value sets corresponding to the first parameter name set of the obtained database system to be tested is arranged and combined to obtain the target parameter value set, further, the parameter value corresponding to each parameter name in the first parameter name set of the database system to be tested is optimized by utilizing the target two-dimensional table generated by the target parameter value set, meanwhile, the database system to be tested is tested by utilizing an automatic test method, and the target database parameter value of the database system to be tested is determined according to the test result. Therefore, by implementing the invention, the parameter tuning of the database system to be tested is performed based on the configuration data of the server, thereby realizing automatic test, reducing human intervention and reducing DBA workload.
As an optional implementation manner of the embodiment of the present invention, the server includes a central processing unit and a memory; the parameter name set comprises a first parameter name related to the central processor, a second parameter name related to the memory and a database connection number name in the database system to be tested, and the server parameter set comprises the core number of the central processor and the memory size of the memory.
Specifically, when obtaining parameter names related to a Central Processing Unit (CPU) and a memory in a server of a database system to be tested, according to a selected database system to be tested, the parameter names of the databases are different, for example, a first parameter name related to the CPU in MySQL is an inpodib_thread_con currency, and a second parameter name related to the memory is an inpodib_buffer_pool_size; the first parameter related to CPU in the dream database is named WORKER_THREADS, TASK_THREADS, IO_THR_GROUPS, and the second parameter related to MEMORY is named MEMORY_ POOL, BUFFER, MAX _BUFFER.
Further, the parameter set-related parameters also include database connection number names.
As an alternative implementation of the embodiment of the present invention, step 102 includes: determining a first parameter value set corresponding to the first parameter name based on the number of cores; determining a second parameter value set corresponding to the second parameter name based on the memory size; determining a database connection value set based on the database connection number name; the plurality of parameter value sets is determined based on the first parameter value set, the second parameter value set, and the database connection value set.
Specifically, a plurality of parameter values corresponding to each parameter name in the parameter name set are determined by using the server parameters.
Firstly, determining a first parameter value set corresponding to a first parameter name (CPU related parameter name) by using a core number; secondly, determining a second parameter value set corresponding to a second parameter name (memory related parameter name) by using the memory size; and finally, determining a database connection numerical value set by using the database connection number name.
In an embodiment, taking the generation of a two-dimensional parameter table corresponding to MySQL as an example, setting a parameter name innodb_thread_conclusion of the CPU, wherein the parameter values are 1 times, 2 times and 3 times of the number of cores of the CPU respectively, and the three values form a first parameter value set;
setting the memory related parameter names of innodb_buffer_pool_size, innodb_buffer_pool_instance and the parameter values of the innodb_buffer_pool_size to be 60%, 70%, 80% and 90% of the memory respectively, wherein the parameter values of the innodb_buffer_pool_instance are 8 and 16 respectively, namely the second parameter value set is formed;
the name max_connections of the connection numbers is set, and the parameter values of the connection numbers are 1000, 1500, 2000, 2500 and 3000 respectively, namely the five values form a database connection value set.
Further, permutation and combination are performed on each parameter value set to obtain a target parameter value set, including: and arranging and combining the first parameter value set, the second parameter value set and the database connection value set to obtain the target parameter value set.
The target parameter value set comprises a plurality of groups of target parameter values, and each group of target parameter values comprises a first parameter value, a second parameter value and a database connection value.
Further, a target two-dimensional table is generated according to the target parameter value set, i.e. each group of target parameter values is one row of data of the target two-dimensional table.
In one embodiment, the generated target two-dimensional table is shown in Table 1 below:
TABLE 1 target two-dimensional Table
Wherein each row is a set of target parameter values.
As an alternative implementation of the embodiment of the present invention, step 104 includes: based on the target two-dimensional table and the parameter name set, performing pressure measurement on the database system to be tested by using an automatic test method to obtain at least one pressure measurement result; determining a target pressure measurement result based on each pressure measurement result; and determining the target database parameter value of the database system to be tested based on the target pressure measurement result.
Based on the target two-dimensional table and the parameter name set, performing pressure testing on the database system to be tested by using an automatic testing method to obtain at least one pressure testing result, wherein the method comprises the following steps: acquiring a tool to be pressed, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set; replacing the initial parameter value corresponding to each parameter name in the parameter name set with each target parameter value in a first group of target parameter values to obtain a preset parameter value corresponding to each parameter name in the parameter name set; generating a test instruction by using the tool to be pressed based on a preset parameter value of each parameter; under the control of the test instruction, the automatic test method is utilized to carry out pressure test on the database system to be tested; and repeating the steps of acquiring a tool to be tested, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set to the step of testing the database system to be tested by using the automatic testing method under the control of the testing instruction until each group of target parameter values in the target two-dimensional table are acquired, and obtaining at least one testing result.
Firstly, acquiring an initial parameter value corresponding to each parameter name in a parameter name set, such as an initial parameter value corresponding to a first parameter name (CPU related parameter name);
next, a first set of data in the target two-dimensional table, such as one line of data corresponding to the number 1 in the above table 1, is acquired.
Further, the first set of numbers is used to replace the initial parameter value corresponding to each parameter name in the parameter name set.
Specifically, the initial parameter value corresponding to the first parameter name (CPU related parameter name) in the parameter name set is replaced by the parameter value corresponding to the CPU related parameter name indb_thread_con_currency in the first set of data, the initial parameter value corresponding to the second parameter name (memory related parameter name) in the parameter name set is replaced by the parameter value corresponding to the memory related parameter name indb_buffer_pool_size and nnodb_buffer_pool_instance in the first set of data, and the initial parameter value corresponding to the connection number name max_connections in the first set of data is replaced by the parameter value corresponding to the database connection number name in the parameter name set.
After the replacement is completed, a test instruction is generated by using the obtained tool to be tested, and under the control of the test instruction, the database system to be tested is tested by using an automatic test method, so that a corresponding test result is obtained.
The test instructions may include a library build instruction, a table build instruction, a data import instruction, a create index instruction, and a performance test instruction.
Further, according to each group of data in the target two-dimensional table, sequentially modifying initial parameter values corresponding to each parameter name in the parameter name set, and simultaneously, under the control of the generated test instruction, performing pressure test on the database system to be tested by using an automatic test method to obtain a pressure test result corresponding to each modification.
Further, after the initial parameter value corresponding to each parameter name in the parameter name set is modified, the database system to be tested can be restarted according to the actual situation.
In one embodiment, the above-described repetitive process is provided, and the corresponding procedure is provided as follows:
for cyclic two-dimensional table:
{
acquiring the parameter name and the corresponding parameter value of the two-dimensional table,
the corresponding parameters are covered with the original parameters of the database,
restart the database service (if necessary),
generating a test instruction sequence (creating a base create database, creating a table create table, importing data into insert into the database, creating all create index),
the test is performed and the test is performed,
the test result is stored and the test result is stored,
}
and finally, after each group of data in the target two-dimensional table is acquired, sequencing each obtained pressure measurement result, and selecting an optimal pressure measurement result, namely a target pressure measurement result.
Further, a group of data in the target two-dimensional table corresponding to the target pressure measurement result is used as a target database parameter value of the database system to be tested.
In an example, a method for optimizing database system parameters based on server configuration is provided, as shown in fig. 2, including:
(1) Acquiring parameter names related to a CPU and a memory in a database to be tested, and acquiring the CPU core number and the physical memory size of a server;
(2) Setting a plurality of groups of parameter values for the related database parameters according to the acquired CPU core number, memory size and the like, then arranging and combining the parameter values, and storing the parameter values into a two-dimensional table;
(3) Selecting a tool for performing pressure measurement on a database to be tested, and generating a test instruction;
(4) According to the set multiple groups of parameter values, sequentially modifying the corresponding parameter values in the database, executing the pressure measurement, and storing the pressure measurement result, wherein the method comprises the following steps: acquiring a first group of parameters in a two-dimensional table, and replacing original parameter values in a database with the group of parameter values; judging whether the execution times are greater than the number of the two-dimensional array lines, if so, acquiring the next group of parameters in the two-dimensional table, replacing the parameters with original parameters in the database, and ending; if not, executing the press test according to the generated test instruction, storing the test result in the two-dimensional array, further obtaining the next group of parameters in the two-dimensional table, replacing the parameters with the original parameters in the database, and returning to the step of judging whether the execution times are greater than the number of rows of the two-dimensional array.
(5) And obtaining a pressure measurement value corresponding to each group of parameters, and recommending the optimal database parameters configured based on the server according to the pressure measurement value.
Further, the database system parameter optimization method based on the server configuration has the following advantages:
(1) Database parameter tuning is performed based on the configuration of the server;
(2) The multiple groups of parameters are arranged and combined, so that human intervention is reduced;
(3) And the multiple groups of database parameter values are automatically tested, so that the DBA workload is reduced.
The embodiment of the invention also provides a database system parameter optimization device, wherein the database system is arranged in the server; as shown in fig. 3, the apparatus includes:
an obtaining module 301, configured to obtain a parameter name set of a database system to be tested and a server parameter set of the server; for details, see the description of step 101 in the above method embodiment.
A determining module 302, configured to determine a plurality of parameter value sets corresponding to the parameter name set based on the server parameter set; for details, see the description of step 102 in the method embodiment described above.
An arrangement and generation module 303, configured to perform arrangement and combination on each parameter value set to obtain a target parameter value set, and generate a target two-dimensional table based on the target parameter value set; for details, see the description of step 103 in the method embodiment described above.
The optimizing module 304 is configured to obtain a target database parameter value of the database system to be tested through optimization by an automatic testing method and a preset processing method based on the target two-dimensional table and the parameter name set; for details, see the description of step 104 in the method embodiment described above.
According to the database system parameter optimization device provided by the embodiment of the invention, each parameter value set in a plurality of parameter value sets corresponding to the first parameter name set of the obtained database system to be tested is arranged and combined to obtain the target parameter value set, further, the parameter value corresponding to each parameter name in the first parameter name set of the database system to be tested is optimized by utilizing the target two-dimensional table generated by the target parameter value set, meanwhile, the database system to be tested is tested by utilizing an automatic test method, and the target database parameter value of the database system to be tested is determined according to the test result. Therefore, by implementing the invention, the parameter tuning of the database system to be tested is performed based on the configuration data of the server, thereby realizing automatic test, reducing human intervention and reducing DBA workload.
As an optional implementation manner of the embodiment of the present invention, the server includes a central processing unit and a memory; the parameter name set comprises a first parameter name related to the central processor, a second parameter name related to the memory and a database connection number name in the database system to be tested, and the server parameter set comprises the core number of the central processor and the memory size of the memory.
As an optional implementation manner of the embodiment of the present invention, the determining module includes: a first determining submodule, configured to determine a first parameter value set corresponding to the first parameter name based on the number of kernels; a second determining submodule, configured to determine a second parameter value set corresponding to the second parameter name based on the memory size; a third determining submodule for determining a database connection value set based on the database connection number name; a fourth determination submodule for determining the plurality of parameter value sets based on the first parameter value set, the second parameter value set and the database connection value set.
As an optional implementation manner of the embodiment of the present invention, the arrangement and generation module includes: the arrangement sub-module is used for arranging and combining the first parameter value set, the second parameter value set and the database connection value set to obtain the target parameter value set, wherein the target parameter value set comprises a plurality of groups of target parameter values, and each group of target parameter values comprises a first parameter value, a second parameter value and a database connection value.
As an optional implementation manner of the embodiment of the present invention, the optimization module includes: the first pressure testing sub-module is used for carrying out pressure testing on the database system to be tested by utilizing an automatic testing method based on the target two-dimensional table and the parameter name set to obtain at least one pressure testing result; a fifth determining sub-module for determining a target crush measurement result based on each crush measurement result; and a sixth determining submodule, configured to determine the target database parameter value of the database system to be tested based on the target pressure measurement result.
As an optional implementation manner of the embodiment of the present invention, the first pressure measurement sub-module includes: the acquisition sub-module is used for acquiring a tool to be pressed, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set; a replacing sub-module, configured to replace the initial parameter value corresponding to each parameter name in the parameter name set with each target parameter value in a first set of target parameter values, so as to obtain a preset parameter value corresponding to each parameter name in the parameter name set; the generating sub-module is used for generating a test instruction by using the tool to be pressed based on the preset parameter value of each parameter; the second pressure testing sub-module is used for carrying out pressure testing on the database system to be tested by utilizing the automatic testing method under the control of the testing instruction; and the repeating submodule is used for repeating the steps of acquiring a tool to be tested, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set to the step of testing the database system to be tested by using the automatic testing method under the control of the testing instruction until each group of target parameter values in the target two-dimensional table are acquired, and obtaining at least one testing result.
As an optional implementation manner of the embodiment of the invention, the test instruction comprises a library building instruction, a table building instruction, a data importing instruction, a creating index instruction and a performance test instruction.
The functional description of the database system parameter optimization device provided by the embodiment of the invention refers to the description of the database system parameter optimization method in the embodiment.
The embodiment of the present invention further provides a storage medium, as shown in fig. 4, on which a computer program 401 is stored, which when executed by a processor, implements the steps of the database system parameter optimization method in the above embodiment. The storage medium may be a magnetic Disk, an optical disc, a Read-Only Memory (ROM), a random access Memory (Random Access Memory, RAM), a Flash Memory (Flash Memory), a Hard Disk (HDD), a Solid State Drive (SSD), or the like; the storage medium may also comprise a combination of memories of the kind described above.
It will be appreciated by those skilled in the art that implementing all or part of the above-described embodiment method may be implemented by a computer program to instruct related hardware, where the program may be stored in a computer readable storage medium, and the program may include the above-described embodiment method when executed. The storage medium may be a magnetic Disk, an optical disc, a Read-Only Memory (ROM), a random access Memory (Random Access Memory, RAM), a Flash Memory (Flash Memory), a Hard Disk (HDD), a Solid State Drive (SSD), or the like; the storage medium may also comprise a combination of memories of the kind described above.
The embodiment of the present invention further provides an electronic device, as shown in fig. 5, where the electronic device may include a processor 51 and a memory 52, where the processor 51 and the memory 52 may be connected by a bus or other means, and in fig. 5, the connection is exemplified by a bus.
The processor 51 may be a central processing unit (Central Processing Unit, CPU). The processor 51 may also be other general purpose processors, digital signal processors (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations thereof.
The memory 52 serves as a non-transitory computer readable storage medium that may be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as corresponding program instructions/modules in embodiments of the present invention. The processor 51 executes various functional applications of the processor and data processing, i.e., implements the database system parameter optimization method in the above-described method embodiments, by running non-transitory software programs, instructions, and modules stored in the memory 52.
The memory 52 may include a memory program area that may store an operating device, an application program required for at least one function, and a memory data area; the storage data area may store data created by the processor 51, etc. In addition, memory 52 may include high-speed random access memory, and may also include non-transitory memory, such as at least one magnetic disk storage device, flash memory device, or other non-transitory solid state storage device. In some embodiments, memory 52 may optionally include memory located remotely from processor 51, which may be connected to processor 51 via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The one or more modules are stored in the memory 52 and when executed by the processor 51 perform the database system parameter optimization method of the embodiment shown in fig. 1-2.
The specific details of the electronic device may be understood correspondingly with reference to the corresponding related descriptions and effects in the embodiments shown in fig. 1 to 2, which are not repeated here.
Although embodiments of the present invention have been described in connection with the accompanying drawings, various modifications and variations may be made by those skilled in the art without departing from the spirit and scope of the invention, and such modifications and variations fall within the scope of the invention as defined by the appended claims.
Claims (10)
1. A database system parameter optimization method, wherein the database system is arranged in a server; characterized in that the method comprises:
acquiring a parameter name set of a database system to be tested and a server parameter set of the server;
determining a plurality of parameter value sets corresponding to the parameter name sets based on the server parameter sets;
each parameter value set is arranged and combined to obtain a target parameter value set, and a target two-dimensional table is generated based on the target parameter value set;
and optimizing by an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested.
2. The method of claim 1, wherein the step of determining the position of the substrate comprises,
the server comprises a central processing unit and a memory;
the parameter name set comprises a first parameter name related to the central processor, a second parameter name related to the memory and a database connection number name in the database system to be tested, and the server parameter set comprises the core number of the central processor and the memory size of the memory.
3. The method of claim 2, wherein determining a plurality of parameter value sets corresponding to the parameter name set based on the server parameter set comprises:
determining a first parameter value set corresponding to the first parameter name based on the number of cores;
determining a second parameter value set corresponding to the second parameter name based on the memory size;
determining a database connection value set based on the database connection number name;
the plurality of parameter value sets is determined based on the first parameter value set, the second parameter value set, and the database connection value set.
4. A method according to claim 3, wherein permutation and combination of each of the parameter value sets results in a target parameter value set, comprising:
and arranging and combining the first parameter value set, the second parameter value set and the database connection value set to obtain the target parameter value set, wherein the target parameter value set comprises a plurality of groups of target parameter values, and each group of target parameter values comprises a first parameter value, a second parameter value and a database connection value.
5. The method of claim 4, wherein optimizing by an automated test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain the target database parameter value of the database system to be tested comprises:
based on the target two-dimensional table and the parameter name set, performing pressure measurement on the database system to be tested by using an automatic test method to obtain at least one pressure measurement result;
determining a target pressure measurement result based on each pressure measurement result;
and determining the target database parameter value of the database system to be tested based on the target pressure measurement result.
6. The method of claim 5, wherein the performing a press test on the database system under test using an automated test method based on the target two-dimensional table and the parameter name set to obtain at least one press test result comprises:
acquiring a tool to be pressed, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set, wherein the tool to be pressed is connected with the database system to be pressed;
replacing the initial parameter value corresponding to each parameter name in the parameter name set with each target parameter value in a first group of target parameter values to obtain a preset parameter value corresponding to each parameter name in the parameter name set;
generating a test instruction by using the tool to be pressed based on a preset parameter value of each parameter;
under the control of the test instruction, the automatic test method is utilized to carry out pressure test on the database system to be tested;
and repeating the steps of acquiring a tool to be tested, a first group of target parameter values in the target two-dimensional table and initial parameter values corresponding to each parameter name in the parameter name set to the step of testing the database system to be tested by using the automatic testing method under the control of the testing instruction until each group of target parameter values in the target two-dimensional table are acquired, and obtaining at least one testing result.
7. The method of claim 6, wherein the test instructions comprise a library build instruction, a table build instruction, a data import instruction, a create index instruction, and a performance test instruction.
8. A database system parameter optimizing apparatus, the database system being provided in a server; characterized in that the device comprises:
the acquisition module is used for acquiring a parameter name set of the database system to be tested and a server parameter set of the server;
the determining module is used for determining a plurality of parameter value sets corresponding to the parameter name sets based on the server parameter sets;
the arrangement and generation module is used for carrying out arrangement and combination on each parameter value set to obtain a target parameter value set, and generating a target two-dimensional table based on the target parameter value set;
and the optimization module is used for optimizing through an automatic test method and a preset processing method based on the target two-dimensional table and the parameter name set to obtain a target database parameter value of the database system to be tested.
9. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program for causing the computer to execute the database system parameter configuration method according to any one of claims 1 to 7.
10. An electronic device, comprising: a memory and a processor, said memory and said processor being communicatively connected to each other, said memory storing a computer program, said processor executing the database system parameter configuration method according to any of claims 1 to 7 by executing said computer program.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202310565219.5A CN116594981A (en) | 2023-05-18 | 2023-05-18 | Database system parameter optimization method and device and electronic equipment |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202310565219.5A CN116594981A (en) | 2023-05-18 | 2023-05-18 | Database system parameter optimization method and device and electronic equipment |
Publications (1)
Publication Number | Publication Date |
---|---|
CN116594981A true CN116594981A (en) | 2023-08-15 |
Family
ID=87598703
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202310565219.5A Pending CN116594981A (en) | 2023-05-18 | 2023-05-18 | Database system parameter optimization method and device and electronic equipment |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN116594981A (en) |
Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN103853786A (en) * | 2012-12-06 | 2014-06-11 | 中国电信股份有限公司 | Method and system for optimizing database parameters |
CN105224599A (en) * | 2015-08-31 | 2016-01-06 | 浪潮(北京)电子信息产业有限公司 | A kind of method and system of database Automatic Optimal configuration |
CN113590576A (en) * | 2021-02-05 | 2021-11-02 | 华中科技大学 | Database parameter adjusting method and device, storage medium and electronic equipment |
CN113872788A (en) * | 2020-06-30 | 2021-12-31 | 中兴通讯股份有限公司 | Database configuration parameter adjusting method, device and storage medium |
US20220222231A1 (en) * | 2021-01-13 | 2022-07-14 | Coupang Corp. | Computerized systems and methods for using artificial intelligence to optimize database parameters |
CN114817212A (en) * | 2022-05-20 | 2022-07-29 | 北京奥星贝斯科技有限公司 | Database optimization method and optimization device |
-
2023
- 2023-05-18 CN CN202310565219.5A patent/CN116594981A/en active Pending
Patent Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN103853786A (en) * | 2012-12-06 | 2014-06-11 | 中国电信股份有限公司 | Method and system for optimizing database parameters |
CN105224599A (en) * | 2015-08-31 | 2016-01-06 | 浪潮(北京)电子信息产业有限公司 | A kind of method and system of database Automatic Optimal configuration |
CN113872788A (en) * | 2020-06-30 | 2021-12-31 | 中兴通讯股份有限公司 | Database configuration parameter adjusting method, device and storage medium |
US20220222231A1 (en) * | 2021-01-13 | 2022-07-14 | Coupang Corp. | Computerized systems and methods for using artificial intelligence to optimize database parameters |
CN113590576A (en) * | 2021-02-05 | 2021-11-02 | 华中科技大学 | Database parameter adjusting method and device, storage medium and electronic equipment |
CN114817212A (en) * | 2022-05-20 | 2022-07-29 | 北京奥星贝斯科技有限公司 | Database optimization method and optimization device |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CN110297701A (en) | Data processing operation dispatching method, device, computer equipment and storage medium | |
CN113238924B (en) | Chaotic engineering realization method and system in distributed graph database system | |
WO2020113526A1 (en) | Chip verification method and device | |
JP2023511464A (en) | SYSTEM, METHOD, AND COMPUTER-READABLE MEDIUM FOR ANALYSIS OF PRODUCT FAILURE FACTORS | |
CN111782452A (en) | Method, system, device and medium for interface contrast test | |
CN113360581A (en) | Data processing method, device and storage medium | |
CN112732556B (en) | Automatic testing method, device, equipment and storage medium of distributed system | |
CN114201346A (en) | HIL (hardware-in-the-loop) testing method and device of CAN (controller area network) communication interface and electronic equipment | |
CN112698827A (en) | Distributed visual modeling platform and method | |
CN115827636B (en) | Method for storing and reading simulation data of logic system design from waveform database | |
CN116594981A (en) | Database system parameter optimization method and device and electronic equipment | |
CN109712181B (en) | Method for extracting open-circuit key area on integrated circuit layout line network | |
CN116402325A (en) | Automatic business process processing method and device | |
CN116009889A (en) | Deep learning model deployment method and device, electronic equipment and storage medium | |
CN112347723B (en) | Layout-based ROM code extraction verification method and device | |
CN113495831B (en) | Method, system, equipment and medium for generating test case based on keywords | |
CN111737347A (en) | Method and device for sequentially segmenting data on Spark platform | |
CN112231236A (en) | Method for testing database performance | |
CN109388385A (en) | Method and apparatus for application and development | |
CN117389841B (en) | Method and device for monitoring accelerator resources, cluster equipment and storage medium | |
CN114167223B (en) | Power supply abnormity detection method and device and computer readable storage medium | |
CN118051779B (en) | Automatic parameter searching method and device for large model training and electronic equipment | |
CN114448851B (en) | Automatic data testing method and system | |
CN116595944A (en) | Parallel routing DRC repairing method and related device | |
CN116627820A (en) | Data creation method, device, equipment and medium |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
PB01 | Publication | ||
PB01 | Publication | ||
SE01 | Entry into force of request for substantive examination | ||
SE01 | Entry into force of request for substantive examination |