Announcement

Collapse
No announcement yet.

Update LastActivity Lagging Slave

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Forum] Update LastActivity Lagging Slave

    We're having increasing problems with our MySQL slave lagging, and much of it seems to be the constant barrage of lastactivity updates to the user database. Worse, they sometimes get stuck for a few full seconds:
    | 2 | system user | | dbname | Connect | 58 | end | UPDATE user
    SET lastactivity = 1357163120
    WHERE userid = 80109 |
    We're running at ~100,000 total users. We've usually got 1,000-1,500 on simultaneously.

    We've added an index to lastactivity.

    Any suggestions for better resolution on this?

    - - - Updated - - -

    For further reference:
    mysql> show index from user;
    +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
    | user | 0 | PRIMARY | 1 | userid | A | 112256 | NULL | NULL | | BTREE | |
    | user | 1 | usergroupid | 1 | usergroupid | A | 4 | NULL | NULL | | BTREE | |
    | user | 1 | username | 1 | username | A | 112256 | NULL | NULL | | BTREE | |
    | user | 1 | birthday_search | 1 | birthday_search | A | 8635 | NULL | NULL | | BTREE | |
    | user | 1 | birthday | 1 | birthday | A | 37418 | NULL | NULL | | BTREE | |
    | user | 1 | birthday | 2 | showbirthday | A | 56128 | NULL | NULL | | BTREE | |
    | user | 1 | referrerid | 1 | referrerid | A | 14032 | NULL | NULL | | BTREE | |
    | user | 1 | fbuserid | 1 | fbuserid | A | 4 | NULL | NULL | | BTREE | |
    | user | 1 | email | 1 | email | A | 112256 | NULL | NULL | | BTREE | |
    | user | 1 | lastactivity | 1 | lastactivity | A | 112256 | NULL | NULL | | BTREE | |
    +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
    10 rows in set (0.00 sec)
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X