The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Data Protection' started by chrishufford, Sep 13, 2013.

  1. chrishufford

    chrishufford Registered

    Joined:
    Sep 12, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  3. chrishufford

    chrishufford Registered

    Joined:
    Sep 12, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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?
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Yes, you are welcome to implement this custom solution to see if it alleviates the issue with the database.

    Thank you.
     
Loading...

Share This Page