Some Quick Pointers on Improving SugarCRM Performance

Getting web applications performing optimally is a never-ending full-time job for a lot of people, including me! I manage the OnDemand environment for thousands of customers at SugarCRM. This post will walk through a few pretty simple steps that can be taken to improve the performance of SugarCRM on a Linux server with MySQL, but they mostly apply to any PHP application running on a LAMP stack.

Tuning a web application is a type of engineering, and as Rico Mariani at Microsoft puts it:

If you are not measuring, you are not engineering.

MySQL Performance

Getting things right in MySQL is another full-time job, but there are a few quick things you can do. First of all, make sure that all of your tables are the same character set. If they are not, this can greatly slow down queries that join between tables of different character sets. Take a look in the output of

SHOW TABLE STATUS FROM sugarcrm;

ALTER TABLE my_custom CONVERT TO CHARACTER SET utf8;

Next up is making sure that you are properly utilizing the MySQL Query Cache. This cache saves the restlts of select queries so that if the same query comes in again before changes to the table, the results can be returned much more quickly. To see the configuration for this, run:

SHOW VARIABLES LIKE "query_cache%";

SET GLOBAL query_cache_type = 'ON';
SET GLOBAL query_cache_size = 32000000;

SHOW STATUS LIKE "Qcache%";

Generally, using the InnoDB storage engine instead of the default MyISAM storage engine will help out as well, but any tables that you want to be able to do full-text search on cannot be InnoDB, and there is a lot more to take into consideration here than any quick fix. If you are using all MYISAM tables, this would be a good thing to investigate. Switching to InnoDB and tuning your MySQL storage engine based on your workload are a lot more than I can fit here today, but there is a lot of information out there about these. Head over to the MySQL Performance Blog for more information than you ever wanted to know about MySQL performance (including an excelent article on the MySQL Query Cache).

All of your MySQL STATUS values should be included in your monitoring system, as graphs of these are very helpful over time for diagnosing problems and tuning cache sizes! Also, remember that the Query Cache uses up RAM so keep an eye on that as well. In this case, it will use 32MB and as you increase the query_cache_size, you are directly increasing RAM usage.

PHP Performance

Perhaps the single biggest perfomance enhancement that you can make for SugarCRM and many other PHP applications is OpCode Caching. I've written about some of the complexity involving this before at SugarCRM and Caching with APC, but the simple version is that OpCode caching will improve the performance of your PHP applications. At SugarCRM, we use APC which is pretty straightforward to install. On CentOS, and other rpm/yum Linux distributions simply:

yum install php-pecl-apc

pecl install apc

Copy /usr/share/pear/apc.php to somewhere in your webroot and visit it in your browser, and after a bit of usage of SugarCRM, you should see the "Hits" percentage climb while the "Misses" percentage gets smaller and smaller. Your hit ratio should be higher than 98%. If the "Cache full count" number is more than a very very small percentage of your hit counter, you should increase the size of memory available to APC. On some Linux systems, shm segment size is limited to 32M so instead of increasing shm_size, you will need to increase shm_segments.

Like MySQL, these numbers should be part of your monitoring system so that you know when it's time to change things. Also like MySQL, this directly uses RAM. This example will use 32MB, and increasing it will use more.

Client-Side Performance

Why bother serving files to clients that never (or seldomly) change? A common industy best practice is to set a Cache-Control header for static content to 10 days in the future. Tools like YSlow will let you know if you are not doing this. (They will also give you a lot of other pointers on perfromance! We have internal bugs open at SugarCRM for most of the client side performance issues that tools like YSlow have identified.)

We use apache on our application servers, and to enable these cache-control headers we added this:

<FilesMatch "\.(ico|pdf|flv|jpg|jpeg|png|gif|js|css|swf|xml|txt)$">  
Header set Cache-Control max-age=36000,public
</FilesMatch>

For this one, monitor your req/s and check your PHP Applications after any changes using a tool like YSlow.

SugarCRM Specific

Lastly, a few specific changes just for SugarCRM:

• Add 'disable_count_query' => true to config.php. This replaces the "1-5 of 200" with a "1-5 of 6+" and makes a big difference when you have a lot of records. This configuration setting should be a default in a future release of SugarCRM.
• Disable explicit caching in the app by setting 'external_cache_disabled' => true in config.php. While SugarCRM can use external caches like APC's user cache and Memcached for some things, this shouldn't be enabled unless you have set up the infrastructure to handle these. This configuration setting be a default in a future release of SugarCRM.
• If developer mode is enabled and you are not using it, turn this off! In SugarCRM 6.0, this is under Adminstration -> System Settings -> Advanced. This is off by default, and if it gets turned on and accidentlally left on, it causes severe performance degradation.
• Turn loging down to the minimum that you need. If your SugarCRM installation is on a NFS based document root with multiple servers, this becomes even more important. This is under Administration -> System Settings -> Logger Settings. "Error" or "Fatal" are good options here.
• Turn off tracker features you are not using. This is in Admin -> Tracker. Each enabled piece of functionality causes things to be written to the database, and will slow down page loads. Leave on only what you need for reports that you have written.

Thats a start!

With those changes, you should be off to a pretty good start at improving the performance of your installation of SugarCRM! These things help us keep SugarCRM performing quickly in the SugarCRM OnDemand environment with a pretty high ratio of active users to servers, and a recent application of these tips that I did to a site that handles 200k+ unique users per month lead to a 65% decrease in system utilization and much improved response times.