Rewriting a MySQL query for performance is an important process that every DBA should be aware of so they can fix the wrong queries on runtime without code changes on the application end. ProxySQL has great support for rewriting the queries, which Alkin Tezuysal already explored in his excellent blog ProxySQL Query Rewrite Use Case.
So far, MySQL community provides two built-in query rewrite plugins to perform this task. Recently they introduced the plugin “ddl_rewriter”.
- query rewriter plugin : It has support for INSERT / UPDATE / DELETE / REPLACE statements from MySQL 8.0.12.
- ddl_rewritter plugin: It supports the CREATE TABLE statement. Introduced at MySQL 8.0.16.
In this blog, I am going to explain the complete process of the implementation and the testing of those plugins. The testing is based on MySQL 8.x features.
Query Rewriter Plugin
The plugin will help to modify the SQL statements which are received by the server before execution. Before MySQL 8.0.12, the plugin was only supported for SELECT. From MySQL 8.0.12 the plugin supports INSERT, UPDATE, DELETE, REPLACE as well.
Implementation
There are two SQL files to perform the install and uninstall operations. The files are located under the shared folder.
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> show global variables like 'lc_messages_dir'; +-----------------+----------------------------+ | Variable_name | Value | +-----------------+----------------------------+ | lc_messages_dir | /usr/share/percona-server/ | +-----------------+----------------------------+ 1 row in set (0.01 sec) [root@hercules7sakthi3 ~]# cd /usr/share/mysql-8.0/ [root@hercules7sakthi3 mysql-8.0]# ls -lrth | grep -i rewriter -rw-r--r--. 1 root root 1.3K Mar 26 14:16 uninstall_rewriter.sql -rw-r--r--. 1 root root 2.2K Mar 26 14:16 install_rewriter.sql |
- We can implement the rewriter plugin on runtime.
- When loading the SQL file “install_rewritter.sql” it will install the plugin “rewriter.so” and creates its own database, table, and function for the operations.
Install the plugin by loading the installer SQL file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | [root@hercules7sakthi3 mysql-8.0]# mysql -vv < install_rewriter.sql | grep -i 'create|install|drop' CREATE DATABASE IF NOT EXISTS query_rewrite CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules ( INSTALL PLUGIN rewriter SONAME 'rewriter.so' CREATE FUNCTION load_rewrite_rules RETURNS STRING CREATE PROCEDURE query_rewrite.flush_rewrite_rules() mysql> show schemas like 'query_rewrite'; +--------------------------+ | Database (query_rewrite) | +--------------------------+ | query_rewrite | +--------------------------+ 1 row in set (0.00 sec) mysql> show tables from query_rewrite; +-------------------------+ | Tables_in_query_rewrite | +-------------------------+ | rewrite_rules | +-------------------------+ 1 row in set (0.05 sec) mysql> show create table query_rewrite.rewrite_rulesG *************************** 1. row *************************** Table: rewrite_rules Create Table: CREATE TABLE `rewrite_rules` ( `id` int NOT NULL AUTO_INCREMENT, `pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `replacement` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `enabled` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES', `message` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `pattern_digest` varchar(64) DEFAULT NULL, `normalized_pattern` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name='Rewriter'; +-------------+---------------+----------------+ | plugin_name | plugin_status | plugin_version | +-------------+---------------+----------------+ | Rewriter | ACTIVE | 0.2 | +-------------+---------------+----------------+ 1 row in set (0.00 sec) |
Plugin installation is completed, and you can verify that from the above logs.
Test Case
(Remove the LOWER function from UPDATE to avoid the FTS)
I have created a table “qrw8012” and made some records for testing purposes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql> show create table qrw8012G *************************** 1. row *************************** Table: qrw8012 Create Table: CREATE TABLE `qrw8012` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, `dob` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from qrw8012; +----+--------+------------+ | id | name | dob | +----+--------+------------+ | 1 | jc | 0001-01-01 | | 2 | sriram | 1993-06-17 | | 3 | vijaya | 1969-11-23 | | 4 | durai | 1963-10-19 | | 5 | asha | 1992-06-26 | | 6 | sakthi | 1992-07-13 | +----+--------+------------+ 6 rows in set (0.00 sec) |
Requirement
The requirement is to update the column “name” from “sakthi” to “hercules7sakthi” WHERE id = 6. The UPDATE query from the application looks like this:
1 | update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'; |
From a database perspective, all my rows are updated with lower case only. So, here the LOWER function is not required. Also, using the LOWER function on the WHERE clause column will hide the index for that particular column. In our case, the query will scan the entire table ( FTS ).
With LOWER Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> show create table qrw8012G KEY `idx_name` (`name`) 1 row in set (0.18 sec) mysql> explain select * from qrw8012 where LOWER(name)='sakthi'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: qrw8012 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) |
It goes to a full table scan (FTS).
Without LOWER Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select * from qrw8012 where name='sakthi'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: qrw8012 partitions: NULL type: ref possible_keys: idx_name key: idx_name key_len: 67 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) |
In this case, the query is using the available index.
Note: I have converted the UPDATE to SELECT for analysis purposes.
From the above example, I have the index for the column “name”. But, still, it is not usable with the LOWER function. If I remove the LOWER function, the index is usable. Let’s see how we can fix this using the query rewrite plugin.
In the first step, I need to update the query rules in the table “rewrite_rules”. Below are the key points to be followed when updating the query rules.
- We have to use the query digest output to configure the query rules.
- We need to always call the function “flush_rewrite_rules” once the rules table is modified.
- If you wrongly configured the query rules, you will get the error message “ERROR 1644 (45000): Loading of some rule(s) failed.” during the flush function call.
- We can check the warning message to know if the query rules are applied or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> insert into rewrite_rules -> (id,pattern_database,pattern,replacement) values -> (1,'percona','update qrw8012 set name = ? where LOWER(name) = ?','update qrw8012 set name = ? where name = ?'); Query OK, 1 row affected (0.01 sec) mysql> call query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.03 sec) mysql> select id,pattern_database,pattern,replacement from rewrite_rulesG *************************** 1. row *************************** id: 1 pattern_database: percona pattern: update qrw8012 set name = ? where LOWER(name) = ? replacement: update qrw8012 set name = ? where name = ? 1 row in set (0.00 sec) |
I configured the query rules, so now executing the query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> show warningsG *************************** 1. row *************************** Level: Note Code: 1105 Message: Query 'update qrw8012 set name='hercules7sakthi' where LOWER(name)='sakthi'' rewritten to 'update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi'' by a query rewrite plugin 1 row in set (0.00 sec) Output from general log : 2020-06-22T11:20:36.952153Z 22 Query update qrw8012 set name = 'hercules7sakthi' where name = 'sakthi' |
Yes, it works well. We can confirm this by checking the warning message and the general log output.
To uninstall the plugin you have to load the SQL file “uninstall_rewriter.sql”. It will drop the database, function, and uninstall the plugin as well.
1 2 3 4 5 6 | [root@hercules7sakthi3 mysql]# cat /usr/share/mysql-8.0/uninstall_rewriter.sql ... ... DROP DATABASE IF EXISTS query_rewrite; DROP FUNCTION load_rewrite_rules; UNINSTALL PLUGIN rewriter; |
DDL Rewriter Plugin
MySQL community team introduced the ddl_rewriter plugin in MySQL 8.0.16. The plugin can be used to modify the CREATE TABLE statements received by the server. The plugin will remove the below clauses from the CREATE TABLE statement.
- ENCRYPTION
- DATA DIRECTORY
- INDEX DIRECTORY
Implementation
We can configure the plugin by using the INSTALL PLUGIN command.
1 2 3 4 5 6 7 8 9 10 | mysql> install plugin ddl_rewriter soname 'ddl_rewriter.so'; Query OK, 0 rows affected (0.04 sec) mysql> select plugin_name,plugin_status, plugin_version from information_schema.plugins where plugin_name like '%ddl%'; +--------------+---------------+----------------+ | plugin_name | plugin_status | plugin_version | +--------------+---------------+----------------+ | ddl_rewriter | ACTIVE | 1.0 | +--------------+---------------+----------------+ 1 row in set (0.01 sec) |
Once ddl_rewriter is installed, you can use the –ddl-rewriter option for subsequent server startups to control ddl_rewriter plugin activation. For example, to deactivate the feature:
1 2 | [mysqld] ddl-rewriter = OFF |
Test Case
(Migrate table structure from source to destination without ENCRYPTION, DATA DIRECTORY, and INDEX DIRECTORY )
Requirement
I have two MySQL environments, which are called “source” and “destination”. At my source environment, all my tables are configured with encryption and some of the tables have different DATA DIRECTORY and INDEX DIRECTORY.
The requirement is I need to migrate the table “ddl_rwtest” from source to destination. The table has encryption and different DATA DIRECTORY and INDEX DIRECTORY as well. I don’t need the encryption and separate data and index directory at my destination.
At the source, the table structure looks like this:
1 2 3 4 5 | create table ddl_rwtest (id int primary key, name varchar(16),dob date,msg text) ENCRYPTION='Y' DATA DIRECTORY = '/mysql/data' INDEX DIRECTORY = '/mysql/index'; |
Process
In the first step, I enabled the ddl_rewriter plugin as shown in the implementation section. Now, I am just going to load the structure using the same SQL command.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | mysql> create table ddl_rwtest -> (id int primary key, name varchar(16),dob date,msg text) -> ENCRYPTION='Y' -> DATA DIRECTORY = '/mysql/data' -> INDEX DIRECTORY = '/mysql/index'; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> show warningsG *************************** 1. row *************************** Level: Note Code: 1105 Message: Query 'create table ddl_rwtest (id int primary key, name varchar(16),dob date,msg text) ENCRYPTION='Y' DATA DIRECTORY = '/mysql/data' INDEX DIRECTORY = '/mysql/index'' rewritten to 'create table ddl_rwtest (id int primary key, name varchar(16),dob date,msg text) ' by a query rewrite plugin 1 row in set (0.00 sec) mysql> show create table ddl_rwtestG *************************** 1. row *************************** Table: ddl_rwtest Create Table: CREATE TABLE `ddl_rwtest` ( `id` int NOT NULL, `name` varchar(16) DEFAULT NULL, `dob` date DEFAULT NULL, `msg` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) |
From the above logs, the ddl_rewriter plugin has removed those encryption and data/index directories from my SQL command. You can verify the warning message to confirm this.
This plugin will really help with huge data structure migrations using logical backups.
Conclusion
It seems the MySQL community team is proactively working on Query rewrite plugin development as we have a new DDL rewriter plugin from MySQL 8.0.16. Right now the plugin supports only CREATE TABLE statements, and I am looking forward to more features and support of other DDL statements as well.
