Percona Server for MySQL 5.7.21-20 GA is released on February 19, 2018. You can download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository. In this blog I would like to share with you few of my favourite enhancements in Percona Server for MySQL 5.7.21-20 GA InnoDB, performance optimization, replication and security :
InnoDB Enhancements:
- Supports online ALTER TABLE RENAME INDEX operations so we can now remake the index without table copy operations .
- We can resize innodb_buffer_pool_size dynamically, This makes buffer pool resize without restarting MySQL server possible .
- InnoDB supports Transportable Tablespace for partitioned InnoDB tables .
- More responsive Innochecksum utility , For those who don’t know about Innochecksum – This tool calculates the checksum for each InnoDB page and reports mismatches .
- MySQL 5.7 InnoDB supports “spatial indexes” and online DDL operation .
- Optimized InnoDB buffer pool dump / reload operations, A new system variable innodb_buffer_pool_dump_pct allows you to specify percentage of most recently used pages in each buffer pool to read out and dump
Performance Optmization
- MySQL 5.7 optimizer avoids the creation of temporary table for results of UNION ALL queries, This greatly benefits in reducing disk I/O and storage. Morgan Tocker blog shares more info. on this .
- MySQL 5.7 supports generated columns (virtual columns or computed columns) . This is a great feature, It’s actually adding a column + index on any logical schema without really any data redundancy .
- Faster bulk data load – InnoDB in MySQL 5.7 performance much faster bulk data loading when creating or rebuilding indexes through sorted index build, This also makes creation of FULLTEXT indexes faster .
- InnoDB in MySQL 5.7 support parallel flushing of dirty pages from the buffer pool(s). I remember using two-threaded flushing from Percona Server 5.6 .
- Explain already running queries with EXPLAIN FOR CONNECTION , This makes proactive MySQL optimization possible .
- JSON format for EXPLAIN improved a lot from MySQL 5.6 to 5.7 – JSON format for EXPLAIN in MySQL 5.7 prints total cost of the query execution so that you can compare execution plans for both optimal and expensive SQL
Replication
- Multi-source replication – You can have several masters for a slave, This makes transactions from multiple master databases propagated towards slave database through multiple communication channels .
- MySQL 5.7 performance schema database tables have all the information about SHOW SLAVE STATUS. Performance schema tables information on SHOW SLAVE STATUS can be used to generate more informative reports, More information on this can be found in MySQL 5.7 documentation here
- MySQL 5.7 generates faster SHOW SLAVE STATUS reports by not waiting from STOP SLAVE to finish which can be blocked by long running SQL query from replication. I remember using Percona LOCK FREE SHOW SLAVE STATUS feature from Percona Server 5.5
- Since MySQL 5.7 you can perform CHANGE MASTER TO without stopping slave via STOP SLAVE command
- MySQL 5.7 has now much improved parallel replication . We have new system variable slave_parallel_type , When using a multi-threaded slave (slave_parallel_workers is greater than 0), this option specifies the policy used to decide which transactions are allowed to execute in parallel on the slave , You can read more about it here
- More responsive Global Transaction Identifiers (GTID) mode, grid_mode system variable can be enabled / disabled dynamically, This means you can now enable / disable GTID in replication without synchronising and restarting the entire set of MySQL servers . In Percona Server 5.6 I have used online GTID deployment feature so that you can deploy GTID on existing replication infrastructure without making MySQL master read_only and stopping all slaves .
