CODEWITHSUNDEEP
phpmysqlperformancecaching
shuunenkinenbi
shuunenkinenbi

Reputation: 97

File based cache or many mysql queries

Lets assume you're developing a multiplayer game where the data is stored in a MySQL-database. For example the names and description texts of items, attributes, buffs, npcs, quests etc.

That data:

To solve this problem, i wrote a file-based caching system that creates .php-files on the server and copies the entire mysql-tables as pre-defined php variables into them.

Like this:

$item_names = Array(0 => "name", 1 => "name");
$item_descriptions = Array(0 => "text", 1 => "text");

That file contains a loot of data, will end up having a size of around 500 KB and is then loaded on every user request.

Is that a good attempt to avoid unnecessary queries; Considering that query-caching is being deprecated in MySQL 8.0? Or is it better to just get the data needed using individual queries, even if ending up with hundreds of them per request?

Upvotes: 1

Views: 514

Answers (4)

Allen Chak
Allen Chak

Reputation: 1950

There are many possible solutions, depends on your requirements. Possible solution could be:

  • File base JSON format caching. Data retrieve from database will be save to a file for next time use before the program process.
  • Memory base cache, such as Memcached, APC, Redis, etc. Similar the upon solution, better performance but more integrated code required.
  • Memory base database, such as NoSQL, MongoDB, etc. It is a memory base database.
  • Multiple database servers, one master write database with multiple salve for read databases, there are a synchronisation between servers.

Quick and minimise the code changes, I suggest using option B.

Upvotes: 1

Szántó Zoltán
Szántó Zoltán

Reputation: 981

I suggest you to use some kind of PSR-6 compilant cache system (it could be filesystem also) and later when your requests grow you can easily swap out to a more performant cache, like a PSR-6 Redis cache.

Example for PSR-6 compatible file system cache.

More info about PSR-6 Caching Interface

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

In my experience...

You should only optimize for performance when you can prove you have a problem, and when you know where that problem is.

That means in practice that you should write load tests to exercise your application under "reasonable worst-case scenario" loads, and instrument your application so you can see what its performance characteristics are.

Doing any kind of optimization without a load test framework means you're coding on instinct; you may be making things worse without knowing it.

Your solution - caching entire tables in arrays - means every PHP process is loading that data into memory, which may or may not become a performance hit in its own right (do you know which request will need which data?). It also looks like you'll be doing a lot of relational logic in PHP (in your example, gluing the item_name to the item_description). This is something MySQL is really good at; your PHP code could easily be slower than MySQL at joins.

Then you have the problem of cache invalidation - how and when do you refresh the cached data? How does your application behave when the data is being refreshed? I've seen web sites slow to a crawl when cached data was being refreshed.

In short - it's a complicated decision, there are no obvious right/wrong answers. My first recommendation is "build a test framework so you can approach performance based on evidence", my second is "don't roll your own - consider using an ORM with built-in cache support", my third is "consider using something like Redis or memcached to store your cache information".

Upvotes: 1

Not A Robot
Not A Robot

Reputation: 2684

Instead of making your own caching mechanism, you can use Redis as it will handle all your caching requirements.

It will be easy to implement.

Follow the links to get to know more about Redis

REDIS

REDIS IN PHP

REDIS PHP TUTORIALS

Upvotes: 1

Related Questions