Persistent updates [Solved]

[ninto]

So I'm currently in development of a game.
I'm not stuck - but in need to make a decision, and I can't really come to a logic solution.

How do I best update persistent progress without killing the database?

Here's A Jason example:
There's a live city, with people in it. You have a character in this city yourself, and you want to go from point A to point B.
The simple way to do this kind of a movement is simply calculating the range, between two spots and voila. You're at point B in X minutes.

But I want to do live updates during this "walk". So lets say he spots an enemy, he'll start attacking. He'll see the cops, he (in lack of better words) cheeses it going on an off-route. All of this time, the player can view the current location and action.
Now, the display part aside (which would indeed be fairly simple) - How would one make these persistent updates, even if the player isn't signed in playing?

My initial thoughts are to simply make a loop that updates the table, but my fear is that it would greatly load the CPU and HD. Have you ever done anything similar? Do you have any tips? I suppose I could do some collision detection within the database, and just continuously update the world position by velocity*time.

Note that My issue isn't about the coordinations, but only about the recurring updates that would have to be made.

By the by, this is just an example of the issue at hand. I'm not making yet-another-gangster-bb-game.

Yours, ninto

[saljutin]

before I can help you I need to know if those "acts" will affect this player only or others too:

so when I walk from A to B and encounter cops/enemy will this affect other players as well or only me?

[Chris]

How are you going present the "history" of what happend when the player was offline? Summary page?

Personally I consider this concept a poor design choice, it provides very low fun factor for player (things happening without any players intervention) and a considerable hardware stress.

[ninto]

saljutin: Okey, to answer that question in short, only the player. What the question basically boils down to if you've had workload issues with a bunch of persistent updates.

Chris: I agree. And that is not how I intend to use it. I am however currently toying around with a flash viewer. With which you can view a previous battle in a Heroes of Might and Magic type of scene. I don't, like you, believe in summary pages with butt loads of data pushed onto the player. I do however find it intriguing if a player could perfect his/hers strategies by reviewing visually - and that's the end goal. But sure, there's some obstacles to work out.

I'll try to rephrase my own example.
I have a city, this city is like SimCity, full of NPC running around with a daily routine. Now think SQL database for that. (I'm using MySQL in a replicated environment, not ideal but works for me so far). If I would to completely control each NPC in that city, I'd have to do a bunch of persistent updates to constantly move all those NPCs. Now, I did some testing and using only MySQL math, I managed to move >20,000 "people" or NPCs one point on a xy-plane each second without the CPU going crazy. And that was based on their current location, direction, and velocity. The RAM did however take some beating. It's rather weird though and I think I'll have to do some other tests because my servers might have been running some heavy-duty crap at the time.

Basically I was wondering if there's anyone here who's been fizzling around with similar stuff, maybe give me some pointers on how to avoid pitfalls and so forth.

[saljutin]

if those random encounters only "bother" player, then you can always check...like when user comes online you have something in user table like traveling=1, so if $user['traveling']==1 then he is traveling and you check when did he start traveling and where is he going, and when did he come online to see for updates. so lets say traveling takes 5h, so every hour you can check if something happened.
meaning when user comes online you only checks what time of this travel was he last online (like 0h), then u compare time now with max travel, so for example he is traveling for 3.5 hours, so you do 3 updates and set time to suit that 3h...dunno how to explain and I am in hurry and eng is not my primary

[JGadrow]

What I would do:

When the player begins the action, plot the ENTIRE course along with the timestamp that the user enters that particular coordinate.

When something occurs that is in that area (a cop walking around, for example) check the timestamps and update the player's position at that time. If he's there, execute whatever event needs to happen. If not, continue on.

This executes the code only when it needs to be executed and gives the system a way of limiting the possible number of spaces the player might be located within at any given moment. If nothing happens to any of the spaces in the travel path before the travel is completed, then the next time the player logs in or an event happens that updates player positions, you just "transport" the player to the destination.

[shoespeak]

Maybe you can divide the map into different sections (like neighborhoods.)
Have a table for each section that gets updated with events as they happen in the game (for example, when a cop enters this neighborhood, write that action to this table). It doesn't matter if a user is in this square or not, just update the table anyway.

Now, when somebody "walks through" the section, you can just print out (to the player's log, or whatever) the events from the neighborhood table that have similar timestamps to when the user actually passed through that coordinate. This information can all just be grabbed from the db when the user visits the page in a single query...Just grab from each neighborhood table a chunk of events that match the timestamp that the user was there

[Chris]

Now, I did some testing and using only MySQL math, I managed to move >20,000 "people" or NPCs one point on a xy-plane each second without the CPU going crazy. And that was based on their current location, direction, and velocity. The RAM did however take some beating. It's rather weird though and I think I'll have to do some other tests because my servers might have been running some heavy-duty crap at the time.

How many!? 20,000 SQL writes a second? Well, you know, I saw shared host providers (paid, not free) that allow 60,000 SQL queries per hour
This would be 28 million writes a day. And I don't think you could do it purely on SQL side (like one query to move them all at once) because of collision detection or other code that simply had to be done on PHP side. Not to mention that that's not all, you need also select from the table for each player to display the NPC placement.
To me these numbers are insane (assuming it is one server architecture).

[yuppio]

If I understood everything correctly, then http://www.ape-project.org/ is the way to go.

I'm trying to learn it currently, because I thing it has great potential, but waiting for the v1.0 version to come out with full documentation, because v0.9 has bad documentation on both server and client side development aspects.

[ninto]

If I understood everything correctly, then http://www.ape-project.org/ is the way to go.

Nah, that's not it. Also this is solved. I've been checking out that project for quite some time. But it's nothing I need currently. The only pushing i do is for my battle viewer and for that I'm using flash.

[ninto]

How many!? 20,000 SQL writes a second? Well, you know, I saw shared host providers (paid, not free) that allow 60,000 SQL queries per hour
This would be 28 million writes a day. And I don't think you could do it purely on SQL side (like one query to move them all at once) because of collision detection or other code that simply had to be done on PHP side. Not to mention that that's not all, you need also select from the table for each player to display the NPC placement.
To me these numbers are insane (assuming it is one server architecture).

First off my company already owns a bunch of dedicated servers so that wont be an issue. I don't use shared hosts.
Second, that was a test: "If I had 20k users doing -something-, how would it perform if I had to do live updates".
Third, the collision detection and the specifics never was part of the issue, it was purely a performance issue.
Fourth, collision detection can be done purely in SQL - though that was never the issue.

Never the less, this issue is solved.

[lolninja]

You could implement a forever script, in php a while(true) with a sleep or usleep, which moves the npc's around in real time, and if you implemented a messaging system you could output specific information to specific users, its a tricky implementation, but it could work.

Doing something like this will severely reduce the load on your database, as you only need to update an NPC's position once every couple of minutes, allowing you to dedicate more memory to the forever script. Within a real time environment your collision detection and path finding should be more responsive, and you can share results between multiple users, meaning your overall over heads should also be reduced.

When I was working on a semi-realtime BBRPG I started to implement something similar to the above, and it was working pretty well, the main reason I abandoned that project was finding a way to get the information out of the server, and into the clients browser, and at the time http://meteorserver.org/ was a bit flaky and slow when being fed information by PHP.

[shoespeak]

Maybe I am missing something, but what did you end up deciding to do?

edit: wait I see your post 2 above mine...sorry