1、技術(shù)創(chuàng)新，變革未來借鑒Oracle深入修改MySQL/PostgreSQL內(nèi)核內(nèi)容介紹借鑒Oracle增進PG/MYSQL內(nèi)核實踐：邏輯讀的改進事務(wù)ID的可改進之處借鑒Oracle的計算與存儲分離、可計算存儲架構(gòu)數(shù)據(jù)庫最頻繁的操作：邏輯讀Redo Size是字節(jié)數(shù)，并不是次數(shù)。每秒超過52萬次邏輯讀，如果一次邏輯以讀次可數(shù)以而節(jié)省論1：毫最高的是logical read。秒，52萬次邏輯讀，共可以節(jié)省520秒的CPU消耗。性能還不是最主要的。試想，如果一個路口，每秒要經(jīng) 過52萬輛車，萬一這個路口出現(xiàn)事故，那怕只停一秒，就 會有52萬輛車擁堵。這就是競爭帶來的隱患。對于高頻的操作，除性能之外，

2、競爭，也是要關(guān)注的點。PostgreSQL邏輯讀：Shard Buffer的HASH表NODENODEPostgreSQL邏輯讀：BufferAlloc()函數(shù)/計算HASH值10121015INIT_BUFFERTAG(newTag, smgr-smgr_rnode.node, forkNum, blockNum); newHash = BufTableHashCode(&newTag);/根據(jù)HASH值，得到Buffer Mapping Partition Lock1016newPartitionLock = BufMappingPartitionLock(newHash);/以共享模式持

3、有Buffer Mapping Partition Lock1019LWLockAcquire(newPartitionLock, LW_SHARED);/搜索HASH表1020buf_id = BufTableLookup(&newTag, newHash);/如果找到了，Buffer命中，進入條件。如果沒找到，跳過此if語句，開始物理讀102110221057if (buf_id = 0)return buf;開始物理讀PostgreSQL邏輯讀：BufferAlloc()函數(shù)/如果找到了，進入條件。如果沒找到，跳過此if語句，開始物理讀10211022if (buf_id = 0)/得到

4、Buffer Descripter結(jié)構(gòu)：1028buf = GetBufferDescriptor(buf_id);/在Buffer Descripter結(jié)構(gòu)上中Pin鎖：1030valid = PinBuffer(buf, strategy);/釋放Buffer Mapping Partition Lock1033LWLockRelease(newPartitionLock);/BufferAlloc()結(jié)束，進入讀取行數(shù)據(jù)模塊1056return buf;1057/Buffer未命中，開始物理讀10631078LWLockRelease(newPartitionLock);buf = St

5、rategyGetBuffer(strategy, &buf_state);PostgreSQL邏輯讀：BufferAlloc()函數(shù)/Buffer未命中，開始物理讀1063LWLockRelease(newPartitionLock);/選擇一個可覆蓋頁（犧牲頁）：1078buf = StrategyGetBuffer(strategy, &buf_state);/計算犧牲頁的HASH值11801181oldTag = buf-tag;oldHash = BufTableHashCode(&oldTag);/計算犧牲頁對應(yīng)的Buffer Mapping Partition Lock1182o

6、ldPartitionLock = BufMappingPartitionLock(oldHash);/加Buffer Mapping Partition Lock，獨占模式。1190LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);1191LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);/在HASH表中，新的位置，插入一個Bucket1221buf_id = BufTableInsert(&newTag, newHash, buf-buf_id);PostgreSQL邏輯讀：BufferAlloc()函數(shù)/

7、加Buffer Mapping Partition Lock，獨占模式。1190LWLockAcquire(oldPartitionLock, LW_EXCLUSIVE);1191LWLockAcquire(newPartitionLock, LW_EXCLUSIVE);/在HASH表中，新的位置，插入一個Bucket1221buf_id = BufTableInsert(&newTag, newHash, buf-buf_id);/刪除犧牲頁在HASH表中的Bucket1321BufTableDelete(&oldTag, oldHash);/釋放Buffer Mapping Partiti

8、on Lock13231326LWLockRelease(oldPartitionLock); LWLockRelease(newPartitionLock);內(nèi)容介PostgreSQL邏輯讀：HASH鎖Buffer Mapping Partition Lock計算規(guī)則：127 #define BufTableHashPartition(hashcode) 128(hashcode) % NUM_BUFFER_PARTITIONS)/* Number of partitions of the shared buffer mapping hashtable*/#define NUM_BUFFER

9、_PARTITIONS 128即：MainLWLockArray 45 + HASH 值 % 128 .lock#define BufMappingPartitionLock(hashcode) (&MainLWLockArrayBUFFER_MAPPING_LWLOCK_OFFSET + 131BufTableHashPartition(hashcode).lock)HASH值 % 128代表了什么：Buffer Mapping Partition Lock數(shù)量有128個。MySQL邏輯讀：HASH表buf_pool : page_hash:cells: n_cells:sync_obj.r

10、w_locks n_sync_obj: 16Lock 0Lock 1Lock 15Node 0Node 1Cells:page frame lockpage frame lockbuf_block_t:MySQL邏輯讀簡要流程從buf_page_get_gen()開始:4157Buf_fetch_normal fetch(page_id, page_size); 4167return (fetch.single_page();進入Buf_fetch_normal:single_page()：3968 template 3969 buf_block_t *Buf_fetch:single_pag

11、e() 3975if (static_cast(this)-get(block) = DB_NOT_FOUND) 3976return (nullptr);3977MySQL邏輯讀簡要流程3415 dberr_t Buf_fetch_normal:get(buf_block_t *&block) 3417for (;) / 計算HASH值 得到HASH表鎖 搜索HASH鏈 3421block = lookup();/ 如果HASH表中找到不相應(yīng)Buffer，開始物理讀3423if (block != nullptr) 3424buf_block_fix(block);/ 相當于在Buffer上

12、加Pin34253427rw_lock_s_unlock(m_hash_lock);/ 釋放HASH表鎖，狹義上的邏輯讀到此結(jié)束3428break;3429/ 如果邏輯讀沒有命中，開始物理讀3431/* Page not in buf_pool: needs to be read from file */3432read_page();3433MySQL邏輯讀簡要流程3489 template 3490 buf_block_t *Buf_fetch:lookup() / 計算HASH值，并根據(jù)HASH值得到Hash表鎖3491m_hash_lock = buf_page_hash_lock_g

13、et(m_buf_pool, m_page_id);/ 加HASH表鎖3495rw_lock_s_lock(m_hash_lock);/ 搜索HASH表3516if (block = nullptr) 3517block = reinterpret_cast(3518buf_page_hash_get_low(m_buf_pool, m_page_id);3519MySQL邏輯讀簡要流程/ 計算HASH值，并根據(jù)HASH值得到3491m_hash_lock = buf_page_hash_lock_get(m_buf_pool, m_page_id);2003 /* Get appropria

14、te page_hash_lock. */2004 #define buf_page_hash_lock_get(buf_pool, page_id) 2005hash_get_lock(buf_pool)-page_hash, (page_id).fold()hash_get_lock(buf_pool)-page_hash, (page_id).fold()計 算 Fload 值 ： page_id_t:flod()：179m_fold = (m_space page_hash-n_cells，17393。hash_get_lock(buf_pool)-page_hash, (page_i

15、d).fold()hash_get_sync_obj_index(table, fold);ut_2pow_remainder(hash_calc_hash(fold, table), table-n_sync_obj)ut_hash_ulint(fold, table-n_cells)/ 最終的計算HASH值的函數(shù)：121key = key UT_HASH_RANDOM_MASK2; 122123return (key % table_size);ut_2pow_remainder的定義：188 #define ut_2pow_remainder(n, m) (n) & (m)-1)n :

16、hash_calc_hash(fold, table)的返回值，剛計算出的HASH值m : table-n_sync_obj，即buf_pool-page_hash-n_sync_obj，值為16。ut_2pow_remainder的作用：Hash值 % m 。 最終，HASH鎖的定位：buf_pool-sync_obj.rw_locks + 宏ut_2pow_remainder的結(jié)果MySQL邏輯讀簡要流程/ 計算HASH值，并根據(jù)HASH值得到3491m_hash_lock = buf_page_hash_lock_get(m_buf_pool, m_page_id);hash_get_l

17、ock(buf_pool)-page_hash, (page_id).fold() hash_get_Msyync_SobQj_LindexH(tAablSe,HfoldL);ock數(shù)量：ut_2pow_remainder(hash_calc_hash(fold, table), table-n_sync_obj)16個。MySQL邏輯讀簡要流程物理讀時的HASH表鎖：在buf_read_page_low()中：90bpage = buf_page_init_for_read(err, mode, page_id, page_size, unzip);在buf_page_init_for_re

18、ad()中：4616mutex_enter(&buf_pool-LRU_list_mutex);4617 4618hash_lock = buf_page_hash_lock_get(buf_pool, page_id);4619 4620rw_lock_x_lock(hash_lock);46214622buf_page_t *watch_page;邏輯讀簡要流程總結(jié)邏輯讀流程總結(jié)：計算HASH值根據(jù)HASH值，計算并得到HASH表鎖共享方式申請HASH表鎖搜索HASH表如果找到目標BufferPin住Buffer釋放HASH表鎖如果HASH表中沒有找到目標Buffer 換獨占HASH表鎖物

19、理讀Oracle邏輯讀流程CBC Latch : 全稱Cache Buffers Chains Latch，就是HASH表鎖，也就是PG中的Buffer Parittion Mapping Lock，MySQL中的Hash Lock。Oracle的HASH函數(shù)代碼int sp1=0 x11; int sp2=0 x7f; int sp3=0 x7;int hash_header=0 x394953140;hash(int p1,int p2)int uk=p1; int rdba=p2;int v1,v2;int target_addr;uk=uksp1; v1=uk&sp2; v1=v10

20、xsp3; v2=v2 show variables like innodb_page_hash_locks;+ + +| Variable_name| Value |+ + +| innodb_page_hash_locks | 16|+ + +1 row in set (0.02 sec)兩點限制：只能增大到1024只在Debug模式下，才有此參數(shù)修改內(nèi)核 減少競爭 增進性能修改內(nèi)核 減少競爭 增進性能修改內(nèi)核 減少競爭 增進性能使用的動態(tài)內(nèi)核跟蹤語言：systemtap，統(tǒng)計腳本comp2.stp，局部代碼：統(tǒng)計邏輯讀次數(shù)：probe process(postgres).function

21、(FileRead) if ($amount = 8192)pread_num 1統(tǒng)計邏輯讀時間：probe process(postgres).function(BufferAlloc) lgrpid(), BufferAlloc = 1tm1pid() = gettimeofday_us()probe process(postgres).function(ReadBuffer_common).return lgrpid(), BufferAlloc = 0lgrpid(), LWLockAcquire = 0 lgrpid(), StrategyGetBuffer = 0 lnum 1tm

22、2 max_trx_id % TRX_SYS_TRX_ID_WRITE_MARGIN) trx_sys_flush_max_trx_id();328 329330return (trx_sys-max_trx_id+); 331 MySQL的事務(wù)ID獲取機制Select操作，在trx_start_low()中調(diào)用trx_sys_get_new_trx_id（）：1280trx_sys_mutex_enter();12811282trx-id = trx_sys_get_new_trx_id();DML操作，在trx_set_rw_mode()中調(diào)用trx_sys_get_new_trx_id(

23、)： 3059mutex_enter(&trx_sys-mutex);30603061ut_ad(trx-id = 0);3062trx-id = trx_sys_get_new_trx_id();PostgreSQL事務(wù)ID的獲取只讀事務(wù)（Select）不會增加事務(wù)ID讀寫事務(wù)增加事務(wù)ID的方式： GetNewTransactionId()：76LWLockAcquire(XidGenLock, LW_EXCLUSIVE);/以獨占方式，得到全局的XidGenLock鎖 77full_xid = ShmemVariableCache-nextFullXid; /從ShmemVariableC

24、ache-nextFullXid中，得到XID（即事 務(wù)ID）xid = XidFromFullTransactionId(full_xid);#define XidFromFullTransactionId(x)(uint32) (x).value)PostgreSQL事務(wù)ID的獲取增加ShmemVariableCache-nextFullXid：185FullTransactionIdAdvance(&ShmemVariableCache-nextFullXid);static inline voidFullTransactionIdAdvance(FullTransactionId *dest) 85 dest-value+;while (XidFromFullTransactionId(*dest) value+;86878889 釋放XidGenLock鎖237LWLockRelease(XidGenLock);PostgreSQL事務(wù)ID競爭測試PostgreSQL事務(wù)ID競爭測試Oracle的事務(wù)ID（XID）策略MySQL/PostgreSQL：事務(wù)ID或XID即是事務(wù)的唯一標志，又代表事務(wù)的先后順序Oracle：事務(wù)ID（即Oracle中的XID）僅僅是事務(wù)的唯一標志，并不代表先后順序SCN，代表先后事務(wù)順序。Oracle的事務(wù)ID策