Chat Database

Question

I am trying to create a chat for the site, and I am wondering if the db solution is the best in case of a large number of users.

In case db is an acceptable solution, I would like to know which one is best to develop it:

• Is one table enough to store all messages from all users? Do I have to store every message every time a user sends it (for example, just “hello” in one record)?

• Do I need to create separate tables for each chat?

Obviously, it is assumed that indexing and splitting are performed.

I am afraid of performance at this stage (db level). Then I can better focus on and manage the middleware section.

Answer 1

MySQL is not designed for real time

MySQL is clearly not the best chat solution: you have no reason to work on a long-term lag to receive the latest messages, and you do not want to run a query per second for each active user. The overhead of a relational database should not be ignored.

Check redis

I would go with redis : this is an extended key store with real-time interoperability (PUB / SUB), automatic expire, and it is definitely faster than MySQL when it comes to a lot of simple data.

Edit: Yes, it has all the data in ram. For every book in every library in the world, a GB ram would be enough. However, MySql also uses ram caching (query caching). And redis is an ACID. (Oversimplified: you can enable save to disk.)

MySql hints if you stick to it

If you still decide to go with MySQL, you will have to write every single row in the database so that others are visible. More explicit, you need to commit every message. Make sure you have some kind of cleaning mechanism, for example. cronjob moves all messages older than a day to some archive tables.

cache!

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

Long polls

As for the external client: Do not start a reboot or ajax request every n seconds! Let us know about Websocket and long polls . This is a method in which the browser opens a site that will not immediately return a result, but will keep the connection open until a message appears (or a timeout occurs).

Edit: OP comment on which programming language to use

It 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 universal, php is easier to start learning. There is no objective, uniform answer to all questions.

Answer 2

use NOSQL because it stores data as a file, which is good for a large database

Answer 3

Determine which features you want. I can immediately think what might matter:

• Do you want to save the stream at all? (If not, then your database needs are minimal.)
• What part of the stream do you want for users? One day? A week? All this? (You can periodically unload messages to maintain a story for yourself, but also maintain a small public database.)
• How many messages per minute / hour / day can a user send? (Hint: not as much as they want)
• Do you need streaming or just one long stream that everyone can see? (Increases difficulty).
• Do you want users to be able to send private messages? (Increases difficulty).
• How often is the chat updated in the browser? (The more it is updated, the greater the load on the server).