SugarCRM – Team security query caching

Following up on my previous article about Sugar’s Team Security implementation, I thought it would be interesting and useful to discuss and mention a new setting to improve it.

Prerequisites

For this article I’ve used:

  • Sugar Enterprise v7.6.0.0
  • The infrastructure setup described on my previous article Setup a Debian Based Sugar development environment (or similar)
  • In-memory caching is enabled using memcache/memcached (my current preference) depending on the linux flavour used

Introduction

Some time ago, I was analysing the performance of the Team Security query. The slowest part of the query seems to be caused by the subquery with joins between the tables team_sets_teams and team_memberships within listview queries.

Then I thought: Sugar caches most of the semi-static data (vardefs, language files, etc) into whatever caching mechanism is used by the system, why don’t we run an IN() condition using the cached list of ids per user, instead of a subquery on every listview load?

Read more

SugarCRM using multiple MySQL databases

Let’s assume you currently have a MySQL Setup with one “Master” and multiple “Slaves”, and you do want to make sure your Sugar system uses the “Slave” instances for reading purposes. Alternatively, if you are looking at deploying MySQL Master/Slave instances with a click of a button to test this out, you can achieve the same setup using Amazon AWS RDS, by deploying one MySQL database with additional read replicas.

SugarCRM commercial editions already have a feature that allows to connect Sugar to a slave database for Reports and/or an additional one for module’s ListViews, providing additional performance and scalability to the application. The feature is provided out of the box and it is available by changing a configuration file, for self hosted systems.

Read more