Page 1 of 1

Efficiently using SQL data over multiple page loads

Posted: Wed Aug 26, 2009 8:26 pm
by Josh1billion
Note: This relates mostly to PHP+MySQL game development, but it can definitely be applied toward a large number of PHP+MySQL websites.

A quick backstory
In 2007-2008, I wrote a large game in PHP+MySQL, and it was fairly successful. However, its downfall was this: my code was largely unoptimized. As a result, I had trouble finding an affordable server that could withstand the heavy load the website received. Upon some investigation, I quickly found that MySQL services were taking the large majority of the server's processing time (as opposed to the PHP service). It seemed my MySQL calls were either too large or too frequent-- perhaps a mixture of both. I didn't have any calls that were particularly hefty-- it was just that I had a dozen or more players loading pages every few seconds, and each page load contained at least a few MySQL queries: a mandatory SELECT query to get the player's character data (which consists of something like 30 or 40 fields), a SELECT query or more to get information on which location the player is in and possibly something like which monster the player is facing, and then often an UPDATE query to update the player's data based upon what happened in that page load. When all this was added up, the server was dedicating a lot of time to processing MySQL queries.

It's my goal now to revive this project and optimize the code, reducing the MySQL query processing load and, as a result, allowing me to host the game on a cheap(er) server.

I've contemplated ways that I can accomplish this optimization. I'm going to focus today on one optimization method I'm considering, but if anyone has more/better ideas of ways to cut down on the MySQL load, I'd love to hear them too.

The main optimization theory I'm posting about
Here's what I'm hoping to accomplish. I'm not sure how well it will work out (will it cut down a significant or insignificant amount of load? are there any problems I might unexpectedly run into using this method?), so that's why I'm posting here (and also to get more ideas on other ways to optimize the SQL queries).

My plan is to essentially eliminate the "mandatory" query that I mentioned earlier: the query that SELECTs the player's character data at the start of every page load. The way I intend to do this is to call that query only right when the player logs in, and have that data saved within session data. I assume that accessing session variables is much faster than querying the same data from a database (if not, by all means let me know). At that point, whenever a page load needs to know something about the character's stats/location/name/etc., it'll request it from the session variable array instead of from the SQL server. Of course, whenever that session data is updated with new information (ex: the player levels up, so the "level" variable's value is increased), an UPDATE query will serve to ensure that the SQL data always remains consistent.

Is this practical? Efficient? Are there any pitfalls to watch out for? Is there a better alternative to solving my problem?

Re: Efficiently using SQL data over multiple page loads

Posted: Thu Aug 27, 2009 12:35 am
by Benjamin
You would be amazed at how much properly optimized queries can reduce the server load. You really need to identify and order (in DESC ;) ) the queries in so far as performance hits. We can help you either rewrite them, or index them properly so that the load is reduced. Also, depending on your plan and hosting company, modify the mysql configuration file (/etc/my.cnf generally) which is a must on high traffic sites for the most part.

Not to say this is the cure all, you could have developed a system that is incredibly inefficient and unscalable, but for now we'll just assume you didn't. :)

Re: Efficiently using SQL data over multiple page loads

Posted: Thu Aug 27, 2009 5:47 pm
by Josh1billion
What kind of query optimizations are you talking about?

Most of my queries are pretty simple:
SELECT * FROM `users` WHERE `id`='53' LIMIT 1
SELECT * FROM `locations` WHERE `id`='3' LIMIT 1
SELECT * FROM `monsters` WHERE `id`='6' LIMIT 1
UPDATE `users` SET `level`='2', `strength`='7', `dexterity`='6', [...] WHERE `id`='53' LIMIT 1

Stuff like that. These aren't direct examples, but they're very close to the real thing. A single page load would usually consist of queries very similar to those, but probably about two or three times as many queries in a typical case.
astions wrote:Also, depending on your plan and hosting company, modify the mysql configuration file (/etc/my.cnf generally) which is a must on high traffic sites for the most part.
What kind of modifications? I usually do use dedicated hosting, so I would normally have access to this sort of thing, I just don't know which specific modifications you mean.

Re: Efficiently using SQL data over multiple page loads

Posted: Thu Aug 27, 2009 6:15 pm
by Benjamin
As I've said, you really need to identify the queries that are causing performance hits. There's no magic bullet. Here are a few things though:

1. Don't use SELECT *, only select the fields you actually need.
2. In a query such as SELECT id, name FROM table WHERE id = 1 AND foo = 10, id and foo should be indexed together.
3. You can sometimes combine multiple queries into single queries by using subqueries and/or joins.
4. You can store the results of queries for a preset duration using memcache, which can drastically reduce the number of queries being sent to mysql.
5. When an INSERT or UPDATE can be delayed, you can use the INSERT LOW_PRIORITY syntax and MySQL will run them in batches reducing table locks.
6. You can increase the key buffer size in my.cnf which I believe (would have to check the manual) allocates the amount of ram allocated for indexes, reducing disk reads.

Re: Efficiently using SQL data over multiple page loads

Posted: Fri Aug 28, 2009 2:20 am
by Christopher
Josh1billion wrote:My plan is to essentially eliminate the "mandatory" query that I mentioned earlier: the query that SELECTs the player's character data at the start of every page load. The way I intend to do this is to call that query only right when the player logs in, and have that data saved within session data. I assume that accessing session variables is much faster than querying the same data from a database (if not, by all means let me know). At that point, whenever a page load needs to know something about the character's stats/location/name/etc., it'll request it from the session variable array instead of from the SQL server. Of course, whenever that session data is updated with new information (ex: the player levels up, so the "level" variable's value is increased), an UPDATE query will serve to ensure that the SQL data always remains consistent.

Is this practical? Efficient? Are there any pitfalls to watch out for? Is there a better alternative to solving my problem?
It is pretty standard to store user information in the session once logged-in. The problem is if you want a multi-server architecture, which implies that the session data is in a database, or you are storing information in a cookie. File bases sessions or cookies will be faster than a database query. A session database query might be faster than a more complex query for user data.