Buffer pool selection method and device, electronic equipment and storage medium

Abstract

The invention relates to a buffer pool selection method and device, electronic equipment and a storage medium. The method comprises the steps that source end library information and a target SQL are obtained, a candidate buffer pool is created according to the source end library information, a default buffer pool is created according to a preset first parameter, the target SQL is analyzed, a target table group is obtained, the target table group is a set of tables interacting with the target SQL, and the target table group is a set of tables interacting with the target SQL according to the page size of the tables in the target table group. And selecting a target buffer pool from the candidate buffer pool and the default buffer pool. Therefore, the candidate buffer pool is created based on the source end library information, and the target buffer pool is selected according to the page size of the table in the target table group, so that the problem that frequent disk I / O operation is caused and the system performance is seriously influenced when the access rate of some data is not high but a large amount of data is read or written and a smaller buffer pool is selected according to the heat of the data can be solved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, electronic device and storage medium for selecting a buffer pool. Background Art

[0002] As database application scenarios continue to grow and become more complex, the requirements for database performance are also increasing. As a critical memory component in a database system, the buffer pool is responsible for temporarily storing data, and its size configuration is directly related to the database's cache performance and I / O efficiency. Especially in distributed database systems, the diversity and complexity of data make buffer pool management more challenging.

[0003] For distributed database systems, different data blocks have different storage requirements, which directly affect the utilization of the buffer pool and the overall performance of the database. Therefore, in order to more effectively utilize buffer pool resources and improve the overall performance of the database, it is necessary to select buffer pools of different sizes based on data characteristics.

[0004] In the related art, the existing buffer pool selection is based on data heat, that is, the data is divided into long-term access, long-term non-access and recent access types according to the access frequency of the data, and different sizes of buffer pools are selected according to different types of data. However, although the buffer pool utilization rate can be improved to a certain extent by selecting the buffer pool based on the data heat, when some data is not frequently accessed, but each access involves the reading or writing of a large amount of data, if these data are simply classified as "long-term non-access" and a smaller buffer pool is selected, then each access may require reading or writing a large amount of data from the disk, which will lead to frequent disk I / O operations, thus seriously affecting system performance. Summary of the invention

[0005] The purpose of the embodiments of the present application is to provide a buffer pool selection method, device, electronic device and storage medium to solve the problem that when the access rate of certain data is not high but involves a large amount of data reading or writing, a smaller buffer pool is selected according to the data popularity, resulting in frequent disk I / O operations and seriously affecting the system performance. The specific technical solution is as follows:

[0006] In a first aspect of an embodiment of the present application, a method for selecting a buffer pool is first provided, the method comprising:

[0007] Get source database information and target SQL;

[0008] Creating a candidate buffer pool according to the source library information;

[0009] Create a default buffer pool according to the preset first parameter;

[0010] Parse the target SQL to obtain a target table group, where the target table group is a set of tables that interact with the target SQL;

[0011] A target buffer pool is selected from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group.

[0012] In an optional implementation, the creating a candidate buffer pool according to the source-end library information includes:

[0013] If the source library information includes tablespace file information of different page sizes, creating candidate tablespace files of corresponding page sizes according to the tablespace file information of different page sizes;

[0014] If the source-end library information does not include tablespace file information of different page sizes, parameter information is obtained from the source-end library information, the page size of the optimized table is obtained by calculating the parameter information, and a candidate tablespace file is created according to the page size of the optimized table;

[0015] The corresponding candidate buffer pool is created according to the candidate table space file, and the page size of the candidate buffer pool matches that of the candidate table space file.

[0016] In an optional implementation, selecting a target buffer pool from candidate buffer pools and a default buffer pool according to a page size of a table in the target table group includes:

[0017] Obtaining page sizes of all tables in the target table group;

[0018] Obtaining a judgment result by judging whether the page sizes of all tables in the target table group are consistent;

[0019] According to the determination result, a target buffer pool is selected from the candidate buffer pools and the default buffer pool.

[0020] In an optional implementation, selecting a target buffer pool from the candidate buffer pools and the default buffer pool according to the judgment result includes:

[0021] If the judgment result is that the page sizes of all tables in the target table group are consistent, the candidate buffer pool that matches the page size of the table in the target table group is used as the target buffer pool;

[0022] If the judgment result is that the target table group includes tables of different page sizes, the default buffer pool is used as the target buffer pool.

[0023] In an optional implementation, if the judgment result is that the page sizes of all tables in the target table group are consistent, then using the candidate buffer pool that matches the page size of the table in the target table group as the target buffer pool includes:

[0024] If the candidate buffer pool that matches the page size of the table in the target table group is not found, the default buffer pool is used as the target buffer pool.

[0025] In an optional implementation, after parsing the target SQL to obtain the target table group, the method further includes:

[0026] If the page size specification is adjusted, the table data is migrated from the table space file of the original page size to the table space file of the page size after the page size specification is adjusted through the data migration tool.

[0027] In a second aspect of the embodiment of the present application, a device for selecting a buffer pool is further provided, the device comprising:

[0028] Get module to get source database information and target SQL;

[0029] A first creation module creates a candidate buffer pool according to the source library information;

[0030] A second creation module creates a default buffer pool according to a preset first parameter;

[0031] A parsing module, which parses the target SQL to obtain a target table group, where the target table group is a set of tables that interact with the target SQL;

[0032] The selection module selects a target buffer pool from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group.

[0033] In a third aspect of the embodiments of the present application, there is further provided an electronic device, comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus;

[0034] Memory, used to store computer programs;

[0035] The processor is configured to implement the buffer pool selection method described in any one of the first aspects above when executing a program stored in the memory.

[0036] In a fourth aspect of the embodiments of the present application, a storage medium is further provided, wherein instructions are stored in the storage medium, and when the instructions are executed on a computer, the computer executes the buffer pool selection method described in any one of the first aspects above.

[0037] In a fifth aspect of the embodiments of the present application, a computer program product comprising instructions is further provided, which, when executed on a computer, enables the computer to execute the buffer pool selection method described in any one of the first aspects above.

[0038] The above technical solution provided by the embodiment of the present application obtains source-end library information and target SQL, creates a candidate buffer pool according to the source-end library information, creates a default buffer pool according to a preset first parameter, parses the target SQL, obtains a target table group, and the target table group is a collection of tables that interact with the target SQL, and selects a target buffer pool from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group. In this way, by creating a candidate buffer pool based on the source-end library information and selecting a target buffer pool according to the page size of the table in the target table group, the problem of frequent disk I / O operations and serious impact on system performance can be solved when the access rate of certain data is not high but involves a large amount of data reading or writing, and a smaller buffer pool is selected based on the data heat. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0040] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0041] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0042] Figure 1 A schematic diagram of an implementation flow of a buffer pool selection method provided in an embodiment of the present application;

[0043] Figure 2 A schematic diagram of an implementation flow of another buffer pool selection method provided in an embodiment of the present application;

[0044] Figure 3 A schematic diagram of an implementation flow of another buffer pool selection method provided in an embodiment of the present application;

[0045] Figure 4 A schematic diagram of the structure of a buffer pool selection device provided in an embodiment of the present application;

[0046] Figure 5A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0047] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0048] The disclosure below provides many different embodiments or examples to realize the different structures of the present application. In order to simplify the disclosure of the present application, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present application. In addition, the present application can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0049] In order to solve the technical problem that in the prior art, a buffer pool is selected based on data heat, although the utilization rate of the buffer pool can be improved to a certain extent, when some data is not accessed frequently, but each access involves reading or writing a large amount of data, if these data are simply classified as "long-term non-access" and a smaller buffer pool is selected, then each access may require reading or writing a large amount of data from the disk, which will lead to frequent disk I / O operations, thereby seriously affecting the system performance, the present application provides a buffer pool selection method, device, electronic device and storage medium, which create a candidate buffer pool based on source library information, and select a target buffer pool according to the page size of the table in the target table group, which can solve the problem that when the access rate of some data is not high but involves a large amount of data reading or writing, a smaller buffer pool is selected based on data heat, resulting in frequent disk I / O operations, which seriously affects the system performance.

[0050] like Figure 1 FIG. 1 is a schematic diagram of an implementation flow of a method for selecting a buffer pool provided in an embodiment of the present application, which may specifically include the following steps:

[0051] S101, obtaining source database information and target SQL.

[0052] In some embodiments of the present application, the source library information may include table structure information (such as table field type, field length, etc.), data statistics information (such as the number of data in the table, data distribution, etc.), business demand information (business concurrency, query mode, etc.), table space file information (table space files of different page sizes and their corresponding tables) and other related parameters for subsequent creation of candidate buffer pools and selection of target buffer pools. The target SQL can be an SQL statement that needs to be executed that is input or parsed according to business needs. These SQL statements represent actual data operation requirements, such as query, update, delete, etc. By parsing the target SQL, you can understand which tables will be accessed, the access mode, and the required amount of data, thereby providing a basis for selecting the most appropriate buffer pool, which is not limited to this example of the present application.

[0053] For example, if the business requirement is to obtain the information of first-year students, after parsing, the target SQL can be obtained as SELECT student_id, name, grade; FROM students; WHERE grade = 'first grade'.

[0054] S102: Create a candidate buffer pool according to the source library information.

[0055] In some embodiments of the present application, a candidate buffer pool is created based on source-end library information, which may be to create corresponding candidate tablespace files based on different page sizes contained in the source-end library information, or to extract relevant parameter information from the source-end library information, wherein the parameter information may include field size, table data volume, business concurrency, and other information, and the optimized page size of the table is obtained by calculating through a calculation device of multiple page sizes based on the extracted relevant parameter information and the relevant business model, and the corresponding candidate tablespace file is created based on the optimized page size, and finally the corresponding candidate buffer pool is created based on the candidate tablespace file. The candidate buffer pool is used to provide matching cache space for different page sizes, to ensure that the database can efficiently use memory resources when processing data blocks of different sizes, and can flexibly respond to different data processing requirements, which is not limited in the present application examples.

[0056] For example, if the source library information contains a tablespace file with a page size of 32KB, you can create a candidate tablespace file with a page size of 32KB, and create a candidate buffer pool with a page size of 32KB based on the candidate tablespace file with a page size of 32KB. If the source library information does not contain clear tablespace file information, you can extract parameter information from the source library information for calculation to obtain an optimized page size of 128KB, and create the corresponding candidate tablespace file and candidate buffer pool accordingly.

[0057] S103: Create a default buffer pool according to a preset first parameter.

[0058] In some embodiments of the present application, the preset first parameter is a page size artificially defined according to business requirements, which can be 4KB, 8KB, 16KB, or 64KB. The default buffer pool is created according to the preset first parameter when creating the embodiment. Generally, 64KB is used as the first preset parameter, that is, the page size is 64KB, and the default buffer pool is created according to the page size of 64KB, which is not limited in the present application examples.

[0059] For example, when the page size of the preset first parameter is 64KB, a default buffer pool is created according to the page size of 64KB; when the page size of the preset first parameter is 8KB, a default buffer pool is created according to the page size of 8KB.

[0060] S104, parsing the target SQL to obtain a target table group, where the target table group is a collection of tables that interact with the target SQL.

[0061] In some embodiments of the present application, after obtaining the target SQL, the target SQL is parsed. By parsing the target SQL, the parsing process may include steps such as lexical analysis, grammatical analysis, and semantic analysis, and then all tables that interact with the target SQL can be obtained, that is, the tables involved in the target SQL. These tables are called target table groups, and the examples of the present application are not limited to this.

[0062] For example, for the target SQL "SELECT * FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.age>30", through parsing, we can get the target table group as {table1, table2}. This table group indicates the two tables involved in processing this SQL statement: table1 and table2.

[0063] S105 , selecting a target buffer pool from the candidate buffer pools and the default buffer pool according to the page size of the table in the target table group.

[0064] In some embodiments of the present application, the page size of the table in the target table group is obtained, and the target buffer pool is selected from the candidate buffer pool and the default buffer pool according to the page size of the table. This application example does not limit this.

[0065] For example, the page sizes of the tables in the target table group are 4KB and 8KB respectively, and 4KB and 8KB correspond to different candidate buffer pools respectively. Then, the target buffer pool can be selected from the default buffer pool and the candidate buffer pool corresponding to the page size of the tables in the target table group.

[0066] Through the above description of the technical solution provided by the embodiment of the present application, the source library information and the target SQL are obtained, a candidate buffer pool is created according to the source library information, a default buffer pool is created according to the preset first parameter, the target SQL is parsed, and a target table group is obtained. The target table group is a collection of tables that interact with the target SQL. According to the page size of the table in the target table group, the target buffer pool is selected from the candidate buffer pool and the default buffer pool. In this way, the candidate buffer pool is created based on the source library information, and the target buffer pool is selected according to the page size of the table in the target table group. This can solve the problem that when the access rate of some data is not high but involves a large amount of data reading or writing, a smaller buffer pool is selected according to the data heat, resulting in frequent disk I / O operations, which seriously affects the system performance.

[0067] like Figure 2 FIG. 1 is a schematic diagram of an implementation flow of another method for selecting a buffer pool provided in an embodiment of the present application, which may specifically include the following steps:

[0068] S201, obtaining source database information and target SQL.

[0069] In some embodiments of the present application, the source library information may include table structure information (such as table field type, field length, etc.), data statistics information (such as the number of data in the table, data distribution, etc.), business demand information (business concurrency, query mode, etc.), table space file information (table space files of different page sizes and their corresponding tables) and other related parameters for subsequent creation of candidate buffer pools and selection of target buffer pools. The target SQL can be an SQL statement that needs to be executed that is input or parsed according to business needs. These SQL statements represent actual data operation requirements, such as query, update, delete, etc. By parsing the target SQL, you can understand which tables will be accessed, the access mode, and the required amount of data, thereby providing a basis for selecting the most appropriate buffer pool, which is not limited to this example of the present application.

[0070] For example, if the business requirement is to obtain the information of first-year students, after parsing, the target SQL can be obtained as SELECT student_id, name, grade; FROM students; WHERE grade = 'first grade'.

[0071] S202: If the source-end library information includes tablespace file information of different page sizes, a candidate tablespace file of a corresponding page size is created according to the tablespace file information of different page sizes.

[0072] In some embodiments of the present application, source library information is obtained. When tablespace file information already exists in the source library and the source library information contains tablespace file information of different page sizes, corresponding candidate tablespace files can be created according to the tablespace file information of different page sizes. The candidate tablespace files are used for the subsequent creation of a candidate buffer pool, which is not limited in the present application examples.

[0073] For example, if the source database information contains tablespace file information with a page size of 8KB and another with a page size of 32KB, then candidate tablespace files corresponding to the page sizes of 8KB and 32KB can be created respectively based on the tablespace file information.

[0074] S203, if the source library information does not include tablespace file information of different page sizes, obtain parameter information from the source library information, obtain the page size of the optimized table by calculating the parameter information, and create a candidate tablespace file according to the page size of the optimized table.

[0075] In some embodiments of the present application, the source library information is obtained. When the source library information does not contain the optimized table space file information, that is, the source library information does not contain the table space file information of different page sizes, the parameter information can be extracted from the source library information, and the parameter information is input into the device of multiple specification page sizes to calculate the parameter information to obtain the optimized page size of the table, and then create a candidate table space file according to the optimized page size of the table. Among them, the parameter information may include parameter information such as the field size of the table, the data volume of the table, the business concurrency, and the business model. The page size of the optimized table refers to the page size that can bring the best performance under the current business scenario and data characteristics, that is, the page size can balance the number of I / O operations, memory usage efficiency, and cache hit rate, thereby improving the overall performance of the database. The device of multiple specification page sizes can be a module or system for calculating the optimized page size, can be an independent software component, or can be part of a database management system (DBMS), which is used to calculate the page size that best suits the current data characteristics and business needs based on complex algorithms and models, and comprehensively considers multiple factors. The present application example is not limited to this.

[0076] For example, the acquired source library information does not include tablespace files of different page sizes. The field size of the table, the amount of data in the table, the business model and other information contained in the source library information are input into a device with multiple page sizes. The optimized page sizes are calculated to be 8KB and 16KB, and the corresponding candidate tablespace files are created based on the optimized page sizes.

[0077] S204, creating a corresponding candidate buffer pool according to the candidate table space file, wherein the page size of the candidate buffer pool matches that of the candidate table space file.

[0078] In some embodiments of the present application, after creating a corresponding candidate tablespace file according to the page size, a corresponding candidate buffer pool is created according to the page size of the candidate tablespace file, and the candidate buffer pool matches the page size of the candidate tablespace file.

[0079] For example, the page sizes of the candidate tablespace files are 8KB and 16KB respectively. According to the candidate tablespace files with different page sizes, corresponding candidate buffer pool 1 (corresponding to the tablespace file with a page size of 8KB) and candidate buffer pool 2 (corresponding to the tablespace file with a page size of 16KB) will be created.

[0080] S205, parsing the target SQL to obtain a target table group, where the target table group is a collection of tables that interact with the target SQL.

[0081] In some embodiments of the present application, after obtaining the target SQL, the target SQL is parsed. By parsing the target SQL, the parsing process may include steps such as lexical analysis, syntax analysis, and semantic analysis, and then all tables that interact with the target SQL, that is, tables involved in the target SQL, can be obtained. These tables are called target table groups, and these tables that interact with the target SQL may be associated with each other through some association. This application example is not limited to this.

[0082] For example, for the target SQL "SELECT * FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.age>30", through parsing, we can get the target table group as {table1, table2}. This table group indicates the two tables involved in processing this SQL statement: table1 and table2.

[0083] S206: Obtain the page sizes of all tables in the target table group.

[0084] In some embodiments of the present application, the page size of all tables in the target table group is obtained, where the target table group is a collection of all tables that interact with the target SQL, and each table has its corresponding page size (page size). The page size can be the size of the data block of the database in physical storage, which determines the amount of data that can be processed in each I / O operation. By accessing the metadata or table data of the database, the page size of the corresponding table can be obtained, and the page sizes of all tables in the target table group are traversed.

[0085] For example, the tables that interact with the target SQL are table1 and table2, and the target table group is a set consisting of table1 and table2. Get the page sizes corresponding to table1 and table2.

[0086] Assume that there is a target SQL query involving two tables: table1 and table2. The two tables have different page sizes: table1 has a page size of 16KB, while table2 has a page size of 8KB. By parsing the target SQL, table1 and table2 are identified as the target table group. The page sizes of table1 and table2 are obtained by accessing the metadata of the database.

[0087] S207, obtaining a determination result by determining whether page sizes of all tables in the target table group are consistent.

[0088] In some embodiments of the present application, the determination result is obtained by determining whether the page sizes of the tables in the acquired target table group are consistent.

[0089] For example, it is determined that the page sizes of the tables in the acquired target table group are all consistent, and judgment result 1 is obtained; the page sizes of the tables in the acquired target table group are all inconsistent, and judgment result 2 is obtained; it is determined that the page sizes of the tables in the acquired target table group are partially consistent, and judgment result 3 is obtained.

[0090] S208: Select a target buffer pool from the candidate buffer pools and the default buffer pool according to the determination result.

[0091] In some embodiments of the present application, a judgment result is obtained based on whether the page sizes of all tables in the target table group are consistent, and a target buffer pool is selected from the candidate buffer pool and the default buffer pool based on the judgment result.

[0092] For example, when the judgment result is result 1, the corresponding target buffer pool is the default buffer pool; when the judgment result is result 2, the corresponding target buffer pool is a candidate buffer pool.

[0093] Specifically, according to the judgment result, a target buffer pool is selected from the candidate buffer pool and the default buffer pool, such as Figure 3 As shown, it is a schematic diagram of an implementation flow of another buffer pool selection method provided in an embodiment of the present application, which may include the following steps:

[0094] S301: If the judgment result is that the page sizes of all tables in the target table group are consistent, a candidate buffer pool that matches the page size of the table in the target table group is used as a target buffer pool.

[0095] In some embodiments of the present application, a judgment result is obtained based on whether the page sizes of the tables in the target table group are consistent. When the judgment result is that the page sizes of all tables in the target table group are consistent, the candidate buffer pool corresponding to the candidate table space file with the corresponding page size is used as the target buffer pool.

[0096] For example, when the page size of all tables in the target table group is 8KB, the page size of all tables is the same, and the candidate buffer pool corresponding to the table space file with a page size of 8KB is used as the target buffer pool; when the page size of all tables in the target table group is 16KB, the page size of all tables is the same, and the candidate buffer pool corresponding to the table space file with a page size of 16KB is used as the target buffer pool.

[0097] In addition, if no candidate buffer pool matching the page size of the tables in the target table group is found, the default buffer pool is used as the target buffer pool. That is, when selecting a candidate buffer pool as the target buffer pool, a candidate buffer pool matching the page size of the tables in the target table group may not be found for some reason, or there is no candidate buffer pool in the candidate buffer pool that completely matches the page size of all tables in the target table group, then the default buffer pool will be automatically selected as the target buffer pool. For example, when the page size of all tables in the target table group is 16KB, the candidate buffer pool corresponding to the page size of 16KB needs to be used as the target buffer pool, but no candidate buffer pool corresponding to the page size of 16KB is found, then the default candidate buffer pool will be used as the target buffer pool.

[0098] Although using the default buffer pool may not be the best choice, it can provide a reliable alternative to ensure smooth database operations when the candidate buffer pool does not match. At the same time, it can also record the occurrence of this situation so that the configuration of the candidate buffer pool can be optimized and adjusted later.

[0099] S302: If the determination result is that the target table group includes tables of different page sizes, the default buffer pool is used as the target buffer pool.

[0100] In some embodiments of the present application, a judgment result is obtained based on whether the page sizes of tables in the target table group are consistent. When the judgment result is that the target table group contains tables with different page sizes (that is, the page sizes of tables in the target table group are all inconsistent or partially inconsistent), the default buffer pool is used as the target buffer pool.

[0101] For example, when the page size of the table in the target table group is 8KB, 16KB, or 32KB, and there are tables with inconsistent page sizes in the target table group, and the judgment result is that the target table group contains tables with different page sizes, the default buffer pool is used as the target buffer pool.

[0102] Through the above description of the technical solution provided by the embodiment of the present application, the source library information and the target SQL are obtained, a candidate buffer pool is created according to the source library information, a default buffer pool is created according to the preset first parameter, the target SQL is parsed, and a target table group is obtained. The target table group is a collection of tables that interact with the target SQL. According to the page size of the table in the target table group, the target buffer pool is selected from the candidate buffer pool and the default buffer pool. In this way, the candidate buffer pool is created based on the source library information, and the target buffer pool is selected according to the page size of the table in the target table group. This can solve the problem that when the access rate of some data is not high but involves a large amount of data reading or writing, a smaller buffer pool is selected according to the data heat, resulting in frequent disk I / O operations, which seriously affects the system performance.

[0103] In addition, after parsing the target SQL and obtaining the target table group, if there is a page size specification adjustment, the table data is migrated from the table space file of the original page size to the table space file of the page size after the page size specification adjustment through the data migration tool.

[0104] According to the page size specification adjustment, select the new page size. According to the selected new page size, the system creates a new table space file to store the adjusted table data. Through the data migration tool, read the data in the table space file of the original page size and convert it into data suitable for the new page size format. Then migrate the data in the target table group from the table space file of the original page size to the new table space file with the adjusted page size. After the data migration is completed, update the source library information to reflect the new page size specification and storage location of the target table group. Ensure the integrity and accuracy of the data migration. If the new page size specification matches a candidate buffer pool in the system, you can update the buffer pool configuration to use the new candidate buffer pool to cache the migrated table data. If it does not match, you can continue to use the default buffer pool or make further adjustments.

[0105] At the same time, the optimal buffer pool configuration can be automatically selected or created according to the page size of the target table group. That is, when the target table group contains tables with different page sizes, the automatic adjustment process is triggered to try to find a more suitable buffer pool configuration.

[0106] Corresponding to the above method embodiment, the embodiment of the present application also provides a buffer pool selection device, such as Figure 4 As shown, the device may include an acquisition module 401 , a first creation module 402 , a second creation module 403 , a parsing module 404 , and a selection module 405 .

[0107] Acquisition module 401, acquiring source database information and target SQL;

[0108] The first creation module 402 creates a candidate buffer pool according to the source library information;

[0109] The second creation module 403 creates a default buffer pool according to a preset first parameter;

[0110] The parsing module 404 parses the target SQL to obtain a target table group, where the target table group is a set of tables that interact with the target SQL;

[0111] The selection module 405 selects a target buffer pool from the candidate buffer pools and the default buffer pool according to the page size of the table in the target table group.

[0112] The present application also provides an electronic device, such as Figure 5 As shown, it includes a processor 501, a communication interface 502, a memory 503 and a communication bus 504, wherein the processor 501, the communication interface 502, and the memory 503 communicate with each other through the communication bus 504.

[0113] Memory 503, used for storing computer programs;

[0114] In one embodiment of the present application, the processor 501, when used to execute the program stored in the memory 503, implements the following steps:

[0115] Get the source library information and target SQL, create a candidate buffer pool according to the source library information, create a default buffer pool according to the preset first parameter, parse the target SQL, get the target table group, which is a collection of tables that interact with the target SQL, and select the target buffer pool from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group.

[0116] The communication bus mentioned in the above electronic device can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0117] The communication interface is used for communication between the above electronic device and other devices.

[0118] The memory may include a random access memory (RAM) or a non-volatile memory, such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0119] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0120] In another embodiment provided in the present application, a storage medium is provided. The storage medium stores instructions. When the storage medium is executed on a computer, the computer executes the buffer pool selection method described in any one of the above embodiments.

[0121] In another embodiment provided by the present application, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute the buffer pool selection method described in any one of the above embodiments.

[0122] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a storage medium, or transmitted from one storage medium to another storage medium, for example, the computer instructions may be transmitted from a website site, a computer, a server or a data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The storage medium may be any available medium that a computer can access or a data storage device such as a server or a data center that includes one or more available media integrations. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive Solid State Disk (SSD)), etc.

[0123] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "including one..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0124] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0125] The above description is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application are included in the protection scope of the present application.

Claims

1. A method for selecting a buffer pool, characterized in that: The method comprises: Get source database information and target SQL; Creating a candidate buffer pool according to the source library information; Create a default buffer pool according to the preset first parameter; Parse the target SQL to obtain a target table group, where the target table group is a set of tables that interact with the target SQL; A target buffer pool is selected from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group.

2. The method according to claim 1, characterized in that The step of creating a candidate buffer pool according to the source library information includes: If the source library information includes tablespace file information of different page sizes, creating candidate tablespace files of corresponding page sizes according to the tablespace file information of different page sizes; If the source-end library information does not include tablespace file information of different page sizes, parameter information is obtained from the source-end library information, the page size of the optimized table is obtained by calculating the parameter information, and a candidate tablespace file is created according to the page size of the optimized table; The corresponding candidate buffer pool is created according to the candidate table space file, and the page size of the candidate buffer pool matches that of the candidate table space file.

3. The method according to claim 1, characterized in that The step of selecting a target buffer pool from candidate buffer pools and a default buffer pool according to a page size of a table in the target table group includes: Obtaining page sizes of all tables in the target table group; Obtaining a judgment result by judging whether the page sizes of all tables in the target table group are consistent; According to the determination result, a target buffer pool is selected from the candidate buffer pools and the default buffer pool.

4. The method according to claim 3, characterized in that The step of selecting a target buffer pool from the candidate buffer pools and the default buffer pool according to the judgment result includes: If the judgment result is that the page sizes of all tables in the target table group are consistent, the candidate buffer pool that matches the page size of the table in the target table group is used as the target buffer pool; If the judgment result is that the target table group includes tables of different page sizes, the default buffer pool is used as the target buffer pool.

5. The method according to claim 4, characterized in that If the judgment result is that the page sizes of all tables in the target table group are consistent, then the candidate buffer pool that matches the page size of the table in the target table group is used as the target buffer pool, including: If the candidate buffer pool that matches the page size of the table in the target table group is not found, the default buffer pool is used as the target buffer pool.

6. The method according to any one of claims 1 to 5, characterized in that After parsing the target SQL and obtaining the target table group, the method further includes: If the page size specification is adjusted, the table data is migrated from the table space file of the original page size to the table space file of the page size after the page size specification is adjusted through the data migration tool.

7. A buffer pool selection device, characterized in that: The device comprises: Get module to get source database information and target SQL; A first creation module creates a candidate buffer pool according to the source library information; A second creation module creates a default buffer pool according to a preset first parameter; A parsing module, which parses the target SQL to obtain a target table group, where the target table group is a set of tables that interact with the target SQL; The selection module selects a target buffer pool from the candidate buffer pool and the default buffer pool according to the page size of the table in the target table group.

8. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-6 when executing a program stored in a memory.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images