Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then how about bin_log_cache is used.
I have googled this but didn't find much on this topic. I hope to have some explanantion here among the between people of this community.
A Google of "peter zaitsev" file systems buffering double write gives several good references (Peter Zaitsev works for Percona who are big hitters in the MySQL world.
Of these, I picked out 4 as being of particular interest[1, 2, 3, 4].
In the first reference, Zaitsev gives a great précis of the various reasons why partial page writes might occur (section entitled "Now lets talk a bit about partial page writes").
Zaitsev then goes on to a section about how double write works, but I think that a better explanation is to be found in reference 2 - in particular this bit:
If a partial page write occurs in the data files, InnoDB / XtraDB will check on recovery if the checksum of the page in the data file is different from the checksum of the page in the doublewrite buffer and thus will know if the page is corrupt or not. If it is corrupt, the recovery process will use the page stored in the doublewrite buffer to restore the correct data.
If a partial write occurs in the doublewrite buffer, the original page is untouched and can be used with the redo logs to recover the data.
Basically, this behaviour is controlled by a system parameter called innodb_flush_log_at_trx_commit. This section is too big to be quoted in its entirety here, I'll just quote the bit about the default value: (I strongly recommend that you read it in full to get a good overview).
◾ The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
However, this section of the manual also says:
Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to a second of transactions in a crash.
In plain English this means that "ye gets what ye pays for" - i.e. there's a balance to be struck between data integrity and performance. Despite searching high and low however, I haven't been able to find any more than this in terms of detail - unless you want to go rummaging round the code (above my pay grade I'm afraid :-) ).
You can switch off the parameter innodb_doublewrite and then this leads one to another system parameter innodb_flush_method - again it's all about performance vs. data integrity. My references 3 and 4 go into detail on this - basically the debate in these two references is about whether it is a good idea to switch off doublewrite if you have a transactional file system.
The consensus appears to be that if you're using ZFS (and other, perhaps esoteric, file systems/devices), you're safe switching off doublewrites - assuming RAID with battery operated disk caches. Reference 4 IMHO suggests that the debate is still open as to whether ext4 is safe - also see here.
The diagram below provides a good overview of the MySQL's InnoDB architecture.
Looking strictly at the the binary log, there is a flow you can follow
Please note what the MySQL Documentation says about binlog_cache_size:
The size of the cache to hold changes to the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large transactions, you can increase this cache size to get better performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.4.4, “The Binary Log”.
Since this setting affects use of transactions, there is a point in time where the binary log is written in the lifecycle of a transaction. Your question really is "When does the binary log come into play in the InnoDB Plumbing ?"
As Verace has already shown the pictorial representation of InnoDB, you can see there is no mention of the binary log. The MySQL Documentation actually mentions when the binary log interacts within a transaction.
Please note what it says in The Binary Log from the MySQL Documentation:
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
Another paragraph says the following:
Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.
Another paragraph says the following about the buffer caching of binlogs:
When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.
binlog_cache_size.
– Rick James
Jul 4 '16 at 4:59
You said : If yes then wont the be data loss in case of power failure in this process ?
I say : This depends whether your tx has been completely written in red log file or not.
Now , if your transaction has been completely written in redo log files then it will be flushed to disk ( by disk i mean , iblogfile is saved now) and hence thus reach to data files.Hence no data loss, even if power failure happened because your data is now committed in redo logs)
If your server crashes and txn hasn't been written completely in the redo log , then it will be rollback at the time of mysql restart . You can check same in your error log file post "crash and mysql server restart" .Here, MySQL comes in crash recovery mode and try to bring redo log,data file , binary log in sync with each other.
So , now as you have mentioned that data comes in buffer pool then goes to log buffer and as per the values of "innodb flush log at trx commit and innodb flush log at time out" , data will reach to redo log (iblog01 file) and flushes to disk and get reached to data files.
So , this is the transaction flow . I have mentioned same in my blog in explanatory manner. You can refer my blogs.
