xtradb 5.7: key performance algorithms
TRANSCRIPT
![Page 2: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/2.jpg)
InnoDB buffer pool• Clean and dirty data pages, distributed among
instances
• Protected by the buffer pool mutex:
• Data pages ordered by last access: LRU list
• Free pages: free list
• Protected by the flush list mutex:
• Dirty data pages ordered by first write: flush list
![Page 3: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/3.jpg)
XtraDB buffer pool• Clean and dirty data pages, distributed among instances
• Protected by the LRU list mutex:
• Data pages ordered by last access: LRU list
• Protected by the free list mutex:
• Free pages: free list
• Protected by the flush list mutex:
• Dirty data pages ordered by first write: flush list
![Page 4: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/4.jpg)
MySQL multi-threaded flushing
LRU instance #0 flush list instance #0
LRU instance #1 flush list instance #1
LRU instance #2 flush list instance #2
coordinator thread
worker thread #0
worker thread #1
time0 s 1 s
LRU…
LRU…
LRU…
![Page 5: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/5.jpg)
Doublewrite bufferData page
Doublewrite buffer in disk
Data file
Doublewrite buffer in memory
Add
Flush
Write
Step 1
Step 2
Step 3
![Page 6: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/6.jpg)
MySQL 5.7.11 OLTP_RW
![Page 7: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/7.jpg)
PFS data is incomplete
![Page 8: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/8.jpg)
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),...
![Page 9: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/9.jpg)
631 pthread_cond_wait,buf_dblwr_write_single_page
![Page 10: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/10.jpg)
Single-page flushing
Is free
page available?
Single-page flush
Take a free page from the free list
Query thread needs a free page
Yes No
Single-page doublewrite
Query thread has a free page
![Page 11: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/11.jpg)
XtraDB innodb_empty_free_list_algorithm=backoff
Is free
page available?
Wait
Take a free page from the free list
Query thread needs a free page
Yes No
Single-page doublewrite
Query thread has a free page
Single-page flush
![Page 12: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/12.jpg)
MySQL multi-threaded flushing
![Page 13: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/13.jpg)
Percona Server multi-threaded flushing
![Page 14: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/14.jpg)
Percona Server 5.7.10-3 OLTP_RW
![Page 15: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/15.jpg)
Percona Server 5.7.10-3 OLTP_RW
2678 nanosleep (libpthread.so.0), … ,buf_LRU_get_free_block (buf0lru.cc:1435), ...
867 pthread_cond_wait,...,log_write_up_to(log0log.cc:1293),...
396 pthread_cond_wait,…, mtr_t::s_lock(sync0rw.ic:433), btr_cur_search_to_nth_level(btr0cur.cc:1022),...
337 libaio::??(libaio.so.1),LinuxAIOHandler::collect (os0file.cc:2325), ...
240 poll(libc.so.6),...,Protocol_classic::read_packet(protocol_classic.cc:810),...
![Page 16: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/16.jpg)
2678 nanosleep, …, buf_LRU_get_free_block
![Page 17: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/17.jpg)
Percona Server 5.7.10-3 OLTP_RW flushers only
![Page 18: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/18.jpg)
Legacy doublewrite: adding pages
![Page 19: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/19.jpg)
Percona Server 5.7.10-3 OLTP_RW flushers only
139 libaio::??(libaio.so.1),LinuxAIOHandler::collect (os0file.cc:2448), LinuxAIOHandler::poll(os0file.cc:2594),...
56 pthread_cond_wait,…,buf_dblwr_add_to_batch (buf0dblwr.cc:1111),…,buf_flush_LRU_list_batch(buf0flu.cc:1555), ...,buf_lru_manager(buf0flu.cc:2334),...
25 pthread_cond_wait,…,os_event_wait_low(os0event.cc:534),buf_flush_page_cleaner_worker(buf0flu.cc:3482),...
21 pthread_cond_wait, …, PolicyMutex<TTASEventMutex<GenericPolicy> (ut0mutex.ic:89),buf_page_io_complete (buf0buf.cc:5966), fil_aio_wait(fil0fil.cc:5754),io_handler_thread(srv0start.cc:330),...
8 pthread_cond_timedwait,…,buf_flush_page_cleaner_coordinator (buf0flu.cc:2726),...
![Page 20: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/20.jpg)
56 pthread_cond_wait, …, buf_dblwr_add_to_batch
![Page 21: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/21.jpg)
Legacy doublewrite: flushing buffer
![Page 22: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/22.jpg)
Parallel doublewrite: adding pages
![Page 23: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/23.jpg)
Parallel doublewrite: flushing buffers
![Page 24: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/24.jpg)
Percona Server 5.7.11-4 OLTP_RW flushers only
![Page 25: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/25.jpg)
Percona Server 5.7.11-4 OLTP_RW flushers only
112 libaio::??(libaio.so.1),LinuxAIOHandler::collect(os0file.cc:2455),...,io_handler_thread(srv0start.cc:330),...
54 pthread_cond_wait,…,buf_dblwr_flush_buffered_writes(buf0dblwr.cc:1287),…,buf_flush_LRU_list(buf0flu.cc:2341),buf_lru_manager(buf0flu.cc:2341),...
35 pthread_cond_wait, …, PolicyMutex<TTASEventMutex<GenericPolicy> (ut0mutex.ic:89), buf_page_io_complete(buf0buf.cc:5986), …, io_handler_thread(srv0start.cc:330),...
27 pthread_cond_wait,...,buf_flush_page_cleaner_worker(buf0flu.cc:3489),...
10 pthread_cond_wait,…,enter(ib0mutex.h:845), buf_LRU_block_free_non_file_page(ib0mutex.h:845), buf_LRU_block_free_hashed_page(buf0lru.cc:2567), …,buf_page_io_complete(buf0buf.cc:6070), …,io_handler_thread(srv0start.cc:330),...
![Page 26: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/26.jpg)
Percona Server 5.7.11-4 OLTP_RW
![Page 27: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/27.jpg)
Percona Server 5.7.11-4 OLTP_RW
![Page 28: XtraDB 5.7: key performance algorithms](https://reader031.vdocuments.us/reader031/viewer/2022030308/58ede2e91a28ab695c8b46f1/html5/thumbnails/28.jpg)
Summary: how we got there• I/O-bound workloads: high demand for free pages,
provided by LRU batch flushing or single-page flushing
• Single-page flushes are bad, w/ and w/o doublewrite
• Removed it
• Made batch LRU flusher truly parallel
• Doublewrite buffer negates parallel flushing gains
• Made it parallel too