Written by Highland Contract CRM Solutions Architect Shad Mickelberry
Managing the sizes of database tables in SugarCRM instances is the type of maintenance task most of us don’t want to deal with. As your deployment matures, more and more data is entered into your CRM. As this data accumulates, storage can become an issue, regardless of whether you are self-hosted in your own environment or on the Sugar Cloud.
As a CRM Solutions Architect at Highland, I’ve learned that managing the size of your database tables will help you get the most out of SugarCRM, both financially and in terms of overall performance.
Want to skip the background and get right to the solution?
Click here to download the SugarCRM Database Tracker to help get your database storage under control!
Some Background on Database Records
The database is where the data for an application is stored. Every record in your CRM has one or more corresponding rows on a database table.
Here’s an example: a Lead record will have rows in the database for all the fields you have configured for the Leads module. A stock Sugar instance will have approximately 270 tables. A moderately customized instance will typically have around 350. Most of these tables are utility tables, meaning they are not represented in the front end of the application by any data most users would see. The Database Tracker tool (which I’ll cover move below) helps decipher which tables are eating up the most storage, allowing us to identify corrective actions we might take.
Why Should I Care?
Bloated databases can impact your organization financially and cause severe performance degradation in the CRM.
Instances hosted in the Sugar Cloud include a designated amount of storage. This designated storage is more closely monitored as of late than in the past. However, if you exceed the designated storage capacity can lead to increased expenses.
In order to avoid unnecessary costs, it is prudent to only retain meaningful data.
For self-hosted instances, you will have an infrastructure team managing the servers running your CRM. Increasing the storage capacity and resources for your database servers can be a good short term solution, but these costs can outpace maintenance.
Financial implications aside, database maintenance prevents slowdowns and keeps the functionality of the CRM running properly.
Challenges when Managing Table Sizes
Many of the tables in a Sugar database have no direct correlation to records users interact with. This makes it difficult for administrators to know what maintenance needs to be done.
In the screenshot below we see an instance with 290,000 Contact records. It stands to reason there is a table with at least 290,000 rows that correspond to the Contact records, and this is true. What is not so obvious is there is likely another table with the same amount of rows that reflect custom fields for the Contact record. Additionally, there are several other tables that may have multiple entries for each Contact record that track relationships, email addresses, and value changes for audited fields.
Simply determining the row count and amount of data contained in each of the 300+ tables in a typical Sugar instance requires a deep knowledge of the database, as well as sophisticated monitoring to track on a regular basis. And this is even before any action can be taken to reduce the size of the database.
Finding Solutions
At Highland, issues around performance and storage limits come up frequently.
To resolve this, we developed a tool that tracks row counts and sizes for all tables in the Sugar database — as well as the database overall — called Database Tracker. This tracking tool gives system administrators a better understanding of the size of tables and the database over time, allowing them to determine what changes may be impacting the database size.
These are stored as records in custom modules so reports or BPM flows can be triggered to alert the proper people to take action before charges or issues arise.
Using the Database Tracker
After installing the Database Tracker we will see two links on the Administration page in Sugar.
This video demonstrates the information stored within the new modules.
With the tracking information stored as records within Sugar, we can create reports and dashlets to easily visualize the status of the database.
Corrective Actions
Once we can quickly identify large tables we can take corrective action on individual tables.
In the screenshot below we see the pmse_bpm_flow table has over 800,000 rows and is taking up over 10,000 MB (10GB) of storage. This table tracks individual actions for every triggered BPM process and is commonly one of the largest tables in a Sugar database.
This is where some understanding of individual tables comes to play and business decisions should be made as to what information needs to be retained.
Takeaways
While managing database table sizes might not seem like an irresistible maintenance task, recognizing which tables are eating up storage and taking corrective actions will help you get the most out of your SugarCRM.
The Database Tracker Highland developed can help you keep your storage under control, alleviating unnecessary finance fees and, ultimately, optimizing your CRM’s performance.
Click here to download the SugarCRM Database Tracker to help get your database storage under control!
Any questions? Don’t hesitate to Elizabeth Mankowski, the Director of our CRM Practice, via email.
