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

Fixed User online feature degrading database performance

Discussion in 'Resolved Bug Reports' started by Sidane, Sep 9, 2014.

  1. Sidane

    Sidane Active Member

    Following the upgrade to XenForo 1.4 today, database performance was noticeably degraded. I'm seeing entries in the mysql slow query log every few minutes like the following:

    Code:
    # Query_time: 19.433014  Lock_time: 0.000054 Rows_sent: 7  Rows_examined: 14136216
    SET timestamp=1410276290;
    SELECT message.*,
                        user.*, IF(user.username IS NULL, message.username, user.username) AS username,
                        user_profile.*
                        ,
                        session_activity.view_date AS last_view_date
                    FROM xf_conversation_message AS message
                    LEFT JOIN xf_user AS user ON
                        (user.user_id = message.user_id)
                    LEFT JOIN xf_user_profile AS user_profile ON
                        (user_profile.user_id = message.user_id)
                   
                        LEFT JOIN xf_session_activity AS session_activity ON
                            (message.user_id > 0 AND session_activity.user_id = message.user_id)
                    WHERE message.conversation_id = '125327'
                    ORDER BY message.message_date
                 LIMIT 40 OFFSET 3520;
    
    I see that xf_session_activity is now joined in this query to enable showing the User Online Status Indicator.

    Disabling the showMessageOnlineStatus option resolves the performance issue.

    Have other large XF installations encountered this issue? My site is http://www.redcafe.net/

    It's a nice feature and I'd like to keep it enabled but if it's not suitable for larger forums then so be it.
     
    Xon and AlexT like this.
  2. Mike

    Mike XenForo Developer Staff Member

    How many rows are in your xf_session_activity table?

    Though I should note that I do see some oddities with the explain value on this query here. It should be a primary key lookup on that table. It's possible it's not behaving because it's a hash index.
     
    Azaly likes this.
  3. Sidane

    Sidane Active Member

    6000 and counting.

    Here's the EXPLAIN output from my DB for that query.

    Code:
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+---------------------------------+
    
    | id | select_type | table            | type   | possible_keys                | key                          | key_len | ref                          | rows | Extra                           |
    
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+---------------------------------+
    
    |  1 | SIMPLE      | message          | ref    | conversation_id_message_date | conversation_id_message_date | 4       | const                        | 4441 | Using temporary; Using filesort |
    
    |  1 | SIMPLE      | user             | eq_ref | PRIMARY                      | PRIMARY                      | 4       | redcafe_live.message.user_id |    1 |                                 |
    
    |  1 | SIMPLE      | user_profile     | eq_ref | PRIMARY                      | PRIMARY                      | 4       | redcafe_live.message.user_id |    1 |                                 |
    
    |  1 | SIMPLE      | session_activity | ALL    | PRIMARY                      | NULL                         | NULL    | NULL                         | 6031 |                                 |
    
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+---------------------------------+
    
     
  4. Mike

    Mike XenForo Developer Staff Member

    It does look like a hash index thing. Try running this query:
    Code:
    ALTER TABLE xf_session_activity
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (user_id, unique_key) USING BTREE;
    I am a little confused by MySQL using a temp table as the where and order by are covered by an index.

    Edit: not sure if it's worth tweaking the query to use both parts of the existing index instead of going b-tree here.
    Edit 2: looks like MySQL doesn't want to use a query in that case anyway, at least without some more type coercion so the b-tree approach may be preferable.
     
    Last edited: Sep 9, 2014
    RoldanLT, Xon and Cool like this.
  5. Sidane

    Sidane Active Member

    That looks better:

    Code:
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+-------------+
    | id | select_type | table            | type   | possible_keys                | key                          | key_len | ref                          | rows | Extra       |
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+-------------+
    |  1 | SIMPLE      | message          | ref    | conversation_id_message_date | conversation_id_message_date | 4       | const                        | 4441 | Using where |
    |  1 | SIMPLE      | user             | eq_ref | PRIMARY                      | PRIMARY                      | 4       | redcafe_live.message.user_id |    1 |             |
    |  1 | SIMPLE      | user_profile     | eq_ref | PRIMARY                      | PRIMARY                      | 4       | redcafe_live.message.user_id |    1 |             |
    |  1 | SIMPLE      | session_activity | ref    | PRIMARY                      | PRIMARY                      | 4       | redcafe_live.message.user_id |   63 |             |
    +----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+-------------+
    I'll turn the feature back on, monitor performance and get back to you. Thanks Mike.
     
  6. Mike

    Mike XenForo Developer Staff Member

    The row count estimate for session_activity is probably off there - I suspect it's an estimate that's skewed by the number of entries with user_id = 0 which should (if MySQL is smart) be short circuited by one of the conditions.
     
  7. imthebest

    imthebest Formerly Super120

    So it's safe to upgrade from 1.4 RC2 to 1.4?
     
  8. Mike

    Mike XenForo Developer Staff Member

    If you were running 1.4 already, then you already had the "problem". Even 1.0 - 1.3 has the issue to some degree in other places.
     
  9. dethfire

    dethfire Well-Known Member

    I usually have 4k-5k concurrent online and I am upgrading in a week. Should I plan to run that query?
     
  10. lazy llama

    lazy llama Well-Known Member

    Upgraded to 1.4 this morning and the delays in xf_session_activity writes were taking the board down.
    I've run the query to change the index as per @Mike's suggestion and that seems to have fixed it.
    We usually have a thousand or so rows in that table.
     
  11. semprot

    semprot Active Member

    Then i think Mike's suggestion should be applied as core on next update? (y)
     
    lazy llama and Alluidh like this.
  12. CyclingTribe

    CyclingTribe Well-Known Member

    ... or even added to the current download package? ;)
     
    semprot and Alluidh like this.
  13. dethfire

    dethfire Well-Known Member

    I think it should be an immediate patch or hot fix.
     
  14. Brogan

    Brogan XenForo Moderator Staff Member

    I vote we let the developers decide how best to implement it.
     
    Liam W, SneakyDave, Kevin and 4 others like this.
  15. imthebest

    imthebest Formerly Super120

    I have been on 1.4 since the first days and haven't experienced the issue reported here. Could it be due to my config.php?

    Code:
    $config['cache']['enabled'] = true;
    $config['cache']['frontend'] = 'Core';
    $config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
    $config['cache']['cacheSessions'] = true;
    $config['cache']['backend'] = 'Xcache';
    
    cacheSessions is set to true... I believe that prevents direct reads to the database? If so, why you people reporting this aren't using a caching system with that huge amount of people active in your forum?
     
  16. lazy llama

    lazy llama Well-Known Member

    I have cacheSessions enabled too, using memcached for caching.

    So it's not that.

    I'm not sure what the relationship is between cacheSessions and the xf_session_activity database table, maybe someone who knows the code better could enlighten?
     
    semprot and imthebest like this.
  17. Mike

    Mike XenForo Developer Staff Member

    Session activity tracks the page people are on and when they were last active. It's unrelated to sessions themselves.

    The query I listed above should be sufficient to fix the issues with the index not being used correctly.
     
    lazy llama likes this.
  18. semprot

    semprot Active Member

    so the database performance decrease if there are lot of visitors doesn't always happen on each installation?
    does the issue happen depend on server configuration / mysql configuration / such?
     
  19. CyclingTribe

    CyclingTribe Well-Known Member

    Can the query be applied to pre-1.4 installations? (I've yet to upgrade, but it would be handy to apply this now so I don't forget later).
     
  20. Mike

    Mike XenForo Developer Staff Member

    If you like, yeah. It's changed in 1.4.1.
     

Share This Page