CODEWITHSUNDEEP
phpmysqlemailnotificationssocial-networking
Markus Steindl
Markus Steindl

Reputation: 93

Notification system (email, social media)

I'm currently writing a small internal social media platform for the company I'm working with, nothing big, approx. 40,000+ users. It is basically something like Facebook or Google+ but with way less features.

This platform has an email notification system (beside the Ajax notifications for online users) as well.

Now here's my issue - it's simply not scaling.

I have a mySQL table holding all the email notifications to be sent out. So as soon as somebody writes a story in a group the system automatically inserts a line into that table and an email_send function (cronjob) will send out those mails to the users (the users can choose between instant, daily or weekly notification mails)

So far, so good - if the groups have a low number of members.

But now image a group with 5k+ members - As soon as the user posts a story into that group it triggers 5,000 SQL inserts to the notification table.

How would you solve this? I thought of a background worker on the server what scans for new stories/comments/stuff and triggers the email_send function in the background. Would this scale better? Or is there just a standard way for this and I'm just thinking in the wrong direction?

Any point in the right direction would be pretty much appreciated, Thank you and merry xmas :)

// Markus.

Upvotes: 4

Views: 1400

Answers (2)

Markus Steindl
Markus Steindl

Reputation: 93

Okay, I came up with the following (possible) solution to my problem:

  • I already have a table "events":

rowID (event_id)| story_id | initiator | group_id

  • create a new table "not_notified":

rowID | event_id

As soon as somebody posts a new story/comment whatever the system will now insert ONE line into the table "not_notified".

Every couple of minutes a cron job will run (can be run from another dedicated server if speed gets an issue here) and SELECTs the "not_notified" table. It simply iterates through all recordsets and looks for each event_id, pulls the story, checks the notification settings for the user to be notified and sends the email notification to the user. Lastly the script DELETEs the line from "not_notified".

I probably even don't need the "not_notified" table - it could be done with a simple "new" field in the events table as well, but image millions of entries in that table...

Thoughts?

Upvotes: 0

Peter Kelly
Peter Kelly

Reputation: 14391

You should not slow down your normal web request/response flow by sending emails - simply dump the data in a queue and let a background job pick up that work - it sounds like you are doing this already with your cron right?

So is it the insert that is the cause of the slowness? Then just normalize your data and simply insert a groupID in your email queue table. That groupID is associated with a group that contains X users and their email addresses. So then you will be inserting 1 record instead of 5000. So very quickly perhaps something like this...

notificationTable
rowID | emailID | groupID | status

groupTable
groupID | groupName 

userTable
userID | userName | emailAddress | groupID

If you want to take it even further then you could introduce a messaging system so that you can fire off simple (and fast) messages and have one or more listeners (on one or more separate servers) waiting for messages on one or more channels.

Upvotes: 3

Related Questions