EnergyLogix turns to DBmarlin to resolve a customer impacting MariaDB performance bug

EnergyLogix provides industry-leading energy consumption and carbon footprint management solutions that cover over 100,000 utility meters in office, manufacturing and retail locations around the world. With over 10 years of innovative thinking invested in its software platform, customers have come to rely on its unique capabilities. Naturally, performance of its web portal has become business critical; we explain how DBmarlin was able to help protect the end-user experience and save money.

Why were EnergyLogix looking for a MariaDB performance monitoring solution?

Like a lot of situations in life, you only go looking for a solution when you run into a problem. Unfortunately, when EnergyLogix upgraded to MariaDB 10.5.8 and deployed it at scale, performance dramatically slowed down. This immediately impacted customers using its energy management web portal.

Having worked with AP previously, the EnergyLogix team decided to give the new DBmarlin performance monitoring tool a try.

How DBmarlin helped EnergyLogix uncover performance issues

"We were immediately impressed by DBmarlin. The installation was simple and in less than an hour we began to understand the root cause of our problem" said Stavros Antoniou, Application Architect at EnergyLogix. Within 24 hours, we could clearly see the exact SQL statements within 100’s of databases that had slowed down and were impacting our customers.

It turns out that when we upgraded to MariaDB 10.5.8, we ran into a performance bug. The good news was that it had been fixed in 10.5.9. The bad news was that MariaDB 10.5.9 was not going to be supported by AWS for at least a month, possibly two.

Naturally we could not afford to wait two months for a fix whilst our customers had a slow user experience, so we decided to performance-tune our way out of the problem with the help of DBmarlin and the team at AP.

DBmarlin has been a huge help. It clearly highlighted which SQL statements were most impacted by the bug, allowing us to focus and prioritise our performance tuning efforts.

The SQL that was affected by the MariaDB bug was doing INSERT into A … SELECT … from B. The number of rows inserted each time were only a few hundred, yet this SQL had gone from taking a few seconds to sometimes more than 10 minutes. Rather than wait for the bug to be fixed and rolled out to RDS by Amazon, EnergyLogix decided to rewrite this as procedural code by defining a Cursor to fetch the records from B and then for each one doing the insert into A. This change saved many hours of DB processing time each day and helped us ensure the speed of the customer web portal and the AWS resources needed to run the service have returned to near ‘pre bug’ levels.”

Overall MariaDB performance after tuning

Here you can see at the start of the month, before any tuning, the DB time was 65 hours/day. After the tuning in mid-April, you can see the daily DB time drops to around 33 hours/day. This is a 49% reduction in total database resources being used to support EnergyLogix customers. The orange line shows that the level of customer activity remains unchanged over the month. The bar graph shows the reduction in resources being used over time.

SQL Statement performance after tuning

Here is an individual SQL statement that was tuned. Here you can see a 90% reduction in DB time for the SQL statement after tuning.

Why would EnergyLogix recommend DBmarlin?

“It is the combination of ease of use, detailed visibility and great pricing that makes DBmarlin compelling for us. Also, the team behind the product are always extremely helpful and ensured we could quickly minimise the impact of our unexpected MariaDB bug.

• DBmarlin allowed us to be up and running, monitoring performance within minutes.
• The user interface is very logical and easy to understand, giving us a great way to visualise performance and share that across technical and non-technical teams.
• DBmarlin clearly highlights which queries across 100’s of databases were most impacted by the bug, ensuring we could focus and prioritise our efforts.
• Having very detailed ‘per second’ performance data offered by DBmarlin Premium ensures we have what we need to make the right decisions.
• DBmarlin time comparison reports allow us to continually compare any two time periods and show that our performance tuning efforts were moving in the right direction.

Performance tuning using DBmarlin has helped us to optimise performance, protect our users' experience and ensuring we were able to control our AWS costs. If you are looking for an ROI, it’s really a no brainer as the cost of DBmarlin is easily paid for when you consider the benefit of being able to keep your customers happy!

Although we originally used DBmarlin to help us solve an urgent customer-impacting performance problem, going forward we will be using it not just for monitoring and tuning our production databases, but throughout our development and testing to ensure we continually optimise performance and protect our customers and reduce infrastructure costs. We can’t recommend it highly enough.”