Hello Guys, im writing this post because I need a help.
I created chat application for my client and in first 5 month we have about 7M dialogues and 30M messages and 50-70 users writing messages at the same time... and my application slow down... and it looks like a growing trend will be the same or faster in future.
In general it is simple app with instant chats in rooms 1:1. Message can be plain text, one of provided gifs or uploaded image. Users Can softdelete messages on their side and in addition chat shows which messages are seen (WS in background)
About archotecture: So for now its just one Laravel instance on one dedicated server with one MySQL DB on the same dedicated server (slave server is synced continuesly)
Here we have simple DB structure: messages >- dialogues >-users (divided into sender and receiver) messages >-users (divided into sender and receiver) [Im using indexes on most of fields]
How I can start some optimisations ? How should I change approach? change DB engine? split table in very old messages and currently used messages ? partitioning DB? DB cluster? archive very old messages to other DB or txt file ? some other cleaver way?
I have many ideas but I totally dont have expirience with handling tons of data.
Each hint/help will be very usefull!
Thank U K
My first thought is to use a Real-time database provider if you can pay for it and you don't have the time or experience to optimize the app, you can use something like Firestore.
For read/update/delete you should use indexed columns to make it faster.
If you get many requests at the same time I suggest you to use something like Octane but be carful with memory leaking.
PS: I didn't try that myself before.
@MohamedTammam than you for your fast answer.
About firestore. We thought about that kind solution but we want to try figure out something our own :) In this volume its a lot of money :)
About octane I read many years ago and I tried 2 times to use it last year but I had many different problems. But solution looks awsome.
And finally about mu problem ... I think that my bottle neck is DB and maybe way how I using it.... PHP utilizing 5% of CPU and 10% of RAM and request flying properly and fast.
Problem is because for example in stress hours especially messages are loaded in 2-3seconds (sometimes even 6-7 seconds)... this is in 90% time of retriving this request by DB
@bor1904 Try to use indexed columns, that might help if you aren't using it already.
If you are using timestamps one each message, you can copy everything old than x days to a history table, that way the main table will be smaller and thus faster,. Using the date as an index you can den call for historic messages according to date and not everything at once.
Another option is to partition your table, either per conversation, per user or per date,. This would also result in a smaller number of records to look through, and of course you need indexes for the values you are looking for, my guess would be the user ids involved, the chat id and the created_at.
Please or to participate in this conversation.