11

I'm making a chat program, and I need a place to store messages. The client would contact the server every x seconds with the last received message id, and the server would find all messages with a id higher than that, in the rooms that the client has joined.

As I'm not going to store things forever, I'm thinking of using flat files (one per room, as well as direct messages) with only the last 40 or so messages. However I think with comparing numbers a database would be faster.

What method of data storage should I use?

apscience
  • 7,033
  • 11
  • 55
  • 89

4 Answers4

9

The flat file may be a bit faster, but it will end up being more buggy in the long run, because instead of just doing a SELECT * FROM messages WHERE room=nnn AND ID > yyy, you will have to load the file, parse it, scan each row for the message ID, go to the right message, and then read it out.

That's only the first problem. A text file doesn't support writing by multiple users (what if two people are posting to the same room at the same time?), and can easily become corrupt.

All things considered, I'd say it's better across the board to use a DB, even if it's something simple like SQLite, which has excellent PHP support. However, consider the many-users condition, MySQL is probably a much better choice. Also, MySQL has excellent caching capabilities, so most of the time, the recent data will come directly from RAM, and will be served faster than you could scan a text file in PHP either way.

Sajid
  • 4,381
  • 20
  • 14
  • Ok, I'll definitely go with a database. Regarding multiple users talking at once, since I'm using php wouldn't the second message be placed in a queue to be executed after the first is completed? – apscience Jul 28 '11 at 02:41
  • 1
    PHP won't magically create transactions around simple file writes. There's always the chance of getting half a record on disk and another worker add's it's own record before your second half gets written. There are some not-so-complex ways around it, but they're way less efficient than a real DB. – Javier Jul 28 '11 at 02:44
  • One last question - the more entries I have in a table, the slower a select will be, right? So shouldn't I create a table for each room? – apscience Jul 28 '11 at 02:49
  • @gladoscc: if you have proper indexing on your tables, then there will be a very minimal difference between filtering from 100 or 100,000 rows of data (assuming the actual dataset you wish to return is the same size in each). A column named room_id that is the first column in your index is likely the way to go. – Jordan Jul 28 '11 at 02:54
  • Not necessarily. What you should do is add an index for the room column (eg http://dev.mysql.com/doc/refman/5.0/en/create-index.html). With an index, the DB can get to the entries for the room significantly faster. Indexes really start mattering when there are tens of thousands of rows, but having it won't hurt anything, so I suggest having indexes on commonly used lookup columns anyway. – Sajid Jul 28 '11 at 02:55
3

Seems you don't have to keep your chat history at all, so why are you considering to use a permanent data storing(database or flat-file) method? You can do this using in memory caching software like Memcashed which faster than database or flat-file.

Dasun
  • 3,244
  • 1
  • 29
  • 40
  • 2
    The user might not be always online. If X send a message to Y and Y is not online, the message needs to be stored until Y comes online which might be a few days. – apscience Jul 28 '11 at 07:36
  • Then yes, you can't use memcached, database is the best choice. If you still more concern about the speed, yet you can use [Membase](http://www.couchbase.com/products-and-services/membase-server) – Dasun Jul 28 '11 at 08:30
1

Ill throw my hat in the ring here even though this is an old question. For speed, reliability, and ease of use a DB is the obvious easy choice... With one major caveat that a lot of people are overlooking, and that is most shared hosts (the most common form of web hosting) only allow 15 or so connections at once, even VPS's usually only allow 100-200, dedicated being 500 or more. What that means is if you have (n) users pooling every (s) seconds those connections are going to get eaten up fast, even faster if you are also running any sort of CMS. Being in the middle of developing my own chat room code on a VPS I am also facing these issues myself.

So far my method has been this.

  • Make sure to pass a lastMessageReceived variable to limit the response.
  • If a public chatroom pass a timestamp filter as well as the above
  • if at all possible use a DB caching engine such as MySQLnd with query caching enabled and TTL set to whatever your pooling rate is.
  • Don't get crazy with your pooling rate 1-2 second intervals may seem neat and snappy, but it'll kill your connection count. Dropping that to 5s or even more wont really make a huge difference and users probably wont notice, and your server load will be much lighter. Even consider a variable pooling rate that raises itself during high load.
  • Write your ajax to use timeout instead of interval for its pooling and place the timeout call in the ajax success callback, this prevents the requests from stacking up during peak ussage.
  • And the biggest one, if using a shared chat room with many users, write your own code to cache the SQL query into a json file and serve that to ajax requests, and write some custom TTL code to check its age and re-populate it as needed during requests, CRON would be great here if your host allows. Age checking a file and redirecting an AJAX request to it is a higher level function with very little server overhead especially compared to querying a DB. And don't parse the file in PHP looking to filter out old messages, store the file with the first message in the filename such as chat_243.json and save it as already formatted json, then just serve up the entire file if a request comes into the php with lastMessageReceived = 243. Since this will create several files you will need a function to clean up files older than (m) minutes, but this is also light duty work for the server.

There are also options like DB engines designed for chat and sockets (node.js), but those require more server tweaking than typical hosting accounts allow, for my purposes I have been writing my chat-room keeping in mind the idea that it may get deployed to a shared server at some point.

Wobbles
  • 3,033
  • 1
  • 25
  • 51
  • Hi - Just also looking into creating my own chat inside an existing web application. May I ask - what did you chose for sockets? Or did you use sockets at all? Thnx. – TV-C-1-5 Apr 13 '22 at 19:59
1

In general, a database would be far faster, since an index would let you go directly to the records the server has to send.

OTOH, with just 40 or so messages, it's quite likely that would fit in RAM, and with so few records, even the simplest linear-search routine would be far faster than a single HD access.

Still, using a database is so much easier, I'd use it for simplicity and not for speed. Also, if you have a lot of simultaneous rooms, writing it all yourself means a lot of opportunities for small, trivial bugs that delay development unnecessarily.

Just go with a database.

Javier
  • 60,510
  • 8
  • 78
  • 126