R
Richard Banville
Guest
For update heavy applications, MTX is often a bottleneck. In this note writing work, the MTX is really used to order the recording of the bi and ai notes as an atomic action and not to protect each one individually. Recording of OLTP actions grab MTX, then BIB, records bi notes and releases BIB, then grabs AIB and records ai notes, then releases AIB and MTX. This is another reason why extending variable length bi or ai extents during OLTP operations can adversely affect performance. The BIWs write full/dirty bi buffers and hold the BIB latch to protect new buffers being put on the full list by OLTP activity while the BIW is grabbing one off the list. It would make sense to separate the latches used to record bi notes and management of the full bi buffer list. A BIB and BIW latch for example. Same goes for the AIB latch but that mechanism is a bit different than the BI. Currently, the ai and bi writers contend with the OLTP activity for the BIB and AIB latches and that is occurring while the OLTP action is holding the MTX. The MTX would not be needed here if there were only one set of files to write notes to. In other words one recovery subsystem for BI and AI as opposed to two. The note recording latch and full buffer write latch would suffice. One set of notes, one set of files. It seems it would make sense to provide such an option. Of course DBA management of such a joined recovery subsystem would be a bit different than today based on archiving and long running txns as well as involvement in replication but should be consumable easy enough. If such changes were ever made, it still would not remove the bottleneck of recording recovery notes since OE still maintains the write ahead logging rule database wide so managing OLTP updates will continue to be a bottleneck or have a funneling effect albeit with improved performance over today's implementation.
Continue reading...
Continue reading...