Cron job help... i think ?

[TEENFRONT]

Hi there

 

I own 8Baller.co.uk with over 114,000 members .... Im having a bit of trouble.

 

Im wanting to lighten the load on the mysql database and iv been thinking of a few ways to do so.

 

Now on the homepage www.8baller.co.uk - i calculate the highest ranking members by games_won column. and thats done live on each page load..which im guessing isnt helping the database at busy times.

 

How do i go about cron jobs, and only updating the ranking every day or so? so when it comes to each page load, it only loads plain html, without pulling and sorting through the whole member database.

 

i have looked around but i just dont seem to get whats going on with cron jobs, and where it keeps the info.. im confused.

 

Please help!

[monk.e.boy]

I guess you want to keep using PHP as your coding language.

 

So you need to create a PHP page that does what you want.

Test it. Make sure it does everything a-ok.

Now put at the top:

 

#!/usr/bin/php
<?php

?>

 

This will allow you to run it as a script from the command line.

 

If you need to find where php is on your system do: whereis php

 

chmod the script so it executable for both apache and root.

Try running the script: ./myscript.php

 

when this works you need to put it in your cron file: crontab -e

 

Look up crontab in google. it's easy to figure out.

 

I'd run this script once every 15 minutes.

 

HINT: use: ./myscript.php > log_file.log

 

to log all your echo's, then use: file_get_contents(log_file.log) in your websites admin page to see the cron job wokring. Try echoing the current date and time, so you can see if it fires every 10 minutes or whatever.

 

;D

 

monk.e.boy

[cuteNephilim]

Or, if you can't use Cron jobs for some reason, you can just check time() on each pageload against the time of the last calculation and take it from there. In this case you will have to save the last calculation time in some way (lika a text file or your database (or the document itself - maybe it's even possible to just check the last time the document was accessed?)), but it will probably lighten the load somewhat...

[Orio]

Or, if you can't use Cron jobs for some reason, you can just check time() on each pageload against the time of the last calculation and take it from there. In this case you will have to save the last calculation time in some way (lika a text file or your database (or the document itself - maybe it's even possible to just check the last time the document was accessed?)), but it will probably lighten the load somewhat...

 

But thats what he is trying to minimize- the queries to the database. Using cron jobs will be the best in that case (and I suppose he wouldn't suggest them if he can't use them).

Also, by using a cron job he can minimize the page load time- instead of checking a timestamp in some sort of way on every page load.

 

Orio.

[TEENFRONT]

Hey guys

 

Thanks for your replys, heres what iv done.

 

Created player-rankings-cron.php which does the mysql and writes the top 15 players to player-rankings.txt every midnight ( iv set up the cron via cpanel ).

 

Then i include player-rankings.txt on the homepage. Seems to be working just great.

 

Iv also done exacly the same with the link exchange top links, which was calculated on every page load and displayed.

 

Will this really help the load on database by much?

[cuteNephilim]

Or, if you can't use Cron jobs for some reason, you can just check time() on each pageload against the time of the last calculation and take it from there. In this case you will have to save the last calculation time in some way (lika a text file or your database (or the document itself - maybe it's even possible to just check the last time the document was accessed?)), but it will probably lighten the load somewhat...

 

But thats what he is trying to minimize- the queries to the database. Using cron jobs will be the best in that case (and I suppose he wouldn't suggest them if he can't use them).

Also, by using a cron job he can minimize the page load time- instead of checking a timestamp in some sort of way on every page load.

 

Orio.

 

Yes, if he has the possibility to use Cron jobs this is obviously preffered. With a question titled "Cron job help... i think ?" I didn't think it was totally clear if he actually could use Cron jobs. I just gave an alternative method of taking some load of the request, and I think I made that clear with the "Or, if you can't use Cron jobs for some reason" part... The web hosting company I use don't offer Cron jobs, so I had to solve it in this less efficient way...

[TEENFRONT]

Hey

 

Will this really help the load on database by much? or are we talking tiny bits? Iv now also done this with my players online stats ( runs every 10 mins )

 

I suppose this is much better than querying the database on every page load isnt it?

[Orio]

The pages will load faster that's for sure

 

Orio.

[monk.e.boy]

Who knows. Database optimization is a bit of an art.

 

Putting some cunning indexes and splitting up your data may help a lot. For example, say you look up users by their email address. Indexing the email address column will speed the DB lookups like, 1,000%

 

Also the index must fit into memory, so if the user table has tons of other stuff you rarely use (like, address, age, eye colour) split the users table into two. Users and user_extra_bits. When you show all user data do a join between these two tables. For normal use, like saying hello to the user on each page, the small table holds enough data, and cos it's small it fits into memory == fast.

 

Put the database on a separate server with 17gigs of memory == fast.

 

monk.e.boy

[redarrow]

is that pool game in php you millonare lol?

[monk.e.boy]

is that pool game in php you millonare lol?

« Last Edit: Today at 11:32:33 AM by redarrow »

 

Man.

 

And he edited it to make it more readable?!? WTF!!

 

monk.e.boy

[TEENFRONT]

pool game is PHP/Flash & MySQL.

 

Just checked out my server load and is dropped by so much, its fantastic! Why on earth did i not know about cron jobs before!! lol!

 

Now i run the players online script once every 10 mins

i run the player ranking once at midnight

and once at midnight with top links.

 

Its saved soooooo much server load! its unbelievable!

 

The table is set up quite well, for example, the database only hold 30,000 users per table then creates a new table when the old one is full up. Im using UNION to pull results from all the user tables, whats with JOIN, is it better than UNION?

[monk.e.boy]

Its saved soooooo much server load! its unbelievable!

 

Cool, that's interesting.

 

The table is set up quite well, for example, the database only hold 30,000 users per table then creates a new table when the old one is full up. Im using UNION to pull results from all the user tables, whats with JOIN, is it better than UNION?

 

Union adds rows to the bottom of the result set.

 

Join adds columns to the result set.

 

Sounds like you have a good system anyway.  :D

 

monk.e.boy