Solved Restore mysql seems to hang, tasq.queue gone on restore

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,529
Location
Maastricht
It's a bit odd. I'm restoring a load of users. I've done most of them via the user backups and restore as reseller.
Now I got a site with a forum. It's already taking longer than 1 hour, while the database is only 800 MB big. The backup went within 10 minutes.

What me stroke as odd, was that at first there was a tasq.queue present in DA's data directory.
After that for a long time a tasq.queue.tmp file with information about the restore.

And then suddenly, that file is also gone, but the database restore process still is busy.
Code: 
root      525777  0.0  0.0 1271196 23080 ?       Ssl  02:00   0:00  \_ /usr/local/directadmin/directadmin taskq --syslog
username  525815  0.0  0.0  14856  3308 ?        SN   02:00   0:00      \_ sh -c /usr/bin/mysql  --defaults-extra-file=/da_temp/satbenelux.525777.cnf.
username  525816  0.2  0.0  45308 11816 ?        SN   02:00   0:06          \_ /usr/bin/mysql --defaults-extra-file=/da_temp/username.525777.cnf.3nF

This keeps busy. And that username.525777.cnf.3nfiEM (in fact) file only has the user's account credentials in it.
Code: 
[client]
user="username"
password="xxxxxxx"

Old version Mariadb 10.4.30. On the new servers 10.6.14. However, I've done various backups and restores from 2 servers this way, but a database restore never took this long.
All user files seem to be restored, just this little database restore is taking very very long.

Do I need to worry, or just wait a few hours more?

P.s. No this is not on the VPS I had issues with, trying on both vps and other server, taking long on both.
 
After 1 hour and 20 minutes, the VPS has restored the account, the big server with twice the ram free and faster cpu and more cores but HDD is still not ready yet.
I'm going to put this as solved since it indeed looks like a waiting issue while the database isn't really that big.
 
Well.... the other server took around almost 4 hours. Seems the search index or something was causing an enormous load of slow queries which caused it to take so long.
Code: 
INSERT INTO `phpbb_search_wordmatch`
and then the load came.

Is there a way to speed that up so this will work a lot faster and don't create so many slow queries anymore?
@Zhenyapan a clue maybe or @jamgames2? Or anyone else with DB knowledge?

I didn't have that issue with other forum software.
 
possible, It still generate key for search index in whole thread of the forum. after that it should fine enought. because People enable this search option after big data table. so it leads to long time generater.

but I don't use phpbb, I don't know real problem.
 
HDD makes it impact :( did you check io-waits while process was in progress?
 
in regular TOP you will see WA section. Normally it must be less then 1
 
Oef... I don't remember, other things went fine and server worked quickly. Only that database took a long time and since I had configured a slow-queury logfile, I could see that this search thing caused slow queries.
The VPS was faster restoring, probably because of the SSD. The server also has the HDD's in raid 1.

Could one make the io-waits better then?
 
yes, ssd much faster than hdd in raid1. usually when I need hdd - than use it as /home, but OS+mysql on mirrored ssd/nvme.
 
The SSD is on the VPS, not in raid 1. The server has 2 * 4 TB HDD in raid. But the VPS is 8 vcpu cores with 30 gb ram. I don't know which cpu.
The server has a i7-6700 also 8 cores with 64 GB ram. So I thought that would be near.
Last week I restored my own hobby forum, which is Xenforo and that did not take that long.

I just checked, seems more phpBB users have this issue that most of the database is this search_word table. Several of them clear the table, move and then re-index again afterwards.
I will keep that one in mind. ;)
 
Just a suggestion, when restoring a lot of users with large MySQL/MariaDB databases it's a good idea to run mysqltuner after. If I know what I'm adding in advance, I will make the changes before migating everything to make everything go smoother (going by the settings from the previous server/install).
 
cjd said:
(going by the settings from the previous server/install).
Click to expand...
Yes that was the case. This was the other main server which is configured exactly the same as the first.

I coulnd't change anything in the database without knowing what I'm doing. So I rather let DA run things. I was thinking of adding the mysql database manually, because I tested and that also went quicker as DA did (a lot quicker).
Maybe it was to the "nice" setting that it was less fast too. I don't know for sure.

The only difference with the other server where it came from, is the mariadb version which is newer on this one.
But mysql settings etc. were optimal already, did mysqltuner some time ago on both servers.
At least if I did it correctly. :) I think there is even a thread about it from me somewhere, not sure anymoer.
 
You must log in or register to reply here.
Back
Top