Improving XenForo Database Backup Speed?
Sim
Well-known member
How long is a "long time" ?
Sim
Well-known member
For 160K posts that is far too long - my site with over 1 million posts only takes a couple of minutes.
Take a look at your DB optimisation - how much RAM does your server have? Are you running on HDDs or SSDs or NVMe ?
Try running https://github.com/major/MySQLTuner-perl to see if there are any basic recommendations you can implement to improve DB performance.
Rasmus Vind
Well-known member
Why is it a problem? Is your site read only when this occurs?
enivid
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.
Sim
Well-known member
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
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
No, it has nothing to do with PHP. I was talking about max_statement_time getting exceeded.
A dedicated server with HDD.
dknife
Well-known member
Slow mechanical drives likely combined with RAID is probably going to be your biggest bottleneck. Have you considered moving to an NVMe VPS?
enivid
Active member
I can always upgrade the server - I am just trying to do what I can with software before resorting to that.
Sam9
Active member
That is almost certainly your bottleneck. What percentage iowait are you seeing on
top whilst the backup is running?Similar threads
