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.Monitoring is the key in all of the tips that follow because if you don't know how bad things are, you won't know which of these fixes help you out. Depending on the details of your situation, some can actually do more harm than good so it's important to go through these one by one, understand the implications of them, and compare you data from befor and after you make any changes.
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;and make sure that everything is the same in your Collation column. If you are using utf8_general_ci in most places, and the table my_custom is a different type, just run:
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%";If query_cache_type is set to something other than "ON" or query_cache_size is set to 0, you will want to change these! 32MB is a fine start, but depending on your workload you may need (a lot) more to get the full benifit from this:
SET GLOBAL query_cache_type = 'ON'; SET GLOBAL query_cache_size = 32000000;This one change can make a significant difference in performance. To tune your numbers, check the output of:
SHOW STATUS LIKE "Qcache%";After some time running with the Query Cache on, you should see hits go up and lowmem_prunes should stay pretty low with free_memory not reaching 0. If free_memory seems to run out, or lowmem_prunes continue to climb, increase the size of the query_cache_size. There will continue to be lots of inserts and not_cached due to constantly changing tables and queries other than SELECT queries, so don't worry about those numbers.
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.
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-apcor
pecl install apcThen, enable apc by adding it's configuration to /etc/php.ini or /etc/php.d/apc.ini, and make sure that apc.enabled is set to 1 and apc.shm_size is set to something reasonable. For one instance of SugarCRM, the default of 32 (for 32MB) is generally plenty but if you have other PHP things running on the server, a bigger number might help.
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.
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 SugarCRM OnDemand, this cut our number of requests-per-second in half and made a several-hundred-millisecond improvement in the time it takes to load pages from SugarCRM OnDemand.
For this one, monitor your req/s and check your PHP Applications after any changes using a tool like YSlow.
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.[Cross posted on developers.sugarcrm.com.]
Graphs are fun, especially when its stuff thats relevant to me and things that have happened over time. A long time ago I posted about the program I made to generate a graph of my AIM usage. That graph recently passed 750,000 lines! Another recent milestone was passing the 30,000 listened song number on my last.fm profile. Thats a lot of lines and songs.
Another favorite graph of mine is the performance data for my server graphed with MRTG. They work great when you configure them properly, but two mistakes have burned me a little bit. Each time, I used a configuration script that someone else had written that had limits hardcoded into it. This lead to the mailserver throughput being limited to 25 messages until May of this year when I realized the problem, and the database throughput being limited to 2000 queries a second until late last month when I noticed. After fixing the problems, the graphs changed substantially. Emails approach 250 in a five minute period every now and then, and database queries approach 10,000 for 5 minutes. Because of the change to the db graphs, it now lines up much more accurately with the CPU usage and network traffic graphs. (The primary load on all 3 of those is the database driven dynamic content load of this site and Faster Mustache.)
The last graph of interest for today is the project activity statistics for Gallery. As the Gallery project manager, I try to stay aware of everything that is going on and the statistics that SourceForge provide are important metrics. Browse around and check them out, they're pretty interesting, but I stumbled across one thing that makes a lot of sense afterwards but was a bit of a surprise: Closing tracker items (bugs, tasks, feature requests, etc) that were recently created increases the average age for open tracker items. I was trying to get the average age down by closing things that were no longer relevant, but because the density of items is a fairly sharp peak of current items with a long tail, closing the same percentage of recently opened items and old items increases the average age. Ah well. I just need to get people to take care of all of the really old open things..