1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Troubleshooting and Problems' started by Forum, Sep 5, 2015.

  1. Forum

    Forum 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
    
     
  2. Robru

    Robru Active Member

    Have you looked in your mysql and apache server log?
     
  3. Mike

    Mike XenForo Developer Staff Member

    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).
     
  4. Forum

    Forum Member

    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?
     
  5. Mike

    Mike XenForo Developer Staff Member

    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.
     
    batpool52! and Robru like this.
  6. Forum

    Forum Member

    it's an old forum, but the average concurrent online members not that much. should I change something in the XF settings?
     
  7. Mike

    Mike XenForo Developer Staff Member

    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).
     
  8. Forum

    Forum Member

    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: Sep 6, 2015
  9. Mike

    Mike XenForo Developer Staff Member

    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

    You've just removed the existing data from it. New data will still be inserted as needed.
     
  10. Forum

    Forum Member

    Is there any way prevent xf_search_index for filing up? Because I don't want to use XF's built-in search engine.
     
  11. Mike

    Mike XenForo Developer Staff Member

    No, data will always be inserted into it (unless you switch to XF Enhanced Search, in which case the data goes to Elasticsearch).
     
  12. Forum

    Forum Member

    So there's no way to disable the indexing-nature?

    Isn't this process kinda raising the high load issue?
     
  13. Mike

    Mike XenForo Developer Staff Member

    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.)
     
  14. Forum

    Forum Member

    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: Sep 7, 2015
  15. Mike

    Mike XenForo Developer Staff Member

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

    Forum Member

    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?
     
  17. Tracy Perry

    Tracy Perry Well-Known Member

    Errrr.. I do believe he said
     
    Forum likes this.

Share This Page