Over the last two days I have been trying to optimize the performance of the Index Charts/Graphs. I think I have found the fix, and you should see results for these charts in the sub 1-second range.
To explain how this works, I have to open up the GrabPERF data model and let you folks peer under the hood.
The Daily Index graphs are created using two tables: hourly_site and data. hourly_site is a rollup produced asynchronously at the end of every hour that aggregates the Arithmetic and Geometric means, as well as a count of successful measurements. data contains over 30 days of raw measurement data which is purged at the end of each day.
All of the days in the daily graph, and all of the hours in the hourly graph are pulled from hourly_site. That is with the exception of the most recent day (daily) and the most recent hour (hourly). These are pulled dynamically from data to ensure that all current results appear on the right-hand side of the graph.
With this design, performance was awful. I have tweaked the code as much as I feel comfortable doing (I am an analyst, not a developer!), but it was slow. I increased the size of the query cache, but the boost only lasted for a while. Then I realized that the data I most cared about was being pushed out of the cache by all of the other cached data.
I have now set MySQL to cache queries only on demand. This reduces the load and means that only the results I care about, and which are accessed most frequently, are cached.
When I restart the database, the Daily Blog Search chart takes about 5.5 seconds to generate, and the hourly chart takes about 30 seconds. Yikes!
After this initial pain, both charts take less than one second to be generated.
I am still working on it, so let me know if you see any weirdness.
UPDATE: Seems that the first query of every hour pays the performance penalty, and all other queries will not.
