Skip to:
Content
Pages
Categories
Search
Top
Bottom

Importing large Vanilla Forum to bbPress – Speed


  • gcoz
    Participant

    @gcoz

    I’ve been trying to import my vanilla 2 forum posts (just under 500000 replies) and it’s been working away for a very very long time. Tried to do it on the server as well as locally on my macbook pro running Mountain Lion. It seems that it’s even running slower locally than on my server and after leaving it to run for a couple of days, I’m about 1/4 of the way there.
    Tried all kinds of delay times and row limits but I can’t find a way to improve the speed of the import.
    I was wondering if anyone here has successfully imported a database this size and has tips on how to speed up the process? Even at a 200 Limit with 3 second delay my MySQL workbench on my local machine shows 11000 Queries/second which seems a bit high?
    I’m using the latest versions of bbpress, wordpress and vanilla.

Viewing 7 replies - 1 through 7 (of 7 total)

  • Stephen Edgar
    Moderator

    @netweb

    Seems a little strange, some recent vBulletin conversions here have been a similar size and completed circa 6-7 hours.

    As long as your web host MySQL is up to scratch (presumably so as per your existing Vanilla forum size) you should be able to reduce the delay from 3 seconds down to 1 or even 0.5 or 0.1 if you are doing it locally. This will reduce the time between each SQL query.

    Did the importer stall or was it still plodding along eg. ‘Converting replies 21,800 – 21,900′


    gcoz
    Participant

    @gcoz

    Thanks for the link, I had a read through the topic and I did see those dashes mentioned over there pop up twice in my import so far. All I needed to do, was to reload the Tools/Forum page with the importer, add all the db details and hit start.
    It picked it up where it left off (no way for me to verify at this stage if anything got messed up but that was the only error I could see)…

    The one odd thing I noticed was that the topic import is blazingly fast but the replies are very slow. Not sure if there’s a reason for that?

    I lowered the delay to 1 second but looking at the rate at which posts are added to the “reply” post-type, it’s not really speeding up the process. Maybe my MySQL config might be to blame there if it’s not catching up?

    Might have to try it on a different machine locally if 6 hours is possible for this kind of import. At this rate it’ll take another 4-5 days which is enough time to set up another machine for testing and letting it run for a while.


    Stephen Edgar
    Moderator

    @netweb

    I just had a quick look at the SQL queries for both topics and replies and they are both very similar and the time per 100 records should not differ greatly.

    It might be that the ‘posts’ table might have some SQL overhead and the table needs to be ‘optimized’, your web host should have an FAQ detailing how to ‘optimize’ your database tables with phpMyAdmin.


    gcoz
    Participant

    @gcoz

    thanks for checking the queries. I believe it’s not your importer, actually…
    I think there’s just an issue with my databases. I did the optimization for all tables, made another local copy (a brand new installation of Ubuntu with default settings for apache, php and the largest settings for mysql allowing it to use more resources).
    However, after some more digging, I’m starting to think that my tables are the reason for this slow-down. Specifically them being a mix of MyISAM and InnoDB after many years of conversions, WP updates and one forum conversion from phpbb to vanilla.

    I tried to get some out-of-memory errors but all it does is slow it down and it keeps going really slowly. Even setting it to 2000 rows at 1 second doesn’t seem to push it to run out of memory. The “top” command just shows me that it’s working away at 60% CPU and 11% memory for mysql and 50% CPU and 2% memory for apache.

    MySQL workbench shows that I only have 6 connections active, which is probably another related issue. I don’t know how to change that but my current running test conversion is with all WP tables converted to InnoDB. It fluctuates at 50-200 writes per second according to MySQL workbench

    This is total guesswork on my end (I’m no expert) and I thought it might be a helpful starting point for others with similar issues…


    Stephen Edgar
    Moderator

    @netweb

    Another option you have is to install a ‘RAM Drive’ just for the MySQL data using symbolic links for the MySQL data store (make sure you regularly copy & backup this data) and this will vastly improve performance over any mechanical hard disk.


    gcoz
    Participant

    @gcoz

    I’m already running it on an SSD. Would RAM Drive still improve the performance significantly?


    Stephen Edgar
    Moderator

    @netweb

    Probably not, an educated guess would be only a slight improvement over SSD.

Viewing 7 replies - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.