Cron jobs?

Status
Not open for further replies.
illusion

illusion

New member
Jun 24, 2006
3,475
35
0
Here is my dilemna, I am coding a site that is a membership site. Now with this site it requires a cron job to be ran at 12:00 PM to clean up the database and a few other random things. Basically running a query on each user to clean some stuff up.

The membership could exceed over 10k and that would require some serious power. Now I don't run any big sites, so I want to know how much power would this require for a server? Also what else would it do, could it screw up my bandwidth etc. ?

I never cron such large databases, so I need to know
 


thats hard to answer without seeing exactly what querry needs to run on every user.

Cron is just a method of running something at a given time... so when you say you never cron such large databases... I am a bit confused.
 
Yeah good point, it is kind of hard to explain without sounding like a complete cockhead.

The query would just be something like,
Code: 
UPDATE `table` SET foo = 'bar' AND foo = 'bar' WHERE id = '0'
Then I am just gonna go through all the id's in the database and update them all.

Yeah I know that is what a cron is, but my head was just thinking about querying the database as cron.

Hope you can help me out more
 
ya that should be super simple. I dont think it will take up any bandwidth since its all done serverside. Also 10k querries is not that bad.

I probably would not do it in php thought probably perl would be more ideal (plus you can multi-thread it)
 
  • Like
Reactions: illusion
if you're worried about server load, you can always break up the query into separate cron jobs
each cron job can handle a certain range of user IDs
 
Illusion, also be sure to check the last 30 days in your stats. Midnight always sounds like the best time but that's not always true. Look for a time where traffic is the least throughout the day, that should help a bit.
 
also as far as doing database updates, generally traffic going from

127.0.0.1 to 127.0.0.1 or localhost (all the same) doesn't count on your bandwidth quota.

So doing a datbase update in the 10k queries wouldn't be bad...
 
If you do use php use mysqli and prepared statements. Best yet if you use mysql 5 use stored procedures.
 
Is this going to be on a dedicated server?

Be sure to test MyISAM versus InnoDB table types. I'm going to guess that MyISAM will be faster for the updates. If you have to use InnoDB, be sure to turn autocommit off, run all your queries, then commit and turn autocommit back on.

I wouldn't worry about it unless it really does end up being a problem. I'd guess that it won't even cause a stutter.
 
stanley said:
hmm.. could you give a little bit more info on those two?
Click to expand...

Sure, mysqli is the "new" mysql connector for php. One great advantage is using prepared statements. If you're going to run the same query over and over again only changing the values a prepared statement is the way to go.

A prepared statement is a way for you to send the query without values to the server which then parses that. After that you just send values, since the server has already prepared the sql statement it can skip that step and lose some overhead. (The conventional way is to send the full query to the server each time, have the query process the query, then execute it).

Stored procedures are blocks of sql code that are always stored in the database. The difference in speed is akin to the difference between running a native application and a script. (a lot)

They usually look like functions with inputs and often outputs as well.

I've only really worked with stored procedures in informix, but noticed they have them in mysql now as well, a search for stored procedures, +mysql turns up the documentation and that can probably give you a better idea of where/when you could use them and ideas for what you could use them for better than myself.
 
  • Like
Reactions: Stanley
Status
Not open for further replies.
Top Bottom
Back