賴明星數(shù)據(jù)庫技術(shù)組2015年4月2日

1、賴明星數(shù)據(jù)庫技術(shù)組2015年4月2日Whats New In MySQL 5.6 OverviewScalability Scalable Read Only Transactions Improved Adaptive FlushingOptimizer and Execution Index Condition pushdown (ICP) Multi-Range-Read (MRR)Replication Multi-Threaded Slave Global Transaction IdentifiersTransparency Many new INFORMATION_SCHEMA

2、Tables Explain for UPDATE/DELETE queriesOperational Improvements Online DDL User DefinedDATA DIRECTORYfor Innodb TablesNew Functionality for Developers Memcached API in Innodb Full Text Search index for InnodbOverview: ScalabilityScalable Read OnlyTransactionsConcurrentInnodbdata fileextensionMultip

3、le background PurgeThreads ImprovedPurge lagcontrol Splitof “KernelMutex” DataDictionaryCache ImprovedAdaptiveFlushingPageCleaner/Separate FlushThreadCache Coherence and False SharingReducedLocking for Partitionedtable ReducedContention forLOCK_open multipletable_open_cacheinstances Large(over 4GB)

4、redo logs support Group Commit for Binary LogOverview: Optimizer and ExecutionIndex Condition Pushdown(ICP)Multi-Range-Read(MRR)Faster Order By nidxcol LIMIT N Persistent Statistics for Innodb Improvements to Innodb Compression Fast Page Checksums(CRC32) 4k and 8k Page sizes for Innodb Subquery Opti

5、mizationsOverview: Replication Multi-Threaded SlaveGlobal Transaction IdentifiersCrash Safe and Binlog Replication Event Checksums Time Delayed Replication Server UUID f Replication Utilites for Failover and AdminOverview: TransparencyMany new INFORMATION_SCHEMA TablesINNODB_METRICSBuffer Pool Infor

6、mation Tables Meta Data Information Tables Statements/Stages instrumentation Aggregations by User, Host etc Explain for UPDATE/DELETE queries JSON output with more informationOverview: Operational ImprovementsSeparate Tablespaces for Innodb Undo Logs Fast Restart Preloading Innodb Buffer PoolOnline

7、DDL Remote Binlog Backup Innodb Transportable Tablespaces User DefinedDATA DIRECTORYfor Innodb Tables Connection AttributesOverview: New Functionality for DevelopersMemcacheD API in Innodb Full Text Search index for InnodbMicrosecond TIME precision Precise spatial operations in GIS Explicit Partitio

8、n Selection in queriesScalibilityScalable Read Only TransactionsImproved Adaptive FlushingPage Cleaner/Separate Flush ThreadGroup Commit for Binary LogScalibility: Scalable Read Only Transactions知識準(zhǔn)備：ReadView是一個事務(wù)的集合，這些事務(wù)在ReadView創(chuàng)建時是活躍的(未提交/回滾) 快照讀操作，需要創(chuàng)建ReadView，為了創(chuàng)建ReadView，需要獲取trx_sys-mutex，然后遍歷

9、活躍事務(wù)鏈表分析：只讀事務(wù)不會產(chǎn)生更新，也就不會產(chǎn)生歷史版本；OLTP應(yīng)用，讀多寫少；將活躍事務(wù)鏈表拆分為只讀事務(wù)與更新事務(wù)兩個鏈表，ReadView創(chuàng)建只需要遍歷更新事務(wù)鏈表，能夠極大的降低ReadView創(chuàng)建的開銷；優(yōu)化：新增SQL語法：start transaction read only;事務(wù)上新增標(biāo)識：trx-read_only維護(hù)兩個活躍事務(wù)鏈表：ro_trx_list vs rw_trx_listScalibility: Improved Adaptive Flushing知識準(zhǔn)備InnoDB的Fuzzy Checkpoint策略，按照內(nèi)部臟頁鏈表(Flush List)，逐步將

10、最老的一部分臟頁寫出磁盤，推進(jìn)系統(tǒng)的檢查點(Checkpoing LSN)。刷新臟頁不能太慢，避免redo空間用完，導(dǎo)致性能抖動；刷新臟頁不能太快，這樣可以盡可能的合并IO之前的算法：僅根據(jù)緩沖池中，臟頁的數(shù)量進(jìn)行flushing：臟頁在緩沖池中所占的比例小于innodb_max_dirty_pages_pct時，不刷新臟頁；大于innodb_max_dirty_pages_pct 時，刷新100個臟頁。新的算法：充分考慮了：1）臟頁刷新速度；2）Redo log的產(chǎn)生速度；3）磁盤的IO能力系統(tǒng)平均刷臟頁速度： avg_page_rate = 過去innodb_flushing_avg_lo

11、ops秒 + + . 的速度系統(tǒng)平均日志速度： lsn_avg_rate根據(jù)系統(tǒng)臟頁比率與日志年齡，計算本次應(yīng)該Flush的臟頁數(shù)量：npages根據(jù)平均刷臟頁速度進(jìn)行調(diào)整： npages = (npages + avg_page_rate) / 2根據(jù)lsn_avg_rate，計算本次日志應(yīng)該Flush到的位置：lsn_limit最后，根據(jù)npages與lsn_limit，進(jìn)行本次Flush；Scalibility: Page Cleaner Flush Thread知識準(zhǔn)備：LRU_list flush 刷新LRU鏈表，保證buffer pool的可用空間Flush_list flush

12、刷新臟頁推進(jìn)checkpoint，釋放redo空間Page Cleaner Flush Thread將LRU List Flush與Flush List Flush全部移到Page Cleaner后臺線程中處理，減少Master Thread與User Thread的壓力；Page Cleaner線程，每秒啟動一次；LRU List Flush 從LRU鏈表尾部開始遍歷：將未使用的Clean Page從LRU鏈表摘除；將未使用的Dirty Page寫出，然后從LRU鏈表摘除；Flush List Flush：使用前頁中介紹的New Adaptive Flush算法；Scalibility: G

13、roup Commit for Binary Log在InnoDB 1.2版本之前，在開啟二進(jìn)制日志后，InnoDB存儲引擎的group commit功能會失效，從而導(dǎo)致性能的下降MySQL 5.6將事務(wù)的提交分為幾個步驟來完成： 在MySQL數(shù)據(jù)庫上層進(jìn)行提交時，首先按順序?qū)⑵浞湃胍粋€隊列中，隊列中的第一個事務(wù)稱之為leader，其他事務(wù)稱之為follower，leader控制著follower的行為。Flush階段：將每個事務(wù)的二進(jìn)制日志寫入內(nèi)存中Sync階段：將內(nèi)存中二進(jìn)制日志刷新到磁盤，若隊列中有多個事務(wù)，那么僅一次fsync操作，就完成了多個事務(wù)的二進(jìn)制日志的寫入Commit階段：l

14、eader根據(jù)順序調(diào)用存儲引擎層事務(wù)的提交http:/mysqlmusings.blogspot.hk/2012/06/binary-log-group-commit-in-mysql-56.htmlOptimizerIndex Condition pushdown (ICP)Multi-Range-Read (MRR)Persistent Statistics for InnodbSubquery OptimizationsOptimizer: Index Condition pushdown (ICP)5.6引入index condition pushdown，允許在存儲引擎層通過索引列提

15、前過濾掉數(shù)據(jù)，從而減少服務(wù)器層訪問的數(shù)據(jù)量，同時也減少存儲引擎訪問基表的次數(shù)/kb/en/mariadb/index-condition-pushdown/Non-ICPICPOptimizer: Multi-Range-Read (MRR)MRR：multi range readselect * from tb where key_column = x在沒有MRR的情況下，它是這樣得到結(jié)果的：1.select key_column, pk_column from tb where key_column=x order by key_column2.for each row in t ; se

16、lect non_key_column from tb where pk_column = pk_column_value在有MRR的情況下，它是這樣執(zhí)行的：1.select key_column, pk_column from tb where key_column = x order by key_column2.將結(jié)果集t放在buffer里面(直到buffer滿了)，然后對結(jié)果集t按照pk_column排序3.select non_key_column fromtb where pk_column in (select pk_column from t_sort)Optimizer: P

17、ersistent Statistics for Innodb原有問題InnoDB中的統(tǒng)計信息是不持久化的，在以下情況下會更新表打開時；表中的大量數(shù)據(jù)被修改時；( 2 000 000 000) or (stat_n_rows/16)Analyze Table；Show table/index Status；統(tǒng)計信息精準(zhǔn)度不夠隨機(jī)采集8個頁面，估算全表的統(tǒng)計信息；Persistent Statistics優(yōu)勢更為精準(zhǔn)的統(tǒng)計信息；更為固定的統(tǒng)計信息；持久化到哪里：mysql.innodb_table_stats;mysql.innodb_index_stats;ReplicationMulti-T

18、hreaded SlaveCrash Safe Slave and BinlogGlobal Transaction IdentifiersReplication: Multi-Threaded Slave & Crash SafeMulti-Threaded Slave：庫級別的并行復(fù)制相關(guān)參數(shù)：slave_parallel_workersCrash Safe:實現(xiàn)方式與InnoSQL類似，保證Binlog的執(zhí)行與記錄執(zhí)行到的binlog文件和位置是原子操作即可（通過事務(wù)保證）相關(guān)參數(shù)：relay_log_info_repository = TABLE TABLE|FILE|NONEmast

19、er_info_repository = FILErelay_log_recovery = ON/blog/2013/09/13/enabling-crash-safe-slaves-with-mysql-5-6/Replication: Global Transaction IdentifiersGTID的概念：全局事務(wù)ID的官方定義是：GTID = server_uuid:transaction_id在MySQL5.6中，每一個GTID代表一個數(shù)據(jù)庫事務(wù)。在上面的定義中，server_uuid表示執(zhí)行事務(wù)的主庫uuid，transaction_id是一個從1開始的自增計數(shù)，表示在這個主庫上

20、執(zhí)行的第n個事務(wù)。MySQL會保證事務(wù)與GTID之間的一對一的映射。例如，下面就是一個GTID：3E11FA47-71CA-11E1-9E33-C80AA9429562:23表示在以3E11FA47-71CA-11E1-9E33-C80AA9429562為唯一標(biāo)示的MySQL實例上執(zhí)行的第23個數(shù)據(jù)庫事務(wù)。GTID還可以用集合的形式表示：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5Replication: Global Transaction IdentifiersGTID的使用：以GTID模式啟動數(shù)據(jù)庫實例：mysqld_safe -gtid_mode=O

21、N -log-bin -log-slave-updates -enforce-gtid-consistency & 主、從都需要以GTID模式啟動數(shù)據(jù)庫實例，以GTID模式啟動數(shù)據(jù)庫以后，不需要指定Binlog文件和偏移量，直接起復(fù)制即可，如下所示：mysql CHANGE MASTER TO MASTER_HOST = host, MASTER_PORT = port, MASTER_USER = user, MASTER_PASSWORD = password, MASTER_AUTO_POSITION = 1;Replication: Global Transaction Identif

22、iersGTID的原理：gtid_executed表示已經(jīng)執(zhí)行了的事務(wù)ID的集合，這些事務(wù)已經(jīng)記錄到了binlog中SHOW GLOBAL VARIABLES LIKE gtid_executed3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5gtid_purged表示從 binlog中刪除的事務(wù)ID集合?？梢酝ㄟ^下面的語句進(jìn)行查看：SHOW GLOBAL VARIABLES LIKE gtid_purged增加GTID以后，對Binlog的修改，新增了兩類事件：名稱功能Previous_gtids_log_event(每個Binlog文件頭)該事件之前的全局事

23、務(wù)ID集合Gtid_log_event(每個Event前面)標(biāo)記之后的事務(wù)對應(yīng)的全局事務(wù)IDReplication: Global Transaction IdentifiersGTID的原理：獲取gtid_executed集合：找到最后一個binlog文件，讀出Previous_gtids_log_event記錄；再遍歷這個binlog文件中所有的Gtid_log_event，把找到的GTID記錄合并起來，作為這臺數(shù)據(jù)庫歷史上執(zhí)行的所有GTIDs放入全局gtid_executed記錄；獲取gtid_purged集合：找到第一個binlog文件，用它的Previous_gtids_log_ev

24、ent信息代替全局gtid_purged的內(nèi)容。因為這是第一個未刪除的binlog文件，這里記錄的就是之前已經(jīng)刪除的binlog文件所包含的全部GTIDs。Request=server_id, binlog_name, binlog_offset, gtids_executedSlave會將自己的gtid_executed集合發(fā)送給Master，Master通過比對Slave發(fā)送過來的gtid_executed和自己的gtid_executed，就知道哪些事務(wù)是Master上執(zhí)行過Slave上沒有執(zhí)行過，只需要將Master上執(zhí)行過，Slave上沒有執(zhí)行過的事務(wù)的Binlog發(fā)送給Slave即

25、可。TransparencyPerformance SchemaExplain for UPDATE/DELETE queriesJSON output with more informationTransparency: performance_schema對Performance_Schema的改進(jìn)：從17張表增加到了52張表默認(rèn)開啟performance_schema（5.5默認(rèn)關(guān)閉）更加容易配置降低性能開銷Statements/Stages哪些是資源密集型查詢？時間花在哪里？Table/Index I/O, Table Locks哪些表和索引負(fù)載最高或者爭用最多？Users/Hosts

26、/Accounts哪些用戶、主機(jī)與帳號最費資源？Network I/O網(wǎng)絡(luò)負(fù)載如何？會話空閑時間如何？SummariesAggregated statistics grouped by statement, thread, user, host, account or object.Transparency: Explain for UPDATE/DELETE queries MySQL 5.6MySQL 5.5Transparency: JSON output with more informationOperational Improvements Fast RestartOnline D

27、DLInnodb Transportable TablespacesSeparate TableSpaces for innodb undo logsOperational Improvements: Fast Restart Dump：將InnoDB Buffer Pool中的頁面標(biāo)識Dump到外存；Restore：讀取Dump文件，根據(jù)其中保存的頁面標(biāo)識，讀取對應(yīng)的頁面填充Buffer Pool；原有問題：重啟MySQL服務(wù)器，對于緩沖區(qū)較大的實例，需要較長的預(yù)熱時間。解決方案：通過BP Dump/Restore，Dump過程，將內(nèi)存頁面標(biāo)識寫出；Restore過程，讀取Dump文件，將頁

28、面標(biāo)識排序，順序讀取外存頁面進(jìn)入Buffer Pool；將預(yù)熱的隨機(jī)I/O轉(zhuǎn)換為順序I/O；SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;SET GLOBAL innodb_buffer_pool_dump_now=ONSET GLOBAL innodb_buffer_pool_load_now=ON;SET GLOBAL innodb_buffer_pool_load_at_startup=ON;Operational Improvements: Online DDL Copy TableMySQL最早的DDL操作方式，DDL通過Copy

29、 Table方式實現(xiàn)：CREATE TABLE temp(a INT, INDEX(a); INSERT INTO temp SELECT * FROM t; RENAME TABLE t TO temp2; RENAME TABLE temp TO t; DROP TABLE temp2;缺點：并發(fā)低；兩倍存儲空間；Online DDL基于Performance、concurrency、Availability和scalability考慮，Online DDL操作過程中不長時間鎖表，并發(fā)操作可讀可寫，提供高并發(fā)；目前支持的操作有：Createsecondary indexesDropseco

30、ndary indexes:Changing theauto-incrementvalue for a columnDrop aforeign key constraintRename a column:/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-gridOperational Improvements: Innodb Transportable TablespacesTransportable Tablespaces就是將一個表數(shù)據(jù)文件從當(dāng)前數(shù)據(jù)庫拷貝出去，然后導(dǎo)入到另外一個數(shù)據(jù)

31、庫之中MySQL 5.6之前，無法通過拷貝數(shù)據(jù)文件的方式實現(xiàn)數(shù)據(jù)的轉(zhuǎn)移，這是因為：1） 數(shù)據(jù)文件ibd中不包含最新紀(jì)錄；2）數(shù)據(jù)文件的日志信息與其他數(shù)據(jù)庫不符；3）Purge與Change Buffer存在影響；通過提供新的命令，使得Transportable Tablespaces成為可能：ALTER TABLE t DISCARD TABLESPACE;ALTER TABLE t IMPORT TABLESPACE;注意事項：innodb_file_per_table參數(shù)必須開啟不支持分區(qū)表不能改變page size/doc/refman/5.6/en/tablespace-copying

32、.htmlOperational Improvement: Separate TableSpaces for undo logsSeparate Tablespaces for Innodb Undo Logs：innodb_undo_tablespaces.innodb_undo_directory.innodb_undo_logs（ innodb_rollback_segments ）User DefinedDATA DIRECTORYfor Innodb Tables：mysql CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY =

33、/alternative/directory;將更熱的數(shù)據(jù)存儲到性能更好、價格更高的固態(tài)盤中，以期獲取更好的性能。 參考資料What Is New in MySQL 5.6/doc/refman/5.6/en/mysql-nutshell.htmlWhats New in MySQL 5.6/tech-resources/articles/whats-new-in-mysql-5.6.htmlSplit of “kernel mutex”: /mysqlinnodb/entry/mysql_5_6_innodb_scalability Improved Adaptive Flushing: /mysqlinnodb/entry/new_