How would I organize data to send many emails to users?

This is more in search of advice/best practice. We have a site with many users ( > 200,000 ), and we need to send emails to all of them about events occurring in their areas. What would be the best way to stagger the adding of the jobs?

Things to note:

• We store everything in a MySQL database
• The emails go out on a queue-based system, with independent workers grabbing the tasks and sending them out.
• We collect username, join date that we can use for grouping

Sending the emails is not the problem, the problem is getting the jobs added. I am afraid of a performance hit if we suddenly try to add tha开发者_如何转开发t many jobs at once.

I hope you requirement is like sending news letters to news groups and subscribers.

Do you already have groups and is it possible to implement.

It will help to filter and avoid filtering the entire 200000 users.

send the emails based on groups will reduce a db load i hope !!.

in db active and inactive status for user can be there.

running the cron job is the solution . but the intervals is based on the load that job can impact your sever. so if db design and job intervals are good performance will be betetr

I assume your queue is a table in a database and you concern is that adding thousands of records to a table will thrash it because the index gets rebuilt each time?

If you add many entries within a single process (eg. a single http-request or a single cronjob script), you can start a transaction before inserting and commit when done. With all the inserts inside a transaction, the index will only be updated once.

If it's a more general problem, you might want to consider using a message queue instead of a database table.

Or am I completely off?

Set a cron job for every 5 minutes. Have it check if there are emails to send. If there are, and none are set as "in progress" yet, pick the first one and set it as being in progress. Select the first users with id < n and send it to them. Keep track of that last id, and repeat until you reach the end of the user list.