Sugar Performance High Response Times

[websitedesigner]

Hi All I've read some of the threads on here about response times but I'm not really sure where to start. I've noticed on our installation of Sugar which is on an Australian VPS is running very slow. Its not uncommon to get response times in the 5 second range. It can be as quick as 2 seconds. Another instance of Sugar I have which is hosted on our other VPS over in the states seems a lot quicker - usually pages load within 1 second or a bit more if they are loading say 30 or 50 leads or accounts or something.

This is going off the response time in the footer but I also checked loading in the Studio which seems to take the longest and on the studio page there is no response time indcator but I timed it and the AUS one was about 9 seconds and the US one about 8 seconds. As a comparison my main website which is a database-driven site will take 1-2 seconds to load a page.

It just seems like its taking too long and while I am a little bit concerned about the performance of our server I've seen Sugar running on 3 different servers and it always seems pretty slow.

I guess my question is are these sorts of response times normal and is there anything we can do to speed them up. Here is some information on the system and the AUS server.

Sugar Version - Community 6 - the latest
Data - My system holds 63 leads, 700 accounts, 900 contacts, 2500 opportunities (because we are using our MYOB import to hold all of our sales in there)
OS - Its a Linux Apache VPS running WHM / cPanel 768 Meg RAM, 20 gig disk space 14 gig used
There are only a handful of sites on our server it doesn't use much bandwidth
We are on ADSL2 in the Brisbane Technology Park pretty quick internet.
PHP 5 MySQL5

I think that is about all the info I can give, I would very much appreciate some advice.

[websitedesigner]

any ideas?

[eitrix]

i suggest you take a look at this thread:

http://www.sugarcrm.com/wiki/index.p..._Large_Systems

If we are talking truth, sugarcrm ain't the fastest system around, but it gets job done

[websitedesigner]

Hi mate I've read that but I don't really know where to start with that. It's not a large system and it seems to always be slow so I guess by the sounds of its just slow across the board. But it really is borderline unusable the amount of time it takes to do things.

[southwestcrm]

768MB RAM = never fast
2GB RAM = sometimes fast
4GB RAM = always fast

Most likely reason

[franckt]

Well.. lots of people complains about sugar response times.... and I'm part of it.
But for months, I try to get a solution for this problem and I can't and I haven't read any serious answer through this forum.
Each time, the same inadequate answer like "developer mode" to be off, or disable_count_query... or log slow query (I never had any slow query in this log.. while sometimes I have to wait more than 2 mn to get 20 accounts on the Accounts module home !!)
I have tried to manage my sql servers variable... but nothing make the response time decrease.
So it should be somewhere in the core code... may be the tracker constant appeal and writing ? may be a lack of relevant indexes on the tables ? may be all these database fields defined as NULL ? may be all theses queries with left join instead of inner join (like I always used) ? may be also all these varchar fields (from drop down sugarmade... instead of having tinyint value with a join table) which consumes a lot when you do a search ? may be also these 36 chars primary keys (I have read several times the guide and understand the utility.. especially when important data from other systems) but I always developed mysql database with smallint or mediumint or int(11) as primary key ?

Is there anyone who can help or give a path to a correst answer ?

If any Pro version people can tell me if they have such problem ir not, I will be pleased because I intend to upgrade to the pro version (because I really ike Sugar and I have been developing a lot the last 8 months.... from now, I only a single file and precisely only an added line in a single file which is not upgrade safe)

Sincerely

I'm using sugarcrm CE 6.01..... updated months after months from primary version 5.02k
I work locally on windows XP with 4bg ram with a lamp environment
and it's worse online on a linux sytem

[southwestcrm]

Well.. lots of people complains about sugar response times.... and I'm part of it.
But for months, I try to get a solution for this problem and I can't and I haven't read any serious answer through this forum.
Each time, the same inadequate answer like "developer mode" to be off, or disable_count_query... or log slow query (I never had any slow query in this log.. while sometimes I have to wait more than 2 mn to get 20 accounts on the Accounts module home !!)
I have tried to manage my sql servers variable... but nothing make the response time decrease.
So it should be somewhere in the core code... may be the tracker constant appeal and writing ? may be a lack of relevant indexes on the tables ? may be all these database fields defined as NULL ? may be all theses queries with left join instead of inner join (like I always used) ? may be also all these varchar fields (from drop down sugarmade... instead of having tinyint value with a join table) which consumes a lot when you do a search ? may be also these 36 chars primary keys (I have read several times the guide and understand the utility.. especially when important data from other systems) but I always developed mysql database with smallint or mediumint or int(11) as primary key ?

Is there anyone who can help or give a path to a correst answer ?

If any Pro version people can tell me if they have such problem ir not, I will be pleased because I intend to upgrade to the pro version (because I really ike Sugar and I have been developing a lot the last 8 months.... from now, I only a single file and precisely only an added line in a single file which is not upgrade safe)

Sincerely

I'm using sugarcrm CE 6.01..... updated months after months from primary version 5.02k
I work locally on windows XP with 4bg ram with a lamp environment
and it's worse online on a linux sytem

Maybe if you detailed the server spec you are running under, someone can help??
It could be the server RAM, or your broadband speed, or poorly configured mysql database

[franckt]

First of all thanks for answering.
Locally, it's not a problem (and it's quite reasonable in term of response times)



Here it is my webserver configuration
Gentoo Base System version 1.6.14
Apache 2.0.59
mysql api version 5.0.44
php 5.2.5-pl1-gentoo

with 512Mb of RAM

Here, you can have a look to the tuning primer report :
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5 sec.
You have 425 out of 12784 that take longer than 5 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.0/...ster-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 6
Current threads_cached = 5
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 20
Current threads_connected = 1
Historic max_used_connections = 6
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 71 M
Configured Max Per-thread Buffers : 41 M
Configured Max Global Buffers : 58 M
Configured Max Memory Limit : 100 M
Physical Memory : 490 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 16 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 67
Key buffer free ratio = 86 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 1000 K
Current query_cache_used = 743 K
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 74.31 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 508 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 1230 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 600 tables
You have a total of 885 tables
You have 600 open tables.
Current table_cache hit rate is 63%
, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 1363 temp tables, 19% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 252 K
Current table scan ratio = 25 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 12973
Your table locking seems to be fine

[southwestcrm]

with 512Mb of RAM

There you go. There's you problem. Up it to 4Gig and Sugar will fly.

[franckt]

Hummmmm.....
I'm reading your answer with lots of sarcasm...
I won't be satisfied with such kind of easyness.
Sorry !!!
It seems U didn't read my 1st post..
Once again, nobody wants to share his experience for making Sugar faster in term of response times.