System Optimization for the smaller installs

bsoremsugar —  March 20, 2010 — 1 Comment

There are several FAQ, tutorials and Wikis about how large systems can optimize SugarCRM but what if you don’t have a System Administrator let alone a DBA on staff.  If you have a system with load balancers and 50 user then you need to hire a system administrator, if you have rented a server and have 5 users then this is the FAQ for you, cause we’re not going to go into load balancers or clusters or anything like that.  these are a just few fairly simple things I use on my own system to keep it humming.

Most all of these can be carried out by someone with few server skills but I do take a few things for granted.

  1. You should have access to your system by SSH or at least FTP
  2. You do have to have your stack installed correctly and have the main server binaries like mysql and mysql-admin in your path.
  3. You should also know at least a little about how to manage your server
    1. If you don’t know how to install admin tools like PHPMyAdmin, or don’t have access to your server then download and install SweetToolsSugarForge and that will install PHPMyAdmin, eXplorer and PHPSysInfo in your admin menu and configure all three to work with your system.  You just load it into SugarCRM through the Module Loader.
    2. If you run Apache then read their official performance tuning page.  That is the 2.0+ version of the page, if you are still running Apache 1.3 go here.
    3. If you run IIS then this would be the official Microsoft performance tuning page.
      1. If you use MySQL that’s OK but there are a few limitations the Windows version of MySQL has that the Linux version does not, see this link.
      2. These are also good links
        1. http://www.15seconds.com/issue/971127.htm
        2. http://207.46.16.252/en-us/magazine/2005.11.pumpupperformance.aspx
      3. In your Windows php.ini make sure you are NOT loading too many PHP extensions, only load the ones that are absolutely needed.
        1. extension=php_curl.dll
        2. extension=php_mbstring.dll
        3. extension=php_mcrypt.dll*
        4. extension=php_ldap.dll*
        5. extension=php_mssql.dll**
        6. extension=php_oci8.dll**
        7. extension=php_mysql.dll**
          *   = Only if you use LDAP on your server
          ** = Depending on what database engine you use
  4. You should know where your configuration files (php.ini, my.cnf and config_override.php) are, and how to edit them in a text editor.  You can use the phpinfo() in your Diagnostic file (or SweetTools above) to find out where your php.ini file is, your config_override.php file is in your root SugarCRM directory and your my.cfg file could be anywhere.  On Linux you could do a sudo find / | grep my.cnf to find it, on Windows you can use the Start menu Search tool.

While almost all of these suggestions will work for Windows/IIS/MSSQL installs I mainly talk in terms of Linux/Apache/MySQL.  This is only because that is what I run.  SugarCRM will work equally as well in a Windows environment as long as PHP is properly installed.

You should also be running MySQL version 5.1.33 or better but I wouldn’t go for 6.0 just yet.  Version 5.1.44 is the current stable release I would use on new installs.  As for apache on Linux almost any version is OK, the current stable release is 2.2.15.  As for Windows, don’t use apache on Windows, use IIS 6 or 7 and install PHP using the new FastCGI (not ISAPI or regular CGI).  Here are instructions for IIS6 and here they are for IIS7.  Also don’t use Windows XP or Vista or Windows 7 as a server.  Or go ahead and do it but don’t expect great results out of it.  As for SQL Server, I would stick to 2005 for now.

So now lets go over a few SugarCRM specific things you can do.

#1 Count Queries

$sugar_config['disable_count_query'] = true;

This code, if inserted into your config_override.php file, will turn off all of the record counts at the top of ListViews.  Users will see 1-20 of 21+ instead of 1-20 of 12345.  Some people don’t like this but you will get used to it and the performance improvement is impressive.  You might think you need to see those numbers but you quickly get used to it and you can always click the ‘end’ button to see how many records there are on those rare occasions that it matters.  This optimization works even more if you are using the InnoDB engine for your tables.

#2 Random ListViews

$sugar_config['save_query'] = 'populate_only';

When you click on a ListView for the first time you get a random list of the first 20 (or whatever) record in the table.  How often is what you are looking for in that list of 20 records?  Almost never.  So this code makes it so that you have to do a search before any records show up on a listview.  It’s a little different at first but again you can quickly get used to it and get used to the extra bandwidth it frees up.

#3 Vcal Updates

http://developers.sugarcrm.com/wordpress/2009/03/23/new-vcal-updates-setting-in-520c/

Turning this off, if you don’t really use the free/busy info on the Calls and Meetings Editview, will speed up those modules a fair amount.  Especially after a few years of data get into the system.

#4 Accelerators

http://www.sugarcrm.com/wiki/index.php?title=PHP_Caches_and_Accelerators

I use eAccelerator on my system, even though it’s not really supported it is a breeze to install and it very good acceleration.  If you download and use the XAMPP stack then all you have to do is uncomment it in the php.ini file.  APC is better but it is a bit harder to install.

To install APC on an existing stack that was not installed from the Linux CD or source repositories (ie you downloaded a seperate stack liek XAMPP and installed it) you can install it like this

  • Download the tarball from http://pecl.php.net/package/APC
  • Unpack it and connect into the directory created.
  • Run /opt/lampp/bin/phpize-x.x.x; use the version of phpize which corresponds to the version of PHP you want to install for.
  • Run the following commands:./configure --enable-apc --with-apxs --with-php-config=/opt/lampp/bin/php-config-5.0.4make
    make install
  • Edit your php.ini file and add the following lineextension=apc.so
  • Reload Apache.
  • Create a PHP page which calls phpinfo(), and check that there is a section for APC. If there is, it’s working.

Of course that Linux,  if you installed your stack with Linux then you would have to do something like this.

To do it in Windows/IIS you would follow these instructions, if you are running Apache in Windows then switch to IIS or Linux.  Almost anything is faster than Windows/Apache.

#5 The correct MySQL settings  (This one is for MySQL only really)

http://blog.mysqltuner.com/

Most servers I see that have MySQL installed on them are either using the stock my.cnf file or using no my.cnf file.  The my.cnf file is where all your settings for MySQL are stored.  If you dont have one then MySQL is running on it’s default settings which are, more than likely, no where near optimal.  I have been using this utility (http://blog.mysqltuner.com/) on my servers for a while now and it does a great job and is childs-play to use.  It is a perl script so you windows servers are going to have to make sure thats installed, bit other than that it’s a breeze to install and use.

MySQLTuner

It’s that bottom part thats important, there it will tell you all the settings that might need tweaked to get your MySQL back in ship shape.  Now it really shines after your server has been up and under load for a few days so don’t run it right after a reboot like I did but wait until after about 48-36 hours of continuous operation and then give it a try.  Also in the “Storage Engine Satistics” you can see that I have 179 tables that are not optimized, that brings us to…….

#6 Table Optimization (This one is for MySQL only really)

http://www.sugarforge.org/frs/?group_id=513

I wrote a little PHP script a long time ago that goes though every table on a MySQL system and checks to see if it needs optimizing and if it does then it runs the optimize command on it.  It is available here.  You just run it from the command like this

php -f opt_mysql.php root password localhost

If you leave off the command line parameters then it will give you it’s ‘help’ screen.   It might run for a while and it will use almost all of the available CPU so only run it during down times and make sure you back up all your MySQL databases first.  It does output a sort of progress trail so you can see what it is doing and that it hasn’t locked up.  This tool will not repair your tables if they are corrupt.  Another thing to consider is that for InnDB table OPTIMIZE doesnt seem to do anything.  I am not sure if this is a bug or by design or maybe if I am wrong all together.  But it seems that if I run this tool against an InnoDB database it says that it needs optimizing, optimizes it and then the next run does exactly the same thing as if it wasnt done the first time.

#7 A little Database maintenance every year helps

http://www.sugarforge.org/frs/?group_id=513

When I was the system administrator of a SugarCRM system one of the things I did every year was a complete cleaning of the database.  Every record you have deleted is still in there, every relationship and every messed up import.  So once a year clean it up.  I wrote a script to do this as well called TableOrginizer.php.  This script will go through the entire database looking for records that are safe to delete like records already marked as deleted, relationship entries that point to deleted records and emails that are not associated with any records (spam or emails connected to deleted records).  It even checks custom modules, tables and relationships.  It also goes through the cache/uploads directory and deletes any file that isn’t used in the system anymore.  Everything is configurable, just edit the file and look at the top for all the configuration options.  This too will also run a long time and take up alot of your CPU so again, run it during down times.

This tool also uses alot of memory and alot of CPU time.  You will have to bring the server down while this script is running.  You might also have to bump up your memory_limit value in your php.ini file.    For one very large database I had to set this value to 1024M to get it to process the whole database.  It can also take a very long time to run.  My 7 Gigabyte test database takes about 13 hours to clean on my laptop.

It is also a very aggressive tool, it is looking for any valid reason to delete a record.  But in some cases it doesn’t delete records, for example if a record has an assigned user that is no longer active (or is deleted completely) then it simply reassigns the record to whatever user you tell it to.  Same with modified_by_user, created_by_user and all the team fields.  But if a call has a parent (like a Lead or Contact) that no longer exists, then the call is deleted.  In many-to-many relationships, if one side of the relationship or the other is invalid then only the relationship is deleted.  If there is enough interest in this tool I might go back and make some settings to dial back the level of aggressiveness to whatever level you are comfortable with.  Email me with any suggestions or comments.

This tool is still in beta so test it on a development or test server first and make sure you back everything up first but it really does clean out just about all the fluff that can accumulate in the average system.   This tool creates a detailed log of everything that it did and even calculates the space savings it generated.  So far I have run it dozens of times on dozens of systems and it has worked every time.  But I make no guarentees on this one yet.

You would run it like this

php -f TableOptimizer.php /opt/lampp/htdocs/opt.log /opt/lampp/htdocs/customers/index_issue/ admin

Where the first path is to a log file you want create and the second path is the path to your SugarCRM instance and the ‘admin’ is the admin user that will be used to do the cleaning.  This tool uses the config.php that is in your SugarCRM instance and whatever database is listed there will be ‘cleaned’.  So the best way to use this tool is to make a copy of your instance, change the database name in the config.php to reflect the copy of th edatabase you created and then use that instance to do the cleaning, if all looks good afterwards you can just point your production instance at the new database.

#8 Relationships

If you have alot of custom relationships you don’t use then delete them.  If they were created before 5.5.1 then you will have to remove them manually in the code, or you wil have to find someone to do it for you.  It isnt hard, there is a good article about it here.  Every relationship in the system can add what is called a JOIN to the SQL code that is run to build ListViews, that adds overhead and if you are not using them anyway then it would speed things up to delete them.

#9 Adding fields in custom modules

If you create a custom module in Module Builder then ALWAYS modify it in Module Builder.  Do not do anything to your module in Studio.  If you use Studio to add a field to your custom module then that field is added to a separate table (the CSTM table) and it will have to JOIN that table in the SQL slowing things down.  If you add the new field in Module Builder and deploy it, then it will become part of the base module and it will be put in the modules main table and not require a JOIN in the SQL and will be faster all around.  Another thing to remember is that any change you make to a custom module in Studio will not be reflected in module builder and if you want to export a module and load it on another system all your studio changes will not be exported with that module.  So I reiterate, if you have a custom module that you created in Module Builder then always use Module builder to modify it, never ever use studio to do anything in a custom module.

If you have any other tricks up your sleeve please let me know, post a comment below.

Trackbacks and Pingbacks:

  1. Leads Page - July 6, 2012

    [...] [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s