Optimizing MySQL 8 performance with Query Rewriter Plugins and DDL Rewriter
When you are building a Database Systems Infrastructure for performance of an WebScale application, the optimal query performance is very important for both customer satisfaction and efficient usage of systems infrastructure. There are several ways to tune MySQL for performance, This can be done on configuration level, efficient indexing, optimal distribution of disk I/O etc. But, if your query is expensive then tuning MySQL will not benefit much and you may eventually invest on very expensive hardware for performance. So application tuning cannot be supplemented either with generous MySQL system variables sizing or infrastructure investments / upgrades, This gets more complicated if you have ORM where you have no control on the application source. In such situations MySQL 8 Query Rewrite comes as a great savior with the capability to rewrite queries on MySQL Server, Just like how regular expression find statements that match a defined pattern and rewrite. When you very closely examine MySQL 8 query rewrite plugin, It is like a feature to automatically insert hint to the query for performance. In this blog post I have explained how to install, configure and use MySQL Query Rewriter Plugin.
» Query Rewriter Plugin installation
There are two files involved to install (install_rewriter.sql) and uninstall (uninstall_rewriter.sql) Query Rewriter Plugin, You can find their location by querying MySQL system variable lc_messages_dir :
|
1 2 3 4 5 6 7 |
mysql> show global variables like 'lc_messages_dir'; +-----------------+-----------------------+ | Variable_name | Value | +-----------------+-----------------------+ | lc_messages_dir | /usr/share/mysql-8.0/ | +-----------------+-----------------------+ 1 row in set (0.00 sec) |
» Install Query Rewriter Plugin
|
1 2 3 |
root@MinervaDB-Infra1:/usr/share/mysql-8.0# root@MinervaDB-Infra1:/usr/share/mysql-8.0# mysql < install_rewriter.sql root@MinervaDB-Infra1:/usr/share/mysql-8.0# |
The install_rewriter.sql will create schema query_rewrite and table rewrite_rules
|
1 2 3 4 5 6 7 8 9 |
mysql> show schemas like 'query_rewrite%'; +---------------------------+ | Database (query_rewrite%) | +---------------------------+ | query_rewrite | +---------------------------+ 1 row in set (0.00 sec) mysql> |
|
1 2 3 4 5 6 7 |
mysql> show tables from query_rewrite; +-------------------------+ | Tables_in_query_rewrite | +-------------------------+ | rewrite_rules | +-------------------------+ 1 row in set (0.00 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> show create table query_rewrite.rewrite_rules\G *************************** 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 8 Query Rewriter Plugin test case
I have created table qryrrt with some data for testing MySQL 8 Query Rewriter Plugin
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show create table qryrrt\G *************************** 1. row *************************** Table: qryrrt Create Table: CREATE TABLE `qryrrt` ( `contact_id` int NOT NULL AUTO_INCREMENT, `contact_name` varchar(25) NOT NULL, `contact_number` int NOT NULL, `contact_dob` date DEFAULT NULL, PRIMARY KEY (`contact_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
Let’s see now MySQL 8 Query Rewriter Plugin in action:
Step 1: Update rewrite_rules table in database query_rewrite with SQL pattern and replacement for the same query
Step 2: Call the function query_rewrite.flush_rewrite_rules()
|
1 2 3 4 5 |
mysql> INSERT INTO query_rewrite.rewrite_rules(pattern_database, pattern, replacement) VALUES ( 'MDBLAB','SELECT * FROM qryrrt WHERE contact_id > ? ','SELECT * FROM qryrrt WHERE contact_id > ? LIMIT 10') ; Query OK, 1 row affected (0.01 sec) mysql> CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.01 sec) |
Note:
If you ever wrongly configure rewrite_rules table, You will get the error message ERROR 1644 (45000): Loading of some rule(s) failed during the flush_rewrite_rules function call and we can also check warning message to confirm if the query rules are applied or not
|
1 2 3 4 5 6 7 8 9 |
mysql> select id,pattern_database,pattern,replacement from query_rewrite.rewrite_rules\G *************************** 1. row *************************** id: 6 pattern_database: MDBLAB pattern: SELECT * FROM qryrrt WHERE contact_id > ? replacement: SELECT * FROM qryrrt WHERE contact_id > ? LIMIT 10 1 row in set (0.00 sec) mysql> |
Testing MySQL 8 Query Rewriter Plugin
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT * FROM qryrrt WHERE contact_id > 100; +------------+--------------+----------------+-------------+ | contact_id | contact_name | contact_number | contact_dob | +------------+--------------+----------------+-------------+ | 101 | Mathew Willy | 985322418 | 1972-07-18 | | 115 | Samsun Gerar | 9853122418 | 1973-09-06 | +------------+--------------+----------------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1105 Message: Query 'SELECT * FROM qryrrt WHERE contact_id > 100' rewritten to 'SELECT * FROM qryrrt WHERE contact_id > 100 LIMIT 10' by a query rewrite plugin 1 row in set (0.00 sec) |
From the above example we can confirm MySQL 8 Query Rewriter Plugin is working as expected
How to uninstall MySQL 8 Query Rewriter Plugin ?
To uninstall the MySQL 8 Query Rewriter Plugin you have to load the SQL file “uninstall_rewriter.sql”. It will drop the database, function, and uninstall the plugin as well.
DDL Rewriter Plugin
MySQL 8.0.16 introduced ddl_rewriter plugin to rewrite the CREATE TABLE statements received by the MySQL Server by removing ENCRYPTION, DATA DIRECTORY and INDEX DIRECTORY clauses, All this happens before server parses and executes them. MySQL DDL Rewriter is very helpful while restoring tables from SQL dump files created from databases that are encrypted or those having tables stored outside the data directory.
Installing DDL Rewriter Plugin:
|
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.02 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) |
Let’s understand how DDL Rewriter Plugin works by creating table ddl_rewrite
|
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_rewrite -> (contact_id int primary key, contact_name varchar(16),contact_dob date,note text) -> ENCRYPTION='Y' -> DATA DIRECTORY = '/var/lib/mysql/data' -> INDEX DIRECTORY = '/var/lib/mysql/index'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1105 Message: Query 'create table ddl_rewrite (contact_id int primary key, contact_name varchar(16),contact_dob date,note text) ENCRYPTION='Y' DATA DIRECTORY = '/var/lib/mysql/data' INDEX DIRECTORY = '/var/lib/mysql/index'' rewritten to 'create table ddl_rewrite (contact_id int primary key, contact_name varchar(16),contact_dob date,note text) ' by a query rewrite plugin 1 row in set (0.00 sec) mysql> show create table ddl_rewrite\G *************************** 1. row *************************** Table: ddl_rewrite Create Table: CREATE TABLE `ddl_rewrite` ( `contact_id` int NOT NULL, `contact_name` varchar(16) DEFAULT NULL, `contact_dob` date DEFAULT NULL, `note` text, PRIMARY KEY (`contact_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) |
In the example above 👆, ddl_rewriter plugin has removed both encryption and data/index directories of create table SQL command (warning confirms the same) and this feature is really useful when doing complex database structure migration.
Conclusion
MySQL infrastructure operations productivity tools simplifies the lives of DBAs and Database SREs, I am sure MySQL 8 Query Rewriter Plugins and DDL Rewriter will improve a lot for good in future releases of MySQL, Thanks for your comments and feedbacks.
