Handle large data sets from MySQL to PHP and then to client in JSON

Question

Situation: I need to grab large amounts of data from the database (~150k+). Then using PHP, I split that data based on a daily figure and count it (1st: ~10k, 2nd: ~15k, etc...), and then increment another value from the daily figures. And after doing that, I need to format all that information into a JSON array and return to client and display a graph on these statistics.

Now, I'm pretty sure this can all be handled well by PHP but it would probably create a lot of load on the server + bandwidth, and especially if the client keeps refreshing the page to view updated stats. Their are also about ~5k+ active users daily so their will be a lot of data being fetched.

What would be the best way to handle this?

Note: The server has 4gb DD3 RAM.

Answer 1

You'd want to implement some kind of caching mechanism, so each user only has a granularity of (say) 1 minute. That way even if the user's hammering on refresh, they'd only execute the db query/data collation once a minute, and otherwise get the previous results.

If the data's relatively the same between users, that'll reduce the total database load even further. Assuming each user hits refresh every 10 seconds, and the data sets are common to 10% other users, then doing a per-query cache with a 1minute granularity takes you from

150,000 rows * 6 times per minute * 5000 users = 4.5 billion rows fetched

to

150,000 rows * 1 times per minute * 500 users = 75 million rows fetched.

(e.g. 1/300th the rows fetched).

Answer 2

Short answer: don't perform the calculations every time; save the results of the calculation in a database table, and return those results.

Longer answer: the above, but understand it can be tricky based upon just how up-to-date you expect your data to be. Consider just how much updated data invalidates your result set, and design around that.