DB for storing chat messages, which one to choose?

Question

Database

Good afternoon, I'm doing a project on Yii2, it has already implemented a chat on the comet server , the chat is already fully working, users can only communicate in private conversations, i.e. only 2 users with each other. When a message is sent, it flies by Ajax to the php script, validates according to certain rules, and if everything is OK, it writes to the database table and returns success to the client side, after which the message enters the chat window.
Database structure:
tbl_dialog:
dialog_id - dialog id
dialog_one_user_id - first user
id dialog_two_user_id - second user id
dialog_time - dialog creation time
tbl_message
chat_messages_id - message id
chat_messages_text - text messages
chat_messages_fk_dialog_id - id Dialogue to which the message
chat_messages_fk_user_id - id sender
chat_messages_fk_to_user_id - id recipient
chat_messages_ip - the ip sender
chat_messages_isRead - whether the message recipient
read chat_messages_isVisible_one_user - visibility of a message for the first user
chat_messages_isVisible_two_user - visibility of a message for the first user (to be able to clean messages if you just delete it, then the message will disappear from the history of both users)
chat_messages_time - the time the message was sent
The whole system works with the MySQL database - InnoDB, messages are written to the database with each sending (INSERT), while the service is not yet running, there are few messages (only my test ones) everything works smartly, but when I start it and the number of messages exceeds several million, which will it be with my database then? Hard brakes will begin at select and insert?
It’s not too late to think whether it’s worth rewriting the storage of messages to another NoSQL database, I never worked with them, I read the docks and at first glance it’s nothing complicated ...
2 implementation options are spinning in my head:
1) transfer the tbl_message label to the MongoDB collection and the same write messages there each time it is sent (i.e. the user sent 1 message and we immediately wrote it to the database, I will make one entry and so on each message).How will Mongo behave when inserting a message when the collection has over 20 million records? Is this diagram correct?
1) Write each message to memory in the form of an array of messages (memcached or redis or something else - advise ) and let’s take the entire array of messages every minute or 5 minutes by cron, write through a transaction to MySQL, then clean up the message array for new records. So it turns out that there will not be several inserts per second, because. messages will be stored in memory. But here again, an ambush, can MySQL work normally with a huge table of 20 million records?
The message text itself is very short, something like "Hello! How are you?"

Answer 1

Any developed modern RDBMS is quite suitable for this task.
MySQL, PostgreSQL...
And as the load grows, you don't need to bother with the choice of a DBMS, but install an MQ server. It is much easier to cope with frantic loads.
As an option - Queue based on Tarantool, for example. I don't even know what you have to do there to shut up his performance. Provided that the server has enough RAM.
Of the most critically suspicious - full-text search.
However, I believe that full-text search using MySQL or PostgreSQL is quite enough.
If you really do a serious chat like Slack, then for full-text search I would generally keep a separate specialized database. For example SphinxSearch.
But, for starters, the capabilities of PostgreSQL or MySQL will be enough.
As for Mongo... If you don't need replication without consistency. But it's fast...
So if you don't need such replication, then you don't need Monga.
RDBMS will be significantly faster.
Now, if you plan to start your chat in a cluster, when one server is not enough for you, then yes, RDBMS is not the best choice here. Here I would recommend just Monga.
But again, you can't do a chat server cluster without MQ.
Conclusion:
Start with a regular RDBMS.
As the plugs begin - consider MQ
How the growth to the scale of the planet begins - consider Monga.
The whole system works with the MySQL database - InnoDB, messages are written to the database with each sending (INSERT), while the service is not yet running, there are few messages (only my test ones) everything works smartly, but when I start it and the number of messages exceeds several million, which will it be with my database then? Hard brakes will begin at select and insert?
Nobody bothers you to check it.
Generate a million random messages.
At competent use of indexes - absolutely no problems neither on millions nor on billions of records.

Answer 2

I took MySQL for a small chat, and took the structure from the article designing the chat architecture . Now I started to rebuild it on SphinxSearch.