Level 73
Have you run explain on the query? Sounds to me that the tables are missing some indexes.
I would not do a precalculation since it will be updated too often as you say.
Aug 25, 2019
5
Level 3
Scaling Laravel
Hello, I am facing a problem in laravel. I am working on a product which is in combination with laravel and node js. The problem that is I need to show some dashboard stats like total messages/total messages by day and average response time by day etc. What I use to do is select the rows count of messages on tables but it was to slow for users that had a large number of messages incoming/outgoing as a result it also affected other users in load time so we decided on precalculating this. since the messages are sent from node js server an internal webhook URL is called on laravel where we incremented the row with the count of messages and calculated other stats. The node js server was hitting the webhook URL quite fast so it caused in the wrong increment in tables. For that, we implemented lock for update with 3 retries on DB transaction but it still created deadlocks and caused the database to slow down more. How do you handle stats like this which are updated quite frequently?.
Level 3
No, we did have indexes on the tables
Level 67
I'd look into using redis for a cache store. It will be a lot faster than having to reach into the db for the updating/retrieving.
Level 3
Caching is not an option right now. We want to show the stats realtime on every page load
Level 67
It's a realtime in-memory datastore. There's no reason why you couldn't use it to show realtime stats. You can use it for realtime chat if you want combined with pusher or something. The only difference is you're not having to reach into the database and access the disk, which is a costly operation. I'd look into it more before dismissing it.
Please or to participate in this conversation.