How to Improve SugarCRM Performance by up to 20x
CRM Online has been working with SugarCRM for more than 10 years now, and in that time, have implemented some large scale systems with large data requirements, as well as built entire industry products on the platform. While this is often not an issue on it's own, any database administrator will tell you than allowing users to run their own (un-optimised) queries on large data sets is a recipe for trouble. When it comes to reporting and customising list views, that's effectively what users are empowered to do.
The technical team at CRM Online share a few insights on how to improve SugarCRM performance on list views and search queries. Our tests have shown that this has given some of our clients a 20x performance improvement on their systems. As a note, we do see this same issue in other CRM systems that we implement, but the power of SugarCRM is that if offers the ability for us to resolve this quickly ourselves rather than submit a case and hope the support team gets to it at some point.
SugarCRM offers a number of features that minimize the impact that a poorly designed query or interface has, such as being able to run reports on a secondary slave database, something that other platforms don't offer. However, this is only part of the problem solved.
What is the cause of performance issues in large mysql implementations?
MySQL is a database that's become the default for many web applications today. There's a common perception that it's a database that's for SME projects, but in reality, it's getting more and more acceptance in mid market and enterprise applications. About 12 months ago, for example, Amazon AWS launched Aurora, a mysql-compatible database engine with a 5x performance improvement for large, enterprise grade platforms. This is a pretty good indication of the acceptance of mysql as a standard for SQL for large web databases.
The real cause of any performance issues is around poorly optimised database queries. Whether that's in the form of a report that orders by or groups by a non-indexed field, or list view queries that do the same. The flexibility of SugarCRM is also it's weakness in the case of performance - when a user tries to order a list view by a newly created (and non-indexed) field in the leads table, and the number of leads that your organisation has is significant (1M+), you'll see a significant impact on performance. There are two key locations that we see this issue:
- List views and associated search queries
- Custom reports
We don't actually see any performance impact on global searching, since SugarCRM leverages the Elasticsearch platform to provide high performance global search capability to it's professional, enterprise and ultimate products. If you're still using the free (CE) edition from a few years back, or one of the branched products such as SuiteCRM, you will likely see performance issues on global search also.
How do we fix these performance issues?
If you would like your SugarCRM instance running smoother than the one of those weird looking hairless cats, there are a few steps you can take.
1. Enable your slow query log in SugarCRM, or in mysql. This one seems like an obvious one, but to solve an issue, you need to first diagnose it. Once you've done this, monitor your system for any slow queries. We've set our slow query limit at 1 second - in a well optimised system, no query should take this long to execute.
Here's an example of one of the queries that we found was taking a long time to run:
Note that the actual query is much longer - this has been significantly simplified so that readers can follow the query, but here were the key takeaways when we analysed the query (using a DESCRIBE statement):
- If you add custom fields to the users module, you almost double the number of tables linked on the query. Stay clear of them, or use our script to move any custom user fields into the main users table (this is still upgrade friendly and is done using the SugarCRM metadata).
- The single most significant issue is the fact that the query is ordered by a custom field that has not been indexed.
Naturally, we did some investigation into how this could be optimised. We tried:
- Adding a simple index to the leads custom fields table. This had a minimal effect on performance (mysql was still resorting to using temporary tables on the query)
- Changing the order by field to be a field within the main leads table. Even when not indexed, this has a significant improvement in query performance. We added an index to the field and the performance increased further.
2. Optimize your SugarCRM Metadata
Based on this, we decided to try moving the field to the main module table, and indexing it. The result was immediate - our query time reduced from 18 seconds down to 300ms, a reduction of more than 50x! The actual performance increase changes based on the number of records and types of data, but this meant a huge performance improvement for that specific query.
In order to make this database change in an upgrade friendly manner, we did this by removing the custom field reference and creating the relevant entries in the fields metadata. This is non-trivial and should only be done by experts, but fortunately, we've written a script to do this (use at your own risk!).
So, how do we extend this to other queries and modules that might need to be optimised? We automated the process of course! Our team have created a script that can be run in a module's root directory and does the following:
- It looks at the list view fields for that module (these are the fields most likely for users to order by), and moves them to the main module table (using SugarCRM metadata so that it's upgrade friendly)
- It adds an index to that field (again, using SugarCRM metadata)
- It provides a script to copy existing data from the custom table into the main table
This can be downloaded from the link at the bottom of the page.
3. Try out Percona for MySQL
Note that we're not associated with Percona, we just think what they do is amazing. This one is a bit of a free win for all involved.
Percona is a company that specialises in getting the most out of MySQL (and other databases). They provide a drop in replacement for mysql that is based on the most recent versions of MySQL, though with some clear improvements in terms of the tools available to manage the database and the raw performance. We found that on the same hardware, the same queries run on the percona version of mysql ran 3x faster than the queries on the stock standard version of mysql. How do they do this? Black magic is my guess, or perhaps they just have some very clever database administrators optimising the underlying engine. It's important to note that Oracle purchased MySQL a few years ago, and have a one could argue that they have a vested interest in not quite optimising it to the level it could be in order to sell the enterprise edition, or the Oracle database itself
To Summarize, SugarCRM is currently deployed in a wide range of large scale deployments, and handles that scale well. However, it's important to invest in optimising your CRM to provide the best user experience possible for your users.
If you would like assistance in optimising your SugarCRM instance, please don't hesitate to get in touch.