Optimization techniques

[oxidati0n]

It's one thing building an application with beautiful code, but when scaling, optimization comes the hardest challenge. It is not regarded by many people until it becomes a problem.

Here's my top tips for optimizing your app (for PHP, specifically - and some of them may be stating the obvious, but that's the point):

1. Use a key-value store such as Redis or Memcached:

We're currently using Redis with Criminal Outlaws, and by using this, it allows you to store little sets of data which are accessed a lot (users online, user counts, hospital counts, statistics, config) to speed up the app significantly. The point of optimization is to reduce as many requests as possible while retaining functionality. Redis, for example, has a read-write ability of milliseconds which MySQL is not capable of. In more simplified terms, you'd stick stuff in Redis that is not critical but can avoid a MySQL request which is significantly slower in this aspect.

2. Avoid using the MySQL star at all costs.

You're all familiar with this. There's so many situations where lazy developers select a whole table of content when they only like need one field, which not only costs speed but it wastes CPU. This also helps you analyze which data you can afford to offload to another data and by using MySQL Join (the fields which are linked using ON such as userid's should be indexed!).

3. Section data.

For example, in Criminal Outlaws, we've already developed a notepad option (and it costs game money to buy it). We sectioned it to it's own table from the original 'users' table, which means that users who don't buy a notepad will not have any data in the field and it will lighten up the table significantly. A lot of data, in say, MCCodes (from what I can remember) was all shoved in one table which did work but was not sustainable.

4. Don't use numbers, use timestamps.

Timestamps are not only fool-proof, but they are extremely efficient to work with. At the cost, they might use up like 10 more characters per row in the database but the benefits outweigh significantly. The only problem you might face is if you use two servers, the timezones might be different, but that's up to you to fix. Timestamps can be checked upon each request, rather than relying on something else to do it's duty which when your maintaining your application can always cause problems. Timestamps can also be built to backdate, which cronjobs can also, but it's much harder.

5. Convert data to in-server arrays.

We've done that significantly, we first thought to do MySQL storage. This wouldn't work with commercial scripts, but to do this is like the god of optimization. MySQL should only ever be for user-generated content and data, and by putting stuff like cities and maps in there, you're only wasting queries and CPU.

It would work like this:

$array = array(

1 => array(

'item_id' => 1,

'item_name' => 'Gun',

'item_price' => '910.00'

)

);

6. Use FULLTEXT and INDEXES for your MySQL data

We use FULLTEXT to speed up data which may grow significantly (such as our username cache) and it's a million times faster and efficient than using LIKE (especially with wildcards). Indexes should be the field which you access a lot such as userid's, usernames, etc (no more than 10-20% of your table should be indexed, or it slows down significantly) because it's MySQL's way of routing to a request.

7. Use a framework - stating the obvious!

I'm myself using CodeIgniter, and so many improvements they've done that we would probably never do, and by using them, you're saving a lot of time.

After all these, we've seen our speed from like 0.40 seconds on average to 0.02 which is an equivalent of 20 times faster. There are quite a more, and when I've found some more, I'll add them. Feel free to add based on your own experiences!

[Sim]

Nice read.

[Dominion]

Number 5 may have some practicality issues when you store who has what item, and using a Framework is going to slow your application down as it's going to load things you don't use. I would be shocked if you use everything on something like a web games anyway. None the less interesting read.

[oxidati0n]

Number 5 may have some practicality issues when you store who has what item, and using a Framework is going to slow your application down as it's going to load things you don't use. I would be shocked if you use everything on something like a web games anyway. None the less interesting read.

If you read again, I said MySQL should only be for user-generated content. Assigning items to users is defined as 'user-generated content' in technical terms. Regarding the framework, not necessarily, it depends on what you use, we use CodeIgniter which only loads what you need so we avoid requesting non-critical files within our app.

We store our arrays in external files, and just the way you make a MySQL request for items, we make a file import for the items array in a PHP file. And yes, we use most of them, as each type of app have different needs, we wouldn't consider using all of them unless they are needed. Even MySQL can be a cache in itself (which in one way is for us), which can be argued as another optimization technique.

[Dominion]

We store our arrays in external files, and just the way you make a MySQL request for items, we make a file import for the items array in a PHP file. And yes, we use most of them, as each type of app have different needs, we wouldn't consider using all of them unless they are needed. Even MySQL can be a cache in itself (which in one way is for us), which can be argued as another optimization technique.

How do you associate the the item owned (stored in the database) to the item information from the array, and how does the time to do this vs the execution time of the query look when put next to each other? I am rather interested if you have any figures. If not what method are you using so I am able to test it.

Another problem I can see is how do you allow staff members to add in game content? Don't forget very few sites will have staff trusted enough to be allowed full access. Sometimes the slightly slower route is more practical.

 

Regarding the framework, not necessarily, it depends on what you use, we use CodeIgniter which only loads what you need so we avoid requesting non-critical files within our app.

CodeIgniter is a framework. By it's very nature it's designed to help development time, however this is at the cost of some execution time. For example at the start (or more or less the start) of running your application it starts timing the execution time of the entire application. Now this is a rather useful feature to be sure, but it's also wasting time when the application goes live.

This is one of many examples I could give for this framework, therefore unless you have edited it's core it's not really considered optimized for most peoples needs. I am in no way saying it's not worth using a framework, for most it's a good option, but it's not the first thing you think of when you have optimization in mind.

Edited August 15, 2011 by Dominion

[a_bertrand]

I shall add some general information:

Optimization is generally (if not always) done at the cost of code readability. Which means you may loose some clean code because you optimize it. In some case optimization will speed up execution at the cost of more RAM requirements.

That means:

- Optimize ONLY when you need it, and not otherwise, as you may end up with a code hardly maintainable which at the end may even prove to be slower due to poorly written code.

- Optimize ONLY the piece of code which really have some impact. That means if you have 2 functions one of which you call it only once and the other 100 times, you may get better results by optimizing the code which really eat up time in total: 1x1000 ms vs 100x1 ms => the single called function will have an higher impact on the other side: 1x10 ms vs 100x10ms will make more sense to optimize the one you call 100x. This makes overall code hard to optimize without a good profiler.

Overall I would tend to start without any optimization, with a code which does exactly what I want and not use tricks unless I do see I have really a bottleneck somewhere. For most of you, PHP will be fast enough without any special tricks. Your players will have more issues with the network / server connections than with your own PHP code. Or if you really need to optimize something, then I would start to check the SQL queries and see if some index may not help, as this is usually something developers tend to keep on the side and forget. That doesn't mean put an index everywhere! as index are also expensive during insert / updates / delete.

To come to your "avoid the usage of * at all cost", in theory I would tend to agree, but instead of making multiple selects (scattered across your code) to retreive the pieces required, a much smarter and efficient way would be to load all at the beginning and then use the pieces where required even if some of the data loaded is not always used. Think that this kind of optimization (avoid to read a whole row) is effective specially where the database server is NOT the same as the web server and that the data need to be sent over the network, otherwise it will have little to no impact.

As conclusion, forget optimizations as "this will fix all your speed issues" as this is not true. Code optimization is extremely difficult, require a full knowledge of YOUR specific problem (as every code work differently) and require quiet some knowledge. So stay away till you really need some.