Today's post comes straight from the development team at Tipstrr HQ. We have been busy pulling apart our inner workings and tweaking them to get the fastest handicapping product on the market.
One of the main points Tipstrr's contact us about is the time it takes for their results to update on the site. This can be really important, especially when you have just won that all important Tipstrr Verified accumulator. Another reason we have been working on optimising the Tipstrr results algorithms is that we are getting a massive number of tips being entered into the system thanks to the awesome growing demand on handicappers wanting a Tipstrr site.
So what are the problems? Can't you just buy faster hardware? Crank it up to 11? Whilst we could just throw hardware at the problem, in the long run it's not a scalable solution and would jeopardise our ability to provide completely free handicapping sites on Tipstrr. Rest assured, this won't ever change.
So the development team here at Tipstrr HQ set off with a task to make results calculations run faster than Mr. Bolt himself. There are three main angles we looked at when targeting these performance improvements and that was Tipstrr's who have a large quantity of tips (more than 2000 tips placed), Tipster sites that contain many categories and Tipstrr sites that house lots of different Tipster's under one roof. We also threw in a mixture of sites that post a varied number of picks per day, anywhere between 1 and 30.
With all of the above in our test data set we started digging into our database internals. At the time of writing we use a customised MySQL 5.6 installation and learned quickly that SHOW PROCESSLIST is your friend when it comes to capturing really long running and intensive operations. Once we had narrowed it down to our suspect stored procedures we took a good hard look at what they were actually doing. MySQL EXPLAIN SELECT is another great tool here which can help identify why the database takes a while querying certain tables.
From our tests we saw that Profit/Loss and Result grouping was being recalculated several times in some complex joined queries. The solution here was to use a Resolution design pattern and start aggressively caching these lookup records in temporary tables. This sounds easier than it was. In reality this took a large amount of investigation making sure our results calculations were correct pre and post change. We also had to get clever with some of our queries so that using multiple temporary tables in single query blocks didn't cause us problems.
All in all, quite a heavy session of optimising based on our initial diagnosis. But what about the results? In short we were elated by how this affected overall performance. In some of our test cases we found it increased result calculation speed by 1700%, whilst the minimum we was able to achieve was a 650% increase. We would go as far as saying this was a success.
But what does this mean for your Tipstrr site? Is anything going to change? Yes and no. Whilst you won't be seeing any fancy new options to enable (sadly) this does mean that your results will update faster and there will be less stress on our hosting environment, thus making your site run faster. Sometimes these behind the scenes changes go unheard of, but they go a long way in making Tipstrr the best sports handicapping solution for all. Rest assured you are in safe hands and lets look forward to all the great new ideas coming to Tipstrr in 2015.
