0

This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components. eg the sequence an UPDATE query goes through from memory to disk. And considering a crash at every stage how 'durability' is maintained.

  1. innodb_log_buffer_size
  2. log_buffer
  3. Double write buffer
  4. ib_logfile0, ib_logfile1 (redolog)
  5. ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
  6. binary log (WAL)
  7. general query log (assuming enabled)

I explored and got few information but those are not sufficient and not explained clearly

MySQL Internals

XtraDb-InnoDB Internals

For Example:

  1. First data will be written in buffer pool which contains the page cache of tables that are used recently.
  2. If it is a READ request it is done in foreground like first it checks if the result is available in buffer pool , otherwise it hits the disk
  3. If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
  4. After innodb_flush_log_at_trx_commit the changes are flushed to disk (table.ibd).

After that I don't know OR I am wrong at the initial 4 steps or missing something. Pardon me as I didn't got any reading material online on this. At what stage it will be written in binary log (contains both committed and uncommitted transaction) or general log (contains everything including SELECT and unsuccessful queries). No idea.

Note1: Not interested in Optimizer, Parser, Other Storage Engines

Note2: Kindly do not mark this as too broad, A short precise answer covering the 7 components mentioned will be enough and really helpful for many in future

closed as too broad by Philᵀᴹ, kevinsky, mustaccio, Lennart, MDCCL May 25 at 18:50

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    MySQL is open source, and if Internals don't give you enough information you can read the sources for more details. – mustaccio May 24 at 15:25
  • #4 is wrong -- it is the log that is flushed to disk, not the table. – Rick James May 24 at 16:05
2

There isn't a clear-cut flow chart online for this, but if you want to understand how it all comes together for InnoDB, one of the best explanations I have seen are at M18 Deep Dive InnoDB Transactions and Write Paths. The slideshow is available at https://mariadb.org/wp-content/uploads/2018/07/Deep-Dive_-InnoDB-Transactions-and-Write-Paths.pdf

That presentation is for MariaDB, but MySQL should be quite similar.

The presentation of overall architecture of MySQL 8.0 at https://www.percona.com/resources/webinars/mysql-80-architecture-and-enhancement is also quite clear.

Not the answer you're looking for? Browse other questions tagged or ask your own question.