FREE trial
FREE trialI can’t stress enough how grateful we are for what the DBmarlin team has been able to achieve and in such a short period of time!
DBmarlin database performance tuning at Academic Excellence helps hundreds of thousands of users in over 50 countries achieve the test results they deserve.
Academic Excellence is an organisation dedicated to delivering quality educational resources to home and private educators. Due to COVID and unprecedented levels of online user testing, the pressure on Academic Excellence’s systems reached breaking point as the 2021 testing season saw a record number of students trying to complete tests before their deadlines.
“The implication of slow performance for our users is a really big deal and something we needed to fix very quickly.'' said Michael Burrei, CEO at Academic Excellence. “Students need our tests to be responsive and intuitive so that they can achieve the best result possible and be recognised for all their hard work. If the tests run slowly and students underachieve, this has a number of serious consequences. First, school districts that rely on these test results to show progress risk losing government funding. Second, students risk missing out on scholarships, and third, students being home-schooled may not be allowed to continue learning in that way.
I can’t stress enough how grateful we are for what the DBmarlin team has been able to achieve and in such a short period of time! They were recommended to us by Scott Moore, the well-known performance expert we asked to help us, as the ideal team to monitor and help optimise the performance of our MySQL database. Scott identified MySQL as our biggest bottleneck after he had successfully tuned our front-end web server performance.”
“Within a week of getting the DBmarlin team involved, they understood our problem, customised their DBmarlin performance monitoring software to support our version of MySQL, deployed monitoring into production, analysed the data gathered, recommended a fix and helped us implement it.
The DBmarlin team's commitment to helping us solve our problem when we were under huge pressure has been second to none. The DBmarlin software and the database index tuning requirements it highlighted were transformational. We have gone from a situation where our customer service team was dealing with over 600 complaints from students and their anxious parents a day, to just a couple of routine customer requests, unrelated to system performance.
That's a huge win for all our students, their parents, schools and everyone at Academic Excellence.”
“Immediately after installing DBmarlin, we could see that the CPU usage on the MySQL database server was running at 100% despite the machine having 24 CPU cores. Looking at the MySQL instance and where it was spending its time, we saw that the MySQL wait event ‘Sorting result’ was the culprit. The machine had 512GB of RAM and the sort area was sized to allow processes to use it, but even still, sorting large amounts of data in memory can still be a very CPU intensive operation.
Next we looked in DBmarlin at the SQL statements, which were spending a lot of time sorting and found that one statement accounted for most of it and was taking 20-30 seconds to execute. It was selecting from a table of 486 million rows with two WHERE clause conditions.
Since no single index could satisfy both conditions, the MySQL optimiser had chosen to use two separate indexes, one for each condition and then join via an Index Merge.
Merging the results from each index scan was the thing that required a large CPU intensive sort. We could see that the first index returned around 10,000 rows and the second around 20,000 rows which then needed to be sorted by the join key so that they could be combined.
The solution to the problem was to add a new index to the table which contained both the columns required so that a single index scan could be used and no sorting was required.”
You can see in the screenshot below the massive impact the new index had. On the left hand side is the period before the index and the right side shows performance after the index was added.
“Having implemented the index recommended by the DBmarlin team, Total MySQL resource usage (DB time) was reduced by 97% with CPU usage dropping from a flatline of 100% to 10% while simultaneously increasing transaction executions, getting more work done, by over 50%.
Now that we have the new Index in place, our normal high quality service has resumed. Everyone is happy and we have enough spare capacity and time to implement the vital upgrades we need, introduce end-to-end application monitoring and new performance testing capabilities so that we are all set to migrate to a more dynamic and flexible cloud environment.
We plan to keep using the DBmarlin software and working with the team behind it as we rearchitect our solution to provide additional scalability and redundancy for our users. With help from our DBmarlin friends, we will be able to test performance and scalability every step of the way and feel confident that moving to the cloud will ensure customers receive the best possible experience even during the busiest times of the year.
If you are looking for database and host monitoring software or performance tuning advice, I recommend you contact Mick and Russ at DBmarlin. They were there for us, working across timezones when we needed them the most. They have had an incredible impact on a lot of people!”
“Academic Excellence contacted me through my main website at scottmoore.consulting and the email was very short. It was something to the nature of ‘help!’. After a brief phone conversation to get some context, I worked with Michael to start solving the problem. The first thing I looked at was their technology stack, which was a basic web N-Tier architecture featuring PHP and MySQL. The only monitoring was the basic operating system default tools. I immediately thought of DBMarlin to help get a clearer picture of what was happening. I advised Michael to install DBMarlin and use both the HOST monitoring feature as well as connecting to their MySQL database.
Within an hour, we could see that ALL of the servers were maxed out on CPU. The first objective was to relieve the pressure on the web servers. By implementing APC User Cache (Alternative PHP Cache), we could cut down on dynamic PHP executions. This kind of caching is specific to PHP. The technical vendor made the change to one of the web servers. It brought CPU utilisation down from 95% to 20%!
APC User Cache (Alternative PHP Cache) opcode (operation code) caching plugin for PHP. With APC caching, your PHP script executions can run more efficiently, by cutting down on dynamic PHP executions. It is specific to PHP.”
“Once we knew this was going to help, the change was applied to the other web server and we saw the same decrease in load. Now the web servers were running at around 20% average the entire day.
This still left the database completely overloaded at 100% during business hours. One issue we faced was that their version of MySQL was not officially supported by DBMarlin. I contacted Mick and Russ and explained the situation. They immediately responded with a plan to make it work. After trying a few quick workarounds, they decided to update the entire software package to support them. What a stellar team to respond to a single customer need that fast!
At this point, I decided that it would be best to let the DBmarlin team take over and do a deep dive into the MySQL issue. Within a day of monitoring, Mick was able to isolate very specific changes that reduced the database CPU utilisation by an amazing 90%. This is one of those cases where a few well placed tuning changes can make a huge difference.
It was great to see how much positive improvement was seen in such a short period of time. Additional recommendations are in process of being implemented. It’s possible that the application could see even more optimisation and enable their platform to scale well beyond what they imagined. This will save them so much money on infrastructure that DBmarlin will have paid for itself several times over on this engagement.”
In response to this success and feedback, Russ Luke, CEO at Application Performance, said:
“We are delighted to have been able to help Michael and his team at Academic Excellence improve the performance of their testing platform for hundreds of thousands of users in over 50 countries. The combination of our DBmarlin database monitoring solution and performance tuning skills really did bring a unique benefit when it was needed most and we look forward to building on that through a long-term partnership.” said Russ Luke, CEO at Application Performance.
Search DBmarlin
We will never share your email address and you can opt out at any time
