Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : PHP Cron, MySQL and Mail.

[josephgarbett]

Hi everyone,

I am in need of some advice. This is not a script help request, just a MySQL sensibility question. I'm making a new website which is almost ready to launch, and part of its functionality is to send users a monthly account report.

I have come up with the idea of splitting the data processing over 2 days where on day 1, all the data is sorted into a MySQL cache table, then the next the cache is loaded and send in an email to the user.

And because the cron runs every 15 mins, this gives me 96 opportunities throughout the day before it is sent, to compile all the cache.

So, without any actual numbers as I am still making it, how many rows should I insert for the cache every 15 mins?

I was thinking around 500 every 15 mins which gives a total of 40,000 for 24 hours (And this will be tweakable at any time). I have seen on other websites queries taking 9/10 mins to perform. I can allow room for that, but I would rather not as I want to keep my nice, fully functioning website running at 100% without hitch while my cron jobs run.

I look forward to reading your sensible ideas.

[nafirici]

I had a similar issue at my job and an approach like this worked for me for a while, but then it started causing me issues. Assuming that your database is growing, right away it could work, but when you start getting hundreds of millions of records, it'll take longer to query your data to move it into a cache table and the inserts will start to put too much load on your db.

An approach that I took, was to put triggers on a couple of tables that would trigger a change in my reports. I then call a stored proc that modifies a denormalized reporting table that allows for fast reads. This isn't the best approach and does have it's limits, but by spreading the work throughout every request, i've had less problems.

[swiftyCMS]

Two ways to approach this; cron or no cron

If you already have 40,000 users, you can take the option not to use cron;
make a custom table for 1st, 2nd, 3rd, etc 500 users emailed.
whenever a user visits your site run the script for the next 500. mysql is very fast, email loading on your server is another issue.

Or use cron and run a php driven bash script that sends the emails. bash is not restricted by php runtime. (still using a mysql table for 1st, 2nd, 3rd, etc 500 users)

my choice; cron with bash script. bash can be executed from cron and it's quick.

[josephgarbett]

I need the cron, theres no two ways about it. I'm having to write some real funky MySQL to do Loads of maths for me as its pulling data from about 9 different tables.

This includes basic user information, user settings, calculating Totals, averages, means and ranges for EACH user.

Every month, this data must be stored as this is the core functionality of the site. Weather the user wants the information via email is a different matter like you said, but the user still needs to be able to have access to this information. Think of it like an online archive of an account which cannot be removed by the user.

I know MySQL is fast, but its the mail bit thats the problem. Would 500 emails every 15 mins sound okay or would it clog up my server? Because I may end up having 40,000 users; but each have the ability to turn this email feature on and off.

[swiftyCMS]

Ok, understand better now.
As you correctly stated mail is going to be the memory problem(especially if you have spamassasin or similar running), it gobbles up resources.
I don't know the specs(ram) of your server, but I would suggest starting at about 200 every 15min and keep an eye on your server.
200 should definately not be a problem - then just slowly increase it until you get to 500 if possible.
Also bear in mind that by the time you have 40,000 users you would propably(if not already) be on a dedicated server with much better specs.

[josephgarbett]

Yes, I do have spam assasin installed. To be honest, I've given it some thought over night and will probably once I've reached 1000 users, think of moving onto a Dedicated server. And will then setup something like a Blade-cluster to send emails

The emails as I've mentioned is my main concern, because while I'm still developing and optimising it on a shared server , there will be active users and I don't want to clog up the server i'll be temporarily using.

[ionisis]

IMO, if you're website is slows down when you compile 40,000 reports, then you may need to consider THAT issue first .

If your database is properly normalised, and your developer is VERY efficient, then this should all be able to do it all at 1 time (well, the emails may need to be split up over a couple of hours, but the compilation should be able to be done in 1 go), without much notice on the server's load.