Let us indulge in some engineering beauties of MySQL. It rarely gets Love compared to Postgres.
Uber reduced their database locks by 94% when they upgraded to MySQL 8.0, thanks to this performance re-architecture.
We know that writing to the index may cause a structure change (btree rebalance) which can cause leaf pages, internal pages and the ROOT to split and update.
Allowing a read while the structure is being changed can cause corruptions, so we need to protect the structure and readers via physical locks or a mutexes (I talk about those in my OS course)
In version 5.6, MySQL InnoDB opted to do a global exclusive lock (X lock) on entire index when a rebalance is triggered, preventing reads from happening (reads take a shared S lock), even when the reads are going to a different part of the tree.
In 8.0 only the pages being restructured or written are X Locked (not the entire index) also a snapshot of those pages are saved so concurrent reads to those pages are allowed. This index is instead locked via a new intent SX Lock which allows shared S locks but prevents X Lock.
What better proof of this engineering marvel than Uber upgrading to MySQL 8 and reducing their database locks by 94% !
We know MySQL is struggling recently against Postgres and I covered that in another video but the engineerings marvels must be acknowledged regardless of the state of the product.
Hussein Nasser
Let us indulge in some engineering beauties of MySQL. It rarely gets Love compared to Postgres.
Uber reduced their database locks by 94% when they upgraded to MySQL 8.0, thanks to this performance re-architecture.
We know that writing to the index may cause a structure change (btree rebalance) which can cause leaf pages, internal pages and the ROOT to split and update.
Allowing a read while the structure is being changed can cause corruptions, so we need to protect the structure and readers via physical locks or a mutexes (I talk about those in my OS course)
In version 5.6, MySQL InnoDB opted to do a global exclusive lock (X lock) on entire index when a rebalance is triggered, preventing reads from happening (reads take a shared S lock), even when the reads are going to a different part of the tree.
In 8.0 only the pages being restructured or written are X Locked (not the entire index) also a snapshot of those pages are saved so concurrent reads to those pages are allowed. This index is instead locked via a new intent SX Lock which allows shared S locks but prevents X Lock.
What better proof of this engineering marvel than Uber upgrading to MySQL 8 and reducing their database locks by 94% !
We know MySQL is struggling recently against Postgres and I covered that in another video but the engineerings marvels must be acknowledged regardless of the state of the product.
---
Watch my full deep dive coverage here
Advanced MySQL Index Locking Explained
https://youtu.be/MK24y7AmKTc
I also try to start crediting the Devs behind the work, this work is done by Zongzhi Chen
Read uber’s article www.uber.com/en-JO/blog/upgrading-ubers-mysql-flee…
1 week ago | [YT] | 318