Database for chat [closed]

Question

I'm trying to create a chat for a site and I'm wondering if the db solution is the best one in case of a large amount of users.

In case the db is an acceptable solution I would like to know which is the best way to design it:

1. Is one table enough to store all the messages from all the users? Do I have to store each message everytime a user send it (like a simple "hello" in one record)?

2. Do I have to create a separate tables for each chat?

Obviously assuming that the indexing and the partitioning are made.

I'm scared about the performance at this stage (db level). Then I can concentrate better to the middleware section and manage it there.

Answer 1

MySQL is not ment for realtime

MySQL is clearly not the best solution for a chat: You have no reason to work on years of backlog to get the most current messages, nor do you want to fire a query per second for every active user. The overhead of a relational database is not to be ignored.

Check out redis

I would go with redis: It is an advanced key-value storage with capabilities of realtime interaction (PUB/SUB), automatic expire, and it is definitely faster than MySQL when it comes to loads of simple data.

Edit: Yes, it has all it´s data in ram. A Gb of ram would be enough for about every book in every libary of the world. That said, MySql also uses ram caching (query caching). And redis is ACID. (Oversimplified: You can enable saving to disk.)

MySql hints, if you stick to it

If you yet decide to go with MySQL, you will have to write every single line into the DB for others to be visible. More explicit, you need a commit on every message. Make sure that you have some sort of cleanup mechanism, e.g. a cronjob moving all messages older than a day into some archive-tables.

cache!

Imagine 100 users in your room, each checking every 3 seconds for new messages. 300 queries per second? (Ok, decent servers can handle this, but you asked for a good solution) Go another way: Have a memcached /redis-saved flag "Last message id". Change it every time someone writes something to the chat. Now make the client submit it´s last-known message id. If you´ve got a hit, exit immediately even without MySql starting up. If you are really good, you can make PHP even make to return the appropriate ETag.

Long polls

As it comes to the frontend client: Do not fire a reload or ajax request every n seconds! Inform yourself about Websocket and long polls. That is a technique where the browser opens a site that will not immediately return a result, but will keep the connection open untill there is something to report (or timeout occurs)

Edit: OP´s comment asking what programming language to use

That depends on your knowledge. I would go with PHP and redis, but that is because I know them well. If you prefer Java, use it. If you have no preference: Java is more versatile, php is easier to start learning. There is no objective one-size-fits-all-answer.