From the Trenches :: Issue #4

[codestryke]

Let's talk about optimization.. I don't mean optimizing your code when you are writing the code.. I'm talking about optimizing after the game is online. I try to dedicate at least 1 day of the month at the minimum to optimize code in my games. Question is though what do you optimize, where should you start and do you need optimization?

To start I look at my web logs. Just about every hosting service provides some sort of web page logging. If yours doesn't there are some free hit counters out there or you could log it yourself to a database. I don't recommend logging to a db as the db can grow very fast, very quickly. A lot of game webmasters look at the web log reports to see where there referrals are coming from, which is understandable. However the true power is to see what pages are most often hit.. This is where you need to start optimizing. You might be surprised at which page is the most "hit", I know I have been quite a few times.. Just cuz you think a page is going to be the main "hub" page doesn't mean its the one thats going to get used by the players..

Look at your most hit page.. Now open it up in your favorite editor and start optimizing.. What to optimize I cannot tell you exactly but you need to find creative and unique ways to optimize that page.. Doing so will reduce stress on the php processor and the database I, personally, like to look at what database queries I could combine or remove to eek out that last bit of performance. Once you have that page done, then go to the next most "hit" page and optimize that.. You should optimize your top 10 most hit pages as much as possible, well you should optimize it all but we all only have so much time in the day...

The second way I optimize my games is to use ADODB's performance monitor. If you don't use ADODB then read on to see a very good feature of this engine. ADODB has what's called a performance monitor that logs and then reports all SQL calls. You don't want to have this on all the time as it enters each query into a table, which any even mildly popular game will fill that table up very quickly. Turn it on for say 10, 15, 30 minutes let it log all the queries and then shut it off. Then you open the performance monitor and it'll report back to you queries and there average execution time.. This is what you need and want.. Now you can see what queries are bogging down your system or taking to long to process. Knowing this you can then tweak the query, add an index or manipulate some fields/tables to optimize the performance of the query!

I used this the other day in one of my games and found that I could actually reduce a query that got called over 600 times in a ten min period down to only getting called 50-100 depending on the number of players online.. Needless to say this was a huge performance gain for the game

Do the above, then repeat again in a month or two.. Just because you optimized it once doesn't mean it's optimized. Go back after a bit and look at the code again with fresh eyes.. Who knows you might spot something new to optimize or you might have learned a new way of doing something since you last looked at it. Optimizing code is an on going project that you need to dedicate at least SOME time to when you can...

[Sinzygy]

Thanks for this article.

I never optimized before but I will start now 

Any hints on what to look for? Combining SQL queries, what else?

[codestryke]

I could go on for along time about what I've optimized before in my games Most of optimizations were game specific so it wouldn't help for a general "how to optimize"..

Here are a couple things I picked up along the way...

First, you may have code that looks something like this:

Code:

for($i=0; $i>5; $i++) <
  echo $i, "<br />";
}

Basically going into a loop and echo'ing the results on each loop is very slow, instead do:

Code:

$output = "";
for($i=0; $i>5; $i++) <
  $ouput .= "$i<br />";
}
echo $output;

Store all the results in a string then output the entire string at once.. This is a HUGE performance gain
Did you also know that a while loop is faster then a for/next loop. Change the above code to use a while loop and you'll get even better performance.

Those though are little things that will help get better performance but your bottleneck will 95% of the time be the database.. Less queries, optimized queries those really are the things to look at when you want to increase performance..

[Broda]

One of the biggest DB access optimization tips I can give is ONLY GET WHAT YOU NEED.

Here are a couple examples

If you are trying to see if a player has enough credits to buy something don't do this:
SELECT * FROM tblPlayer WHERE ID = 123
do this: SELECT Credits FROM tblPlayer WHERE ID = 123
You may even add another condition to the query: AND Credits >= $creditsNeeded
and then if the query is empty you know they don't have enough and you don't actually have to do a compare after the fact

*Edit* The more I look at this example the more ideas I get - if you use the Credits >= $creditsNeeded condition it may even be faster to SELECT ID instead of Credits because ID should be an INDEX and/or Primary Key...


If you are showing a Hall of Fame page don't do this:
SELECT * FROM tblPlayer ORDER BY TotalStats DESC
and then loop through and output only the first 10-20 records
do this: SELECT TOP 10 ID, Name, TotalStats FROM tblPlayer ORDER BY TotalStats DESC
(the TOP # syntax may be different for MySQL - I haven't looked at it yet - as that is for SQL Server)

*Edit again* once you get the recordset back then use codestryke's suggestion of building a string and then outputing it and use a while loop instead of a for/next loop, i.e. while not rs->eof()...


Get smart with your queries and don't be lazy when it comes to your database.

[Sinzygy]

Seems like I got a lot to do concerning optimiziaton -_-

Thanks for these tips, Brado and Codestryker

[Chris]

- break some rules - we all know that we should not duplicate data, but how about username? It's needed in so many places... I have dublicated the username field on every table that need to use it (use CHAR not VARCHAR so the table stays fixedwidth otherwise the whole optimisation will give opposite effect). Is it agains the rules? - Yes. Does it make the change username a chore? - Yes. Does it improve the performance? - Yes

- split the tables in a smart way - use two tables to hold player data. The first should hold password, email, ip, last login data and other data that is used only upon login or by admins/moderators. The second table should hold all game data like money, units, assets. In addition you can always delete the second table when a new round starts and recreate it when a player logins first time in a round. This way you are making the table much smaller without deleting inactive players (who might return later and just login).

- make donator features - the purpose of optimisation is not optimisation but improvement of the game performance. So why not instead of polishing the code make a feature encouraging the players to donate and then spend these money on 3 high speed dual Xeon servers - one for database, one for the php/html output and one for hosting images). Many people forget about this fairly simple and effective optimisation technique

[Zeggy]

I like the idea of splitting player data into two tables. Should be quite useful in round/world-based games.

Just wondering though, why would adding an extra 'username' column to each table improve performance?

[leZourite]

Using split tables is what i do with afterdoomsday (for the player data among other things). As for de-normalization it helps for reads mostly when you have a lot of tables linked to one table, duplication may help. It is not a good thing to use it on userID, or any id for that matter.

Also if it helps reducing the joins when doing sql read, it adds overhead in Updates/Inserts as more tables need updates.

So why not instead of polishing the code make a feature encouraging the players to donate and then spend these money on 3 high speed dual Xeon servers - one for database, one for the php/html output and one for hosting images)

Not to sound rude, (and i am sorry if it sounds), but it's bad practice imo, fix bad code by upgrading hardware ?! hell now this is the easy way, and should not be encouraged !

I am with Codestrike on this, improve the code first, improve graphics (if you use any), add compression if possible, check any expensive queries (the ones with a lot of LEFT JOIN in it for example) and see if you don't miss some indexes in your tables.

When possible aggregate some queries, while it's more expensive in the long run it's better : have one query that check  n tables, instead of a batch of queries, it's not to be used all the time, but here is a practical example.

Say you have a small class that retrieve some basic info from a user : like the character name, id etc.

If you need to build a query to display the players it's better to do

SELECT charname, userid, 'insert here whatever field you need' From usertable INNER JOIN chartable ON userid = charid; (in this scenario, charid is a foreign key equal to userid)

than :

SELECT userid FROM usertable;

And then while you loop the userids, you load the smallclass from before (say) character(userid);

I am not sure what i say is totally clear but you get the big picture, in the first scenario only one request is made, and even if the join creates a little overhead its still more efficient then the second way, because the second way will execute N requests to get the names you could get in one shot.

[JGadrow]

I agree with leZourite, leaving issues of performance up to be solved by hardware is BAD practice. Yes, you want to make some money from the game, but why spend on hardware that perhaps you don't 'need' if you just took some time to optimize your code?

Also, this sort of attitude has led to the technology industry in the state that is in now: technology has gotten fast enough to hide poor coding practices and then development teams run into a bug in the code and look at each other with blank expressions saying, "Where did that come from?"

Knowing HOW to code also involves being able to maintain and fine-tune your application over time. Any idiot who can read can code, but it takes a TRUE programmer to optimize and organize their code.

I also split user data into two parts. However, I usually have mine in separate databases as opposed to just tables. This allows me to create a dedicated authentication server if traffic becomes an issue.

[codestryke]

Are you using it just to display the name of the player?

If that's the case then no that is not a valid reason to include the player name in each table.

Player's are given an ID if you span across tables then the player ID is the link between the accounts, not the name. Try adding a rename function in your admin panel with you have 4 tables with player information in them. Plus index are faster when referencing numbers then char or varchar.

[JGadrow]

I utilize the name as an index and do not allow re-naming. Thus the name IS the id. I do this as the index is largely redundant because the name should already be a unique identifier.

Yeah, indexes run slightly faster on numeric fields, but processing computer code works the same way and I really don't want to go back to the days of coding in binary

I like to use the username as an index instead of an id number for better understanding when reading the data apart from the program. It places a slight unnecessary burden on the system but makes it easier for me to deduce any problems related to data. And, yeah, it eats more storage space as well, but *shrug* memory is cheap

[codestryke]

I utilize the name as an index and do not allow re-naming. Thus the name IS the id. I do this as the index is largely redundant because the name should already be a unique identifier.

That works fine when you have the player name set as a primary or unique key but there are numerous instances where this is simply not the case. Do you offer in-game messaging or a type of battle log? In both of those instances you no longer have unique or primary key associated to your members, you now have a straight index as multipule messages could go to the same player (ie one to many relationship).

It is in the one to many relationship that indexes based on char or varchar start to break down very quickly. Our game State of Crime which we inherited the code uses the player name as the index on the in-game messages. This table hovers around 500k records. To do any type of processing on that table (ie clean out dead accounts etc) We literally have to shut the message center down, download the data and process it locally because if you do it on the server it kills the server. We don't have a weak or poor server either. mySQL needs a LOT of memory to process those keys to execute the query. On the other hand the built in form doesn't have those problems even though its the same size if not larger. Why? Because everything is keyed of an integer and mySQL can process any of the indexes needed much more quickly.

There comes a time when throwing more power/memory no longer works.

[JGadrow]

Yes, the keys are primary. And they also serve as references for foreign keys. I try and normalize my database structure as much as is possible. Messages are handled in a separate table with a third table tying message id to a player as recipient as it is a one to many relationship as well.

[Chris]

I don't know if you feel the same way, but I'm here not because I'm a programmer but because I'm a game owner. To me the goal it the fun and satisfaction of my players. Software, hardware and staff are merely tools to reach the goal. The clealiness, quality and optimisation of the code is not important on its own. It has zero importance to me. It is only important as long as it increases the fun of the players. So, if there is a chance to reach the goal faster using hardware instead of software it would be strange to not use it...

What you said above is perfectly right... from programmer's point of view. If you want to learn how to be a good programmer then you should do exactly what people above said. But if you want to make and run a succesful game you would be better if you stop thinking like a programmer and take all factors (software, hardware, staff, time, funds) into account. I just wanted to say that optimisation is done for a reason and if you are started doing optimisation for the sake of optimisation you are doomed.

I agree with leZourite, leaving issues of performance up to be solved by hardware is BAD practice. Yes, you want to make some money from the game, but why spend on hardware that perhaps you don't 'need' if you just took some time to optimize your code?

It was not true, at least in my case. On my old hardware when a new round started and a few hundred people tried to login at once the page was taking to open like 3 minuters. So, I have spent a lot of time finetuning my code. After some time I moved to a better hardware and... the highest CPU usage was 5% even when the player base increased 4 times. What was my optimisation for then? I could have spend the tame making so many nice features. The optimisation back then was one of the stupiest things I have done.
(of course assuming the code is not written poorly, because then no hardware could save you, but if your code is good already then making it perfect is pointless, hardware approach would be more reasonable then)


As for the doubled username field (used to display information, not identifier) I have done it because the PHP code would be simplier, the query would not need to use joins and the database would need to read one file only (they say that the most expensive it the disk seek not the data transfer). It was done for the price of a bigger table (which still is below 3 MB total, so unimportant from modern disk transfer cost point). I would say it was a good exchange. (I'm coding everything myself, so there is no problem with other coders being confused by double data)


As for indexes, how many should we use? How exactly does it affect the read/modify speed? I understand MyISAM uses different files for each index? So while it improves seek it hinders data retrieval and modify, correct?