MySQL 8 Query Rewriter Plugins and DDL Rewriter

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 :

» Install Query Rewriter Plugin

The install_rewriter.sql will create schema query_rewrite and table rewrite_rules 

» MySQL 8 Query Rewriter Plugin test case

I have created table qryrrt with some data for testing MySQL 8 Query Rewriter Plugin

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()  

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

Testing MySQL 8 Query Rewriter Plugin

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:

Let’s understand how DDL Rewriter Plugin works by creating table ddl_rewrite

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.

 

About Shiv Iyer 18 Articles
Open Source Database Systems Geek in MySQL, MariaDB, PostgreSQL and ClickHouse with core expertise in performance, scalability, high availability and database reliability engineering, Shiv currently is the founder and principal of MinervaDB, An boutique private-label consulting, support, remote DBA and education services provider for MySQL, MariaDB, Percona Server, MyRocks, PostgreSQL and ClickHouse with over 300 customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN Microsystems, AOL, eBay, PayPal, PalominoDB and Percona. Shiv also is a frequent speaker in open source conferences worldwide.