Backend renovations & graph generation

MCStats has used PHP to generate graphs since it was created. The first “backend” (the endpoint servers send data to) was also in PHP, but it was soon written in Java once a need for caching in-process was desperately needed.

For the past few months graph generation has been taking longer and longer. It got to the point of taking ~15 minutes to generate all of the graphs for every plugin. For the graphs to even generate the backend needs to keep MySQL up to date. Keeping it very up to date requires some 2,000+ queries/sec constantly. In a single machine and keeping MySQL small this destroys MySQL and response times were through the roof as the queries done to aggregate data for the graphs themselves were expensive and took even longer with the massive amount of queries the backend was doing.

So what what was the main step towards fixing this?

The main step towards fixing this was simple: generate graphs on the backend instead. The backend has all servers in the last 30 minutes (at the very least) cached in memory already. By keeping as much as possible cached almost all of those queries used to keep MySQL up to date could be completely removed or ran later in bulk one time (instead of constantly) as not much else was depending on them. This was the biggest step in making the site much more stable because MySQL is not being rammed by the backend and now the backend can process requests extremely fast without worrying about “lag” on selects caused by heavy updates/inserts being done in another thread.

The actual conversion to generate graphs on the backend was actually pretty straightforward as some caching was already being done.

Comparison of queries/sec and time to generate all graphs before and after the conversion:

queries/sec (backend) time to generate all graphs
before 2,000+ ~15 minutes
after ~0-10 (~500 for a couple minutes after each graph generation) < 3 minutes

PHP is now only being used to display the website (for the most part). It is nice to finally have PHP doing just that 😀

Leave a Reply

Your email address will not be published. Required fields are marked *