When you frequently insert rows to a table and batch delete rows from the table at the same time, or perform a large number of concurrent updates on the table, you may experience a significant reduction in the query and update performance, even though the table does not have a large number of data rows. This table is defined as a queuing table in OceanBase Database. In terms of business, such a table is known as a buffer table. This topic describes how to handle a queuing table in OceanBase Database.
Overview
A queuing table, or a buffer table, is so named because the table is used like a buffer, which means that a large portion of table rows are being updated, inserted, and deleted. A queuing table has the following characteristics:
Trigger condition: A large portion of table data is updated frequently.
If a table is under a large number of updates, which means a large number of data rows are inserted and deleted, it is possible that the table has only thousands of data rows has experienced millions of insert and delete operations.
Symptom: The table does not have a large number of rows, but the table query is slow.
An obvious characteristic of a buffer table is that the table query is slow while the table has a small amount of data, such as a few thousand rows. This is because when the SQL query of a buffer table is processed by the kernel, millions or tens of millions of rows may be scanned. By default, when you delete a row from a table in OceanBase Database, the row is marked with a delete tag in the memory. Then, the row is deleted during the daily major freeze or major compaction.
Cause: The frequent change of the execution plan doubles the time consumed for a full table scan.
The issue caused by the mark-for-delete method is shared by all storage engines of the LSM-tree architecture. As the data rows of a buffer table is deleted during a major compaction, the data amount of the table is small when OceanBase Database collects statistics to update the execution plan. Therefore, the cost-based optimizer (CBO) of OceanBase Database may generate a full-scan execution plan for some SQL queries. However, as the actual data amount in the table increases along with business traffic during the day, the performance of SQL queries decreases dramatically.
Emergency procedure
In most cases, the database system is put into operation when a buffer table is generated. You can use one of the following methods to stop the loss at the earliest opportunity:
If the OceanBase Database optimizer generates a full-table scan plan despite an index, you need to bind the execution plan to the index. For more information, see Exceptions of SQL queries
If no index of key filters is available for the SQL query, we recommend that you create an index online and bind it to the plan.
If you cannot create an index or most of the SQL queries execute range scans, you can manually start a major compaction as needed. This way, you can clear the deleted or updated versions of data to speed up the query.
Optimization of queuing tables in OceanBase Database
To optimize the query of buffer tables, OceanBase Database allows you to set a threshold for the scan of deleted empty rows in MemTables and SSTables. If the number of the empty rows is larger than the specified threshold, such as 256, the empty rows are marked with a skippable tag after they are scanned once. This way, the empty rows are skipped during the same SQL query next time to speed up the query.
By default, the range marking of empty rows fails at the moment of the minor compaction or major compaction in OceanBase Database, and succeeds only in the next full table scan. Therefore, if you create a proper index for the buffer table and bind it to the SQL execution plan, the query becomes normal without any intervention after a time-consuming scan.
The preceding methods are emergency stop-loss solutions. OceanBase Database V2.2.7 and later support the buffer minor compaction strategy to implement a special minor compaction mechanism for queuing tables. This resolves the issue of invalid scans. If queuing tables are foreseen, we recommend that you enable this strategy as a long-term solution.
ALTER TABLE user_table TABLE_MODE = 'queuing';
Minor compaction of queuing tables
OceanBase Database supports the adaptive buffer minor compaction strategy. For a queuing table, the storage layer automatically determines whether to apply the strategy to the table based on the statistics before each minor compaction. If a table works like a buffer table, a buffer minor compaction is scheduled, and a buffer minor SSTable is generated at the current snapshot read time based on the major SSTable and the latest incremental data. This minor compaction eliminates all Delete tags in the incremental data, and burdensome useless scans can be prevented in subsequent queries based on the new buffer minor SSTable.
For more information, see Storage architecture in OceanBase Database Overview.