huge performance issue

[jengal]

Hi,
I am using sugar 4.01 on a windows Server (CPU 1.5Ghz x 2, RAM:1Gb) , using spike package.

When i go in Calendar, the server response (logged on the bottom of) page is around 7 sec for a day with no activity.
about 10 sec for a day with activity and about 20 sec for the shared calendar.

I have about 6000 calls in my database.

Is this response delay normal ?

It s very slow.... May i add an indexed column in my mysql database ?

Thanks for any clues.

[rliebscher]

Hi,

yep, sugarcrm has some bad performance issues.

u can do something against with a good index structure in database.
To find slow queries, read this.

http://www.sugarcrm.com/forums/showt...+Query+Logging

But i think the biggest performance-issue is php itself.

Dont know, what system u use.
We have installed the fast-cgi-wrapper for php on our windowssystem, to get better performance. (Fastest on windows is, to use the php-isapi interface, but it is unstable and buggy)

But for all that, with sugarcrm and php u will never get a "speed-flush".
Ronny

[jengal]

Thx for reply.

I have investiget a bit more.
It s not a query issue. I logged all queries, each query execution time is under 0.01.
And when i watch my task manager it's Apache process that take time, not mysql.

It s really strange cause contact tab, or home tab is quick (under 1sec). But calendar is so slow ....

[stevec]

Just as a comparison, we had two test systems with 10,000 accounts, 15,000 contacts and 5000 calls and 5000 meetings.

On a 2Gz, 2GB Pentium III - Debian Linux, MySQL4, PHP4, we got server response times all well below 1 second. Calendar views were similar. Apache served the data at reasonable speed and they were rendered by IE and Firefox at reasonable speed too - though Firefox was a little faster with more complex themes. I would say most pages were being served completely in under one second. The only slow response was an unconfigued shared calendar when it tried to bring up 100 user's appointments at once then it took about 5 seconds..

On a 3GHz, 4GB dual-Intel Xeon Ubuntu Apache server with a sperate identical spec mysql server, the server side responses were about 1.5 to 2 times as fast. (Bottle neccked at the connection between the seperate database and web servers for redundancy/security).

I know your spec is lower and PHP web apps with large databases aren't the speediest things. But it doesn't sound right.

It sounds like a new installation - but with that number of contacts did you import them directly into the database?

[ravindram]

Hi,
I am using sugar 4.01 on a windows Server (CPU 1.5Ghz x 2, RAM:1Gb) , using spike package.

When i go in Calendar, the server response (logged on the bottom of) page is around 7 sec for a day with no activity.
about 10 sec for a day with activity and about 20 sec for the shared calendar.
.....

Hi,

I would like to know what is the memory usage and CPU usage of your server when you are using SugarCRM. If possible try to post your php.ini and mysql configuration files.

Then we can find why you are getting that much less performance.

I hope its slow, but not that much. you should be able to load a page in 1 sec and around it.

[jengal]

Thanks again.

Yes it s a new install.
We imported all data from another soft. (filling tables with queries)

here is config:

my.ini

[mysqld]
# set basedir to your installation path
basedir="d:/SugarCRM/oss/mysql"
# set datadir to the location of your data directory
datadir="d:/SugarCRM/oss/mysql/data"

# log="D:/SugarCRM/oss/mysql/logs/query.txt"
log_slow_queries="D:/SugarCRM/oss/mysql/logs/longue.txt"
long_query_time = 1

php.ini is very large, sorry; it s default one, nothing changed.

[PHP]
; Enable the PHP scripting language engine under Apache.
engine = On

; Enable compatibility mode with Zend Engine 1 (PHP 4.x)
zend.ze1_compatibility_mode = Off

short_open_tag = On

; Allow ASP-style <% %> tags.
asp_tags = Off

; The number of significant digits displayed in floating point numbers.
precision    =  12

; Enforce year 2000 compliance (will cause problems with non-compliant browsers)
y2k_compliance = On

output_buffering = Off

zlib.output_compression = Off

;zlib.output_handler =

implicit_flush = Off

unserialize_callback_func=

serialize_precision = 100

safe_mode = Off

safe_mode_gid = Off

safe_mode_include_dir =

safe_mode_exec_dir =

safe_mode_allowed_env_vars = PHP_

safe_mode_protected_env_vars = LD_LIBRARY_PATH

;open_basedir =

disable_functions =

disable_classes =

expose_php = On

; Resource Limits ;

max_execution_time = 30 
max_input_time = 60	
memory_limit = 8M    

error_reporting  =  E_ALL & ~E_NOTICE & ~E_STRICT

display_errors = On

display_startup_errors = Off

log_errors = Off

log_errors_max_len = 1024

ignore_repeated_errors = Off

ignore_repeated_source = Off

report_memleaks = On

; Store the last error/warning message in $php_errormsg (boolean).
track_errors = Off

;html_errors = Off

;docref_root = "/phpmanual/"
;docref_ext = .html

; String to output before an error message.
;error_prepend_string = "<font color=ff0000>"

; String to output after an error message.
;error_append_string = "</font>"

; Log errors to specified file.
;error_log = filename

; Log errors to syslog (Event Log on NT, not valid in Windows 95).
;error_log = syslog


;arg_separator.output = "&amp;"

;arg_separator.input = ";&"

variables_order = "EGPCS"

register_globals = Off

register_long_arrays = On

register_argc_argv = On

; Maximum size of POST data that PHP will accept.
post_max_size = 8M

; Magic quotes
;

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = On

; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off

; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off

auto_prepend_file =
auto_append_file =

default_mimetype = "text/html"
;default_charset = "iso-8859-1"

;always_populate_raw_post_data = On

; Paths and Directories ;

; UNIX: "/path1:/path2"
;include_path = ".:/php/includes"
;
; Windows: "\path1;\path2"
;include_path = ".;c:\php\includes"

doc_root =

; The directory under which PHP opens the script using /~username used only
; if nonempty.
user_dir =

; Directory in which the loadable extensions (modules) reside.
extension_dir = "./"

enable_dl = On

; cgi.force_redirect = 1

; cgi.nph = 1

; cgi.redirect_status_env = ;

; fastcgi.impersonate = 1;

;cgi.rfc2616_headers = 0


; File Uploads ;

; Whether to allow HTTP file uploads.
file_uploads = On

;upload_tmp_dir =

; Maximum allowed size for uploaded files.
upload_max_filesize = 2M

; Fopen wrappers ;

; Whether to allow the treatment of URLs (like http:// or ftp://) as files.
allow_url_fopen = On

; Define the anonymous ftp password (your email address)
;from="john@doe.com"

; Define the User-Agent string
; user_agent="PHP"

; Default timeout for socket based streams (seconds)
default_socket_timeout = 60

; auto_detect_line_endings = Off


; Module Settings ;

[mail function]
; For Win32 only.
SMTP = localhost
smtp_port = 25

; For Win32 only.
sendmail_from = me@example.com

; For Unix only.  You may supply arguments as well (default: "sendmail -t -i").
;sendmail_path =

;mail.force_extra_parameters =

[SQL]
sql.safe_mode = Off


[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
mysql.max_links = -1

mysql.default_port =

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

mysql.default_password =

; Maximum time (in secondes) for connect timeout. -1 means no limit
mysql.connect_timeout = 60

mysql.trace_mode = Off

[MySQLI]

; Maximum number of links.  -1 means no limit.
mysqli.max_links = -1

mysqli.default_port = 3306

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysqli.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysqli.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysqli.default_user =

mysqli.default_password =

; Allow or prevent reconnect
mysqli.reconnect = Off
[dbx]
dbx.colnames_case = "unchanged"

[bcmath]
; Number of decimal digits for all bcmath functions.
bcmath.scale = 0

[browscap]
;browscap = extra/browscap.ini


[Session]
; Handler used to store/retrieve data.
session.save_handler = files

session.save_path = "d:\SugarCRM\oss\php5\tmp"

; Whether to use cookies.
session.use_cookies = 1

; Name of the session (used as cookie name).
session.name = PHPSESSID

; Initialize session on request startup.
session.auto_start = 0

; Lifetime in seconds of cookie or, if 0, until browser is restarted.
session.cookie_lifetime = 0

; The path for which the cookie is valid.
session.cookie_path = /

; The domain for which the cookie is valid.
session.cookie_domain =

; Handler used to serialize data.  php is the standard serializer of PHP.
session.serialize_handler = php

session.gc_probability = 1
session.gc_divisor     = 100

session.gc_maxlifetime = 1440

session.bug_compat_42 = 1
session.bug_compat_warn = 1

session.referer_check =

; How many bytes to read from the file.
session.entropy_length = 0

; Specified here to create the session id.
session.entropy_file =

;session.entropy_length = 16

;session.entropy_file = /dev/urandom

session.cache_limiter = nocache

; Document expires after n minutes.
session.cache_expire = 180

session.use_trans_sid = 0

; Select a hash function
; 0: MD5   (128 bits)
; 1: SHA-1 (160 bits)
session.hash_function = 0

session.hash_bits_per_character = 4

url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset="


[Assertion]
; Assert(expr); active by default.
;assert.active = On

; Issue a PHP warning for each failed assertion.
;assert.warning = On

; Don't bail out by default.
;assert.bail = Off

; User-function to be called if an assertion fails.
;assert.callback = 0

; Eval the expression with current error_reporting().  Set to true if you want
; error_reporting(0) around the eval().
;assert.quiet_eval = 0


[Verisign Payflow Pro]
; Default Payflow Pro server.
pfpro.defaulthost = "test-payflow.verisign.com"

; Default port to connect to.
pfpro.defaultport = 443

; Default timeout in seconds.
pfpro.defaulttimeout = 30

; Default proxy IP address (if required).
;pfpro.proxyaddress =

; Default proxy port.
;pfpro.proxyport =

; Default proxy logon.
;pfpro.proxylogon =

; Default proxy password.
;pfpro.proxypassword =

[Sockets]
; Use the system read() function instead of the php_read() wrapper.
sockets.use_system_read = On

tidy.clean_output = Off

[soap]
soap.wsdl_cache_enabled=1
soap.wsdl_cache_dir="/tmp"
soap.wsdl_cache_ttl=86400

[ravindram]

Hi,

Based on your my.ini file you are not using 'Innodb' as mysql database type. Try to use InnoDB as the database type and configure innodb then you will see a lot of improvement on this.

Here are some steps you can follow for configuring InnoDB using MySQL:

Configuring MySQL – InnoDB

a.Open MySQL Administrator.
b.Go to Service Control tab.
c.There enable the Support for InnoDB.
d.Go to Startup Variables and open the InnoDB parameters.
e.There make sure that Activate InnoDB check box is checked.
f.Then Increase the buffer pool size. For example I am using a PC with 512 MB RAM. Its especially for SugarCRM application. My current sugarCRM DB occupying some 120 MB. And from RAM usage I found the sytem is using only some 100MB and remaining is free. So I used 250 MB for InnoDB. So even my Database reaches 250 MB till that time all the database will be loaded into RAM. So to retrieve any information there is no need of Disk I/O is needed. So based on this you need to decide about you environment settings.
g.With this InnoDB configuration is Over. Now your SugarCRM should work much faster while retriving the data or importing records into Database.

You can find more information from web on this.

[jengal]

Thx Ravin, but do you really think that my problem come from mysql?
If i am displaying an empty calendar i have 7 sec of Apache work (CPU 100% used) and if I time the Queries on mysql, it take less than 0.01 sec

[jmaxwell]

We have experienced this same sort of performance issue - a major lack of speed. Sugar is built with some foolishly unoptimized queries (counts for example), which fixing those helped a bit but still it's damn slow. We too have seen the 100% apache usage problem as well. Of course sugar tech support is basically no help. We are paying customers using the Pro version, have a few versions running incl. 4.2.0a and 4.0.1c. both are slow.

any ideas - post them here!

[ruchida]

To separate the issue, how much impact will it have to switch from Apache to IIS & ISAPI? (if you're using a Windows server)

Do you use eAccelerator with the Apache MPM mode? Some people say this configuration consumes 100% CPU.