Live chat with PHP and jQuery. Where to store information? Mysql or file?

Question

There are 1 on 1 live chat. Two solutions:

1) I store every message into database and with jQuery's help I check if there is a new message in database every second. Of course I use cache either. If there is, we give that message.

2) I store every message in one html file and every second through jQuery that file is shown over and over again.

What is better? Or there is third option? And in general, what is better, mysql or file for this kinda project?

Thank you very much.

P.S. The most important question is: what is more efficient and what way will eat less resources!

Edit: And is it, nowadays, very bad for many chats (let's say 2,500 chats, that means 5,000 users) to use long polling and check when file was edited every second through javascript? I use very similiar methods like this chat: http://css-tricks.com/jquery-php-chat/ Will it kill my hosting?

Answer 1

Everyone has given a wide range of opinions but I don't think anyone has really hit the nail on the head.

When it comes down to storing data, the amount of data, the rate it is to be accessed, and several other factors all determine what's the best storage platform.

Some people have suggested using memcached. Now although this is a valid answer (you can use it), I don't think that this is a good idea, solely based on the fact that memcached stores data within your server's memory.

Your memory is not for data storage, it's for use of the actual applications, operating system, shared libraries, etc.

Storing data within the memory can cause a lot of issues with other applications currently running. If you store too much data in your RAM your applications would not be able to complete operations assigned to them.

Although this is faster then a disk based storage platform such as MySQL, it's not as reliable.

I would personally use MySQL as your storage engine server-side. This would reduce the amount of problems you would come across and also makes the data very manageable.

To speed up the responses to your clients I would look at running node on your server.

This is because it's event driven and non-blocking.

What does that mean?

Well, when Client A requests some data that is stored on the hard drive, traditionally PHP might say to the C++, fetch me this chunk of data stored on this sector of the hard drive. C++ would say 'ok no problem', and while it goes of to get the information PHP would sit and wait for the data to be read and returned before it continues it's operations, blocking all other client's in the meantime.

With node, it's slightly different. Node will say to the kernel, 'fetch me this chunk of information and when your done, give me call', and then it continues to take requests from other clients that may not need disk access.

So suddenly because we have assigned a callback to the kernel, we do not have to wait :), happy days.

Take a look at this image:

This really could be the answer your looking for, please see the following for a more descriptive and detailed information regarding how node could be the right choice for you:

• http://blog.mixu.net/2011/02/01/understanding-the-node-js-event-loop/

Answer 2

A fourth option, probably not what you want if you already have PHP code you want to use, but maybe the most efficient is to use a Javascript based server instead of php.

Node.js is easily capable of being a chat server and can store all the recent messages as a Javascript variable.

You can use long polling or other comet techniques so that you so not have to wait a second for messages to update.

Also, the event based architecture of a Javascript server means that there is no overhead for idling around waiting for messages.

Answer 3

It depends on number of chats in the same time. If it's for support and you expect average load to be 1 to 5 chat sessions at a time then you don't to worry too much. Just make sure that when there is no activity for some time stop refreshing and show a message for user to click to resume chat session.

If the visitors will chat with each other and you expect big number of sessions - 10-50 at the same time you can still use PHP + database. Just make sure you don't make redundant queries and your queries are cached correctly. To reduce load you can also deny chat script from being logged in web server:

SetEnvIf Request_URI "^/chat.php$" dontlog
CustomLog /var/log/apache2/access.log combined env=!dontlog

Edit:

you can have delay schema. For example if you query 2 times with delay 1 second and you get no data you can increase delay to 2 seconds. if you reach 10 queries with no response - increase delay to 5 seconds. After 10 minute you can pause the conversation, requiring users to click on a button to resume the chat. That'll, combined with advices above will guarantee low enough load to have many concurrent chats

Edit2:

I suggest you to find some flash or java solution and buy it. With 5000-10000 users you have to be genius to make it work on VPS, especially if RAM is not much. Not that it's not possible but you can rent cheaper VPS and with the rest of the money buy some solution in java or flash (don't know if flush supports 2 way connection, I'm not a flash expert).

Note about number of users: if you have 10 000 users my guess is that you'll have not more than 100 chats at the same time. Go and look dating sites - they have not more than 10% of the users online and maybe most of them are doing something else and not chatting

Answer 4

3rd option. use MEMCACHE. infinitely faster read/writes. perfect for your application.