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.
- Double write buffer
- ib_logfile0, ib_logfile1 (redolog)
- ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
- binary log (WAL)
- general query log (assuming enabled)
I explored and got few information but those are not sufficient and not explained clearly
- First data will be written in buffer pool which contains the page cache of tables that are used recently.
- 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
- If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
- 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