Advice on different backup approach to reduce server load and increase database availability

[chrishufford]

My backup with WHM 11.38 was taking about 1 hour 40 minutes. This was using the new backup system with compression and pigz. During that time, it caused the server load to go up to 10+ at some points. Also, it locked the main database for 30 minutes which causes customers frustration because the entire site stops responding. This leads to a backlog of requests and a higher server load. I want to keep 7 daily copies of the backup as well as 3 monthly backups. I like being able to use WHM's restore capability.

Here is the scenario that I have come up with to keep the database unlocked and to keep the server load down.

1 - change the owner of the main database to root so that the backup system will skip it
2 - change the backups to incremental. Those reduce the 1:40 backup down to about :25 and the server load stays under 3.
3 - make a new crontab that runs after backup is complete which does a backup of the main database using mysqldump -single-transaction which does not lock the database (the tables are all InnoDB). Put the database backup into the incremental/accounts/account1/mysql (the place the database backups would normally go)
4 - then run a script like this:
tar -zcf /2nddrive/2013-09-12/accounts/account1.tar /2nddrive/incremental/accounts/account1/
tar -zcf /2nddrive/2013-09-12/accounts/account2.tar /2nddrive/incremental/accounts/account2/
etc.
This is to create my own copies of the incremental backup in a format and location that can be restored from WHM.
5 - delete old backups

Does this sound like a good idea? Is there a better way to accomplish lower server load without locking the database? I have tried some of this manually but haven't automated it yet.

Thank you.

 

[cPanelMichael]

Hello

You may want to consider utilizing the following option under "Stats and Logs" in "WHM Home » Server Configuration » Tweak Settings":

"Extra CPUs for server load"

Per it's description:

The load average above the number of CPUs at which cpuwatch, cpanellogd, backups, and CPU stats consider the system to be in a critical load state. For example, a server with 4 physical CPUs and a value of 2 in this field will be considered “critical” in these cases once the load reaches 6.

This will ensure the backup process pauses when the load reaches a certain threshold.

Thank you.

 

[chrishufford]

Thank you. I have 4 physical CPU and have set the value from 0 to 2. I forced a backup and set it back to Compressed (rather than incremental). The load was lower than before but I will watch it for a few days. However, the database locking problem remains.

Perhaps I should still:

1 - change the owner of the main database to root so that the backup system will skip it
2 - make a new crontab that runs after backup is complete which does a backup of the main database using mysqldump -single-transaction which does not lock the database (the tables are all InnoDB).

Thoughts?

 

[cPanelMichael]

1 - change the owner of the main database to root so that the backup system will skip it
2 - make a new crontab that runs after backup is complete which does a backup of the main database using mysqldump -single-transaction which does not lock the database (the tables are all InnoDB).

Yes, you are welcome to implement this custom solution to see if it alleviates the issue with the database.

Thank you.