Not the answer you need?
Register and ask your own question!

how to calculate a proper log file size

Lenny AndersenLenny Andersen EntrantCurrent User Role Beginner

I'm creating a backup plan for my MySQL Server (5.5). I was thinking about a full backup every sunday morning and an incremental backup monday - saturday.
I figured that innobackupex pretty much will do the trick for me.

So far so good.

I was reading about how the backup is done, and I understand that the incremental backup is done by reading the changes in the logfile since last backup (Based on the LSN).

What happens if my logfile is i.e. 5 MB but in the time between two backups there is written 20 MB to the log.

I guess I won't be able to create a valid incremental backup since 15MB of information must be missing in the log?
and I also guess I will have to increase the log file size to fix this problem?

But how do I calculate a proper size for the logfile?

any "best practices" on this matter?


  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    The incremental backup is not taken reading the redo log file. What Xtrabackup does is the following:

    - When a full backup is taken, it writes the last LSN (log sequence number) copied on that backup to a file. Let's say it is 500.
    - When you take an incremental, xtrabackup reads that file and checks that the incremental should continue on LSN 500.. Then, it scans InnoDB data and copies all pages with a LSN greater than 500.

    So, for incremental backups the redo log size is not important. Maybe you read something about log archiving feature in Percona Server, but that's a different way to do the backups.

    Anyway, having a proper size of the logfile is good for overall performance, so check this blog post to learn how to calculate it:
  • Lenny AndersenLenny Andersen Entrant Current User Role Beginner
    Thanks for the answer and the link. Very helpful :)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.