Which is the optimal way of performing the same task?

bmaciag

I have a mysql database that will potentially have thousands of user records. Each user record will have a value that I want to increment once per hour or per half hour for every user.

There are two ways I can think of to achieve this result:

The first is to set up a cron job to increment the variable by integer x once per hour.

The other is to use php code to cache the time of the last time the value was incremented, and when the user logs into his account, it will find the amount of hours that have passed and increment only his variable in the database. Which means this routine will be run for every user separately.

My question is, which of these two methods would create the most strain on the server? Obviously the second would create a more distributed load, but is the mysql command in the first method resource heavy enough to warrant using this second method? Keep in mind that we are talking about upwards of 5,000-10,000 user records.

Sxooter

Pick the one that's like to work if you have a 1,000,000 users someday. I'd say the individual one is best, as it's likely you'll be needing that user data then anyway, so you'd be hitting his data in the db anyway. A cron job will empty out all your caches for a large data set and then the usually snappy little "select * from usertable where userid=123" get slow for a minute while the cache gets rebuilt with data.

Plus it's easier to track how much server you're going to need with lots of small incemental updates, as they're easier to graph and not prone to spiky behaviour.

dkruythoff

One extreme approach could be to keep a separate table with last_visit times, linked to a user_id
That way you'd be writing to that table, instead of the one with ALL the user data, which of course will be a tad larger.

Sxooter

The real question to me is does this need to really increment each hour, because of other functions going on, or does this only need to magically appear to have happened when they actually log in?

Sxooter

See what I'm getting at is that what we want to do is as little as possible.

If you're going to need this interval or whatever only when the user logs in to be updated, then just stick an update at the top of the session that does what you wants, then gets you the number you really need.

And I'm not sure what that is, because you've described what you want, but not how you're using it. So, any optimizations I could make would be limited by that.

I'd probably look at some kind of update with a return clause in pgsql. Not sure how to do it in mysql. You could use innodb tables and get appropriate locks that still perform well (myisam = table locks bad for highly concurrent updates). Then begin;update;select;commit; the data up to date and out of the db. In pgsql, a short example could be:

smarlowe=# create table time_table (id int, first_time timestamp, last_time timestamp, check (first_time < last_time));
CREATE TABLE
smarlowe=# insert into time_table values(1,'2007-01-01 12:00:00','2008-01-30 12:00');
INSERT 0 1
smarlowe=# insert into time_table values(2,'2007-01-05 12:00:00','2008-02-05 16:00');
INSERT 0 1
smarlowe=# select * from time_table ;
 id |     first_time      |      last_time      

----+---------------------+---------------------
  1 | 2007-01-01 12:00:00 | 2008-01-30 12:00:00
  2 | 2007-01-05 12:00:00 | 2008-02-05 16:00:00
(2 rows)

smarlowe=# update time_table set last_time=now() where id=1 returning now() - first_time as how_long;
        how_long         

-------------------------
 403 days 05:53:48.09591
(1 row)

UPDATE 1
smarlowe=# select * from time_table ;
 id |     first_time      |         last_time         

----+---------------------+---------------------------
  2 | 2007-01-05 12:00:00 | 2008-02-05 16:00:00
  1 | 2007-01-01 12:00:00 | 2008-02-08 17:53:48.09591
(2 rows)

See, the time is updated, and we get an interval back. If you need that in hours, that's pretty simple to do in most dbs.

Sxooter

In pgsql you covert to an "epoch" value for some of these kinds of things. You get timezone handling on the data when it's stored, so you get right answers across time zone changes.

Example:

Now we want to log in id number 2... We need to update and return the new number of hours.

smarlowe=# select * from time_table where id=2;
 id |     first_time      |         last_time         

----+---------------------+---------------------------
  2 | 2007-01-05 12:00:00 | 2008-02-05 16:00:00
(2 rows)

According to this:

smarlowe=# select id,extract(epoch from last_time-first_time)/3600 from time_table where id=2;
 id | ?column? 
----+----------
  2 |     9508
(1 row)

It's 9500 hours. we update:

smarlowe=# update time_table set last_time=now() where id=2 returning extract(epoch from now()-first_time)/3600 as how_long;
     how_long     

------------------
 9582.12815829694
(1 row)
smarlowe=# select id, first_time, last_time, extract(epoch from last_time-first_time)/3600 as total_hours from time_table where id=2;
 id |     first_time      |         last_time          |   total_hours    

----+---------------------+----------------------------+------------------
  2 | 2007-01-05 12:00:00 | 2008-02-08 18:07:41.369869 | 9582.12815829694
(1 row)

And it's updated and we get a return of the time.
[/code]

bmaciag

Sxooter wrote:
The real question to me is does this need to really increment each hour, because of other functions going on, or does this only need to magically appear to have happened when they actually log in?

It only needs to have appeared this way, which is why I offered my second resolution as a possible way of achieving the result. It doesn't have to be every hour on the hour.

It's a turn based game, in which the user will use their turns to complete actions. Every hour they will be given 10 more turns. I've already made up my mind that there will be a script that is run for each user to take care of this, as a cron job will be a bit resource heavy. I prefer a distributed load.

I'll take a look at your other 2 posts when I have some more time. (currently at work)

Thanks!

Sxooter

Cool. The basic philosophy behind what I was posting was to do the date math in the database where possible, in as few steps as possible. Letting the db do your math and comparisons for you is almost always a win.

Sxooter

bmaciag wrote:
It only needs to have appeared this way, which is why I offered my second resolution as a possible way of achieving the result. It doesn't have to be every hour on the hour.

It's a turn based game, in which the user will use their turns to complete actions. Every hour they will be given 10 more turns. I've already made up my mind that there will be a script that is run for each user to take care of this, as a cron job will be a bit resource heavy. I prefer a distributed load.

Yeah, this is the exact kind of thing that doing in the db gets you the reward of constant updates for the cost of a single commit. I.e. every page the user brings up can update it's turn counter to see if 6 minutes have passed and bing pop in another turn to his queue.

If he uses his turns at almost exactly one every 6 minutes he could play indefinitely without wait.

Sxooter

OK, for what you're doing I think you need maybe three data points, if you want to track total time you need: Start time, last update, time since last update. You can arrange all of these as timestamps or a timestamp and either one more timestamp and an interval, or two intervals. But I'm not sure you need to track total time. You could just track then and now and turns.

If then and now fall in the same 6 minute pie of an hour, no increase in turns. If they do not fall in the same 6 minute interval, then compute how many turns, up to max turns to set their turn counter for. Do it in a single statement OR in a transaction and it should be race proof.

bmaciag

Sxooter wrote:
Cool. The basic philosophy behind what I was posting was to do the date math in the database where possible, in as few steps as possible. Letting the db do your math and comparisons for you is almost always a win.

Ah yes I understand. So instead of hard coding this into my php modules, I should look into creating some functions within the DB to accomplish this? I suppose that makes sense, and does appear to be a much more streamlined way to achieve this result.

Sxooter

Well, the functions are already in the db. MySQL has date math capabilities and can do it all for you. They're not exactly the same as pgsql date math. And I'm not sure MySQL has the equivalent, exactly, of a returning clause. But you can just select it right after and operate off of that. Without a lock there is the chance of a race condition.

But with the constant math, you could even show a count down in minutes:seconds on when you're getting a next turn.

Sxooter

Then build a little javascript thingie that counts down for them, so they don't have to refresh. OK, enough thinking, I'm going to bed... 🙂

bmaciag

Sxooter wrote:
OK, for what you're doing I think you need maybe three data points, if you want to track total time you need: Start time, last update, time since last update. You can arrange all of these as timestamps or a timestamp and either one more timestamp and an interval, or two intervals. But I'm not sure you need to track total time. You could just track then and now and turns.

If then and now fall in the same 6 minute pie of an hour, no increase in turns. If they do not fall in the same 6 minute interval, then compute how many turns, up to max turns to set their turn counter for. Do it in a single statement OR in a transaction and it should be race proof.

I think I'll go with this method. It seems like it will be low resource, and allow players with more usability.

bmaciag

Sxooter wrote:
Then build a little javascript thingie that counts down for them, so they don't have to refresh. OK, enough thinking, I'm going to bed... 🙂

Thank you and good night Sxooter!

Sxooter

I've been thinking, and that can lead to nothing good...

You want to optimize to the fewest updates possible to get the job done. Reads are infinitely cheaper than updates. So, the update needs to have some kind of and thing that actually changes the time only when it actually has to.

What I mean is that we truncate the date down to 6 second intervals, and we do mod math to see if we've moved to the next one then to update and return etc, otherwise do nothing, because it doesn't matter. Does that make sense? From a programmatic point of view you'd want something like:

id=6
lock the row for id
update time_table set last_time=now() where id=4 and (mod math on 6 sec intvls goes here) returning <that extract epoch thing from above>

-- that last bit to keep it from actually updating if it doesn't have to. No rows match both id and the mod6 test and nothing happens.

Now, most databases don't have modulo math for dates and timestamps, so we pretty much are stuck with doing the date math in unix epoch integer format, seconds since jan 01 1972 or something like that.

So, we build a new table with a credit column. Note that I've chosen some arbitrary limit on number of turn credits. It's easy enough to just drop if you don't need it. And on MySQL you can ignore them as it doesn't implement check constraints.

smarlowe=# create table time_table (id int, first_time timestamp, last_time timestamp, credit int, primary key (id));NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "time_table_pkey" for table "time_table"
CREATE TABLE
smarlowe=# alter table time_table add constraint max_credit check (credit <= 10);
ALTER TABLE
smarlowe=# alter table time_table add constraint min_credit check (credit >= 0);
ALTER TABLE
smarlowe=# alter table time_table add constraint no_time_warps check (first_time <= last_time);
ALTER TABLE

A test to see if the constraints are working:

smarlowe=# insert into time_table values (1,'2008-01-01 00:00:00','2008-02-07 13:43:34',22);
ERROR:  new row for relation "time_table" violates check constraint "max_credit"
smarlowe=# insert into time_table values (1,'2008-01-01 00:00:00','2007-02-07 13:43:34',2);
ERROR:  new row for relation "time_table" violates check constraint "no_time_warps"

Now a good row of data.

smarlowe=# insert into time_table values (1,'2008-01-01 00:00:00','2008-02-07 13:43:34',2);
INSERT 0 1

Test to make sure the pk failure happens:

smarlowe=# insert into time_table values (1,'2008-01-01 00:00:00','2008-02-04 18:21:16',2);
ERROR:  duplicate key violates unique constraint "time_table_pkey"
smarlowe=# insert into time_table values (2,'2008-01-01 00:00:00','2008-02-04 18:21:16',7);
INSERT 0 1
smarlowe=# select * from time_table ;
 id |     first_time      |      last_time      | credit 
----+---------------------+---------------------+--------
  1 | 2008-01-01 00:00:00 | 2008-02-07 13:43:34 |      2
  2 | 2008-01-01 00:00:00 | 2008-02-04 18:21:16 |      7
(2 rows)

And the data in the database is ready to work on. So, I'll assume that if it's been more than x amount of time, you give them credits for each 6 minute period up to the max you're allowed to have.

Well, I've got this all worked out on my database but am losing cohesion of thoughts. Will finish it up tomorrow.