2012-06 网易DBA 王洪权:Mysql 5.6新特性介绍

文字内容
1. Mysql 5.6 新特性介绍 网易DBA 王洪权 mydbalife@gmail.com 2012-06-06
2. 主要内容 mysql 5.6 查询优化 InnoDB performance mysql replication
3. Mysql 5.6 新特性--index condition pushdown Index Condition Pushdown MySQL 5.6+, MariaDB 5.3+
4. 为什么需要index condition pushdown A non-index only read is a two-step process: 1.读索引 2.读记录 3.检查where条件,做过滤
5. index condition pushdown原理 1.读索引 2.检查索引的条件 3.读整行记录 4.检查where条件,过滤数据
6. Mysql 5.6 新特性--index condition pushdown #基础表 注意: mysql 在复合索引中,第一列是范围查询,第二列通常是无法利用索引的,建议第一列的查询为=, <=>, or IS NULL
7. using where VS index condition pushdown # 早期版本,5.6 以下 MariaDB 5.3 以下 # MariaDB 5.3+, MySQL5.6+
8. using where VS index condition pushdown # 早期版本,5.6 以下 MariaDB 5.3 以下 # MariaDB 5.3+, MySQL5.6+
9. Mysql 5.6 新特性-- index condition pushdown
10. Mysql 5.6 新特性--index condition pushdown Counter Name MySQL5.5 MySQL5.6 Created_tmp_disk_tables 0 0 Handler_read_key 491 495 Handler_read_next 14667 113 Handler_read_rnd_next 200761 200693 Innodb_buffer_pool_read_ahead 1913 1912 Innodb_buffer_pool_read_requests 103626 37440 Innodb_buffer_pool_reads 13909 1265 Innodb_data_read 261410816(249M) 54235136(51M) Innodb_data_reads 15834 3189 Innodb_pages_read 15821 3176 Innodb_rows_read 214667 200113 Select_scan 4 4 Sort_scan 0 0 Innodb_buffer_pool_pages_data 239.13M 44.8906M 查询耗时 1m13.334s 0m9.907s
11. index condition pushdown 性能上的提升 一方面提升了查询性能,使的联合索引的范围查询速度 得到很大提升 令一方面,节省了BP中的内存空间。
12. Mysql 5.6 新特性--Multi-Range-Read Multi-Range-Read MySQL 5.6+, MariaDB 5.3+
13. Mysql 5.6 新特性--Multi-Range-Read 随机读,接近转换成顺序读
14. Mysql 5.6 新特性--Multi-Range-Read
15. Multi-Range-Read 在IO密集型情况测试数据 MySQL5.5 MySQL5.6 MySQL5.5 read_rnd_bufer_size=4M MySQL5.6 read_rnd_bufer_size=4M Created_tmp_disk _tables 0 0 0 0 Handler_read_key 283752 340796 283752 340796 Handler_read_nex t 286093 286093 286093 286093 Handler_read_rnd _next 37944 37944 37944 37944 Innodb_buffer_po ol_read_ahead 0 17776 0 17776 Innodb_buffer_po ol_read_requests 1551628 1461964 1551384 1461847 Innodb_buffer_po ol_reads 77336 40371 77347 40370 Innodb_data_read 1269256192(1.2G) 954863616(910M) 1269436416(1.2G) 954847232(910M) Innodb_data_read s 77350 58161 77361 58160 Innodb_pages_rea d 77335 58146 77346 58145 Innodb_rows_rea d 398157 455197 398157 455197 Select_scan 1 1 1 1 Sort_scan 1 1 1 1 查询耗时 10m57.584s 2m18.757s 6m31.233s 2m43.835s Counter Name
16. Multi-Range-Read 小结 在数据量很大的情况下, 使用MRR,变成顺序读,性能提高还是很 大的,顺序读是非常快,因为: 1 磁头寻道变得顺序,不会再来回寻道。 2 mysql 有线性预读功能。 3 每个数据页将只被读取一次,避免了多次对同一个页的读 但是也有一种除外: 如果你的表很小的话,填充在OS cache中,采用MRR会有一定 的CPU开销
17. Mysql 5.6 新特性--Batched Key Access Batched Key Access MySQL 5.6+, MariaDB 5.3+
18. Mysql 5.6 新特性--Batched Key Access 依赖MRR,随机读,接近转换成顺序读
19. Batched Key Access
20. Batched Key Access 在IO密集型情况测试数据 Counter Name MySQL5.5默认配置 (join_buffer_size=128K read_rnd_bufer_size=128K) MySQL5.6默认 join_buffer_size=128K read_rnd_bufer_size=256K MySQL5.5 join_buffer_size=6M read_rnd_bufer_size=6M MySQL5.6 join_buffer_size=6M read_rnd_bufer_size=6M Created_tmp_disk_tables 0 0 0 0 Handler_read_key 203944 1076746 203944 1076746 Handler_read_next 872798 872798 872798 872798 Handler_read_rnd_next 161500 164237 161500 161500 Innodb_buffer_pool_read _ahead 1726 1663 1728 65826 Innodb_buffer_pool_read _requests 4141420 3898204 4141212 3360574 Innodb_buffer_pool_read s 164888 180491 164838 13584 Innodb_data_read 2731986944(2.5G) 2986594304(2.7G) 2731200512(2.5G) 1303236608(1.2G) Innodb_data_reads 166627 182167 166579 79423 Innodb_pages_read 166613 182153 166565 79409 Innodb_rows_read 1022798 1895596 1022798 1895596 Select_scan 2 2 2 2 Sort_scan 1 1 1 1 查询耗时 20m10.271s 19m55.038s 18m39.165s 6m16.147s
21. Batched Key Access 小结
22. InnoDB performance Page cleaner 线程的引入 (before master thread) 1 减轻了主线程的工作,脏页的刷新由page cleaner线程进行。 2 page cleaner线程,处理dirty page的flush动作(包括LRU list flush与 flush list flush),降低page flush对于用户的影响. page_cleaner 这个线程每秒都会被唤醒一次
23. InnoDB performance 死锁检测增强 set global innodb_print_all_deadlocks=on 5.6 中引入参数innodb_print_all_deadlocks,这个参数是全局设置的, 可以把所有的死锁状况打印到error日志中,如果应用程序不具有相应的 错误处理逻辑检测回滚操作,这个参数将对你在进行故障诊断的时候很有 帮助 先前检测锁状况show engine innodb status\G; 在mysql库下创建表create table innodb_lock_monitor(a int)将信息 打印到error log
24. InnoDB performance Undo 从系统表空间分离 涉及到分离出undo表空间的参数 1. innodb_undo_directory 只读变量,是不能动态修改的,在启动的时候设置,注意这个不能指定多 个undo表空间到多个位置 2. innodb_undo_tablespaces 设置undo 表空间的个数.默认单个undo_tabkespace大小10M大小 3. inodb_undo_logs(代替了先前的参数innodb_rollback_segments) 控制着回滚段的数量(注意范围是0-128) 默认不指定的时候是128个回 滚段。(注意要想增加回滚段的时候必须要重启mysql) 官方建议最好是放在SSD上,有待改进:动态的添加和删除undo表空间
25. InnoDB performance Innodb page size 增强 Innodb_page_size 设置4k,8k,16k灵活设置 1.小的内存页以为着每个页中存储的数据就更少,对于SSD设备,它没有 寻道花费的开销,这个时候就会读更多的页到内存,而每个页中有效的记 录更多,这样的话,整体的会使内存更有效的利用. 2.更多的数据页,肯定会造成BP管理上的开销 实施: 导出你现有库的数据,通过逻辑导出(mysqldump),移动或者直接删除 系统表空间ibdata1和日志文件(ib_logfile0 & ib_logfile1),重新再 my.cnf 中设置innodb-page-size 到 4k 或者 8K,导入。
26. InnoDB performance Page checksum 增强 innodb_checksum_algorithm 值innodb ,strict_innodb, crc32 ,strict_crc32,none strict_none 好处: 保护数据损坏 硬件损坏, 软件的Bugs, Innodb 自身的Bugs 并不能完全代替文件系统的Checksums Checksum在什么时候发生呢? 当页从硬盘读到BP的时候 当页更新的后刷新到磁盘 可能会有一定的开销 注意: 在crc32之前,对页头和页尾的校验算法是不一样,因此页头和页尾存 储的值是不一样的。crc32的校验算法页头和页尾的校验方法是是一样的, 所以页头和页尾的值是一样的。
27. InnoDB performance 统计信息持久化 5.6 引入innodb_analyze_is_persistent 默认 OFF,innodb_stats_persistent_sample_pages 默认是20,可动态调整的作用范围 是全局 innodb_stats_sample_pages 废弃默认8 innodb_stats_transient_sample_pages 新版本默认8 innodb_stats_on_metadata 默认ON innodb_stats_on_metadat 该参数控制着以下操作是否自动收集统计信息 * 当表打开的时候 * 当表有太多的改变(由于插入更新或者删除操作,表的1/16数据已经发生变化) * 当运行 ANALYZE TABLE 分析具体的表的时候 * 当执行SHOW TABLE STATUS 或者 SHOW INDEX FROM *当访问 INFORMATION_SCHEMA.TABLES 或者INFORMATION_SCHEMA.STATISTICS. 优缺点: 可以让sql 的执行计划更稳定。 需要定期analyze table 以保证执行计划的信息部过期
28. InnoDB performance innodb_purge_threads 可以设置大于1,有效的进行purge InnoDB REDO log size up to 512 Gbyte (日志大小达到了512G,有效的 提高了检查点的写入)
29. InnoDB performance 优化器跟踪 这个特性默认是关闭的,全局开启优化器跟踪会造成大约20%的性能开销, 可以查看join顺序,执行计划中的详细信息,花费的成本。最终为什么选 择该执行计划 设置跟踪信息占用内存大小,开启跟踪 SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; SET end_markers_in_json=true; set optimizer_trace='enabled=on,one_line=off'; SQL语句 查看跟踪信息 select * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; dump跟踪信息 SELECT TRACE INTO DUMPFILE "/tmp/trace22.json" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
30. InnoDB performance Explain 增强 在MySQL 5.6.3 中 EXPLAIN 命令可以用于数据修改执行计划可以应用于 INSERT, REPLACE, UPDATE and DELETE和SELECT EXPLAIN FORMAT=JSON SQL; 可以更加清晰的看到执行计划的一些额外信息。 infomation_schema增强 MySQL 5.6:添加11个New INFORMATION_SCHEMA 表 7 数据字典相关的表 3 个 Buffer Pool 相关的表 1 个全局统计相关的表 PERFORMANCE_SCHEMA库下添加一些用于诊断数据库性能的表,可 以通过这些表监控系统的整个状况
31. mysql replication slave使用表来保存复制信息 复制事件checksum 多线程slave 延时复制 优化了基于行的复制 查询日志中的内容写入到binlog 远程备份binlog 全局事务标识IDS
32. slave使用表来保存复制信息 MySQL <5.6 事务数据在表里 复制信息在文件里 MySQL 5.6 事务数据在表里 复制信息在表里
33. slave使用表来保存复制信息 • 系统表: – mysql.slave_master_info (master.info) – mysql.slave_relay_log_info (relay-log.info) 启动的时候设置或天添加参数到my.cnf --master-info-repository=TABLE --relay-log-info-repository=TABLE 启动后设置 SET GLOBAL master_info_repository = 'TABLE'; SET GLOBAL relay_log_info_repository = 'TABLE'; start slave;
34. slave使用表来保存复制信息 Slave Tables for Replication Information select * from slave_master_info
35. 复制事件checksum 当事件被应用之前,检测复制事件中的错误。 Guards against bugs and disk or network corruptions CRC-32 校验, 比 ISO-3309 更精准(支持zlib算法) mysqld 设置: binlog-checksum= NONE or CRC32 每个 session都会产生checksum值,并且写入到binlog SET GLOBAL binlog_checksum = 1; master-verify-checksum= 0 or 1 Master 当从binlog dump事件的时候会校验checksum值 SET GLOBAL master_verify_checksum = 1; slave-sql-verify-checksum= 0 or 1 SQL线程当从relay log读取事件应用到slave之前会校验checksum 值 mysql> SET GLOBAL slave_sql_verify_checksum=1;
36. 复制事件checksum binlog-checksum对应图中1 master-verify-checksum对应图中2 slave-sql-verify-checksum对应图中5 http://mysqlmusings.blogspot.com/2011/04/replication-eventchecksum.html
37. Multi-Threaded Slaves
38. Multi-Threaded Slaves mysql> SET GLOBAL slave_parallel_workers=2; (root:testdb:Wed'>root:testdb:Wed May 30 11:21:19 2012)[(none)]> SELECT @@slave_parallel_workers; +--------------------------+ @@slave_parallel_workers +--------------------------+ 2 +--------------------------+ 1 row in set (0.00 sec) slave_parallel_workers 线程数量 • 0 – 禁用 • 最大 1024 (root:testdb:Wed'>root:testdb:Wed May 30 11:21:50 2012)[(none)]> show processlist; +----+-----------------+-----------+------+---------+------+----------------------------------------------------------------------------+------------------+ Id User Host db Command Time State Info +----+-----------------+-----------+------+---------+------+----------------------------------------------------------------------------+------------------+ 12 system user NULL Connect 190 Waiting for master to send event NULL 13 system user NULL Connect 190 Slave has read all relay log; waiting for the slave I/O thread to update it NULL 14 system user NULL Connect 190 Waiting for an event from Coordinator NULL 15 system user NULL Connect 190 Waiting for an event from Coordinator NULL +----+-----------------+-----------+------+---------+------+----------------------------------------------------------------------------+------------------+
39. Multi-Threaded Slaves (root:testdb:Wed May 30 11:19:16 2012)[(none)]> SELECT * FROM mysql.slave_worker_info\G *************************** 1. row *************************** Master_id:'>id:'>id:'>id:'>id:'>id:'>id:'>id: 30082 Worker_id:'>id:'>id:'>id:'>id:'>id:'>id:'>id: 0 Relay_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Relay_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Master_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Master_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_relay_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Checkpoint_relay_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_master_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Checkpoint_master_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_seqno:'>seqno: 0 Checkpoint_group_size:'>size: 64 Checkpoint_group_bitmap:'>bitmap: *************************** 2. row *************************** Master_id:'>id:'>id:'>id:'>id:'>id:'>id:'>id: 30082 Worker_id:'>id:'>id:'>id:'>id:'>id:'>id:'>id: 1 Relay_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Relay_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Master_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Master_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_relay_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Checkpoint_relay_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_master_log_name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name:'>name: Checkpoint_master_log_pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos:'>pos: 0 Checkpoint_seqno:'>seqno: 0 Checkpoint_group_size:'>size: 64 Checkpoint_group_bitmap:'>bitmap:
40. Multi-Threaded Slaves 影响单个slave 的worker线程的队列的参数为slave_pending_jobs_size_max
41. Multi-Threaded Slaves
42. Multi-Threaded Slaves 性能上的改进: • 提高了slave 性能 • slave端的sql应用在不同数据库间变成并行:减少了slave的滞后,增 加了slave的吞吐量 • 变成数据库(schema)级别的复制,每个数据库的更改将会被应用, 注意这里每个库的应用还是串行的,并且提交变得相互独立 • 在重启恢复的时候是自动完成的(串行) • Slave段所有的事务日志都已经写入到了relay log保证了数据部丢失。 最糟糕的情况是所有的操作都是对同一个库进行操作。
43. 延时slave •在每个slave sql线程执行的时候都要等延迟时间到后进行下一个操作。 • 最大支持 68 年。 • 在一定程度上防止了误操作,比如说删表等等。 • 可以一定程度上作为有效的数据库备份,无需再另行备份。
44. 延时slave 案例 操作人员上午误删除了test库的test1 表,数据库没有备份,只有延时slave ,延时slave 配置延时1天 当发生问题的时候我们要做的是,在主库上查找删除表的位置,也可 以用 mysqlbinlog查找。 master> SHOW BINLOG EVENTS\G *************************** 12. row *************************** Log_name: ws2-bin.000001 Pos: 984 Event_type: Query Server_id: 1 End_log_pos: 1096 Info: use `test`; DROP TABLE `test1` /* generated by server */ 在从库上 slave2> STOP SLAVE; slave2> START SLAVE UNTIL -> MASTER_LOG_FILE='ws2-bin.000001', -> MASTER_LOG_POS=984;
45. 全局唯一server IDs 在主库上: 获得主库的uuid (root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu May 31 13:18:58 2012)[(none)]> show variables like '%uuid%' ; +---------------+--------------------------------------+ Variable_name Value +---------------+--------------------------------------+ server_uuid dad8f1f6-18af-11e1-992c-000c29d4b063 +---------------+--------------------------------------+ 1 row in set (0.00 sec) 获得从库的uuid (root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu May 31 13:19:01 2012)[(none)]> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ Server_id Host Port Master_id Slave_UUID +-----------+------+------+-----------+--------------------------------------+ 30082 3306 30081 9fe4cfec-a971-11e1-891c-000c299499ff +-----------+------+------+-----------+--------------------------------------+ 在从库上: (root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu'>root:testdb:Thu May 31 13:16:32 2012)[(none)]> show slave status\G; Master_Server_Id: 30081 Master_UUID: dad8f1f6-18af-11e1-992c-000c29d4b063 Master_Info_File: mysql.slave_master_info SQL_Delay:'>Delay: 60 SQL_Remaining_Delay:'>Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: 获得主库的uuid 另外这连个在mysql启动时候会在数据目录下生成一个auto.cnf ,uuid存储在 auto.cnf 文件 中 [root@master mysql]# cat auto.cnf [auto] server-uuid=dad8f1f6-18af-11e1-992c-000c29d4b063 [root@save mysql]# cat auto.cnf [auto] server-uuid=9fe4cfec-a971-11e1-891c-000c299499ff
46. Global Transaction Identifier
47. Global Transaction Identifier Global Transaction Identifier(全局事务表示符,实际上是以一个事件的 形式存在mysql 中) 在每个事务提交时会立刻记录到binlog 中,在binlog中每次的提交都会有 GTD标示符。 GTD结构如下:GTID = · SID目前,它是一个128位的数字,用于标识提交的事务/(组提交事务) 事件。 SID通常是服务器的UUID,但可能不同,如果事务是由innodb以外的 存储引擎可能不同。例如,对于NDB的,它将表示整个集群。 · GNO是一个64位序列号:1代表第一次改变记录的SID,2代表第二变化, 等等。没有变化,可以表示为:GNO0。
48. Global Transaction Identifier GTD配置 要求mysql 5.6 在master 和 slave都要做 # 修改配置文件 my.cnf 在msater和slave 上 # (master and slaves) [mysqld] log-bin log-slave-updates gtid-mode=ON disable-gtid-unsafe-statements # 重启 servers Disable-gtid-unsafe-statements 将会使下述情况发生时,向用户返回错误s 不能在以下的情况下工作: 1 非事务性更改,例如mysam,因为非事务性数据部能回滚操作。 2 在一个事物中使用了临时表 3 使用create table ....select
49. Global Transaction Identifier Slave2 具体改变语句如下: Stop slave; CHANGE MASTER TO MASTER_HOST='...', MASTER_PORT=SOME_PORT, MASTER_USER='...', MASTER_AUTO_POSITION=1; START SLAVE; SET @@global.read_only = ON; MASTER_AUTO_POSITION=1 将会代替以前手动使用的MASTER_LOG_FILE 和 MASTER_LOG_POS,告诉服务器使用GTD(GTD协议会使得中从实现握手操作),这个时候, 当slave启动的时候,master和slave 将会自动相互通信,然后slave从正确的时间 点向后开始复制,这个切换过程只需要slave上操作MASTER_AUTO_POSITION=1,以 后的整个复制过程将是自动的。 注意这里从的binlog里记录的 GTD是主的GTD。
50. Thank you 谢谢