XF 2.0 CLI Import very slowly

[developr]

Hi Community,

since yesterday I run the importer for vBulletin 3.8 with Blogs and it takes very long (actually 23 hours to step 19)!

The machine is not so bad:
Intel Core i7-3930 (3.2 GHz, 6 Cores, 12 Threads)
64 GB DDR3
PHP 7.2 (CLI)
MySQL 5.7 (on SSD)

Starting import from vBulletin 3.7, 3.8 with vBulletin Blog (Beta)...
 - Step  1 of 31: Usergroups           00:00:00 [11]
 - Step  2 of 31: Individual Profilefields 00:00:05 [27]
 - Step  3 of 31: User                  00:11:32 [48.986]
 - Step  4 of 31: Avatars                   00:52:19 [6.148]
 - Step  5 of 31: Buddy and ignore lists    00:00:29 [2.278]
 - Step  6 of 31: Paid subscriptions        00:00:00 [0]
 - Step  7 of 31: Individuelle BBCodes      00:00:00 [2]
 - Step  8 of 31: Private messages          00:40:55 [187.848]
 - Step  9 of 31: Profile comments          00:02:17 [22.395]
 - Step 10 of 31: Forums                     00:00:00 [93]
 - Step 11 of 31: Moderators               00:00:01 [6]
 - Step 12 of 31: Prefixes            00:00:00 [4]
 - Step 13 of 31: RSS feed sources          00:00:00 [1]
 - Step 14 of 31: Threads                    00:08:42 [81.935]
 - Step 15 of 31: Tags               00:05:37 [63.927]
 - Step 16 of 31: Posts                 03:32:17 [2.000.173]
 - Step 17 of 31: History Edits       02:05:34 [178.222]
 - Step 18 of 31: Polls                  00:00:02 [335]
 - Step 19 of 31: Attachments                  15:31:00 [124.306] 93,29%

Is there a way to speedup the importer?

 

[S Thomas]

You have 124k attachments. Even if it was 1 attachment per second which was being processed, it would take 34 hours solely for that. Right now you're quite double as fast as that.
You could delete the unimportant stuff first. Or reduce the attachment size.

 

[developr]

Wow... 1 second per file just for moving and renaming? I wrote an import for vb3.8 albums and it tooks only 10 minutes for 58k albumimages. Only reading the source database, write it in target database, copy and rename images.

 

[S Thomas]

Look into the importer code. If I had to guess I would say it builds the attachment thumbnails aswell.

 

[developr]

Yes it seems you are right. But in my opinion it would be better to increase the speed of the import and run the rebuild later to decrease the downtime.

 

[developr]

Maybe for people with a lot of RAM I have a solution to reduce the time of import. It is about 500% faster as SSD.
In my case the import takes only 1:45h for about 3 million posts.

Move the source and target database into tmpfs (ramdisk). You have to check the size of mysql data directory first and if you have free memory. In my example I have to create a ramdisk of 20G.

sudo du -sh /var/lib/mysql/DBNAME
sudo free -mh

If you have enough free memory you can stop you mysql server, rename the data directry, create a tmpfs and mount it.

sudo service mysql stop 
sudo mkdir -p /home/backup/DBNAME/
sudo mv /var/lib/mysql/DBNAME/* /home/backup/DBNAME/
sudo mount -t tmpfs -o size=20G none /var/lib/mysql/DBNAME
sudo mv /home/backup/DBNAME/* /var/lib/mysql/DBNAME/
sudo service mysql start

Now you can run the cli importer.

sudo php cmd.php xf:import

After import you can move the database vom ram back to disk.

sudo service mysql stop 
sudo mv /var/lib/mysql/DBNAME/* /home/backup/DBNAME/
sudo umount /var/lib/mysql/DBNAME
sudo mv /home/backup/DBNAME/* /var/lib/mysql/DBNAME/
sudo service mysql start

Of course you should not reboot your machine while your database is on ramdisk ;-).