I have a website which provide a newsletter for 7 feeds. Every user has their own newsletter filters, so I have to run 7 queries for every user.
Also when a particular feed is loaded, every item from the feed is added to user_history table. Just to know that this item has been sent to this user, and do not send it again.
That's another 7 queries.
They're 7 more, because I do an INSERT-SELECT query. I don't iterate through every item and make a query for it.
After that I generate an email message, which is also stored to the database and sent to the user.
So that's at least 15 queries per user. It takes ~25 min to notify all of my subscribers.
Currently I have 2500 users, but I have to register another 6000 soon. I'm worried about how this will work at all.
This is app is using Kohana PHP Framework.
I have an htop video of the server when run the newsletter task - https://youtu.be/9VnkXVS51j4
The strange thing here is that core 1 is 100% loaded, while core 2 is not.
Any suggestions here? Do I need more cores?
I'm thinking of some kind of mechanism that generate all the email messages and store them to the database. I wish this message generation to be done when the load is low, maybe at 2 o'clock in the morning. Also I don't want to put the server on fire, running the task that raise the load to 100%. I want it to be done gently.
After all the messages are generated through the night, I'll run the cron job which will send all the emails to my subscribers.
Can anyone help me with that?
