XF 1.3 Small Forums, High Load, Please help..

ASap

ASap

Active member
Code: 
mysql> show processlist;
+--------+----------+-------------------------+-------------------+----------------+------+---------------------+------------------------------------------------------------------------------------------------------+
| Id  | User  | Host  | db  | Command  | Time | State  | Info  |
+--------+----------+-------------------------+-------------------+----------------+------+---------------------+------------------------------------------------------------------------------------------------------+
| 318925 | DELAYED  | localhost  | xenforo_db  | Delayed insert |  139 | Waiting for INSERT  |  |
| 333108 | DELAYED  | localhost  | xenforo_db  | Delayed insert |  91 | Waiting for INSERT  |  |
| 333595 | DELAYED  | localhost  | amanda_db | Delayed insert |  36 | Waiting for INSERT  |  |
| 334959 | xenforo_db | forum.somehost.net:55963 | xenforo_db  | Execute  |  618 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335167 | xenforo_db | forum.somehost.net:56143 | xenforo_db  | Execute  |  542 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335204 | xenforo_db | forum.somehost.net:56168 | xenforo_db  | Execute  |  501 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335242 | xenforo_db | forum.somehost.net:56192 | xenforo_db  | Execute  |  466 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335269 | xenforo_db | forum.somehost.net:56212 | xenforo_db  | Execute  |  447 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335307 | xenforo_db | forum.somehost.net:56234 | xenforo_db  | Execute  |  417 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335347 | xenforo_db | forum.somehost.net:56267 | xenforo_db  | Execute  |  383 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335385 | xenforo_db | forum.somehost.net:56300 | xenforo_db  | Execute  |  371 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335443 | xenforo_db | forum.somehost.net:56342 | xenforo_db  | Execute  |  341 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335480 | xenforo_db | forum.somehost.net:56365 | xenforo_db  | Execute  |  310 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335533 | xenforo_db | forum.somehost.net:56411 | xenforo_db  | Execute  |  274 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335570 | xenforo_db | forum.somehost.net:56436 | xenforo_db  | Execute  |  220 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335617 | xenforo_db | forum.somehost.net:56481 | xenforo_db  | Execute  |  202 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335679 | xenforo_db | forum.somehost.net:56534 | xenforo_db  | Execute  |  179 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335734 | xenforo_db | forum.somehost.net:56584 | xenforo_db  | Execute  |  151 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335740 | xenforo_db | forum.somehost.net:56590 | xenforo_db  | Execute  |  148 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335774 | xenforo_db | forum.somehost.net:56618 | xenforo_db  | Execute  |  119 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335779 | xenforo_db | forum.somehost.net:56623 | xenforo_db  | Execute  |  117 | Creating sort index | SELECT search_index.content_type, search_index.content_id
  FROM xf_search_index AS search_index
|
| 335821 | root | localhost  | NULL  | Query  |  0 | init  | show processlist  |
+--------+----------+-------------------------+-------------------+----------------+------+---------------------+------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

mysql>
Code: 
top - 03:57:02 up 77 days, 20:57,  3 users,  load average: 34.27, 46.77, 55.81
Tasks: 200 total,  1 running, 199 sleeping,  0 stopped,  0 zombie
%Cpu(s): 48.1 us, 33.3 sy,  0.0 ni, 17.4 id,  0.8 wa,  0.0 hi,  0.4 si,  0.0 st
KiB Mem:  10268324 total,  9100476 used,  1167848 free,  751440 buffers
KiB Swap:  1997820 total,  158884 used,  1838936 free,  4028324 cached

  PID USER  PR  NI  VIRT  RES  SHR S  %CPU %MEM  TIME+  COMMAND
9215 mysql  20  0 3573m 2.3g 6596 S 496.3 23.9  12198:35 mysqld
4053 amanda  20  0 95436 2032  484 S  37.1  0.0  5144:27 sc_serv
3990 amanda  20  0 95436 1864  472 S  34.4  0.0  5083:29 sc_serv
4113 amanda  20  0 95436 1964  460 S  33.1  0.0  5068:49 sc_serv
4169 amanda  20  0  119m 1768  484 S  31.1  0.0  5197:27 sc_serv
  250 root  20  0  0  0  0 S  3.3  0.0  37:49.62 jbd2/sda2-8
6638 apache  20  0  305m  86m  47m S  1.0  0.9  0:04.31 httpd
  13 root  rt  0  0  0  0 S  0.7  0.0  0:30.02 migration/2
11675 root  20  0  0  0  0 S  0.7  0.0  0:00.08 kworker/0:0
14762 amanda  20  0 41428 2520 1864 S  0.7  0.0  0:34.58 ssh
  1 root  20  0 10652  80  48 S  0.3  0.0  2:39.21 init
  8 root  rt  0  0  0  0 S  0.3  0.0  0:32.06 migration/1
1522 root  20  0  0  0  0 S  0.3  0.0  5:44.54 jbd2/sdb1-8
2059 root  20  0  0  0  0 S  0.3  0.0  0:00.56 kworker/7:1
2624 root  20  0  0  0  0 S  0.3  0.0  0:00.62 kworker/0:2
3593 ircd  20  0  227m  12m  780 S  0.3  0.1  21:29.93 ircd
4008 amanda  20  0 23264  240  72 S  0.3  0.0  3:59.59 screen
 
Sort by date Sort by votes
How big are the forums?

Those are all searches. MySQL FT search is not very good as the amount of data increases (and it's also a function of the number of documents that match a search). Normally, performance can get questionable around 1 million posts, but it should be ok up until then. That said, there may be MySQL configuration issue that could be relevant and causing disk swapping (though it looks like there's ample amount mmap'd in).
 
Upvote 0 Downvote
Thanks for replying, the forum has 1.055.850 threads, 3.775.008 posts, and 168.922 members.

But not all active at once.

It wasn't swapping issue:

Code: 
root@svr1:~# free -m
  total  used  free  shared  buffers  cached
Mem:  10027  9654  372  0  263  6416
-/+ buffers/cache:  2975  7052
Swap:  1950  171  1779
root@svr1:~#

Mike, what do you mean by MySQL FT searches? Do I need to change something?
 
Upvote 0 Downvote
With the size of your forum -- which is really not a "small" forum -- the default search, which uses a MySQL full text index, is very unlikely to have good performance, especially for common words. We would recommend using Elasticsearch (and XenForo Enhanced Search) beyond around 1 million posts.
 
Upvote 0 Downvote
Regardless of the activity, the issue is the amount of data. There isn't anything you can really do beyond limiting the data in the search index (xf_search_index table, you could empty it if you don't mind search not functioning) or by switching away to a different search system (Elasticsearch/XenForo Enhanced Search).
 
Upvote 0 Downvote
If I purchase XF Enhanced Search, do I still need to install Elastic Search, and other prerequisites?

I have truncated the `xf_search_index` table and disabled the search engine, but why is it filling up again?
 
Last edited:
Upvote 0 Downvote
Forum said:
If I purchase XF Enhanced Search, do I still need to install Elastic Search, and other prerequisites?
Click to expand...
Yes, you need to install Elasticsearch on your server. You'll need to point XF at it after installation. See for a discussion on configuring it: XenForo Enhanced Search | XenForo

Forum said:
I have truncated the `xf_search_index` table and disabled the search engine, but why is it filling up again?
Click to expand...
You've just removed the existing data from it. New data will still be inserted as needed.
 
Upvote 0 Downvote
No, data will always be inserted into it (unless you switch to XF Enhanced Search, in which case the data goes to Elasticsearch).
 
Upvote 0 Downvote
No, the issue is the process of searching itself. The indexing is not a significant problem in comparison to that. (Not to mention it's also more a factor of the amount of data in the index.)
 
Upvote 0 Downvote
Mike said:
No, the issue is the process of searching itself. The indexing is not a significant problem in comparison to that. (Not to mention it's also more a factor of the amount of data in the index.)
Click to expand...
Question #1:
Code: 
Mike, I just rebuilt my `xf_search_index` table.

But while rebuilding, on 3.000.000 th post it's stopped, it supposed to be 3.500.000 posts.

It reported Cache rebuilt successfully.

How to continue from 3.000.001 th to the last one?

Apart from that, threads, pages, etc are not rebuilt.

Any clue what's wrong?
Question #2:
Code: 
Is it possible to set XenForo indexing only new Threads, and *NOT* all new posts, and etc..?
 
Last edited:
Upvote 0 Downvote
Are you still running the default MySQL FT search? If so, you may be running into the performance challenges it has when it relates to bulk indexing, probably leading to your PHP process being killed. See what I discussed here: XF 1.2 - Very slow search index rebuild All of the reindexing issues are likely related to this.

There is no way to change what content is indexed.
 
Upvote 0 Downvote
Mike said:
Are you still running the default MySQL FT search? If so, you may be running into the performance challenges it has when it relates to bulk indexing, probably leading to your PHP process being killed. See what I discussed here: XF 1.2 - Very slow search index rebuild All of the reindexing issues are likely related to this.

There is no way to change what content is indexed.
Click to expand...
Yes, that was exactly what I've just done.

Btw is it possible to index only threads (yes threads only) and not all posts, and everything?
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

X
Fixed Lock contention on thread-read tracking under very high load
Replies
1
Views
658
XF Bug Bot
XF Bug Bot
D
  • Question Question
XF 1.5 High Load with Xenforo 1.5
Replies
1
Views
577
thedude
thedude
imthebest
Please help me to understand and change this small piece of code
Replies
11
Views
1K
AndyB
AndyB
CivilWarTalk
Issues with Server Load, Suggestions...
Replies
10
Views
1K
hellreturn
H
Back
Top Bottom