Percona Server for MySQL 5.7.21-20 GA is released on February 19, 2018. You can download\u00a0the latest version from the Percona web site<\/a><\/strong><\/span>\u00a0or the Percona Software Repositories<\/a><\/strong><\/span>. You can also run Docker containers from the images in the Docker Hub repository<\/a><\/strong><\/span>. In \u00a0this blog I would like to share with you few of \u00a0my favourite enhancements in Percona Server for MySQL 5.7.21-20 GA\u00a0 InnoDB, performance optimization, replication and security :<\/p>\n

InnoDB Enhancements:<\/strong><\/span><\/p>\n

\n
• Supports online ALTER TABLE RENAME INDEX operations so we can now remake the index without table copy operations .<\/li>\n
• We can resize innodb_buffer_pool_size dynamically, This makes buffer pool resize without restarting MySQL server possible .<\/li>\n
• InnoDB supports Transportable Tablespace for partitioned InnoDB tables .<\/li>\n
• More responsive Innochecksum utility , For those who don’t know about Innochecksum – This tool\u00a0calculates the checksum for each InnoDB page and reports mismatches .<\/li>\n
• MySQL 5.7 InnoDB supports “spatial indexes” and online DDL operation .<\/li>\n
• 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<\/li>\n<\/ul>\n

  Performance Optmization<\/strong><\/span><\/p>\n

  \n
  • MySQL 5.7 optimizer avoids the creation of temporary table for results of UNION ALL\u00a0<\/strong>queries, This greatly benefits in reducing disk I\/O and storage. Morgan Tocker blog<\/a><\/strong><\/span> shares more info. on this .<\/li>\n
  • 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 .<\/li>\n
  • 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 .<\/li>\n
  • 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 .\u00a0<\/em><\/li>\n
  • Explain already running queries with EXPLAIN FOR CONNECTION , This makes proactive MySQL optimization possible .<\/li>\n
  • 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<\/li>\n<\/ul>\n

    Replication<\/strong><\/span><\/p>\n

    \n
    • 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 .<\/li>\n
    • MySQL 5.7 performance schema database tables have all the information about SHOW SLAVE STATUS. \u00a0Performance 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<\/a><\/strong><\/span><\/li>\n
    • 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<\/li>\n
    • Since MySQL 5.7 you can perform CHANGE MASTER TO without stopping slave via STOP SLAVE command<\/li>\n
    • MySQL 5.7 has now much improved parallel replication . We have new system variable slave_parallel_type<\/em><\/strong>\u00a0, When using a multi-threaded slave (slave_parallel_workers is greater than 0<\/strong><\/em>), 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<\/span><\/strong><\/a><\/li>\n
    • 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 . \u00a0In 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 .<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

      Percona Server for MySQL 5.7.21-20 GA is released on February 19, 2018. You can download\u00a0the latest version from the Percona web site\u00a0or the Percona Software Repositories. You can also run Docker containers from the images […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,2,15,91],"tags":[24,94,16,93],"yoast_head":"\n