Percona Server for MySQL 5.7

1 . Percona Server for MySQL 5.7: Key Performance Algorithms Laurynas Biveinis Alexey Stroganov Percona firstname.lastname@percona.com 

2 .Percona Server for MySQL 5.7 Key Performance Algorithms • Focus on the InnoDB: buffer pool, flushing, the doublewrite buffer • Talk assumes familiarity, but feel free to interrupt • What we learned • What we did • How we did it 

3 . InnoDB buffer pool • Memory cache of disk data pages, sized by innodb_buffer_pool_size • In-memory data pages accessible through several data structures Hash array Data page lists • 1) Page hash for lookup (space_id; page_id) ? Fold 

4 . InnoDB buffer pool • 2) flush list for dirty page management. Dirtying: Clean page INSERT INTO foo VALUES(bar) Dirty page, LSN = 42 Flush list tail: Dirty page, LSN = 25 Dirty page, LSN = 32 Dirty page, LSN = 42 

5 . InnoDB buffer pool • 2) flush list for dirty page management. Flushing: Flush list head: Dirty page, LSN = 5 Dirty page, LSN = 7 Dirty page, LSN = 12 Flush up to LSN 10 Clean page Flush list head: Dirty page, LSN = 12 Clean page 

6 . InnoDB buffer pool • 3) LRU list for deciding which pages to evict • Preventing eviction for recently-used pages (making them young) • innodb_old_blocks_pct, innodb_old_blocks_time Clean page Dirty page Dirty page Clean page Clean page old Page access young Clean page Clean page Dirty page Clean page Dirty page 

7 . InnoDB buffer pool • 4) free list for having free space in the buffer pool to read currently non-present pages. Reading: Free page Free page Free page Free page Free page Page read Clean page Free page Free page Free page Free page 

8 . InnoDB buffer pool • 3/4) Evicting/flushing pages from the LRU list and putting them on the free list (up to innodb_max_lru_scan_depth) LRU Clean page Dirty page Dirty page Clean page Clean page free Free page Free page Free page Free page LRU Dirty page Dirty page Clean page Clean page free Free page Free page Free page Free page Free page 

9 . The doublewrite buffer • innodb_doublewrite • Protects again data loss in case of a crash in the middle of a page write • Implemented by writing data pages twice 

10 .The doublewrite buffer Data Step 3 Step 1 page Add Write Doublewrite buffer in memory Step 2 Flush Doublewrite buffer in disk Data file 

11 .Buffer pool concurrency page hash latch flush list mutex buffer pool mutex LRU list page hash flush list free list misc. 

12 . Buffer pool instances page hash latch flush list mutex buffer pool mutex page hash flush list LRU list free list buffer pool instance 0 misc. page hash latch flush list mutex buffer pool mutex page hash flush list LRU list free list buffer pool instance 1 misc. 

13 . Buffer pool instances • innodb_buffer_pool_instances • Problem: some instances are cold and some are hot • “First the accesses to the buffer pools is in no way evenly spread out.” • http://bit.ly/bpsplit • Six year-old quote, still relevant the same today 

14 . Concurrency in XtraDB misc mutex / page hash latch flush list mutex LRU list mutex free list mutex atomics page hash flush list LRU list free list misc Patch contributed to MySQL, and merged in 8.0.0 http://bugs.mysql.com/bug.php?id=75534 

15 . The concurrency solutions are compatible misc mutex / page hash latch flush list mutex LRU list mutex free list mutex atomics page hash flush list LRU list free list misc buffer pool instance 0 misc mutex / page hash latch flush list mutex LRU list mutex free list mutex atomics page hash flush list LRU list free list misc buffer pool instance 1 

16 . Buffer pool mutexes are so 5.5 Improvement by the buffer Improvement pool mutex by adaptive split flushing 

17 . 5.6+ changed things • In 5.5 and earlier: reduce mutex contention by X%, observe TPS increase by ~X% • Changing flushing heuristics is driven by performance stability, not necessarily by peak performance • Pre-release Percona Server 5.6: reduce mutex contention by X%, observe TPS increase by ~0% • What happened? InnoDB cleaner thread happened 

18 . Buffer pool / flushing concurrency in 5.5 Time Master thread Query thread 1 Query thread 2 access page flush list flush access page access page LRU list flush flush list flush LRU list flush access page flush list flush 

19 . Buffer pool / flushing concurrency in 5.6+ Time Cleaner thread Query thread 1 Query thread 2 LRU list flush access page flush list flush access page LRU list flush access page flush list flush LRU list flush access page flush list flush 

20 . Buffer pool / flushing concurrency in 5.6+ • In 5.6+, code-level changes to reduce locking granularity are still relevant, but • Increasing thread specialization means that… • …flushing - including LRU - heuristics are very important now 

21 . MySQL 5.7 multi-threaded flushing innodb_page_cleaners coordinator thread LRU instance #0 flush list instance #0 LRU… worker thread #0 LRU instance #1 flush list instance #1 LRU… worker thread #1 LRU instance #2 flush list instance #2 LRU… time 0s 1s 

22 .MySQL 5.7.11 OLTP_RW 

23 .PFS data is incomplete 

24 . MySQL 5.7.11 OLTP_RW 660 pthread_cond_wait,enter (ib0mutex.h:850), buf_dblwr_write_single_page (ib0mutex.h:850),buf_flush_write_block_low(buf0flu.cc:1096),buf_flush_page (buf0flu.cc1096),buf_flush_single_page_from_LRU (buf0flu.cc:2217), buf_LRU_get_free_block(buf0lru.cc:1401),... 631 pthread_cond_wait,buf_dblwr_write_single_page (buf0dblwr.cc:1213), buf_flush_write_block_low(buf0flu.cc:1096),buf_flush_page (buf0flu.cc:1096), buf_flush_single_page_from_LRU (buf0flu.cc:2217), buf_LRU_get_free_block(buf0lru.cc:1401),... 337 pthread_cond_wait,PolicyMutex<TTASEventMutex<GenericPolicy> (ut0mutex.ic:89),get_next_redo_rseg (trx0trx.cc:1185), trx_assign_rseg_low(trx0trx.cc:1278),trx_set_rw_mode (trx0trx.cc:1278), lock_table(lock0lock.cc:4076),... 

25 .631 pthread_cond_wait,buf_dblwr_write_single_page 

26 .Single-page flushing Query thread needs a free page Is free Yes page No available? Single-page flush Take a free page from the free list Single-page doublewrite Query thread has a free page 

27 . Percona Server innodb_empty_free_list_algorithm=backoff Query thread needs a free page Wait Is free Yes page No available? Single-page flush Take a free page from the free list Single-page doublewrite Query thread has a free page 

28 . MySQL 5.7 multi-threaded flushing Single page flushes! coordinator thread LRU instance #0 flush list instance #0 LRU… free pages worker thread #0 LRU instance #1 flush list instance #1 LRU… free pages worker thread #1 LRU instance #2 flush list instance #2 LRU… time 0s 1s 

29 . Percona Server 5.7 multi- threaded flushing LRU flusher #0 LRU instance #0 LRU instance #0 LRU… free pages LRU flusher #1 LRU instance #1 LRU… free pages coordinator flush list instance #0 flush… worker #0 flush list instance #1 flush… time 0s 1s