Question recounting

Question

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.

Answer 1

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.

Comments

Sorry my mistake, I mean reindexing.  I truncated all tables you wrote and it is still too slow. My CPU isn't fully utilized. It is at 10%... RAM isn't full and READ/WRITE operations are at 10% too. I think there is some another problem.

Anyway thanks for your answer,  I appreciate it.

---

The number of reindexed questions is incremeanting by 20 only. How can I increase this number or run more instances of this query? Maybe this is the problem...

---

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/

---

No better results...

Answer 2

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 ...

Comments

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.  :(

Answer 3

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']);
    }