Percona Server for MySQL 5.7

请加入Percona工程技术总监Laurynas Biveinis讨论“Percona Server for MySQL5.7:关键性能算法”。
在本次网络研讨会中,Laurynas将讨论InnoDB和Percona Server for MySQL5.7内部的选定领域,因为它们与缓冲池管理和刷新(从性能和可扩展性的角度)有关。他将描述在Percona Server for MySQL5.7中缓冲池互斥、多线程lru flusher和并行doublewrite特性背后的动机,因为MySQLInnoDB5.7已经重新实现了Percona Server for MySQL中的许多相同特性,特别是对于磁盘绑定的r读取/写入工作负载。
本网络研讨会将是技术性的,假设您熟悉InnoDB内部。网络研讨会的目标受众是InnoDB和性能工程师。

展开查看详情

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