Using Primary Key Parts In A Secondary Index Of An InnoDB Table
Recently, I was asked what would happen if a primary key was explicitly put into a secondary index of an InnoDB table. For example, given a tableemployees, ... read more
Tagged with "mysql"
Fun MySQL fact of the day: hypothetical buffer pool size
Yesterday, we imagined a hypothetical 200GB database and an InnoDB buffer pool of 204.8GB. We also ignored some details to decide that we can hold something like 92% of the database in memory. Today, I propose we continue being ignorant of details and further explore this hypothetical situation. ... read moreFun MySQL fact of the day: buffer pool size
Yesterday, we started to discuss the InnoDB buffer pool and I noted that 80% of a system's memory makes a decent starting size for the buffer pool. However, this is somewhat meaningless since we didn't consider how much memory we'd want our system to have. Today we will. ... read moreFun MySQL fact of the day: the buffer pool
Since February, we've been intentionally working off a half-formed mental model about InnoDB. In just about all cases, we considered a model wherein InnoDB always reads from and writes to the on-disk index files to serve each query. And while considering a mental model like this can certainly simplify ... read moreFun MySQL fact of the day: everything is two-phase
Yesterday, we started thinking about InnoDB's redo log, and I left you with a thought to consider: how can MySQL keep multiple storage engines' redo logs in sync with the binary log. Today, we'll consider how MySQL does it. ... read moreFun MySQL fact of the day: redo logs
Over the last few weeks, we've considered how useful the MySQL binary log can be, but you may be thinking, "if the binary log doesn't get written until commit, how does MySQL undo a transaction if it crashes?". And, well, if you remember back through March and April, you'll ... read moreFun MySQL fact of the day: group commit
We now know that each MySQL thread has its own binary log cache to which it writes the binary log events for a single transaction. We also briefly discussed that MySQL will, upon commit of a transaction, write the thread's binary log cache to the actual binary log file. And ... read moreFun MySQL fact of the day: binlog cache overflow
Yesterday, we started to consider how MySQL writes its binary log file, and we started to look at how MySQL uses a per-thread binary log cache, which, by default, is 32KB. Depending on your systems' access patterns, however, 32KB may seem rather small. For example, what if you frequently ... read moreFun MySQL fact of the day: binlog cache
By now, I think we have a shared understanding that the binary log is an important feature in MySQL. And maybe, you may have been wondering, "how does the binary log get recorded"? And that's a good question with a couple fun facts we'll look at next! ... read moreFun MySQL fact of the day: binlog row image
Considering yesterday's downsides to theROW binary log format, you may have spent the night sad and upset, maybe even unable to sleep. Likely, the narrative was along the lines of, "why, oh why, MySQL, must you make me choose between binary log size and safety?!" Well, rest-assured, tonight, you'll ... read more