:Computing pushdown performance optimization for the FEDERATED storage engine

Overview

For queries that involve the FEDERATED storage engine, MySQL Community Edition can push down conditions on indexes only when indexes are scanned by using the RANGE or REF methods. Other conditions can only be used in local databases. In actual scenarios, the WHERE condition of a query may involve a large number of fields, or functions are used on index fields. These make it impossible to directly use indexes. In this case, the FEDERATED storage engine sends a full table scan query to the remote database and pulls all data to the local database for query. The condition pushdown feature of PolarDB for MySQL pushes down as many compatible conditions as possible to filter the data in remote databases. This improves the query performance, reduces the network usage, and reduces the costs of local copy and data format conversion.

How to enable condition pushdown

You can enable the condition pushdown feature by configuring the loose_optimizer_switch parameter. For more information, see Configure cluster and node parameters.

Examples

The following examples use a Sysbench table that contains 10 million entries to simulate the performance improvement brought by condition pushdown at different selectivities. The cluster specification of the local and remote servers is polar.mysql.x8.large 4-core and 32 GB (dedicated resources). The storage type is PLS5.

Create the remote table and FEDERATED table

# Create a remote table#
CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Create a server#
CREATE SERVER s
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'username', PASSWORD 'password', HOST 'hostname', PORT 3306, DATABASE 'dbname');

# Create a FEDERATED table#
CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_1` (`k`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='s';

SQL queries that have the condition pushdown feature enabled or disabled

# The SQL query that has the condition pushdown feature disabled#
set optimizer_switch='engine_condition_pushdown=off';
Query OK, 0 rows affected (0.03 sec)

EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE id + 1 < 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.04 sec)

# The SQL query that has the condition pushdown feature enabled#
set optimizer_switch='engine_condition_pushdown=on';
Query OK, 0 rows affected (0.10 sec)

EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE id + 1 < 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                                                               |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using where with pushed condition ((`federated`.`sbtest1`.`id` + 1) < 100) |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.05 sec)

In the preceding SQL query, the WHERE condition compares a function that contains the primary key with a constant. The optimal execution plan is to use a full table scan. The performance varies depending on whether the WHERE condition is pushed down to the remote server for execution. You can change the constant value to selectivity * table_size to give conditions different selectivities for the performance test.

As shown in the preceding figure, when the selectivity is low (less than 0.1), enabling condition pushdown increases the performance by around 100%.

When you perform SQL queries, the condition pushdown feature is frequently enabled and disabled. You can use the performance monitoring feature of the PolarDB console to view the bandwidth usage of the SQL queries.

• When the conditions are not pushed down, the SQL queries consume a large amount of network bandwidth. The network bandwidth usage is the same for queries that have different selectivities.

• When the conditions are pushed down and the selectivity is low (less than 0.1), the network traffic is very small. The network traffic gradually increases as the selectivity increases. This is clearly different from the network IO of the SQL queries whose conditions are not pushed down.

Overview

When a SQL query that involves the FEDERATED storage engine obtains data from a remote table, the values of all columns are returned. In actual scenarios, a query may need only the values of some columns instead. The values of other columns that are not needed by the query increase the cost of data selection and format conversion for the remote server, and occupy more network bandwidth. Therefore, PolarDB for MySQL optimizes the column return logic of MySQL Community Edition and allows SQL queries that involve FEDERATED tables to obtain only the required columns from the remote server. This significantly reduces the cost of data selection and the network usage of the remote server and improves the query performance. The more columns a table contains, the more the query performance improves.

Examples

The following examples use a table that contains 1 million data records and different numbers of columns for simulation purpose. A table that contains 100 columns is defined by using the following sample code. Repeatedly define the k, c, pad columns in the Sysbench table to widen the table. By reducing the length of the character type, you can define more columns without the need to occupy more space.

Define the table

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(15) NOT NULL DEFAULT '',
  `pad` char(8) NOT NULL DEFAULT '',
  `k1` int(11) NOT NULL DEFAULT '0',
  `c1` char(15) NOT NULL DEFAULT '',
  `pad1` char(8) NOT NULL DEFAULT '',
  ...
  `k32` int(11) NOT NULL DEFAULT '0',
  `c32` char(15) NOT NULL DEFAULT '',
  `pad32` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

SQL queries that have the on-demand column return feature enabled or disabled

Use SELECT pad FROM sbtest1 with the on-demand column return feature enabled or disabled to the test the performance. The pad field is not indexed in the remote table. Therefore, the execution plan uses a primary key full table scan. The following table shows the execution time in seconds.

As shown in the preceding figure, as the number of columns increases, the on-demand column return feature provides a linear acceleration in the query speed.

In addition, the on-demand column return feature provides more execution plans for the remote server. If the queried columns are indexed, the execution plan will use an index scan on the remote table instead of a full table scan. This further improves the performance.

# The query that has the on-demand column return feature disabled#
SET federated_fetch_select_field_enabled=OFF;
Query OK, 0 rows affected (0.19 sec)

SELECT SUM(k) FROM federated_col_64.sbtest1;
+--------------+
| SUM(k)       |
+--------------+
| 499868973740 |
+--------------+
1 row in set (5.20 sec)

# The query that has the on-demand column return feature enabled#
SET federated_fetch_select_field_enabled=ON;
Query OK, 0 rows affected (0.11 sec)

SELECT SUM(k) FROM federated_col_64.sbtest1;
+--------------+
| SUM(k)       |
+--------------+
| 499868973740 |
+--------------+
1 row in set (0.45 sec)

The preceding example shows that after the index is used on k column. Enabling the on-demand column return feature improves query performance by over 1000%.

Overview

MySQL Community Edition cannot push down all conditions in a paged query of a query that involves a FEDERATED table. As a result, all data is returned from the remote server to the local server. You must use the WHERE condition to filter data before paging. If condition pushdown is enabled for PolarDB for MySQL and the SQL statement involves only one FEDERATED table and does not contain clauses that affect the result accuracy, such as aggregation, window, UNION, DISTINCT, and HAVING, the [ORDER BY <cols>] LIMIT OFFSET clause can be pushed down to the remote server for processing. The local server can directly return the result to the client.

How to use [ORDER BY <cols>] LIMIT OFFSET pushdown

You can configure the loose_optimizer_switch parameter to enable [ORDER BY <cols>] LIMIT OFFSET pushdown. For more information, see Configure cluster and node parameters.

Examples

Create a table

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(15) NOT NULL DEFAULT '',
  `pad` char(8) NOT NULL DEFAULT '',
  `k1` int(11) NOT NULL DEFAULT '0',
  `c1` char(15) NOT NULL DEFAULT '',
  `pad1` char(8) NOT NULL DEFAULT '',
  ...
  `k32` int(11) NOT NULL DEFAULT '0',
  `c32` char(15) NOT NULL DEFAULT '',
  `pad32` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

SQL queries that have the [ORDER BY <cols>] LIMIT OFFSET pushdown feature enabled or disabled

# The query that has the [ORDER BY <cols>] LIMIT OFFSET pushdown feature enabled#
set optimizer_switch='limit_offset_pushdown=on';
Query OK, 0 rows affected (0.05 sec)

EXPLAIN SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | Using limit-offset pushdown |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.06 sec)

# The query that has the [ORDER BY <cols>] LIMIT OFFSET pushdown feature disabled#
set optimizer_switch='limit_offset_pushdown=off';
Query OK, 0 rows affected (0.05 sec)

EXPLAIN SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET 1000;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9850101 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.05 sec)

# Disable on-demand ORDER pushdown#
SET federated_pushdown_order_enabled=OFF;
Query OK, 0 rows affected (0.19 sec)

# Enable on-demand ORDER pushdown#
SET federated_pushdown_order_enabled=ON;
Query OK, 0 rows affected (0.11 sec)

mysql> explain select * from federated.sbtest1 order by id limit 100 offset 1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 9850101 | 100.00 | Using limit-offset pushdown; Using order pushdown `id` |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+

You can use SELECT * FROM federated.sbtest1 LIMIT 100 OFFSET number with the [ORDER BY <cols>] LIMIT OFFSET feature enabled or disabled to test the performance on a Sysbench table with 10 million data records. The following table describes the query performance for different OFFSET values.

The smaller the OFFSET value is, the more the query performance is improved. The query performance can be improved by up to 600%.