tencent cloud

Feedback

Buffer Pool Isolation

Last updated: 2024-10-09 10:30:39

Feature Overview

Innodb Buffer Pool (BP) can avoid contamination caused by operations like full table scans as much as possible by inserting new pages at midpoints, but the effects are limited. To further improve the BP performance, this feature allows setting an independent space in the BP, which is specifically used to execute operations like full table scans. When there is no full table scan, this space can be fully used by a normal LRU list. When there is a full table scan, this space is managed by using the CLOCK eviction algorithm. Through physical isolation of data pages, the BP can be protected from contamination caused by operations like full table scans.

Supported Versions

The kernel version should be 3.1.15 or later for TXSQL 8.0.

Applicable Scenarios

It is applicable to scenarios where frequent full table scans and large-scale data operations are required for databases.

Use Instructions

Parameter Descriptions

Parameter Name
Dynamic
Type
Default Value
Valid Values/Value Range
Description
innodb_txsql_independent_buffer_pool_evict_interval
yes
ulong
50
0-50
The time unit for a background thread to actively evict pages in a CLOCK list. If the value is smaller, the pages in the CLOCK list will reside longer in memory. Setting it to 0 can quickly clear the CLOCK list.
innodb_txsql_independent_buffer_pool_list_move_action
yes
ulong
0
0-2
Behavior after pages in a CLOCK list are read by a normal query. 0 indicates no changes, 1 indicates synchronously moving to an LRU list, and 2 indicates asynchronously moving to an LRU list. The moving operations are handled by a background thread.
innodb_txsql_independent_buffer_pool_size_pct
yes
ulong
5
1-100
Maximum proportion of the BP size that can be used for isolation. If the proportion is higher, the impact on normal queries will be greater, but operations like full table scans will be more effective.
innodb_txsql_independent_buffer_pool_users
yes
string
nullptr

Specifies users that use BP isolation. The specific configuration format is user1@ip1;user2@ip2.
innodb_txsql_independent_buffer_pool_enabled
yes
bool
ON
ON/OFF
Enables or disables BP isolation. When BP isolation is disabled, no new pages will enter the isolation space and old isolated pages will be evicted as soon as possible.
innodb_txsql_independent_buffer_pool_max_expire_minutes
yes
bool
120
1-1440
Maximum eviction time for pages in a CLOCK list for BP isolation. The control logic is as follows. use_times indicates the activity level of a page and is an important metric in the CLOCK algorithm. Each time a page is read, its use_times is incremented by 1, and a background thread will decrement the use_times of all pages by 1 at intervals of a time unit. innodb_txsql_independent_buffer_pool_max_expire_minutes is used to control the upper limit of use_times.
A new status parameter is described as follows:
Parameter
Type
Description
txsql_independent_buffer_pool_usage_counts
longlong
Number of SQL statements using BP isolation.

Method 1 for Using BP Isolation

Use a hint named independent to manually trigger the use of BP isolation. Specifically, add /*+ independent */ immediately after a keyword such as INSERT, DELETE, or UPDATE of a DML statement, for example, select /*+ independent */ id from t;. It's important to note that adding /*+ independent */ at any other position cannot trigger the use of BP isolation, for example, select id /*+ independent */ from t;.

Method 2 for Using BP Isolation

Specify users that use BP isolation by default through innodb_txsql_independent_buffer_pool_users. These users are referred to as BP isolation users for short. Adding, modifying, or deleting a BP isolation user in innodb_txsql_independent_buffer_pool_users will only affect the user's behavior of using BP isolation by default in new connections, but will not affect that in existing connections. For troubleshooting purposes, a new status parameter Independent_buffer_pool_session is added in show detail processlist; to indicate whether BP isolation is used by default in a connection.

Relationship of BP Isolation, Prepared Statements, and Stored Procedures

Both prepared statements and stored procedures support using BP isolation through Method 1.
The use of BP isolation in prepared statements and stored procedures is irrelevant to users who create them but only relevant to users who execute them.

Observation on BP Isolation

The BP isolation feature supports monitoring on the BP isolation space (CLOCK list) through show engine innodb status. The length and distribution status of a CLOCK list are added into the BUFFER POOL AND MEMORY module. Specifically, use_times indicates the activity level of a page and is an important metric in the CLOCK algorithm. Each time a page is read, its use_times is incremented by 1, and a background thread will decrement the use_times of all pages by 1 at intervals of a time unit. When the use_times of a page reaches 0, the page is either evicted or flushed. In the BUFFER POOL AND MEMORY module, pages are classified by use_times into 6 ranges: [0,10), [10, 100), [100, 1000), [1000, 10000), [10000, 100000), and [100000, unlimited).
Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon