I’m at the LinuxTag event here in Berlin this week and am really pleased with how good of an event it is. It is easily on par with the LinuxWorld events back in the US.  And of course Germany leads Europe in open source enthusiasm and growth.

As always, one of the best parts of attending a conference for me is to see what other companies and projects are doing with SugarCRM.

One of the hits for us at the show has been the Talend connector for SugarCRM. Talend is a commercial open source provider of data integration solutions. With Talend, you can synchronize customer information between Sugar and any other system such as MS Navision or SAP. This greatly simplifies your sales person’s life when it comes to updating customer information. He can simply update the customer’s billing address in Sugar and the change is automatically pushed out to the financials system. They have the Sugar connector built into their Business Connector suite and provide implemenation help as well.  Talend is working with several Sugar customers providing this exact data integration solution today.

Another very cool integration that I saw for the first time is between Starface and SugarCRM. Starface is a very well designed VoIP system that has recently built an extremely cool integration with SugarCRM. When you run Starface for your telephony system and Sugar for your CRM system, your sales people can be working inside of Sugar, a new phone call comes in and the customer record automatically comes up in Sugar. All inbound and outbound phone calls are automatically recorded in Sugar, thereby giving you a very clear and well-organized view of when you talked last with a customer. Like Talend, this is a very real product ready to be installed in your company today.

The Sugar team has recently released ER (entity relationship) diagrams for the Sugar 5.0 database. We will make ER diagrams available with each release moving forward.

The package includes a PDF document and a DDL (data definition language) file that creates the schema with foreign key relationships. You can use the DDL to reverse engineer the diagrams with any diagram tool such as MySQL WorkBench.

You will find the Sugar 5.0 Community Edition ER diagrams here in the Developer Zone. The ER diagrams for the Sugar Professional and Sugar Enterprise Editions are available for subscribers in the download manager at http://support.sugarcrm.com.

As you know, Sugar 5.1 will be our first release where we support only PHP 5 and will drop support for PHP 4. The minimum supported version of PHP for Sugar 5.1 will be PHP 5.1.

With PHP 5.2 now the primary release supported by the PHP team and no more work being done on PHP 5.1, we plan to drop support for PHP 5.1 and require a minimum version of PHP 5.2 with the Sugar 5.5 release targeted at the end of this year. We still support PHP 5.1, but do recommend that you use the latest version of PHP 5.2 as that is proving to be a far more stable PHP release for Sugar to run.

To summarize:

• Sugar 5.0 requires a minimum of PHP 4.4.1, but we recommend PHP 5.2
• Sugar 5.1 will require a minimum of PHP 5.1.0, but again we recommend PHP 5.2
• With Sugar 5.5 at the end of the year, we will require a minimum of PHP 5.2

Onwards and upwards!

I was catching up on the work done at Sun Microsystems around performance testing SugarCRM and MySQL on the Sun Coolthreads server technology.  I see that Satish Vanga, the engineer working on this project, has updated his blog post detailing his performance results and system configuration.  With further tweaking of the MySQL configuration, he has been able to increase the number of concurrent users from 700 to 900.  Very cool!

We have just posted the Sugar Developer Webinar schedule for June.  We have two great deep-dive developer sessions coming up on customizing SugarCRM.

In the Building Dashlets and RSS Feeds session on June 12th, Majed will take you through the details of creating both a Dashlet and an RSS Feed off of a custom-built module.

In the Adding Custom UI Field Types session on June 26th, Max will show you how to create an upgrade safe custom field type which will appear as a new field type choice in Studio and Module Builder.

Sign up now!

We are pleased to announce that the Funambol-SugarCRM Connector Project led by Phil Shotton is the SugarForge Project of the Month for May 2008.  Phil really deserves this award as he truly exemplifies the dedication and passion of a Sugar Open Source Community member and the leadership qualities of a project admin for a top SugarForge project.  Congrats to Phil!

For those of you who haven’t heard of Funambol yet (where have you been hiding?), Funambol is a top-notch technology that allows you to sync contacts, tasks and appointments between various applications and your favorite mobile phone device.  And of course this connector syncs your Sugar customer info with your mobile phone.  Very cool!

On another cool note…  Funambol is just now launching their own Funambol Forge. How long before Phil earns project of the month there as well?  ;^)

We just concluded a survey of the developers in the Sugar Community.  This was our first developer survey and it is always great to get real data to back up what we think the community looks like and wants.  In general we weren’t too surprised with the results, but we were amazed on how consistent the feedback was across the respondents.

The majority of the Sugar Developer Survey respondents are developers using the Community Edition in small companies in North America and Europe and are relatively new to SugarCRM (less than two years).  They almost all have customized the Sugar code and definitely want to see more code comments and better developer documentation.  Though there was some pointed feedback on how to improve the product and documentation (hey, this was their opportunity to say it all) they still rank us very well against other technology vendors, would recommend SugarCRM to others and definitely plan to stick with SugarCRM going into the future.

About 60% of the respondents are running Sugar 5.0, 30% on Sugar 4.5 and the rest on Sugar 4.2 and 4.0.  About 90% are running PHP 5 and 10% PHP 4.  85% are running MySQL and 15% are running MS SQL Server.  2/3rds  are running Linux and 1/3rd are running Windows.  All of the developers use PHP at work, with Java, C++ and Perl being the next most common programming languages.  Standard text editors were the favorite IDE for half the respondents with Eclipse and Zend Studio coming in at 30% and 20%.  3/4ths of the respondents had worked with PHP applications prior to SugarCRM.

The number one request was for better developer documentation.  We are already moving quickly on this with a dev guide in the works.  Stay tuned for a complete update mid-Summer to the Sugar Developer Zone site including all new developer documentation and tutorials.

I’d like to thank everybody who took the time to answer the survey and give us your feedback. In conclusion, expect to see these community-focused surveys more frequently.  Your input drives what we focus on next!

I have recently been asked to answer this question a few times, “Does SugarCRM require GUIDs for primary keys in the database? ”

At a high level, Sugar was designed to allow the primary keys to be replaced by any unique string. This could either be a different GUID algorithm, a key that has some meaning (such as bean type first, followed by info), an external key, and/or auto-incrementing numbers.

The GUIDs in Sugar are generally only used as unique primary keys.  They have no other special meaning.  We don’t bother to look at what is in the string and do not rely on what they contain other than the ability to match them against records in the DB and having the include/utils.php function create_guid() return a valid one. Because all of our relationship tables are typed, we link two records (such as an account record with a contact record) with a specified ID in the record type relationship table (e.g. accounts_contacts) instead of just storing the ID and searching to find out what type has that ID.

We chose GUIDs rather than auto-incrementing keys to allow for easier data synchronization across databases. This data synchronization issue comes into play when the Sugar Offline Client (part of Sugar Enterprise) syncs data between the main Sugar installation and the Offline Client or when developers use the Sugar SOAP APIs or a tool like Talend for data synchronization.

The strategy of the Offline Client using GUIDs for primary keys was very easy to implement and we don’t have to worry about data conflicts. If you change the system to use some other ID scheme and do need to accommodate data synchronization across data stores, then you would have to either partition the IDs ahead of time or work out a system similar to what we have for cases and bugs where we have a server ID that is globally unique and a incrementing case or bug number. This is a moderate customization though which would require some careful planning and implementation.

However if you don’t care about data synchronization issues, then you can certainly change the primary key format to some other unique string.

To implement a new primary key method or to import existing data with a different primary key format and then rely on the existing GUID mechanism for new records, there are a few things to look out for:

• The system expects primary keys to be string types and will format the SQL with quotes. If you change the primary key types to an integer type, you might have SQL errors to deal with since we put all ID values in quotes in our generated SQL. The database might be able to be able to ignore this issue. MySQL running in Safe mode will have issues for instance.
• Case-sensitivity can be an issue. IDs “abc” and “ABC” are typically treated the same in MySQL. Some other CRM systems from which people were migrating to SugarCRM were using case sensitive strings as their IDs on export. If this is the case, and you are running MySQL, you need to run an algorithm on the data to make sure all of the IDs are unique. One simple algorithm is to MD5 the ids that they provide. A quick check will let you know if there is a problem. If you imported 80,000 leads and there are only 60,000 in the system, some might have been lost due to non-unique primary keys resulting from this case-sensitivity issue.
• Sugar only tracks the first 36 characters in the primary key. Any replacement primary key will either require changing all of the ID columns with one of an appropriate size or to make sure you don’t run into any truncation or padding issues. MySQL in some versions has had issues with Sugar where the IDs were not matching because it was adding spaces to pad the row out to the full size. MySQL’s handling of char and varchar padding have changed in some of the more recent versions. To protect against this, you will want to make sure the GUIDs are not padded with blanks in the DB.

Jacob

I’ve been doing some testing over the last few months on our new Data Center Edition (DCE) of SugarCRM. One of the key components that’s been giving me trouble was our NFS. I could successfully test with hundreds of users on local disk, but switching to NFS would cause a cascading failure where the system would become almost unresponsive.

The tests on NFS started out fine. I routinely had response times around 170ms for the login page. That’s the average response for the local disk tests on these servers. The problem started as soon as my "users" would start going down multiple paths of code. The CPU load would spike and response times would jump. Both would continue to get worse the longer the test ran.

Took some research and talking with one of the core engineers at Zend before I finally isolated the problem. PHP’s realpath_cache_size setting was the culprit.

realpath_cache_size is used by PHP to keep from having to look up file names. Every time you perform any of the various file functions or include/require a file and use a relative path, PHP has to look up where that file really exists. PHP caches those values so it doesn’t have to search the current working directory and include_path for the file you’re working on.

Since the default setting of realpath_cache_size is only 16K, Sugar was filling that up pretty quickly with 50 instances of Sugar running at the same time on one server. I had never seen any ill-effects from this on the local disk runs, but NFS was the perfect breeding ground for this problem. Simple lstat calls take roughly 10x longer to respond than the standard local disk because of the network overhead. Those slight delays—ten-thousandths of a second—were just enough to cause response times to jump.

The fix was easy enough. Up the realpath_cache_size setting. Finding the sweet spot required some trial and error though. My current setting is 128k. I ran tests with 32k and 64k. 32k is still too small and 64k was ok but I still saw random spikes which I think were caused by the cache filling up. Since there’s no way to expose the realpath cache data in PHP, there’s no way to know for sure what is happening inside it. If I have some downtime in the next week or two, I may throw together a quick extension for getting that data so I can make a quick monitor script.

In the meantime, setting realpatch_cache_size to a value between 64k and 128k should work fine for you if you’re serving Sugar off of an NFS. You’ll notice some benefits on a local disk too, but a local disk can keep up with Sugar even without the the optimal realpath caching. Keep in mind that each PHP process allocates memory for the cache, regardless of how much of it is used. For example, if your web server can handle 100 requests to PHP files and all of them run at the same time, PHP will allocate 12.5 megs of memory if your realpatch_cache_size is set to 128k.

I experimented with the realpath_cache_ttl setting some too. My tests ran the same regardless of whether it was configured to have a TTL of a few hours or the default 120 seconds.