Method and apparatus for obtaining buffer pool attribute information
By testing the access information of the buffer pool at different time periods, buffer pool attribute information is generated, which solves the problem of inaccurate adjustment of the buffer pool size, realizes precise adjustment and memory resource optimization, and improves database access performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- AGRICULTURAL BANK OF CHINA
- Filing Date
- 2022-11-01
- Publication Date
- 2026-07-03
AI Technical Summary
In existing technologies, when technicians adjust the size of the buffer pool based on experience, the accuracy is low, leading to wasted memory resources or poor access performance of the database platform, and frequent adjustments affect database functionality.
By obtaining access information at different time periods, the size of the buffer pool of the target data table is adjusted, and buffer pool attribute information is generated based on the access information to indicate the size of the buffer pool of the target data table, thereby reducing the number of synchronous reads and improving the accuracy of adjustment.
It enables precise adjustment of the buffer pool size without affecting the functionality of the database platform, thereby reducing memory resource consumption and improving data table access performance.
Smart Images
Figure CN115905218B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of computer technology, and in particular to a method and apparatus for obtaining buffer pool attribute information. Background Technology
[0002] As business grows and transaction volume increases, the access performance of data tables in the database platform significantly declines, sometimes even impacting normal business operations. Therefore, it's necessary to optimize the size of the buffer pool used by these tables. Statistics from the buffer pool show that excessive synchronous read operations are the primary cause of the performance degradation. Therefore, the most direct way to improve the performance of these tables is to increase the memory size of the buffer pool. A larger buffer pool allows more data to be cached in memory, reducing the number of synchronous reads and thus improving the table's access performance.
[0003] Host memory is an expensive resource, and the memory usage of the buffer pool corresponding to a data table should be minimized while ensuring data table accessibility. In related technologies, technicians can only adjust the size of each buffer pool based on experience. However, this experience-based adjustment is often inaccurate; the adjusted buffer pool may consume excessive memory, or the data table accessibility may remain low. To improve the accuracy of buffer pool adjustments, technicians need to perform multiple adjustments, which can significantly impact the functionality of the database platform. Summary of the Invention
[0004] To address the aforementioned technical problems, this application provides a method and apparatus for obtaining buffer pool attribute information, which improves the accuracy of adjusting the buffer pool size of a target data table.
[0005] To achieve the above objectives, the technical solutions provided in this application are as follows:
[0006] This application provides a method for obtaining buffer pool attribute information, including:
[0007] Obtain the first access information of the first buffer pool within the first time period; the first buffer pool corresponds to the target data table in the memory of the database platform;
[0008] Update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool.
[0009] Obtain the second access information of the second buffer pool within the second time period; the database platform replays the tasks that ran in the first time period within the second time period;
[0010] The buffer pool attribute information corresponding to the target data table is obtained based on the first access information and the second access information. The attribute information is used to indicate the size of the buffer pool of the target data table.
[0011] In some possible embodiments, the first access information indicates the number of synchronous accesses to the first buffer pool within a first time period.
[0012] In some possible embodiments, the second access information indicates the number of synchronous accesses to the second buffer pool within a second time period.
[0013] In some possible embodiments, it also includes:
[0014] Update the buffer pool corresponding to the target data table to the third buffer pool; the size of the third buffer pool is greater than the size of the second buffer pool.
[0015] Obtain the third access information of the third buffer pool within the third time period; the database platform replays the tasks that ran in the first time period within the third time period;
[0016] Based on the first access information and the second access information, obtain the buffer pool attribute information corresponding to the target data table, including:
[0017] Obtain the buffer pool attribute information corresponding to the target data table based on the first access information, the second access information, and the third access information.
[0018] In some possible embodiments, it also includes:
[0019] The size of the third buffer pool is determined based on the first and second access information.
[0020] In some possible embodiments, it also includes:
[0021] Store the buffer pool attribute information corresponding to the target data table in the folder corresponding to the target data table.
[0022] This application also provides a device for obtaining buffer pool attribute information, including:
[0023] The first acquisition module is used to acquire the first access information of the first buffer pool within a first time period; the first buffer pool corresponds to the target data table in the memory of the database platform.
[0024] The first update module is used to update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool.
[0025] The second acquisition module is used to obtain the second access information of the second buffer pool during the second time period; the database platform replays the tasks that ran in the first time period during the second time period.
[0026] The third acquisition module is used to obtain the buffer pool attribute information corresponding to the target data table based on the first access information and the second access information. The attribute information is used to indicate the size of the buffer pool of the target data table.
[0027] In some possible embodiments, the first access information indicates the number of synchronous accesses to the first buffer pool within a first time period.
[0028] In some possible embodiments, the second access information indicates the number of synchronous accesses to the second buffer pool within a second time period.
[0029] In some possible embodiments, it also includes:
[0030] The second update module is used to update the buffer pool corresponding to the target data table to the third buffer pool; the size of the third buffer pool is greater than the size of the second buffer pool.
[0031] The fourth acquisition module is used to obtain the third access information of the third buffer pool during the third time period; the database platform replays the tasks that ran in the first time period during the third time period.
[0032] The third acquisition module is specifically used to obtain the buffer pool attribute information corresponding to the target data table based on the first access information, the second access information, and the third access information.
[0033] As can be seen from the above technical solution, this application has the following beneficial effects:
[0034] This application provides a method and apparatus for obtaining buffer pool attribute information. The method includes: obtaining first access information of a first buffer pool within a first time period; a target data table in the memory of a database platform corresponding to the first buffer pool; updating the buffer pool corresponding to the target data table to a second buffer pool; the size of the second buffer pool being greater than the size of the first buffer pool; obtaining second access information of the second buffer pool within a second time period; replaying tasks running within the first time period within the second time period on the database platform; and obtaining buffer pool attribute information corresponding to the target data table based on the first and second access information, wherein the attribute information is used to indicate the size of the buffer pool for the target data table.
[0035] Therefore, the method for obtaining buffer pool attribute information provided in this application obtains the accessibility of the target data table with different buffer pool sizes by testing the access information of the target data table with different buffer pool sizes, thereby obtaining the buffer pool attribute information of the target data table. In this way, technicians who adjust the target data table can, based on the buffer pool attribute information of the target data table, achieve relatively precise adjustment of the buffer pool size of the target data table with minimal impact on the functionality of the database platform. Attached Figure Description
[0036] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0037] Figure 1 A flowchart illustrating a method for obtaining buffer pool attribute information provided in this application embodiment;
[0038] Figure 2 A mapping diagram showing the relationship between the simulated size of the buffer pool and the number of synchronous reads avoided, provided for embodiments of this application;
[0039] Figure 3 This is a schematic diagram of a device for obtaining buffer pool attribute information provided in an embodiment of this application. Detailed Implementation
[0040] To help better understand the solutions provided in the embodiments of this application, before introducing the methods provided in the embodiments of this application, we will first introduce the application scenarios of the solutions in the embodiments of this application.
[0041] Host memory is an expensive resource, and the memory usage of the buffer pool corresponding to a data table should be minimized while ensuring data table accessibility. In related technologies, technicians can only adjust the size of each buffer pool based on experience. However, this experience-based adjustment is often inaccurate; the adjusted buffer pool may consume excessive memory, or the data table accessibility may remain low. If technicians want to improve the accuracy of buffer pool adjustments, they need to perform multiple adjustments, which will impact the functionality of the database platform.
[0042] To address the aforementioned technical problems, embodiments of this application provide a method and apparatus for obtaining buffer pool attribute information. The method includes: obtaining first access information of a first buffer pool within a first time period; identifying a target data table in the memory of a database platform corresponding to the first buffer pool; updating the buffer pool corresponding to the target data table to a second buffer pool; ensuring the size of the second buffer pool is greater than the size of the first buffer pool; obtaining second access information of the second buffer pool within a second time period; replaying tasks running within the first time period within the second time period on the database platform; and obtaining buffer pool attribute information corresponding to the target data table based on the first and second access information, wherein the attribute information indicates the size of the buffer pool for the target data table.
[0043] Therefore, the method for obtaining buffer pool attribute information provided in this application embodiment obtains the accessibility of the target data table with different buffer pool sizes by testing the access information of the target data table with different buffer pool sizes, thereby obtaining the buffer pool attribute information of the target data table. Thus, technicians who adjust the target data table can make more precise adjustments to the buffer pool size of the target data table based on the buffer pool attribute information of the target data table.
[0044] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, the embodiments of this application will be further described in detail below with reference to the accompanying drawings and specific implementation methods.
[0045] See Figure 1 The figure is a flowchart of a method for obtaining buffer pool attribute information provided in an embodiment of this application.
[0046] like Figure 1 As shown in the embodiments of this application, the method for obtaining buffer pool attribute information includes:
[0047] S101: Obtain the first access information of the first buffer pool within the first time period; the first buffer pool corresponds to the target data table in the memory of the database platform.
[0048] S102: Update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool.
[0049] S103: Obtain the second access information of the second buffer pool in the second time period; the database platform replays the tasks that ran in the first time period in the second time period.
[0050] S104: Obtain the attribute information of the buffer pool corresponding to the target data table based on the first access information and the second access information. The attribute information is used to indicate the size of the buffer pool of the target data table.
[0051] The database platform in this embodiment can be a DB2 database, and both the first buffer pool and the second buffer pool can be located on the DB2 host. In this embodiment, the first access information and the second access information can be obtained by calling the DB2 host interface via an API (Application Program Interface), without needing to log in to the DB2 host management interface. This avoids the risk of misoperation by technical personnel after logging into the DB2 host management interface.
[0052] In this embodiment, the first time period can be the time period during which the database platform is operating normally. To ensure the accessibility of the target data table, a time period during which the target data table is accessed most frequently is generally selected as the first time period, such as a certain time period during the daytime. In this embodiment, the second time period can be the time period during which technicians test the database platform, such as a certain time period at night.
[0053] It should be noted that the first access information indicates the number of synchronous accesses to the first buffer pool within the first time period. The second access information indicates the number of synchronous accesses to the second buffer pool within the second time period. When the data requested by the SQL statement is not in the buffer pool, the database needs to read the relevant data from the disk into the buffer pool, which results in a synchronous read. When the actual space of the buffer pool has just been used up, if there is a request to access data, and the data is not in the buffer pool, a synchronous read will occur, and a piece of data (data 1) will be replaced from the buffer pool. If another request to access data 1 occurs at this time, another synchronous read will occur, and a piece of data (data 2) will be replaced from the buffer pool. However, if a buffer pool with a simulated increase in size is added, a synchronous read will still occur, but no data will be replaced during the synchronous read; that is, data 2 will not be replaced. If another request to access data 2 occurs at this time, a synchronous read will not occur again, and the data can be read directly from the buffer pool.
[0054] As one possible implementation, this embodiment of the application can record the number of times the simulated increase in the newly added buffer pool is accessed, and then count the number of synchronous reads avoided in the second time period relative to the first time period. As another possible implementation, this embodiment of the application can also record the number of synchronous reads in the first time period and the number of synchronous reads in the second time period, and then calculate the difference between the number of synchronous reads in the first time period and the number of synchronous reads in the second time period, which is the number of synchronous reads avoided in the second time period relative to the first time period.
[0055] In this embodiment, the buffer pool of the target data table can be further expanded to a third buffer pool, and then the increased accessibility of the target data table can be statistically analyzed. Specifically, in this embodiment, the buffer pool corresponding to the target data table can be updated to a third buffer pool; the size of the third buffer pool is larger than the size of the second buffer pool; third access information of the third buffer pool within a third time period is obtained; the database platform replays the tasks that ran in the first time period within the third time period. Then, the attribute information of the buffer pool corresponding to the target data table is obtained based on the first access information, the second access information, and the third access information. It should be noted that the third access information can indicate the number of synchronous reads within the third time period.
[0056] This application embodiment enables the acquisition of the number of synchronous reads corresponding to the buffer pool of the target data table at different sizes during the test period, thereby generating a mapping relationship between the increased size of the buffer pool of the target data table and the number of synchronous reads avoided, thus obtaining the attribute information of the buffer pool of the target data table.
[0057] See Figure 2 This figure is a mapping relationship between the increased simulated size of the buffer pool and the number of synchronous reads avoided, according to an embodiment of this application.
[0058] like Figure 2 As shown in the graph, the horizontal axis represents the increase in simulated size of the target data table during each test, in MB. The vertical axis represents the number of synchronous reads avoided after each increase in simulated size of the target data table. It should be noted that the increased simulated size in this embodiment refers to the original buffer pool (first buffer pool). The number of synchronous reads avoided in this embodiment refers to the number of synchronizations in the first buffer pool. For example, in this embodiment, the increased simulated size of the second buffer pool relative to the first buffer pool can be 10 MB, in which case the number of synchronous reads avoided by the second buffer pool relative to the first buffer pool can be 1000. The increased simulated size of the third buffer pool relative to the first buffer pool can be 20 MB, in which case the number of synchronous reads avoided by the third buffer pool relative to the first buffer pool can be 1900.
[0059] It should be noted that the buffer pool attribute information of the target data table in this embodiment can be a mapping relationship between the increased size of the buffer pool corresponding to the target data table and the number of synchronous reads avoided, or it can be the inflection point of the mapping curve between the increased size of the buffer pool corresponding to the target data table and the number of synchronous reads avoided. This embodiment does not limit this.
[0060] like Figure 2 As shown, the applicant discovered that when running the same task, the increase in the size of the buffer pool for the target data table in the database platform is generally not linearly related to the reduction in the number of synchronous reads. The reduction in the number of synchronous reads and the increase in memory size are not linearly related, but rather follow a gradually flattening curve. Technical personnel, based on experience, are usually unable to precisely adjust the size of the buffer pool. Due to the specific nature of the production environment, the optimal size of the buffer pool cannot be determined by frequently adjusting its size.
[0061] The method provided in this application allows for adjustments to the size of each buffer pool requiring optimization within a simulated environment. This involves collecting statistics on the system buffer pools used by each table, then initiating database replay. After replay, the statistics are collected again. This process is repeated multiple times. Based on the collected statistics, the absolute number of reduced synchronous reads and the ratio of these reductions to the number of synchronous reads without memory increases are calculated for each table. Finally, the required increase in memory size for each table's buffer pool is considered, thereby improving the accuracy of buffer pool adjustments.
[0062] Furthermore, the difference between the third buffer pool and the second buffer pool may not be equal to the difference between the second buffer pool and the first buffer pool. Embodiments of this application can also determine the size of the third buffer pool based on the first access information and the second access information. For example, when the first access information and the second access information indicate that the number of synchronous reads avoided by the buffer pool is small, in order to increase the data difference between the buffer pools in different tests, the difference between the third buffer pool and the second buffer pool may be greater than the difference between the second buffer pool and the first buffer pool.
[0063] After obtaining the attribute information of the buffer pool corresponding to the target data table in this embodiment, the attribute information of the buffer pool corresponding to the target data table can be stored in the folder corresponding to the target data table. When the tester needs to adjust the size of the buffer pool, they can call the attribute information in this file for reference.
[0064] Based on the method for obtaining buffer pool attribute information provided in the above embodiments, this application also provides a device for obtaining buffer pool attribute information.
[0065] See Figure 3 The figure is a schematic diagram of a device for obtaining buffer pool attribute information provided in an embodiment of this application.
[0066] The first acquisition module 100 is used to acquire the first access information of the first buffer pool within a first time period; the first buffer pool corresponds to the target data table in the memory of the database platform.
[0067] The first update module 200 is used to update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool.
[0068] The second acquisition module 300 is used to acquire the second access information of the second buffer pool during the second time period; the database platform replays the tasks that ran in the first time period during the second time period.
[0069] The third obtaining module 400 is used to obtain the buffer pool attribute information corresponding to the target data table based on the first access information and the second access information. The attribute information is used to indicate the size of the buffer pool of the target data table.
[0070] In one possible implementation, the first access information indicates the number of synchronous accesses to the first buffer pool within a first time period. The second access information indicates the number of synchronous accesses to the second buffer pool within a second time period.
[0071] As one possible implementation, the apparatus provided in this application embodiment may further include: a second update module, used to update the buffer pool corresponding to the target data table to a third buffer pool; the size of the third buffer pool is greater than the size of the second buffer pool; a fourth obtaining module, used to obtain third access information of the third buffer pool in a third time period; the database platform replays the tasks running in the first time period in the third time period; the third obtaining module is specifically used to obtain the buffer pool attribute information corresponding to the target data table based on the first access information, the second access information and the third access information.
[0072] The method provided in this application allows for adjustments to the size of each buffer pool requiring optimization within a simulated environment. Statistical information on the system buffer pools used by each table is collected, and then database replay is initiated. After replay, the statistical information on the system buffer pools used by each table is collected again. This process is repeated multiple times. Based on the collected statistical information, the absolute number of reduced synchronous reads and the ratio of the reduced synchronous reads to the number of synchronous reads without increasing memory size are calculated for each table. Finally, the required increase in memory size for the buffer pools used by each data table is considered, thereby improving the accuracy of buffer pool adjustment.
[0073] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that all or part of the steps in the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, a server, or a network communication device such as a media gateway, etc.) to execute the methods described in various embodiments or some parts of the embodiments of this application.
[0074] It should be noted that the various embodiments in this specification are described in a progressive manner, with each embodiment focusing on the differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. Regarding the methods disclosed in the embodiments, since they correspond to the systems disclosed in the embodiments, the descriptions are relatively simple; relevant parts can be referred to the system section description.
[0075] It should also be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.
[0076] The above description of the disclosed embodiments will enable those skilled in the art to make or use various modifications to these embodiments. It will be readily apparent to those skilled in the art that the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A method of obtaining buffer pool attribute information, characterized by, include: Obtain the first access information of the first buffer pool within the first time period; The first buffer pool corresponds to the target data table in the memory of the database platform; The first time period is the period during which the database platform operates normally; Update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool. Obtain second access information from the second buffer pool within a second time period; the database platform replays the tasks that ran within the first time period within the second time period; the second time period is the period during which technicians test the database platform; Based on the first access information and the second access information, a mapping relationship is generated between the increase in the buffer pool corresponding to the target data table and the number of synchronous reads avoided in the second time period relative to the first time period, thereby obtaining the buffer pool attribute information corresponding to the target data table. The attribute information is used to indicate the size of the buffer pool of the target data table. The method for obtaining the number of synchronous reads avoided in the second time period relative to the first time period includes: obtaining it by recording the number of accesses to the simulated increase in the newly added buffer pool and then calculating the difference between the number of synchronous reads in the first time period and the number of synchronous reads in the second time period.
2. The method according to claim 1, characterized in that, The first access information indicates the number of synchronous accesses to the first buffer pool within a first time period.
3. The method according to claim 1, characterized in that, The second access information indicates the number of synchronous accesses to the second buffer pool within the second time period.
4. The method according to claim 1, characterized in that, Also includes: Update the buffer pool corresponding to the target data table to the third buffer pool; the size of the third buffer pool is greater than the size of the second buffer pool. Obtain the third access information of the third buffer pool within the third time period; the database platform replays the tasks that ran in the first time period within the third time period; The step of obtaining the buffer pool attribute information corresponding to the target data table based on the first access information and the second access information includes: The buffer pool attribute information corresponding to the target data table is obtained based on the first access information, the second access information, and the third access information.
5. The method according to claim 4, characterized in that, Also includes: The size of the third buffer pool is determined based on the first access information and the second access information.
6. The method according to claim 1, characterized in that, Also includes: Store the buffer pool attribute information corresponding to the target data table in the folder corresponding to the target data table.
7. A device for obtaining buffer pool attribute information, characterized in that, include: The first acquisition module is used to acquire the first access information of the first buffer pool within a first time period. The first buffer pool corresponds to the target data table in the memory of the database platform; The first time period is the period during which the database platform operates normally; The first update module is used to update the buffer pool corresponding to the target data table to the second buffer pool; the size of the second buffer pool is greater than the size of the first buffer pool. The second acquisition module is used to acquire the second access information of the second buffer pool within a second time period; the database platform replays the tasks that ran in the first time period within the second time period; the second time period is the time period during which technicians test the database platform; The third obtaining module is used to generate a mapping relationship between the increase in the buffer pool corresponding to the target data table and the number of synchronous reads avoided in the second time period relative to the first time period, based on the first access information and the second access information, thereby obtaining the buffer pool attribute information corresponding to the target data table. The attribute information is used to indicate the size of the buffer pool of the target data table. The method for obtaining the number of synchronous reads avoided in the second time period relative to the first time period includes: obtaining it by recording the number of accesses to the simulated increase in the newly added buffer pool and then calculating the difference between the number of synchronous reads in the first time period and the number of synchronous reads in the second time period, based on the recorded number of synchronous reads in the first time period and the number of synchronous reads in the second time period.
8. The apparatus according to claim 7, characterized in that, The first access information indicates the number of synchronous accesses to the first buffer pool within a first time period.
9. The apparatus according to claim 7, characterized in that, The second access information indicates the number of synchronous accesses to the second buffer pool within the second time period.
10. The apparatus according to claim 7, characterized in that, Also includes: The second update module is used to update the buffer pool corresponding to the target data table to a third buffer pool; the size of the third buffer pool is greater than the size of the second buffer pool. The fourth acquisition module is used to acquire the third access information of the third buffer pool during the third time period; the database platform replays the tasks that ran in the first time period during the third time period. The third obtaining module is specifically used to obtain the buffer pool attribute information corresponding to the target data table based on the first access information, the second access information, and the third access information.