Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

Question recounting

+4 votes
101 views
asked Jun 12 in Q2A Core by Joozty
Hi. I recently manually import to qa_posts table more than 8 000 000 question. I need recount related question and everything else but there is a problem. Recounting is very slow. My database is hosted on Google Cloud (8 CPUs, 32 GB RAM) Recounting of  30 000 questions took 2 hours. So whole recounting would be take 22 days.  How can I speed up this process? Thanks a lot.
Q2A version: 1.8

3 Answers

+1 vote
answered Jun 13 by Scott
Recounting shouldn't be that slow, but reindexing can be very slow.

However, reindexing can be sped up by first emptying (TRUNCATE in SQL) the qa_words, qa_contentwords and qa_titlewords tables. You'll need to put your site in maintenance mode first, otherwise users adding new posts could slow it down again.
commented Jun 14 by Scott
I may have missed a table you need to truncate.
Try emptying all the tables in point 4 here: http://docs.question2answer.org/install/security/
commented Jun 14 by Joozty
No better results...
+1 vote
answered Jun 13 by sama55

In fact, "Admin" > "Stats" > "Reindex content" is slow. The reason will be that only 10 posts are processed per one Ajax request. Your server seems to have sufficient performance. Therefore, if your server (PHP and MySQL) has sufficient resources (settings), you may be able to speed up by increasing this processing count.

Hacking example of reindex count per one ajax request:

Source: qa-include/app/recalc.php around L133 in case of V1.8

Code:

case 'doreindexcontent_postreindex':
    //$posts=qa_db_posts_get_for_reindexing($next, 10);
    $posts=qa_db_posts_get_for_reindexing($next, 100);

Note:

This process consumes a lot of server resources. Especially in case of shared server, you need to be careful. And, this change does not always work correctly on your server. It is important to gradually increase the value. For example, 50 > 100 > 300 ...

commented Jun 14 by Joozty
Hi, I don't see any improvements even when I use bigger numbers (>10 000). Process doesn't  consume a lot of server resources. CPU and READ/WRITE operations are still at 10%, RAM isn't full.  :(
+1 vote
answered Jun 15 by sama55
edited Jun 15 by sama55

If extra plugins do not use unindex_post and index_post events, following hack may be effective. You need to make sure that unindex_post and index_post are not processed for all of your plugins.

Hacking example of reindex process per one ajax request:

Source: qa-include/app/recalc.php around L133 in case of V1.8

Code:

// Change from 10 to 1000. You need to adjust according to your server environment.
$posts=qa_db_posts_get_for_reindexing($next, 1000);

if (count($posts)) {
    require_once QA_INCLUDE_DIR.'qa-app-format.php';

    $lastpostid=max(array_keys($posts));

    qa_db_prepare_for_reindexing($next, $lastpostid);
    qa_suspend_update_counts();

    // Remove all indexes by DELETE query. TRUNCATE query will be faster.
    if($next == 0)
        qa_db_truncate_indexes($next);

    foreach ($posts as $postid => $post) {
        //qa_post_unindex($postid); // Reduce DELETE queries
        qa_post_index($postid, $post['type'], $post['questionid'], $post['parentid'], $post['title'], $post['content'],
            $post['format'], qa_viewer_text($post['content'], $post['format']), $post['tags'], $post['categoryid']);
    }

...