Caching code

[kaithar]

Sooo, now that things are settling a little :)

You put a bunch of caching code wrapped around the mysql functions, was that based on an actual performance need or was it purely speculative? Just trying to gauge if it should be ripped out completely or just made optional ;)

[frdmn]

@kaithar That function is in there since the beginning and it used to cache SQL queries. Depending on the load and the performance of your machine, it could cause issues if you disable the cache completely.

It is also kind of optional. If you just set all caching values in the settings.php to 0, the cache should be disabled completely. May I ask why do you want to remove it?

[confuser]

From what I recall (this was quite some time ago) I wrapped all the queries around the caching function to mitigate the website from taking down the mysql database or causing it heavy load affecting the java plugin/minecraft server. Due to the nature of this system, it's paramount it's robust.

[kaithar]

With respect, by the sound of it either your mysql server is misconfigured or there is something else not right.

To give context, I have a "big data" table containing ~1.48 billion rows and a query against that table's index takes a tiny amount of time. For example:

Select for a specific value in the index... Showing rows 0 - 29 (11520 total, Query took 0.0026 sec)

Select 10000 rows from a select * ... WHERE 1 Showing rows 10000 - 19999 (10000 total, Query took 0.0188 sec)

Grabbing a DISTINCT select against the key Showing rows 0 - 29 (7133 total, Query took 0.0142 sec)

In case you're curious, that table is 91GiB in size, of which nearly 25GiB is consumed by the index. The index cardinality is 18. I have other tables in the many hundreds of thousands range, also not really a problem.

I would hope that the ban database is orders of magnitude smaller.

[confuser]

By heavy load I meant mitigation against DDoS attacks. It'd be fairly rudimentary to deny players from joining the minecraft server by attacking the web interface via a layer 7 attack if no caching is in place. This would cause queries on the minecraft server to slow down, and will eventually cause the AsyncPreLoginEvent's to hang waiting for a response back from the database, preventing players from joining.

Your big database may be able to query fast, but I very much doubt it would withstand easily against 10k+ requests a second which is an incredibly common scenario Frostcast faces on a daily basis in terms of people trying to take us down.

This is Minecraft, attacks are incredibly common. The application should handle such scenarios, which it currently does.

[kaithar]

10k req/s isn't a particularly helpful number without also knowing the concurrency value for the attack. 10k req/s at C50 is a very different problem from at C5000 ...

In either case, putting a cache around mysql is trivial to dodge (it's just cache misses after all) and is just moving the choke point on to other parts of the stack such as the mpm and/or the fastcgi manager. Personally I'd drop the max client value of whatever web daemon you're using down to a number that's decently below the cap of what the server can handle, then wait for them to get bored. Or even just throw a rate limit on the web server.

I'm also puzzled why you don't use the mysql query cache, given that it's purposely designed for this job.

First request: Showing rows 60000 - 60019 (~20 total , Query took 0.0554 sec) Repeat requests: Showing rows 60000 - 60019 (~20 total , Query took 0.0002 sec) Showing rows 60000 - 60019 (~20 total , Query took 0.0002 sec) Showing rows 60000 - 60019 (~20 total , Query took 0.0003 sec)

Without sticking an actual a multiconnection test in front of it I can't be sure, but looks like it could indeed handle 10k queries/s against my 1.4 billion row table.

Or you could just stick a caching reverse proxy (eg nginx or varnish) in front of the thing and slap a 5-10 second cache TTL on anything dynamic.

I don't doubt you got some attack resistance bonuses out of it, I'm just not sold on it being a good way of dealing with that specific problem.