Questions about multiple mysql queries....

[seafarer]

So, I started building my own BBG a few weeks ago.  I can only work on it an hour or two a day, but I've gotten a lot accomplished.  The basic idea of the game is a mmo merchant trading sim.  There will be multiple cities that all produce specific goods, and consume other goods.  Each user will sail around the world, buying and selling goods.  The game is a lot more in depth, and I have a TON more ideas for the future, but that gives you a pretty basic concept.

I have most of the basic elements working (nothing yet in the way of graphics), but I wonder if I'm putting too many queries into my main page.  Because there are going to be multiple users online and interacting with the cities all the time, I have an ajax query running every 2 seconds on my main page to update the quantity of goods in any given city.  Additionally, I have other queries running through ajax every 1 or 2 seconds to put different information into different divs throughout the page. 

Is there a rule of thumb for how many queries you should run like this?  How many is too many?  At what point will it bog down the user and their internet connection? 

Thanks for all of your help

[chrisjenkinson]

Chances are it won't bog down the user's internet connection, but it might bog down your server's! You can set it to only update if the player is looking at the window (if you are using jQuery, have a look at the .blur() event). If you are going to use Ajax to get the updates try and limit the number of hits by requesting more information per hit.

Alternatively, you could let the client estimate the goods quantity and only request it from your server if the player tries to "do" something.

You should make it easy to customise the frequency of Ajax requests so if you are starting to use a lot of bandwidth you can change it.

[andrewjbaker]

One technique I've seen is for the server to communicate to the client the frequency at which AJAX requests should be made. That way, it'll be your server tuning the frequency of AJAX requests based on its load, not your client blindly making requests.

You might also consider altering your requests so that they're only made once the previous one has returned.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[DV8]

Personally, and this has been mentioned to some degree before, the most important part of the interval at which you fire your AJAX calls, is how fast your server is able to process them. That's a matter of a bit of trial and error. Also, on top of that, it's important to always test the current level of goods of a city at the time a character makes a purchase, since they might be dealing with outdated information. Start with an update every two seconds until you start to see that an increase in server load is interfering with the responsiveness of those AJAX calls.

Good luck, and show us something if and when you can.

[Chris]

2 queries every 2 second per player is insanely high to me. It means that one player who left the page open and went to do some shopping will consume 3600 queries per hour. Even with mere 100 players it is 360,000 per hour or 100 queries per second. And this is without playing the game and doing any actions yet.

In comparison my game uses 2.5 queries per second per 100 active players (very rough estimate, can be wrong).

[CygnusX]

I started a PHP chat app a while back that had a 2.5 second interval... and slowly added 1 second per interval each 10 seconds of inactivity.  I never had a large load on it to test, but I would recommend a solution to this effect if you must have constant updates.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[seafarer]

Well, I absolutely must have constant queries.  The majority of the queries are just reads from my mysql DB.  The only time an update is done is upon user action.

The reason I have to have constant updates, is that multiple users will have to interact with the same city at the same time.  Therefore, two users are in the same city, both of them are watching the amount of the goods in the warehouse.  The price of the good is directly related to how much is in the warehouse.  Therefore if user1 buys a good, the price will increase, so user2 has to be able to see that change instantly.  

If you guys know of a better way to perform this action, please let me know.  

[CygnusX]

For what you're trying to do.... 1 second ajax queries is a strong approach.

However, I am concerned about your general design.  Market based games have many issues....  multi-accounting being a major such issue.  And if somehow your game defeats this problem, and becomes very successful, you'll be looking at a pretty beefy server load.  If you were with a well established company like Zanga, I could understand.... but being new to the design game, i cringe a little listening to your plan.

PS.  I went back and looked at my chat app, and it actually had a 3 second delay.  And frankly, it wasn't that bad being live.  I could provide a demo if required. 

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[seafarer]

Mike,

Thank you.... that's exactly what the point of my posting is - to see if there's a better way to do what I already have working.  I don't use memchached, and hadn't heard of it.  However, I do pass a few variables through the session/cookies.  Then run a query from that variable that gives me additional variables to perform quick calculations with.  Let me back up a bit, and tell you a little more about the process I have in place right now...that's already working with a handful of testers on at once...

When the user logs in, there is one main query that is just a read, that refreshes every 2 seconds or so.  It has pertinent information like the amount of gmoney the user has, the user's current location, etc. 

Then, when a user clicks on a city, one static query is performed that pulls the city's basic information, then a dynamic query is set up to run every 2 seconds that the city is active on the screen.  It pulls the amount of each good the city consumes and produces. 

The last query I do is when the user is travelling.  When they decide to travel, I query a timestamp every second for a countdown.  Once the countdown is complete, the usertable is updated to their location, etc.

Like I said, right now, there aren't any issues with any of this, but I'm cognizant of the necessity to create scalability from the start. 

Any helpful info is greatly appreciated.

[chrisjenkinson]

I made the point in my original post - "Alternatively, you could let the client estimate the goods quantity and only request it from your server if the player tries to "do" something."

Is this not something you could implement?

[Renex]

seafarer i'd suggest you use http://www.ape-project.org/ or node.js for this.

learn more about it here: http://www.ape-project.org/ajax-push.html

[seafarer]

@chrisjenkinson: That won't really work, because I can't have an approximation - it needs to be precise.  I want the user to be able to see exact costs of the goods - which will matter when you're only planning on making a couple bucks * 1000+ units. 

@Renex: The ajax-push seems very interesting.  I'll definitely look into it!  Thank you.

[seafarer]

Well, the APE seems fantastic - if I could get it to work with my current host.  I don't host it myself, as I don't have a box on all the time.  I already have an unlimited bandwidth plan for my business, so I was just using it...

Any thoughts/ideas on how to get the PUSH functionality on my host?

[Barrikor]

What about this?

Make a folder of empty files, one file for each city. Whenever the city's data changes, touch() the empty file.
Once a second, check filemtime() via Ajax and only do a database query when it changes.

For traveling, instead of querying a timestamp every second, just query it once and have js do the countdown.


[Edit: did you get my pm, it doesn't show up in my outbox]

[seafarer]

I did get it, yes.

I like this idea, and I hope it helps keep the queries down.  I guess now, I'll just have to pass more variables from the PHP to the JS, and perform most of the calculations there? 

Or, I could just keep most of the calculations in the PHP, but only requery for changes to variables if the filemtime is recent...?