tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Buffer Pool Isolation

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

      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.

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy