通知系统（电子邮件，社交媒体）

Question

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.

Answer 1

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.