Faststack Install - Performance Tuning Ideas

[sgarbade]

Greetings,

I am new to SugarCRM and not particularly knowledgable about Sugar (although I have learned a ton in a short period of time). I have been reading about performance tuning via the Wiki and also the forums and I have a few questions that I would like some input on. I am looking for two type of solutions, short term and long term.

Here is the issue...

Response time is sometimes slow and sometimes snappy. The "home" page loads in approx 4.56 seconds and the Leads tab in approx .98 seconds. These values represent the snappy side of things. When things are on the slower side, I have had remote users state that it is taking them 5-6 seconds to load the leads page. Although this sounds snappy, when you are trying to work with a lead on the phone it can be frustrating. Additionally, when things get bogged down, we sometime get a blank white page or a white page with just a firstname and lastname field.

My environment...

-WAMP FastStack running on Windows 2003 running as a VM in ESX 3.5.
-SugarCRM 5.0.0g (Build 3391)
-8 GB RAM Allocated
-2 Processors Allocated
-7 full time users
-Approx 1800 active records (4000+ if you include the deleted records still tracked in the DB)
-Users connect over HTTPS using a self signed cert in Apache (since it is being used by employees from outside our network in another state).
-Customization - A custom leads form was created with custom fields. Most tabs are turned off excpet Home, Calendar, Activities, Emails and Inquiries (leads)

Additional Challenges...

-Users have very old/slow computers and I do not know what kind of impact this may be having yet as I have not been able to evaluate their systems.
-It is possible that their Internet connectivity is less than optimal. I am trying to gether more info on this also.

Solutions - Short Term

I am trying to figure out if there are any tweaks I can make to improve performance:

- I have read about implementing HTTP compression.

Based on experience - how much would this really help?

Would this also lighten the impact of having my users connect over HTTPS ( I am assuming yes)?

- I was curious about MYSQL tweaks. From what I have read it does not seem to make sense to convert the DB to INNODB as I have only a few users and couple thousand of leads. I have seen a few posts about tweaking DB memory settings but since I don't really know the impact and there were no clear guidelines that I could find, I wanted some feedback first. Would it make sense to tweak any of the DB memory settings? For instance, my setting are as follows in my.ini and are defaults from the faststack install.

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# set basedir to your installation path
basedir=C:/PROGRA~1/SUGARC~1.0G/mysql
# set datadir to the location of your data directory
datadir=C:/PROGRA~1/SUGARC~1.0G/mysql\data
port=3306
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-Are there any other tweaks I could make to improve perfromance in the short term on the faststack install.

Solutions - Long Term

-Rebuild this environment on a LAMP stack and possibly seperate the Web tier from the DB tier

Do you think I would see a difference using a LAMP stack. I only have 7 users and approx 2000 leads in the DB?

-Engage a SugarCRM expert to do performance tuning.

Any and all of your thoughts would be appreciated. Thanks in advance for all your help and support.

-Steve

[mapm]

Try firefox plugin yslow, it will sugest some stuff that is helpful, the main things are related to compression and content distribution.
My main concern regarding SugarCRM is the size of the generated pages, with compression you may gain some speed...

[ptemplin7]

I would also take a look at your network. Are you monitoring network usage as well as response times?

Are you using unmanaged switches? any hubs in the network? are you running gigabit or 10/100? What else is on the server. Is Sugar running as a VM or alone?

Is sugar local or external? Any proxies in the way?

I can think of lots of things that would be causing this. Sometimes it Sugar but it sounds like you have a busy network.

Looking forward to more info and if you found anything,

Paris

[ptemplin7]

In reply to your last couple of lines. Our company does Sugar/VOIP installs and has some networking options that you may want to consider. I would be very interested in seeing what info you have about the network. Your DB is really not all that large.

PAris

[sgarbade]

Greetings,

Thanks to everyone for their thoughts. I did find one setting (that I thought I had turned off previously) that made a huge difference and that was the setting to verify the IP address. Once I turned that off, things got a lot quicker.

Some additional answers...

My users are outside of my corporate network and they are accessing sugar via HTTPS. I did find that they are having some network issues also. So the ultimate plan is to rebuild this server on a standalone server and put that server on their local network so they do not have to go over the internet or use HTTPS.

The next question that I need to answer is LAMP vs WAMP. I could build it on a LAMP stack, however, it is harder for me to administer since I am not as proficient with LAMP as I am with WAMP.

With 200 leads and 7 user will LAMP make much of a difference?

Thanks,

-Steve

[ptemplin7]

Hi Steve,

LAMP vs WAMP, with that few users and leads etc... Probably little or no difference.

The fact thy are using HTTPS to access probably give s the primary reason for the slowness (as well as that pesky IP!) Remember that the internet (and your outbound connection on BOTH sides) generally are significantly or excessively slower than the regular network. Also, one thing to consider is that many firewalls (remember there are two!) usually have a max throughput. I wouldn't be surprised to see that you are pushing up against the max ceilings of the outbound pipe and/or the capability of the firewall.
BTW what firewall type do you use? Is it a simple Linksys/netgear/Cable modem? or something else? What else do you have piled ont hat server that is handling Sugar? How much Cache and memory does it have? Using Windows Server also will slow things down. Have you thought about adding a Linux stack instead for the Sugar hosting? There are some open source firewalls/server that can handle this even better than the Linux stack installer Sugar has.

Good luck and happy computing,
Paris

[sgarbade]

Greetings,

We are currently using a Checkpoint firewall. I doubt that it is maxed out as there is little traffic going through it right now. I think the issue is more on their end in terms of networking/bandwidth issues.

Therefore, I am going to rebuild this on a dell 1U pizza box with dual quad procs and 4GB RAM. Nothing else is on the box. I am going to stick with the WAMP stack since I am more proficient with it and since I only have a limited amount of users/leads I don't think the LAMP stack will make that large a difference. I will ship it to them so they can put it on their local network and avoid using HTTPS (internally) which should speed things up.

Thanks for all the suggestions...

-Steve

[jjwdesign]

Just throwing in a quick response.

8GB of memory available. Hell, you could probably hold your entire MySQL daatabase in memory. That would speed up things! But seriously, don't overlook the power of INNODB database tables and the ability to cache queries and data results. It has drastically improved performance on my 1GB mem Plesk system.

[agcopley]

Similarly a quick response...a simple way to isolate the problem...network or server is log on to the server directly or remotely, bring up a browser, and check the performance of Sugar on the server itself.

[sgarbade]

How could I go about loading the whole DB into memory?

Thanks,

-Steve