Fixed User online feature degrading database performance

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.
 
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.
 
Mike said:
How many rows are in your xf_session_activity table?
Click to expand...

6000 and counting.

Mike said:
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.
Click to expand...

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

+----+-------------+------------------+--------+------------------------------+------------------------------+---------+------------------------------+------+---------------------------------+
 
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:
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.
 
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.
 
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.
 
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.
 
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?
 
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?
 
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.
 
Mike said:
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.
Click to expand...
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?
 
Mike said:
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.
Click to expand...

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).
 
Back
Top Bottom