Creating High Performance Online Users Application in PHP & MySQL

If you own a website then you may be interested to see how many users are currently online on your website or you may have integrated third party online user tracking system on your website or you may written your own custom online users application that tracks users activity on your website and periodically remove inactive users from database. This is most wanted feature if you are running a social network system because most of the users prefer to talk online users than waiting for offline people to online and answer them. I also run a social network application and I wrote a simple online users script that actually keep track of loggined users. Initially it worked fine but after few months when site start became popular MyISAM table was keep crashing because of delete and update statements running on every page. If you know about MyISAM storage engine then perhaps you also know how unreliable storage system is this.
I will show you in this article how we are going to track all our loggined members. Usually there is no reason to track how many guests are online in social networks but if you are interested you may customize the functionality of this program.  I have tested with 2000 members online at the time with 2 minutes interval and daily more than 45000 users per day traffic on the site, it works without any problem.
First we need to create a table in MySQL database I assume you already have users/members table in your database. For your reference I am going to write users table here, but this can be different from my users table.

In this example my users table has InnoDB storage type you may have different storage engine depending on your application needs. I have used user_id as a foreign key in online table (just for relationship, though it doesn't work) and created index on it because we will search by user id and lookup will be fast. Notice I have used memory storage engine on online table instead of MyISAM or InnoDB. There are many reasons here I will mention few important reasons. I already discussed above MyISAM tables crashed very often when you run update and delete statements simultaneously. If table does not crash it will put exclusive write lock on table and perform update or delete operation. So  for instance if you have thousands people online then they have to wait for the table to be released by update or delete lock. On the other hand Innodb storage engine puts row level locking instead of table lock so why I didn't choose Innodb storage engine, why memory storage engine? I did a benchmark on online table and used three storage engines, MyISAM, Innodb and Memory engine with 1 hundred thousand users in users table and 5 thousand online users in 3 online tables and I got following results.
Memory: 0.00 sec
Innodb: 0.2 sec
MyISAM: 0.3 sec
If you would like to benchmark on your computer you may download benchmark script from bottom of this page.
One of the drawback of having memory storage engine is when mysql server shutdowns all records will be removed from online table because those records reside in mysql server memory, but its not big deal if your site is pretty much active you will see again all active users in few seconds.
It's simple to setup the code in your existing application. I have included test.php for demonstration purpose and for your ease I will explain the setup code here.

On the first line I have called session_start() function. This function must be called before you initiate the online class. I presume your application already have established the database connection if not you must establish the connection first before we go next. Here I have included the database connection code for demonstration purpose, change following variables db_server, db_name, db_user and db_pass values so it connects with your mysql database.

I am keeping all user login information in an array so I don't have to query database each time. Uncomment the line numbers from 2 to 7 if you would like to test it with dummy record. On line number 8 I am checking whether the user is loggined or not if its loggined then I am storing its value to CURUSER array. You must validate this record from database because cookies values are highly unreliable. Soon I will write an article on website protection and security you will learn in this article how to protect your site against hacking, xss and sql injection attacks. On line 14 and 15 I have included the online class and initiated it. If you would like to keep online users session for 5 mins then leave the settings as it is or change the session time, line 17 checks if its loggined user and session is not created or its expired then it inserts or update the users record. I would recommend you to call cleanup function by cron instead of calling it on every page. You should put above code in a php file which calls on every page usually header or configuration file are the best place to insert this code.

Download Code Benchmark Online Users Script

(3 votes, average: 5.00 out of 5)

 Loading ...