SugarCRM Server Performance Optimization / How to Benchmark Yourself

In an effort to improve the response time of my sugarcrm installation.  I developed a simple performance testing benchmark tool that allowed me to measure the impact each optimization had on the response time.  In this blog post I will a) present what optimizations worked the best for me b) explain how you can do the same and c) provide specific steps and recommendations for users who used the sugarcrm “FastStack” installers as their Apache+Mysql+PHP stack.

Test Methodology

To isolate the performance gain of each optimization, I’m changing one thing at a time and then running a performance benchmark script I wrote using the Apache Jmeter.  Jmeter is a free open source tool for load testing and performance measurement.  At the bottom of this article I provide a brief tutorial if you want to try testing the results yourself.

My test plan mimics the webbrowser.  It logs and fetches about 10 different module list view pages and loops 3 times.  My load test script does not fetch embedded resources in the php. It just calls index.php and passes relevant params such as module=Account, action=ListView.  So, none of the image files, js files, or css files are downloaded.  In this article i’m focusing purely on server side optimizations.  Perhaps I’ll dive into client side optimizations in a seperate article.

One thing that’s important to point out is that all these tests were done to measure the response time of the server when only one user was active.  And while there is generally a correlation between response time and amount of My tests focused on the average response time so just because the optimzations listed didn’t impact response time doesn’t mean that they do not decrease the amount of processing required or be asome of the tweaks I tested that didn’t have an improvement in terms of response time could very well reduce the amount of processing needed which in turn could provide a much greater impact when a lot of users are active.

Test Server Details

Windows Server 2003
Intel Core i7 2 cores @2.9Ghz
2GB Ram
250 Megs of diskspace free.

* All testing was done on SugarCRM Pro Edition v6.4.0 (unless otherwise specified)
* My Database is ~200mb, 1500 Accounts.
* We have ~20 users.  Only 1 active user when I did testing.
* To account for network delays, most results I ran test about 5 times, removed the worst and best and averaged the remaining 3.  

• I was unable to isolate the reason for why Apache 2.2 / PHP 5.3 had such a major performance benefit.  I posted to the sugarcrm forums about it here: http://www.sugarcrm.com/forums/f6/need-help-identifying-cause-speedup-blog-article-its-not-apc-mysql-query-78499/
• The performance improvements with APC opcode cache seem to make my webserver a little unstable.  So, I ended up disabling it.  
• The tests marked with low confidence are ones that I’m not extremely confident on.  I did less tests on them and since the performance impact was so small all of them are kind of within the margin of error.
• Mod Deflate was somewhat of a surprise.  Keep in mind that this test is only retrieving the .php file (none of the resources).  Since mod_deflate compresses the response, it makes sense that this would take more server processing time.  This slight increase in processing time is typically more then made up for by the shortend download time.  In my case, when running the test I was getting average bytes around 250k when deflate was off versus 44k when it was on.  So, I was compressing the html being returned a lot.  I’m still going to keep mod deflate on in production but it’s something to consider if you’re CRM is on your LAN as it might speed things up slightly.

Recommendations For FastStack Users

if you’re like me, I assumed when I installed the FastStack provided by SugarCRM that it would already be pre-optimized for sugarCRM.  This isn’t the case.  After going through this process for my own server, I suspect this was intentional to consume a low memory footprint and generally minimize potential for issuess such as needing to disable opcode cache before upgrading sugar.

First recommendation, install the latest stack.  I don’t know what changes were made in each version of the stack (I’ve looked everywhere for Release Notes and haven’t been able to find them…).  But, I know that between faststack version 6.0 and faststack version 6.4 there were signifigant improvements made.  Apache got upgraded from 2.0 to 2.2, PHP 5.2 —> 5.3, the php version installed with stack 6.4 comes with extension DLLs like php_apc, and php_gd which are compatible… so no need to go through the headache of finding the compatible binaries…  The GD extension is needed for the UI enhancements in 6.4 which make use of sprites.  Note: run repair after switching stacks.

Here’s a bulletted list of changes you should make:

1. Enabled PHP opcode caching (maybe…) — After posting this I found that it was causing my Apache to crash.  I didn’t notice that much of an impact.
2. Enabled Mysql Query Caching / Increase table size
3. Enable mod_expires / mod_deflate 
4. Make sure ajax ui is enabled.

I was planning on posting how to do 1-4 but got kinda of bored by the time I finished this article.  The links I provide below provide good guidance on it.  Or you can leave some comments and I’ll make a new post.

How to Test Against Your Own Tutorial

1. Download jmeter: Apache JMeter - Downloads
2. Run bin/jmeter.(bat|sh)
3. Download my Test Plan from here: http://www.blakerobertson.com/files/…e-Clean-v2.jmx
4. In Jmeter open the test plan (File->Open)
5. Edit variables & run test as shown above.

I have created other more complex TestPlans.  The one I posted is sort of universal since it didnt’ depend on specific records to be around etc.  I have created more advanced scripts which also go to specific records.  You can have it download the CSS/JS files.  You can measure the server CPU / disk usage so you can compare the response time vs. server load.  There is a lot you can do.  If you’re interested in any of these things then please leave me a comment and I’ll see about writing more tutorials.

Useful SugarCRM articles 

All the tweaks I made in this article I got the idea from these articles.  I’d highly recommend reading them.

* Some Quick Pointers on Improving SugarCRM Performance - great overview all around.  Specifically this article has good information on mysql tuning and sugarcrm specific tweaks.

* Improving SugarCRM client-side performance: The server - Another good all around, specifically has great Apache snippets.  

* The developer blog as a ‘performance’ tag.  So, check here for the most up to date: Performance

Performance comparison mod_jk vs. mod_proxy_ajp for Apache 2.2

We use apache webserver as a frontend to tomcat and had been using mod_jk for years.  When we transitioned from Apache 2.0 to 2.2 we decided to try using mod_proxy_ajp because it seemed to be the direction most blogs/stackoverflow posts were recommending these days.  Not to mention I was unable to find mod_jk binaries for windows.  (I later found windows binaries for mod_jk (32-bit and 64-bit) here:
http://apache.osuosl.org//tomcat/tomcat-connectors/jk/binaries/windows/)

What we found is that if you have a high performance web application.  You’re absolutely going to want to use mod_jk.  I think this point is greatly understated on many other comparisons.

I did a quick and dirty performance test… using windows performance monitor (heh).  It has a neat feature that lets you calculate the average cpu over time which is pretty helpful.  Ironically, windows performance monitoring consumes an insane amount of cpu itself… but I digress.

Test #1 - CPU Utilization:

Win2008 R2, Apache 2.2.22, VMware
Generated a load of 400 requests/second (using a proprietary tool I wrote that simulates our application)
I let performance monitor run and calculate the average load consumed by each process over that 2 minute time period.
In between tests, all I did was modify the httpd.conf to change a few JkMount (mod_jk) statements to ProxyPassMatch (mod_proxy_ajp) statements.  Didn’t restart tomcat.   

Here are the results:

Average CPU %	Apache Webserver	Apache Tomcat	Total
Mod JK	12.96%	8.18%	21.14%
Mod Proxy AJP	18.49	25.94	44.43%

For the same load, our application used half as much CPU when using Mod JK!

Test #2 - Max Throughput Tests

After my initial post, I decided to go back and do another test using apache JMeter.  Click the thumbnail below to see the full results marked up with comments.  Essentially though, with mod_jk I was able to get 1400/sec and with mod_proxy_ajp I’d max out at about 900 (but with errors also).

Errors when using Mod Proxy AJP

As we ramped up number of active users we’d get errors much sooner with mod_proxy_ajp.  This is definitely more of an issue on Windows xp and Windows 2003 server machines then it was 2008 R2 or windows 7 box.  I don’t recall off hand all the things we tried to fix this.  I’m pretty sure we tried increasing ThreadsPerChild and other settings and it didn’t really help the problem at all.  I also remember we tried turning on mod_status and from the looks of things it did not appear that we were running out of workers.  It just didn’t add up.  But, afraid I didn’t investigate too much longer as the performance reasons alone were reason enough for me to switch back to Mod JK.  Perhaps someone else can shed more light on this.

Error Messages:
This is the error that would show up in apache-error.log when using mod_proxy_ajp under high load:

Windows 2008 R2 (with apache 2.2.22):

[Mon Feb 27 15:28:30 2012] [error] proxy: AJP: failed to make connection to backend: localhost
[Mon Feb 27 15:28:30 2012] [error] (OS 10055)An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.  : proxy: AJP: attempt to connect to 127.0.0.1:8029 (*) failed

Another error message we saw is below.  I’m not sure if we got this error message only when we tested against windows XP/server 2003 boxes or if it was because we had Apache 2.2.21 on them.

[Tue Feb 07 14:48:00 2012] [warn] Server ran out of threads to serve requests. Consider raising the ThreadsPerChild setting
[Tue Feb 07 15:05:13 2012] [error] (OS 10061)No connection could be made because the target machine actively refused it.  : proxy: AJP: attempt to connect to 127.0.0.1:8029 (*) failed
[Tue Feb 07 15:05:13 2012] [error] proxy: AJP: failed to make connection to backend: localhost

Configuration Files

httpd.conf for mod_jk

JkWorkersFile C:/myapp/webserver/conf/workers.properties
JkLogFile     C:/myapp/logs/apache-mod_jk.log
JkLogLevel    error
JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "
# JkOptions indicate to send SSL KEY SIZE, 
JkOptions     +ForwardKeySize +ForwardURICompat -ForwardDirectories
# JkRequestLogFormat set the request format 
#JkRequestLogFormat will print an entry such as: [Tue Feb 10 13:13:38 2009] worker2 tb1.alertustech.com 0.032000 -- for each jsp request.
#JkRequestLogFormat     "%w %V %T"
# The following mount points forward requests matching the URL sequence to an application server.
JkMount /mywebapp/get*.jsp worker2

Workers.properties

worker.list=worker2
worker.worker2.type=ajp13
worker.worker2.host=localhost
worker.worker2.port=8029
worker.worker2.lbfactor=1
worker.worker2.socket_keepalive=false

httpd.conf for mod_proxy_ajp

ProxyPassMatch \/(mywebapp\/get.*\.jsp)$ ajp://localhost:8029/$1

Apache2: Allowing Directory Listings for a Particular Directory

Assuming you haven’t modified the default httpd.conf <Directory> section for your webroot folder or done some else funky with permissions all you have to do is add the following lines somewhere in your httpd.conf.

<Directory “R:\webroot\some\subdirectory”>
   Options +Indexes
</Directory>

— Apache 2.2.x