The LSN (Log Sequence Number) is analogous to binary log position, but the redo log is a different log. It applies only to InnoDB pages, and it contains physical diffs of the pages affected. During crash recovery, it can reconstruct pages by applying the diffs to the original pages. It can also detect if a change has already been applied to a page, because pages are stamped with the LSN. It handles the case of pages that were flushed before the crash, which don't necessarily occur in any order. So starting from the earlier LSN, it doesn't need to apply every change in the redo log.
The binary log applies to tables of any storage engine, and it contains logical changes. It may contain row based events or statement based events. It is not used for crash recovery. Binary logs are strictly ordered, so if you use it for syncing a database (e.g. replication or point-in-time recovery), then it assumes all changes are to be applied from the starting position you give it.
There's no need for Percona XtraBackup to record the last LSN, because crash recovery just processes all the redo log up to the end.
[I] think the process of a commit would be as: commit command -> update data in innodb buffer (in memory) -> append to (redo log file + binary log) -> respond success to client
No, that's not correct. Keep in mind transactions have no limit on the number of rows modified, or the number of changes. So the changes must be queued somehow to allow the size of a transaction to be large.
In the case of the binary log, changes are written to a binary log cache. Each session is allocated its own binary log cache. This is in memory by default, but if the transaction is larger than the cache in memory, it can be copied to disk. As you execute INSERT/UPDATE/DELETE, these changes are appended to the binary log cache for the respective session.
On COMMIT, all the changes are finally copied from the session's binary log cache to the binary log. Therefore changes do not appear in the binary log until COMMIT.
The redo log is separate, and this is implemented only in the InnoDB storage engine. When you execute INSERT/UPDATE/DELETE, pages of data in the buffer pool are modified. At that time, these uncommitted changes to pages are also written to the redo log. Since the redo log has fixed size, you could make too many changes to fit in the redo log, and it wraps around to the beginning. To prevent losing changes in the event of a crash, modified pages in the buffer pool must be flushed to disk before the redo log wraps around.
On COMMIT, InnoDB does not need to copy any logs or flush any modified pages. Pages may remain modified in the buffer pool. Only the transaction is noted as being committed, by writing a small record written to the redo log. Therefore if crash recovery is necessary, it knows which pages contain committed changes. Pages that were not committed at the time of the crash are marked with a transaction id which is not recorded as committed in the redo log, so those pages don't need to be recovered.
[I] want to understand at which point would this redo log would be helpful and what is use of LSN in this.
The redo log is written to continuously, as you do INSERT/UPDATE/DELETE, and it is written to during COMMIT to mark each transaction as committed. But the redo log is not read, except during crash recovery. Its sole purpose is in reconstructing modified pages in the buffer pool.
The use of the LSN is so crash recovery knows where to start processing the redo log. For example, if most of the modified pages were already flushed at the time of the crash, there is no need to process the redo log prior to the oldest page that was still modified in the buffer pool. Crash recovery can skip directly to the LSN in the redo log corresponding to the oldest modified page. This helps crash recovery run a little faster.
Percona XtraBackup uses the same redo log processing code, because preparing a backup is similar to crash recovery. When Percona XtraBackup starts copying pages for the backup, some of those pages may have modified copies in the buffer pool. The copying takes a while, and some pages may be flushed or may become modified during that time, as subsequent query traffic executes INSERT/UPDATE/DELETE. When the XtraBackup prepare runs, it must inspect all the redo log back to the oldest modified page corresponding to the LSN at the time of the start of the backup, because any of those could be "lost" pages, i.e. only the page from disk was copied to the backup, without the modified version in the buffer pool.
when is the updated data persisted in tables? (is it when redo log is flushed?)
Modified pages are flushed to the tablespace continually. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html
Hopefully, the rate of flushing modified pages is at least fast enough that the redo log doesn't get 100% full. InnoDB does not allow un-flushed modified pages to exist in the buffer pool if they are not recoverable by the redo logs in case of a crash.
If the redo log reaches 100% full, then writes to the redo log must pause until some flushing of pages can catch up. This means all INSERT/UPDATE/DELETE action is blocked temporarily. You would have to overload your MySQL instance pretty badly to get to this state (i.e. load it with a higher rate of writes than it can sustain).
