"Who's Online" overhead

[MattG]

Hi, I guess this is the best area to put it in as it comes down mainly to the database.

I have a "Who's Online" feature to track the amount of members and guests online just like you would find in any modern forum software (i.e. http://phpbuilder.com/board/online.php)

Obviously with the regular INSERT, UPDATE and DELETE queries required for this it takes very little time for the table to build up an overhead which slowly grows until I optimize it.

I was wondering if anyone had any tips to reduce this so that the overhead doesn't build up so quickly and so that I wouldn't have to regularly optimize the 'online' table during the day?

[johanafm]

I'd be surprised if you'd really need to optimize that often.

If you do need to optimize table T while minimizing table lock time, you could
1. Create a new table N
2. copy all data from T to N
3. lock T
4. copy all data from T to N where T.id > MAX N.id
5. remove T
6. rename N to T.

That should minimize the time when T is locked.

Also, if deletions is a problem, you don't really need to continously delete from online. Keep a status field, such as a TINYINT UNSIGNED, which can be 0 for offline or 1 for online. When someone goes online, you INSERT INTO online ... ON DUPLICATE KEY UPDATE set status=1.
This way, you always insert into online when someone goes online. If the user allready exist in online, make sure status is set to 1.
But, for purposes of showing an online list, the people who have status offline have the same effect as if they were deleted (fragmentation). What you may save is some indexing work, except for index on online.status.
If you don't use indexes, there would be no gain with this approach, and this leads me to believe your problem is proper indexing rather than a dire need for optimize table. Sure, indexes take time to update, making each insert/update/delete slower while speeding up selects. But this is where I'd start looking.

Another approach is to forgo the DB completely, e.g. by using memcached.

[MattG]

Thank you, I'll look into those options. The table seems to start getting an overhead within a minute or two of getting optimized even with less than half a dozen visitors on the site so I will definitely need to look at improving this. Maybe increasing the timeout for visitors might help, it is currently set for just two minutes.

I have the 'title' field set as the index which represents the username of registered users or the ip address of guests (so the field is unique). Every time a visitor loads up a page on the site it goes through the online table deleting 'offline' visitors and updating the current visitor's timestamp by matching their username or ip.