Improving XenForo Database Backup Speed?

enivid

Active member
Hello all!

Our forum got pretty large and with 160k+ posts it takes quite a long time to backup the database.

Is there any guide or tips for improving the database backup speed for XenForo?

We are using XF 2.2 with MariaDB 10.1.
 

enivid

Active member
No, it's the third-party service connecting via SSH and doing the job, but the result is probably the same as a cron job doing that.
 

Sim

Well-known member
Hello all!

Our forum got pretty large and with 160k+ posts it takes quite a long time to backup the database.

Is there any guide or tips for improving the database backup speed for XenForo?

We are using XF 2.2 with MariaDB 10.1.

How long is a "long time" ?
 

Masetrix

Well-known member
I would also recommend upgrading MariaDB if possible, because a lot has happened in terms of speed as well.
I'm really excited about MariaDB 10.6.5...
 

Mr. Jinx

Well-known member
What command is used to backup mysql?
Try adding '--single-transaction' to mysqldump.
This will prevent locking the complete database during backup.
 
Last edited:

Sim

Well-known member
Would standard MySQL optimizations make a difference with a mysqldump? Wouldn't it be down to purely single thread speed and I/O?

I suspect it's all I/O causing the issues, which is usually caused by a lack of RAM - or poorly configured software.

In my experience, running with insufficient memory allocated with a database on slow disks is going to make database operations very slow.

A mysqldump taking 10 minutes to complete on a smallish database sounds like a disk thrashing problem.

So either the server is using too much RAM and is causing memory to be swapped to disk, causing thrashing - or else the database is needing to read everything from disk for the mysqldump, and the disk is slow or busy being I/O bound - or it's a combination of both issues! That's my guess anyway.

The default options on most database servers are not appropriate for a small XenForo forum - for example, a default install of MySQL 8.0 on Ununtu uses completely inappropriate settings for XenForo running on a small VPS and presumes you have far more RAM available than you likely do.

This is why I recommend using mysqltuner - it will make suggestions about RAM allocation and other settings appropriate for your actual usage.

Basically - if you can ensure that your DB has sufficient RAM allocated to allow all Innodb data to be cached (innodb_buffer_pool_size) - then performance will be very good, since data will be served from RAM rather than disk.

The other trick is to not allocate too much memory to threads and buffers - especially if they aren't needed.

Some MySQL settings allocate RAM on a per-thread basis, so if you have a lot of threads and large buffer values - then you are allocating far more RAM than you need which restricts its availability for caching which can improve performance.

You also need to look at RAM used by other processes on your machine to make sure that enough is available for MySQL.
 

Sam9

Active member
Because for the backup to finish I already had to set max execution time to 10 minutes. That's not too good for the overall server setup.
Your PHP max execution time? Are you running the mysqldump via php exec()?

Is this a dedicated server, VPS or shared hosting? What disks do you use?

An alternative to traditional mysql backups is xtrabackup which is a lot faster, although I don't recommend using it unless you're experienced with server administration.
 

enivid

Active member
Slow mechanical drives likely combined with RAID is probably going to be your biggest bottleneck. Have you considered moving to an NVMe VPS?
I can always upgrade the server - I am just trying to do what I can with software before resorting to that.
 
Top