Mysql5.6新特性介紹.ppt

網(wǎng)易DBA 王洪權(quán) 2012-06-06,Mysql 5.6 新特性介紹,主要內(nèi)容,mysql 5.6 查詢優(yōu)化 InnoDB performance mysql replication,Index Condition Pushdown MySQL 5.6+, MariaDB 5.3+,Mysql 5.6 新特性-index condition pushdown,為什么需要index condition pushdown,A non-index only read is a two-step process: 1.讀索引 2.讀記錄 3.檢查where條件，做過濾,index condition pushdown原理,1.讀索引 2.檢查索引的條件 3.讀整行記錄 4.檢查where條件，過濾數(shù)據(jù),Mysql 5.6 新特性-index condition pushdown,注意： mysql 在復(fù)合索引中，第一列是范圍查詢，第二列通常是無法利用索引的，建議第一列的查詢?yōu)?, , or IS NULL,#基礎(chǔ)表,# 早期版本，5.6 以下 MariaDB 5.3 以下,using where VS index condition pushdown,# MariaDB 5.3+, MySQL5.6+,using where VS index condition pushdown,# MariaDB 5.3+, MySQL5.6+,# 早期版本，5.6 以下 MariaDB 5.3 以下,Mysql 5.6 新特性- index condition pushdown,Mysql 5.6 新特性-index condition pushdown,index condition pushdown 性能上的提升,一方面提升了查詢性能，使的聯(lián)合索引的范圍查詢速度得到很大提升 令一方面，節(jié)省了BP中的內(nèi)存空間。,Mysql 5.6 新特性-Multi-Range-Read,Multi-Range-Read MySQL 5.6+, MariaDB 5.3+,Mysql 5.6 新特性-Multi-Range-Read,隨機讀，接近轉(zhuǎn)換成順序讀,Mysql 5.6 新特性-Multi-Range-Read,Multi-Range-Read 在IO密集型情況測試數(shù)據(jù),Multi-Range-Read,小結(jié) 在數(shù)據(jù)量很大的情況下， 使用MRR,變成順序讀，性能提高還是很大的，順序讀是非?？?，因為： 1 磁頭尋道變得順序，不會再來回尋道。 2 mysql 有線性預(yù)讀功能。 3 每個數(shù)據(jù)頁將只被讀取一次，避免了多次對同一個頁的讀 但是也有一種除外： 如果你的表很小的話，填充在OS cache中，采用MRR會有一定的CPU開銷,Mysql 5.6 新特性-Batched Key Access,Batched Key Access MySQL 5.6+, MariaDB 5.3+,Mysql 5.6 新特性-Batched Key Access,依賴MRR,隨機讀，接近轉(zhuǎn)換成順序讀,Batched Key Access,Batched Key Access 在IO密集型情況測試數(shù)據(jù),Batched Key Access,小結(jié),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 這個線程每秒都會被喚醒一次,InnoDB performance,死鎖檢測增強 set global innodb_print_all_deadlocks=on 5.6 中引入?yún)?shù)innodb_print_all_deadlocks，這個參數(shù)是全局設(shè)置的，可以把所有的死鎖狀況打印到error日志中，如果應(yīng)用程序不具有相應(yīng)的錯誤處理邏輯檢測回滾操作，這個參數(shù)將對你在進行故障診斷的時候很有幫助 先前檢測鎖狀況show engine innodb statusG; 在mysql庫下創(chuàng)建表create table innodb_lock_monitor(a int)將信息打印到error log,InnoDB performance,Undo 從系統(tǒng)表空間分離 涉及到分離出undo表空間的參數(shù) 1. innodb_undo_directory 只讀變量，是不能動態(tài)修改的，在啟動的時候設(shè)置,注意這個不能指定多個undo表空間到多個位置 2. innodb_undo_tablespaces 設(shè)置undo 表空間的個數(shù).默認(rèn)單個undo_tabkespace大小10M大小 3. inodb_undo_logs（代替了先前的參數(shù)innodb_rollback_segments） 控制著回滾段的數(shù)量（注意范圍是0-128） 默認(rèn)不指定的時候是128個回滾段。（注意要想增加回滾段的時候必須要重啟mysql) 官方建議最好是放在SSD上,有待改進：動態(tài)的添加和刪除undo表空間,InnoDB performance,Innodb page size 增強 Innodb_page_size 設(shè)置4k,8k,16k靈活設(shè)置 1.小的內(nèi)存頁以為著每個頁中存儲的數(shù)據(jù)就更少，對于SSD設(shè)備，它沒有尋道花費的開銷，這個時候就會讀更多的頁到內(nèi)存，而每個頁中有效的記錄更多，這樣的話，整體的會使內(nèi)存更有效的利用. 2.更多的數(shù)據(jù)頁，肯定會造成BP管理上的開銷 實施： 導(dǎo)出你現(xiàn)有庫的數(shù)據(jù)，通過邏輯導(dǎo)出（mysqldump）,移動或者直接刪除系統(tǒng)表空間ibdata1和日志文件（ib_logfile0 & ib_logfile1），重新再f 中設(shè)置innodb-page-size 到 4k 或者 8K,導(dǎo)入。,InnoDB performance,Page checksum 增強 innodb_checksum_algorithm 值innodb ,strict_innodb, crc32 ,strict_crc32,none strict_none 好處： 保護數(shù)據(jù)損壞 硬件損壞, 軟件的Bugs, Innodb 自身的Bugs 并不能完全代替文件系統(tǒng)的Checksums Checksum在什么時候發(fā)生呢？ 當(dāng)頁從硬盤讀到BP的時候 當(dāng)頁更新的后刷新到磁盤 可能會有一定的開銷 注意： 在crc32之前，對頁頭和頁尾的校驗算法是不一樣，因此頁頭和頁尾存儲的值是不一樣的。crc32的校驗算法頁頭和頁尾的校驗方法是是一樣的,所以頁頭和頁尾的值是一樣的。,InnoDB performance,統(tǒng)計信息持久化 5.6 引入innodb_analyze_is_persistent 默認(rèn)OFF,innodb_stats_persistent_sample_pages 默認(rèn)是20，可動態(tài)調(diào)整的作用范圍是全局 innodb_stats_sample_pages 廢棄默認(rèn)8 innodb_stats_transient_sample_pages 新版本默認(rèn)8 innodb_stats_on_metadata 默認(rèn)ON innodb_stats_on_metadat 該參數(shù)控制著以下操作是否自動收集統(tǒng)計信息 * 當(dāng)表打開的時候 * 當(dāng)表有太多的改變(由于插入更新或者刪除操作，表的1/16數(shù)據(jù)已經(jīng)發(fā)生變化) * 當(dāng)運行 ANALYZE TABLE 分析具體的表的時候 * 當(dāng)執(zhí)行SHOW TABLE STATUS 或者 SHOW INDEX FROM *當(dāng)訪問 INFORMATION_SCHEMA.TABLES 或者INFORMATION_SCHEMA.STATISTICS. 優(yōu)缺點： 可以讓sql 的執(zhí)行計劃更穩(wěn)定。 需要定期analyze table 以保證執(zhí)行計劃的信息部過期,InnoDB performance,innodb_purge_threads 可以設(shè)置大于1，有效的進行purge InnoDB REDO log size up to 512 Gbyte (日志大小達(dá)到了512G，有效的提高了檢查點的寫入),InnoDB performance,優(yōu)化器跟蹤 這個特性默認(rèn)是關(guān)閉的，全局開啟優(yōu)化器跟蹤會造成大約20%的性能開銷,可以查看join順序，執(zhí)行計劃中的詳細(xì)信息，花費的成本。最終為什么選擇該執(zhí)行計劃 設(shè)置跟蹤信息占用內(nèi)存大小，開啟跟蹤 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;,InnoDB performance,Explain 增強 在MySQL 5.6.3 中 EXPLAIN 命令可以用于數(shù)據(jù)修改執(zhí)行計劃可以應(yīng)用于 INSERT, REPLACE, UPDATE and DELETE和SELECT EXPLAIN FORMAT=JSON SQL; 可以更加清晰的看到執(zhí)行計劃的一些額外信息。 infomation_schema增強 MySQL 5.6:添加11個New INFORMATION_SCHEMA 表 7 數(shù)據(jù)字典相關(guān)的表 3 個 Buffer Pool 相關(guān)的表 1 個全局統(tǒng)計相關(guān)的表 PERFORMANCE_SCHEMA庫下添加一些用于診斷數(shù)據(jù)庫性能的表，可以通過這些表監(jiān)控系統(tǒng)的整個狀況,mysql replication,slave使用表來保存復(fù)制信息 復(fù)制事件checksum 多線程slave 延時復(fù)制 優(yōu)化了基于行的復(fù)制 查詢?nèi)罩局械膬?nèi)容寫入到binlog 遠(yuǎn)程備份binlog 全局事務(wù)標(biāo)識IDS,MySQL 5.6 事務(wù)數(shù)據(jù)在表里 復(fù)制信息在文件里,slave使用表來保存復(fù)制信息,MySQL 5.6 事務(wù)數(shù)據(jù)在表里 復(fù)制信息在表里,slave使用表來保存復(fù)制信息, 系統(tǒng)表: mysql.slave_master_info () mysql.slave_relay_log_info () 啟動的時候設(shè)置或天添加參數(shù)到f -master-info-repository=TABLE -relay-log-info-repository=TABLE 啟動后設(shè)置 SET GLOBAL master_info_repository = TABLE; SET GLOBAL relay_log_info_repository = TABLE; start slave;,slave使用表來保存復(fù)制信息,Slave Tables for Replication Information select * from slave_master_info,復(fù)制事件checksum,當(dāng)事件被應(yīng)用之前，檢測復(fù)制事件中的錯誤。 Guards against bugs and disk or network corruptions CRC-32 校驗, 比 ISO-3309 更精準(zhǔn)(支持zlib算法) mysqld 設(shè)置: binlog-checksum= NONE or CRC32 每個 session都會產(chǎn)生checksum值，并且寫入到binlog SET GLOBAL binlog_checksum = 1; master-verify-checksum= 0 or 1 Master 當(dāng)從binlog dump事件的時候會校驗checksum值 SET GLOBAL master_verify_checksum = 1; slave-sql-verify-checksum= 0 or 1 SQL線程當(dāng)從relay log讀取事件應(yīng)用到slave之前會校驗checksum 值 mysql SET GLOBAL slave_sql_verify_checksum=1;,復(fù)制事件checksum,binlog-checksum對應(yīng)圖中1 master-verify-checksum對應(yīng)圖中2 slave-sql-verify-checksum對應(yīng)圖中5 /2011/04/replication-event-checksum.html,Multi-Threaded Slaves,Multi-Threaded Slaves,mysql SET GLOBAL slave_parallel_workers=2; (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),(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 | +-+-+-+-+-+-+-+-+,slave_parallel_workers 線程數(shù)量 0 禁用 最大 1024,Multi-Threaded Slaves,(root:testdb:Wed May 30 11:19:16 2012)(none) SELECT * FROM mysql.slave_worker_infoG * 1. row * Master_id: 30082 Worker_id: 0 Relay_log_name: Relay_log_pos: 0 Master_log_name: Master_log_pos: 0 Checkpoint_relay_log_name: Checkpoint_relay_log_pos: 0 Checkpoint_master_log_name: Checkpoint_master_log_pos: 0 Checkpoint_seqno: 0 Checkpoint_group_size: 64 Checkpoint_group_bitmap: * 2. row * Master_id: 30082 Worker_id: 1 Relay_log_name: Relay_log_pos: 0 Master_log_name: Master_log_pos: 0 Checkpoint_relay_log_name: Checkpoint_relay_log_pos: 0 Checkpoint_master_log_name: Checkpoint_master_log_pos: 0 Checkpoint_seqno: 0 Checkpoint_group_size: 64 Checkpoint_group_bitmap:,Multi-Threaded Slaves,影響單個slave 的worker線程的隊列的參數(shù)為slave_pending_jobs_size_max,Multi-Threaded Slaves,Multi-Threaded Slaves,性能上的改進： 提高了slave 性能 slave端的sql應(yīng)用在不同數(shù)據(jù)庫間變成并行:減少了slave的滯后,增加了slave的吞吐量 變成數(shù)據(jù)庫（schema）級別的復(fù)制，每個數(shù)據(jù)庫的更改將會被應(yīng)用，注意這里每個庫的應(yīng)用還是串行的，并且提交變得相互獨立 在重啟恢復(fù)的時候是自動完成的（串行） Slave段所有的事務(wù)日志都已經(jīng)寫入到了relay log保證了數(shù)據(jù)部丟失。 最糟糕的情況是所有的操作都是對同一個庫進行操作。,延時slave,在每個slave sql線程執(zhí)行的時候都要等延遲時間到后進行下一個操作。 最大支持 68 年。 在一定程度上防止了誤操作，比如說刪表等等。 可以一定程度上作為有效的數(shù)據(jù)庫備份，無需再另行備份。,延時slave,當(dāng)發(fā)生問題的時候我們要做的是，在主庫上查找刪除表的位置，也可以用 mysqlbinlog查找。 master SHOW BINLOG EVENTSG * 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;,案例,操作人員上午誤刪除了test庫的test1 表，數(shù)據(jù)庫沒有備份,只有延時slave ，延時slave配置延時1天,全局唯一server IDs,在主庫上： 獲得主庫的uuid (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 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 May 31 13:16:32 2012)(none) show slave statusG; Master_Server_Id: 30081 Master_UUID: dad8f1f6-18af-11e1-992c-000c29d4b063 Master_Info_File: mysql.slave_master_info SQL_Delay: 60 SQL_Remaining_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啟動時候會在數(shù)據(jù)目錄下生成一個f ,uuid存儲在 f 文件中 rootmaster mysql# cat f auto server-uuid=dad8f1f6-18af-11e1-992c-000c29d4b063 rootsave mysql# cat f auto server-uuid=9fe4cfec-a971-11e1-891c-000c299499ff,Global Transaction Identifier,Global Transaction Identifier,Global Transaction Identifier(全局事務(wù)表示符，實際上是以一個事件的形式存在mysql 中) 在每個事務(wù)提交時會立刻記錄到b