Cron Job Support

[currend]

Ok so my Webhosting company sent me an email today saying that im using LOTS of system resources because I have a CRONJOB that executes every minute.  I informed them that this was a critical part of my website and it had to be executed every minute.  They asked if possible if i could modify it in some way it would reduce system load.  I now give you the entire script and please tell me if there is some way to optimize it so it doesn't use all there resources.

 

This is the email they sent: Hello,

 

Can you at least look into optimizing this script so that it takes minimum time for execution and puts minimum stress on our database ?

 

<?php

include "config.php";

global $_CONFIG;

if($_GET['code'] != $_CONFIG['code']) { die(""); }

define("MONO_ON", 1);

require "class/class_db_{$_CONFIG['driver']}.php";

$db=new database;

$db->configure($_CONFIG['hostname'],

$_CONFIG['username'],

$_CONFIG['password'],

$_CONFIG['database'],

$_CONFIG['persistent']);

$db->connect();

$c=$db->connection_id;

$db->query("UPDATE users set hospital=hospital-1 WHERE hospital>0");

$db->query("UPDATE `users` SET jail=jail-1 WHERE `jail` > 0");

$hc=$db->num_rows($db->query("SELECT * FROM users WHERE hospital > 0"));

$jc=$db->num_rows($db->query("SELECT * FROM users WHERE jail > 0"));

$db->query("UPDATE settings SET conf_value='$hc' WHERE conf_name='hospital_count'");

$db->query("UPDATE settings SET conf_value='$jc' WHERE conf_name='jail_count'");

 

?>

 

What this script does is basically this.  If a user is sent to jail for example 10 minutes.  This script being executed every minute reduces jail time over the 10 minute period.  If i was to delete this script then they simply would never get out of jail.  If i executed it every 5 minutes (webhosting min.) then it would really take them well 5 times longer to get out of jail.  They asked if it was possible to optimize this.  So I ask you if there is a way to either optimize it so it doesn't eat system resources OR possibly find an alternative to this script.

[DarkWater]

Don't update the count...just run a script every 10 minutes that checks a timestamp to see if it's been 10 minutes yet. =/

[currend]

Don't update the count...just run a script every 10 minutes that checks a timestamp to see if it's been 10 minutes yet. =/

 

I know absolutly nothing about scripting.  Could you please assist me in this process? It would greatly be appreciated.  I also need to point out.  A user can be jailed for 4 minutes 6 minutes.  I was just giving an example.  So how would this go about being done?

[corbin]

Instead of minutes, store a time stamp of when a user will be out of jail.

 

 

For example, it's 17:22 (GMT) right now, so if you wanted to jail someone for 4 minutes you could store the time 17:26.

 

(DATETIME column would work or a unixtimestamp.)

 

 

 

Also,

 

$db->query("UPDATE users set hospital=hospital-1 WHERE hospital>0");

$db->query("UPDATE `users` SET jail=jail-1 WHERE `jail` > 0");

 

Make sure you have indexes on hospital and jail or that query will take a long time.

[currend]

Instead of minutes, store a time stamp of when a user will be out of jail.

 

 

For example, it's 17:22 (GMT) right now, so if you wanted to jail someone for 4 minutes you could store the time 17:26.

 

(DATETIME column would work or a unixtimestamp.)

 

 

 

Also,

 

$db->query("UPDATE users set hospital=hospital-1 WHERE hospital>0");

$db->query("UPDATE `users` SET jail=jail-1 WHERE `jail` > 0");

 

Make sure you have indexes on hospital and jail or that query will take a long time.

 

Do you think you could add this to the whole script for me?  and maybe a SQL database input and where

[Mchl]

Instead of

$hc=$db->num_rows($db->query("SELECT * FROM users WHERE hospital > 0"));
$jc=$db->num_rows($db->query("SELECT * FROM users WHERE jail > 0"));
$db->query("UPDATE settings SET conf_value='$hc' WHERE conf_name='hospital_count'");
$db->query("UPDATE settings SET conf_value='$jc' WHERE conf_name='jail_count'");

 

You could do

 

$db->query("UPDATE settings SET conf_value=(SELECT COUNT(*) FROM users WHERE hospital> 0) WHERE conf_name='hospital_count'");
$db->query("UPDATE settings SET conf_value=(SELECT COUNT(*) FROM users WHERE jail > 0) WHERE conf_name='jail_count'");

 

To create indexes you need to do this

 

ALTER TABLE users ADD INDEX `Index_hospital`(`hospital`), ADD INDEX `Index_jail` (`jail`);

 

[edit]

 

Just check if you don't have those indexes already.

[currend]

And how often should i set the CRON to run?

[currend]

Also,  what will this tell the system to do?

[Mchl]

The first part will still update your hospital_count and jail_count, but will use less queries (which is usually a gain)

 

The second will create indexes on users table, that will speed up those queries. You need to run in once only.

[currend]

The first part will still update your hospital_count and jail_count, but will use less queries (which is usually a gain)

 

The second will create indexes on users table, that will speed up those queries. You need to run in once only.

 

Run once only what?  I mean if its only run once, then after its run how will the system know when a user should be removed from jail or hospital with no script actually updating?  You have to excuse me because I dont know anything about this...

[Mchl]

You need to run this query once only to set up those indexes. And to this only if you don't have them already (you can check in phpmyadmin)

 

ALTER TABLE users ADD INDEX `Index_hospital`(`hospital`), ADD INDEX `Index_jail` (`jail`);

[currend]

Ok that I understand.  Now my previous question.  This PHP script is actually a CRONJOB that was executed

* * * * * OR every minute.

 

Now, the webhosting MIN is 5 minutes.  So how would I set it? 

 

 

OR

 

Are you telling me that once this is added, I can delete the cron that currently runs OR would it simple reduce the lag on there system?

[Mchl]

My advice are only a way to slightly optimize your current script. It would still need to be run each minute.

 

If you go with corbin's advice, you will most likely be able to resign from the cron job at all. It will however need some more changes in your script.

[currend]

Well my webhoster would like it to be executed in 5+ minutes not every minute^^

 

Can you help me with corbins idea?  If i had to give u my whole database i would^^ you have no clue how much your support means to me

[Mchl]

Sorry but I will not go into it.

Post your request in freelancer area. Maybe someone will be willing to help you for free or for a reasonable fee.