MySQL 5.5&5.6 new features summary

Transcript

1. www.vcmd.org MySQL 5.5 &5.6 new feature Summary In last article MySQL-oslayer-performance-optimization we talked about some important tips for using MySQL on OS layer. Continue . . . MySQL 5.5 GA was the first GA after Oracle taking over MySQL AB (Oracle bought Sun in 2009 including MySQL). As a very important part of Oracle software (main for database area) especially in LAMP area MySQL 5.5 has a very huge performance promotion (compare old versions). Many features have been released to make MySQL better for OLTP database system. In 5.5 GA InnoDB has a huge performance promotion in SMP system . Later in 5.6 with more and more features (especially replication and management area) MySQL becomes more smart and controllable. Totally MySQL 5.5 has below features:  Using CMake as default compile system.  InnoDB was the default engine of MySQL  InnoDB performance has a huge performance promotion  MySQL performance was better and better on all of platforms  Better utilization of SMP system  More performance monitor and management (new DB performance_schema)  More features of Replication
2. www.vcmd.org  Performance improvement in 5.5 InnoDB becomes default database engine. In MySQL 5.5 InnoDB becomes the default engine instead of MyISAM. And before 5.5 many DB systems still use MyISAM as their engine and even can get nice performance for particular SQL operation (like count without “where” conditional) but in today’s OLTP system (more CPUs more parallel processing and more TPS requirement) ACID transactions seems very important . As InnoDB has already done coding reconstruction and has better performance for parallel transactions so to be the default engine seems necessary. (MyISAM read write queue)
3. www.vcmd.org  File format “Barracuda” support dynamic and compressed row format. The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but this new format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row. The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format, with additional storage and performance considerations from the table and index data being compressed and using smaller page sizes. With the COMPRESSED row format, the option KEY_BLOCK_SIZE controls how much column data is stored in the clustered index, and how much is placed on overflow pages. For full details about the COMPRESSED row format Result of diff engine for compression Size on disk Engine Compression Ratio 7.1M Archive 173.07:1 304M InnoDB row_format=compressed key_block_size=4 4.04:1 648M compress() 1.897:1 1.2G InnoDB 1:01
4. www.vcmd.org  MDL (meta data locking) In MySQL 5.5 when you reconstruct table MySQL will gather MDL to lock table metadata (DML operations will be blocked) in some situations read operation maybe blocked also ( add primary key , show create table will hang as metadata was locked in X mode) Improve MySQL performance on win32/64 bit https://blogs.oracle.com/MySQLinnodb/entry/MySQL_5_5_innodb_performance
5. www.vcmd.org  InnoDB recovery is now faster…much faster! By Calvin Sun on Apr 13, 2010 Note: this article was originally published on http://blogs.innodb.com on April 13, 2010 by Inaam Rana. One of the well known and much written about complaint regarding InnoDB recovery is that it does not scale well on high-end systems. Well, not any more. In InnoDB plugin 1.0.7 (which is GA) and plugin 1.1 (which is part of MySQL 5.5.4) this issue has been addressed. Two major improvements, apart from some other minor tweaks, have been made to the recovery code. In this post I’ll explain these issues and the our solution for these. So at time of crash we had: Modified db pages 1007907 Redo bytes: 3050455773 And the recovery times were: Plugin 1.0.7 (also Plugin 1.1): 1m52s scan, 12m04s apply, total 13m56s Plugin 1.0.6: 31m39s scan, 7h06m21s apply, total 7h38m 1.0.7 (and Plugin 1.1) is better 16.95x on scan, 35.33x on apply, 32.87x overall https://blogs.oracle.com/MySQLinnodb/entry/innodb_recovery_is_now_faster
6. www.vcmd.org  InnoDB support multi buffer pool In 5.5 MySQL will support multi buffer pool instance to reduce contention of free list flush list LRU and so on. Mutex contention will be distribute to different pool instance  Better Scalability with Multiple Rollback Segments Starting in InnoDB 1.1 with MySQL 5.5, the limit on concurrent transactions is greatly expanded, removing a bottleneck with the InnoDB rollback segment that affected high-capacity systems. The limit applies to concurrent transactions that change any data; read-only transactions do not count against that maximum. The single rollback segment is now divided into 128 segments, each of which can support up to 1023 transactions that perform writes, for a total of approximately 128K concurrent transactions. The original transaction limit was 1023.
7. www.vcmd.org  InnoDB thread concurrency limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads. The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks. The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). When “innodb_thread_concurrency”=0 “innodb_spin_wait_delay” and “innodb_sync_spin_loops” will be disabled “innodb_thread_concurrency” exact best value based on your system configuration(adjust parameter setting based on your H/W) Detail test by dimitrik : 5.5 and InnoDB thread concurrency  InnoDB IO capacity There’re three parameters effect IO capacity : “innodb_read_io_threads” “innodb_write_io_threads” and “innodb_io_capacity”
8. www.vcmd.org innodb_flush_method=O_DIRECT Use Direct IO and fsync to flush data and log files innodb_max_dirty_pages_pct=60 Percentage of dirty pages to keep in the pool. Keep at 60-80% of total system memory. Always monitor memory usage to make sure the system does not start to swap out pages. innodb_write_io_threads=16 Number of background write I/O threads innodb_read_io_threads=8 Number of background read I/O threads innodb_adaptive_flushing=1 Adaptively flush dirty pages innodb_read_ahead=0 Disable read-ahead for ioMemory innodb_flush_neighbor_pages=0 Do not flush neighbor pages on fast seek-less ioMemory innodb_adaptive_checkpoint=ke ep_average Use adaptive check-pointing that keeps a steady I/O rate innodb_log_files_in_group=2 Number of log files in the group. The default and recommended value is 2 innodb_log_file_size=4G Reduce checkpoint activity by increasing log file size. This also increases recovery time, but fast storage like ioMemory handles this well. innodb_io_capacity=10000 Number of I/O operations (indicates capability of underlying hardware) innodb_thread_concurrency=0 Set unlimited number of threads Recommended best practice when using FusionIO on MySQL Reintroducing Random Readahead in InnoDB
9. www.vcmd.org  Adaptive hash index can be controlled Now we can disable adaptive hash index – there’re some problems when using AHI It appears when you have some scanning by secondary key select queries and write queries at the same time. InnoDB again uses single global mutex for adaptive_search (single mutex for ALL table and ALL indexes), so write query blocks ALL select queries. Usually first action was to disable adaptive_search (it’s possible via global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to do much more operations while reading secondary keys. Solution was provided by Percona: The InnoDB adaptive hash index can have contention issues on multi-core systems when you run a mix of read and write queries that need to scan secondary indexes. This feature splits the adaptive hash index across several partitions to avoid such problems.The number of adaptive hash partitions specified by the variable “innodb_adaptive_hash_index_partitions” are created, and hash indexes are assigned to each one based on index_id. This should help to solve contention problems in the adaptive hash search process when they occur. Version Info: 5.5.8-20.0 – Introduced Command Line: Yes Config File: Yes Scope: Global Dynamic: No Variable Type: Numeric Def : 1 Range: 1-64, (on 32-bit platform 1-32)
10. www.vcmd.org  Group Commit Group commit in InnoDB worked until MySQL 4.x, and works once again with MySQL 5.1 with the InnoDB Plugin, and MySQL 5.5 and higher. The introduction of support for the distributed transactions and Two Phase Commit (2PC) in MySQL 5.0 interfered with the InnoDB group commit functionality. This issue is now resolved. The group commit functionality inside InnoDB works with the Two Phase Commit protocol in MySQL. Re-enabling of the group commit functionality fully ensures that the ordering of commit in the MySQL binlog and the InnoDB logfile is the same as it was before. It means it is totally safe to use the MySQL Enterprise Backup product with InnoDB 1.0.4 (that is, the InnoDB Plugin with MySQL 5.1) and above. When the binlog is enabled, you typically also set the configuration option sync_binlog=0, because group commit for the binary log is only supported if it is set to 0.
11. www.vcmd.org  New parameters for replication Add three parameters (similar behavior as sync_binlog and you can set another two parameters "relay_log_info_repository" "master_info_repository" in 5.6) And you can set “relay_log_recovery” parameter to control “Automatic Relay Log Recovery” In the end, MySQL 5.5 has a huge performance promotion when using InnoDB engine. And Sysbench test also give a detail comparison graph between 5.1 and 5.5.
12. www.vcmd.org
13. www.vcmd.org And what’s new In MySQL 5.6 ? As MySQL 5.5 was the first release under Oracle’s ownership and giving a huge performance promotion ( InnoDB is more and more similar with oracle database) . In 5.6 Oracle focus on new features (seems that mysql was learning from oracle database including memory management and replication management etc..) In this article we will give some examples of these features.  New In replication 1. GTID was introduced Global Transaction Identifiers (GTIDs) – a unique identifier that is used accross your replication topology to identify a transaction. Makes
14. www.vcmd.org setting up and managing your cluster (including the promotion of a new master) far simpler and more reliable. [MySQLd] gtid-mode=on # GTID enforce-gtid-consistency=true # GTID master-info-repository=TABLE # Chrash-safe replication relay-log-info-repository=TABLE # Chrash-safe replication slave-parallel-workers=2 # MTS binlog-checksum=CRC32 # Checksums master-verify-checksum=1 # Checksums slave-sql-verify-checksum=1 # Checksums binlog-rows-query-log_events=1 # Informational logs slave> CHANGE MASTER TO MASTER_HOST='black', MASTER_USER='repl_user', MASTER_PASSWORD='billy', MASTER_AUTO_POSITION=1; 2. Multi-threaded slaves (MTS)
15. www.vcmd.org Replicate by per database to lead fast replication and resolve lag issue . For more replication info : multi Master and Transfer 3. Binary Log Group Commit -- Improves replication performance on the master
16. www.vcmd.org Reducing binary logging lock contention on the transaction execution path in 5.6: – MySQL 5.1 – all events are serialized and written while holding the log lock; – MySQL 5.5 – DDL and transaction boundaries events are serialized and written to the binary log while holding the log lock; – MySQL 5.6 – events are serialized and written without holding the log lock (except the incident event).
17. www.vcmd.org 4. Safe Replication Transfer to ACID transaction to ensure no data lose (using table to store information instead of system file) Controlled by “master_info_repository” and “relay_log_info_repository” parameters (default MyISAM tables and switch them to InnoDB for supporting ACID transactions) ALTER TABLE MySQL.slave_master_info ENGINE = InnoDB; ALTER TABLE MySQL.slave_relay_log_info ENGINE = InnoDB; 5. Optimized Row Based Replication Reduces the amount of data that needs to be replicated; reducing network usage and potentially speeding up replication. 6. Replication Event Checksums Reference these two articles: http://www.MySQLperformanceblog.com/2013/02/15/replication-checksums-in-MySQL-5-6/
18. www.vcmd.org http://MySQLmusings.blogspot.com/2011/04/replication-event-checksum.html 7. Time-Delayed Replication You can define a time delay for events to be replicated from a master to each slave, defined in millisecond increments up to a maximum of 68 years! Time-Delayed Replication affords protection against operational errors made on the master, for example accidently dropping tables, in which event the slave can be promoted to the new master in order to restore the database to its previous state. Time-Delayed Replication can also be useful in testing application behavior by emulating any instances of replication lag. Time-Delayed Replication is implemented at the per-slave level (via holding execution of the SQL_THREAD), so you could configure multiple slaves to apply replication events immediately, and another slave to apply only after a delay of 5 minutes, therefore providing deployment flexibility.  InnoDB new features 1. Online DDL support (Ref: http://dev.MySQL.com/doc/refman/5.6/en/innodb-online-ddl.html) 2. Transportable tables MySQL> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.5.27 |
19. www.vcmd.org +-----------+ 1 row in set (0.00 sec) MySQL> CREATE TABLE tt_55 (a INT PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.06 sec) MySQL> INSERT INTO tt_55 VALUE (1); Query OK, 1 row affected (0.01 sec) MySQL> EXIT Bye C:\MySQL-5.6.6-m9-winx64>REM shutting down 5.5 to get clean tt_55.ibd file C:\MySQL-5.6.6-m9-winx64>bin\MySQLadmin -uroot -P3309 shutdown C:\MySQL-5.6.6-m9-winx64>bin\MySQL -uroot -P3307 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.6-m9 MySQL Community Server (GPL) ... MySQL> CREATE TABLE tt_55 (a INT PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.09 sec) MySQL> FLUSH TABLES tt_55 FOR EXPORT; Query OK, 0 rows affected (0.02 sec) MySQL> -- save a copy of tt_55.cfg MySQL> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
20. www.vcmd.org MySQL> ALTER TABLE tt_55 DISCARD TABLESPACE; Query OK, 0 rows affected (0.04 sec) 3. MySQL> -- copy tt_55.ibd from 5.5 install to 5.6 MySQL> -- make sure tt_55.cfg is in proper location MySQL> ALTER TABLE tt_55 IMPORT TABLESPACE; Query OK, 0 rows affected (0.10 sec) MySQL> SELECT * FROM tt_55; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.00 sec) 4. FULLTEXT indexes (Ref: http://dev.MySQL.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index) CREATE TABLE egs (id int unsigned auto_increment primary key , author varchar(64) , name varchar(4000) , source varchar(64) , fulltext(name) ) engine=innodb;
21. www.vcmd.org SELECT author AS "MySQL" FROM egs WHERE match(name) against ('MySQL' in natural language mode); 5. InnoDB and memcached Integration When integrated with MySQL Server, memcached is implemented as a MySQL plugin daemon, accessing the InnoDB storage engine directly and bypassing the SQL layer -standard memcached client and php mem-cli will both work -Passing SQL layer direct use memcached in MySQL
22. www.vcmd.org 6. Separate Tablespaces for InnoDB Undo Logs This feature allows you to store the InnoDB undo log in one or more separate tablespaces outside of the system tablespace. The I/O patterns for the undo log make these tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces.  Partition new features Explicit partition selection http://dev.MySQL.com/doc/refman/5.6/en/partitioning-selection.html Partition exchange http://dev.MySQL.com/doc/refman/5.6/en/partitioning-management-exchange.html Partitions: lock pruning http://dev.MySQL.com/doc/refman/5.6/en/partitioning-limitations-locking.html
23. www.vcmd.org  SQL Optimizer Subquery has bad performance before MySQL 5.6 and now MySQL becomes more smart to do some similar works. ICP was introduced to reduce MySQL data transfer and internal data filter http://dev.MySQL.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html http://www.MySQLperformanceblog.com/2012/03/12/index-condition-pushdown-in-MySQL-5-6-and-mariadb-5-5-and-its-performance-impac t/ http://jorgenloland.blogspot.com/2011/08/MySQL-range-access-method-explained.html Explain now can cover DML (Insert/update/delete not only select) Explain Json format 18:04:30 (none)> select version(); +------------+ | version() | +------------+ | 5.6.12-log | +------------+ 1 row in set (0.00 sec) 18:06:43 cobub_data> EXPLAIN FORMAT=JSON SELECT count(*) from razor_hour24 where hour >1 \G; *************************** 1. row ***************************
24. www.vcmd.org EXPLAIN: { "query_block": { "select_id": 1, "table": { "table_name": "razor_hour24", "access_type": "range", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "hour" ], "key_length": "1", "rows": 22, "filtered": 100, "using_index": true, "attached_condition": "(`cobub_data`.`razor_hour24`.`hour` > 1)" } } } 1 row in set, 1 warning (0.01 sec) ERROR: No query specified
25. www.vcmd.org To Be Continue . . .