Scaling SugarCRM with MySQL on Sun’s Coolthreads server

 

Scaling SugarCRM and MySQL on Sun’s Coolthreads Server and Solaris Operating System

Updates 4/9/2008

I have since done some more work on this project and found that the system could scale up to 900 concurrent users with further tuning to mysql. I have updated the graph and the relevant configuration details.

Introduction

In case you are not familiar with SugarCRM. It is a open source Customer Relationship Management software that runs on Apache, MySQL and PHP (aka AMP stack). Here I would like to show how we scaled SugarCRM on Sun’s Coolthreads Server running Solaris Operating system. The results are generated with the SugarCRM 4.5.1i with the specific configuration mentioned in this document and the work is in progress.

Installation

Installing SugarCRM on Solaris requires the AMP (Apache2, MySQL5, and PHP5) stack. I have followed the steps below to get the optimized AMP stack for Solaris Operating System. You may want to try the Open Solaris latest build that comes pre-installed with Web Stack more information can be found here http://opensolaris.org/os/project/webstack/

 

Cool Stack Web Site is available on-online at: http://cooltools.sunsource.net/coolstack/ Downloaded Cool Stack 1.2. Specifically the following required packages to get SugarCRM running to /var/tmp. /var/tmp/CSKamp_1.2_sparc.pkg.bz2 /var/tmp/CSKphplibsbundle_1.2_sparc.pkg.bz2 /var/tmp/CSKruntime_1.2_sparc.pkg.bz2 we also downloaded 64bit MySQL as the default CSKamp package only comes with 32bit MySQL version. /var/tmp/CSKmysql_1.2_sparc.pkg.bz2

Installing Coolstack

After downloading the stack to a temporary directory as indicated above. The following steps would install the Coolstack on the system. Also make sure that you update your PATH environment variable. The syntax will vary based on your shell. I have provided info on Bourne Shell below. Please note that Coolstack 1.2 also released a patch for dtrace, apc, perl and lighttpd packages. Although we did not use the apc provided by coolstack. We did apply the patches. More information on the patches can be found here http://cooltools.sunsource.net/coolstack/csk12patches.html. You could achieve similar results without coolstack as long as you are using the latest versions of AMP.

Coolstack 1.2 comes with the following specific versions of AMP:

• Apache 2.2.6
• PHP 5.2.4 and APC 3.0.14
• MySQL 5.0.45

 

Uncompressed and Installed Packages (accepted default values where needed. Installed with ‘root’ privileges) cd /var/tmp bunzip2 CSKruntime_1.2_sparc.pkg.bz2 bunzip2 CSKamp_1.2_sparc.pkg.bz2 bunzip2 CSKphplibbundle_1.2_sparc.pkg.bz2 bunzip2 CSKmysql_1.2_sparc.pkg.bz2 By default the application installs in the default dir. /opt/coolstack directory pkgadd -d ./CSKruntime_1.2_sparc.pkg pkgadd -d ./CSKamp_1.2_sparc.pkg (skipped mysql32 bit installation while installing as we planned to install 64bit. version) pkgadd -d ./CSKphplibbundle_1.2_sparc.pkg pkgadd -d ./CSKmysql_1.2_sparc.pkg Updated PATH environment variable export PATH=/opt/coolstack/php5/bin:$PATH

Installing SugarCRM

At the time of testing these scripts SugarCRM 4.5.1i was available for download. I’m providing the steps that I followed to install SugarCRM 4.5.1i below. A more detailed performance tuning data for SugarCRM 5.0 will be made available soon. Please note that a production environment may need additional security related changes that are not covered below. You would also need /etc/my.cnf with the default values before you run the following commands. The configuration we used is provided toward the end.

We installed SugarCRM 4.5.1i with the default configuration. We did not install the demo data as we used a separate kit to seed the database with 1200 users. More details on seeding the database can be found in the preparing database section below.

 

We followed these steps to install SugarCRM. Downloaded SugarCRM from http://www.sugarforge.org/frs/download.php/3733/SugarOS-4.5.1i.zip and followed the installation instructions available here Downloaded SugarOS-4.5.1i.zip to the DOCROOT directory of Apache2. Change the ownership of SugarOS-Full-4.5.1i to webservd and group webservd and make sure apache is running cd /opt/coolstaack/apache2/htdocs unzip SugarOS-4.5.1i.zip chown -R webservd:webservd SugarOS-Full-4.5.1i /opt/coolstack/apache2/bin/apachectl restart made sure that data dir. is owned by mysql. created the mysql db instanc and started mysql smf service ls -l /db/data drwx—— 5 mysql mysql 512 Jun 6 20:39 /db/data/5.0 cd /opt/coolstack/mysql/ bin/mysql_install_db –user=mysql –datadir=/db/data/5.0 svcadm enable csk-mysql #(should show online status) svcs -a | grep csk-mysql Now rest of the configuration using the web browser firefox http://localhost/SugarOS-Full-4.5.1i/index.ph

Benchmark Configuration

The benchmark configuration consists of Sun SPARC Enterprise T5220 server. We used single server to host both MySQL database and Apache HTTP Server. A separate Storage device Sun ST2540 is attached to the server for MySQL database. This storage is mounted as two volumes one for table spaces (/db/data/5.0) and the other for log files (/db/logs/5.0).

Sun SPARC Enterprise T5220

Sun StorageTek2540

Hardware Configuration

Sun SPARC Enterprise T5220 Server

• 1 UltraSPARC-T2 Processor 1.4GHz (8 Cores, 8 h/w threads per core)
• 16GB RAM
• 2 x 146GB, 10K RPM Internal SAS Disks
• 4 x 10/100/1000Mbps Ethernet, 2 PCI-X, 4 PCIe

Sun StorageTek ST2540

• 365GB (5 x 73GB, 15K RPM SAS Disks)
• 2 x 4Gb/s FC Host Connections

Preparing Database

We used a data generator kit provided by SugarCRM to generate the seed data. Using the kit SugarCRM database is populated with 1200 users [and a database proportional to 10,000 accounts] that breaks down to the following number of records for each module/business object.

 

Business Objects	Number of Records
Accounts	10000
Calls	24000
Emails	26000
Contacts	24000
Leads	14000
Opportunities	30000
Cases	14000
Bugs	3000
Meetings	9000
Tasks	13000
Notes	13000
Total Records*	*521,730

A total of 521,730 records were inserted to initialize the database for the tests. It includes the main objects and the relationship rows that link the data together.

Load Generation

We relied on Sugar to provide us with a kit to test the load on the server. Luckily they already had fully automated script that uses Apache JMeter to generate the load. All the tests were done using the kit provided by Sugar. We noticed that at higher loads JMeter was spending more time in GCs than driving the load. The following changes were made to the default JVM options to reduce the GC pause times. Here’s the JMeter tuning option that worked for us:

 

-server -d64 -Xms6128m -Xmx6128m -Xmn2559m -XX:MaxTenuringThreshold=2 -XX:InitialSurvivorRatio=4 -Xloggc:/tmp/gc.log -Dsun.rmi.dgc.client.gcInterval=600000 -XX:+AggressiveHeap -XX:+UseParallelGC -XX:+UseParallelOldGC -Dsun.rmi.dgc.server.gcInterval=600000 -verbose:gc -XX:+PrintGCDetails -XX:+PrintGCTimeStamps

Load Test Scenario

A test scenario used for performance or benchmark should closely represent a typical CRM users interactions with the server. Here’s a scenario that we used to load test the server. Each user goes through the following steps. The test scenario is repeated for all the concurrent users with randomness introduced to accessing the Business Objects while keeping the Login/Logout constant. Here’s a list of the Business objects that are accessed during the test.

 

	Business Object Module	Create / Delete	Browse (List View, Detail View)
1	Login	–	Home Page
2	Contacts	Yes	Yes
3	Accounts	Yes	Yes
4	Opportunity	Yes	Yes
5	Lead	Yes	Yes
6	Email	Yes	Yes
7	Notes	Yes	Yes
8	Cases	Yes	Yes
9	Tasks	Yes	Yes
10	Logout	–	Home Page

 

Establishing Baseline

As with any performance tuning project establishing a baseline is important to measure the effectiveness of the tuning. We recorded the baseline performance for 100 concurrent users with the default configuration of apache, mysql and php. Since SugarCRM is a user interactive application and hence measuring the response times along with the number of concurrent users provides a good measure of success. Without applying any tuning the application measured as shown below:

 

Concurrent Users	Response Times	Total Number of Requests	Sum % of Requests
100	2 seconds	9536	71.72

These numbers represent that at 100 concurrent user load for a total of 9,536 web requests only 71.72% of them made it in less than 2 seconds Response Time. Or in other words close to 30% of the time the user was waiting for the server to respond for more than 2 seconds. The user perceives that the server is slow in this kind of situation. This is the default performance without any tuning applied to the stack, SugarCRM or the Solaris operating system.

Scaling Number of Concurrent Users

From a baseline of 100 concurrent users we scaled the server to 900 concurrent users at the same time maintaining less than 2 second response times for more than 90% of the web requests.

The above graph shows the number of web requests to the SugarCRM server shown on Y-axis and the time the server took to respond back for a given request in seconds is shown on the X-axis.

 

Concurrent Users	Total Web Requests	% of Requests Under 2 Second Response Time
100 (Baseline)	9536 (60min run)	71.72%
200	10532 (30min run)	93.62%
300	15577 (30min run)	92.52%
400	20180 (30min run)	91.28%
500	24359 (30min run)	96.5%
600	28362 (30min run)	95.43%
700	65594 (60 min run)	92.33%
800	42972 (30 min run)	92.89%
900	48347 (30 min run)	90.10%

Response Time is calculated from the time a request for a business object is made till all the related resources are received by the client browser (includes images, css, js files) per request. If you offload the static content to a separate server the results could be better than what we see here since the apache server would be doing only dynamic content generation. It should be noted however that the system could support more users than we tested as the following vmstat output confirms. We found that the single client for load generation was not scaling well. We will follow up with multiple client load generation and conclude the max concurrent users supported by this platform soon.

 

#vmstat output at steady state  kthr      memory            page            disk          faults      cpu r b w   swap  free  re  mf pi po fr de sr s1 s2 s3 sd   in   sy   cs us sy id 0 0 0 44821464 43882784 15 172 0 0 0 0  0  0  0  0  8 10777 87164 13865 43 2 55 0 0 0 44800368 43803168 18 236 0 0 0 0  0  0  0  0 22 10870 87505 13341 43 2 55 0 0 0 44829320 43752280 15 337 0 0 0 0  0  0  0  0 21 12008 92601 15902 45 2 52 0 0 0 44725232 43641104 14 386 0 0 0 0  0  0  0  0 26 11707 94303 14516 47 2 50 0 0 0 44665184 43579904 19 399 0 0 0 0  0  0  0  0 33 11687 97145 15574 53 3 45 0 0 0 44658600 43601448 15 279 0 0 0 0  0  0  0  0 30 11906 96558 14861 48 2 49 0 0 0 44611776 43560232 18 391 0 0 0 0  0  0  0  0 33 11750 97666 14828 49 3 48

Tuning Options Used

APC (Alternative PHP Cache) that caches the PHP opcodes that comes bundled with the Coolstack 1.2 is a good start. However we compiled APC the latest version of APC 3.0.16 with the following options using Sun Studio compilers:

 

#cd APC-3.0.16  #phpize #./configure –enable-apc –enable-apc-sem –enable-apc-mmap –with-apxs=/opt/coolstack/apache2/bin/apxs –with-php-config=/opt/coolstack/php5/bin/php-config #make #make install

Although eAccelerator is also believed to give similar or better results we have not tried it yet. The performance of APC as measured during the load tests of SugarCRM is shown below:

Solaris 10 Operating System:

We have applied the following tuning options to the /etc/system file

 

set sq_max_size=0 set rlim_fd_max=32768 set rlim_fd_cur=32768 set ip_squeue_soft_ring=1 set ip:ip_soft_rings_cnt=8 set autoup=900

Also to support higher http requests the following ndd parameters were applied.

 

ndd -set /dev/tcp tcp_conn_req_max_q 16384  ndd -set /dev/tcp tcp_conn_req_max_q0 16384 ndd -set /dev/tcp tcp_naglim_def 1 ndd -set /dev/tcp tcp_smallest_anon_port 2048

Apache2 Configuration Optimized for SugarCRM

The default configuration of Apache httpd.conf loads several modules that are not needed to run SugarCRM. Depending on your web site requirements you can disable most of them. We have used the following minimal set of modules to successfully run SugarCRM.

 

#httpd.conf  ServerRoot «/opt/coolstack/apache2» Listen 0.0.0.0:80 LoadModule authz_host_module modules/mod_authz_host.so LoadModule cache_module modules/mod_cache.so LoadModule mem_cache_module modules/mod_mem_cache.so LoadModule log_config_module modules/mod_log_config.so LoadModule env_module modules/mod_env.so LoadModule mime_magic_module modules/mod_mime_magic.so LoadModule expires_module modules/mod_expires.so LoadModule headers_module modules/mod_headers.so LoadModule setenvif_module modules/mod_setenvif.so LoadModule mime_module modules/mod_mime.so LoadModule suexec_module modules/mod_suexec.so LoadModule cgi_module modules/mod_cgi.so LoadModule php5_module modules/libphp5.so <IfModule !mpm_netware_module> User webservd Group webservd </IfModule> <IfModule mime_module> PHPINIDir /opt/coolstack/php5/lib AddType application/x-httpd-php .php .phtml AddType application/x-httpd-php-source .phps </IfModule> EnableMMAP Off EnableSendfile Off Include conf/extra/httpd-mpm.conf <IfModule mod_mem_cache.c> CacheEnable mem / MCacheSize 32768 MCacheMaxObjectCount 8192 MCacheMinObjectSize 1 MCacheMaxObjectSize 2048 </IfModule>

#httpd-mpm.conf  <IfModule mpm_prefork_module> StartServers 1200 MinSpareServers 2000 MaxSpareServers 2000 MaxRequestsPerChild 0 ListenBacklog 8192 ServerLimit 2500 MaxClients 2500 </IfModule>

MySQL Tuning

Before you create the database please make sure that the following file is saved in /etc/my.cnf so that it picks the InnoDB configuration while creating the tablespaces and the logfiles. The settings applied to query cache, table cache and thread concurrency helps at higher loads and reduces the latency.

 

#/etc/my.cnf  [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = «/db/data/5.0» back_log = 50 max_connections = 3000 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 2M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 32M join_buffer_size = 32M thread_cache_size = 3000 thread_concurrency = 3000 record_buffer = 8M query_cache_size = 256M query_cache_type = 1 query_cache_limit = 4M query_prealloc_size=65536 ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 512M log_long_format server-id = 1 key_buffer_size = 32M read_buffer_size = 4M read_rnd_buffer_size = 32M bulk_insert_buffer_size = 64M innodb_additional_mem_pool_size = 256M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:4096M:autoextend innodb_data_home_dir = /db/data/5.0 innodb_file_io_threads = 8 innodb_thread_concurrency = 0 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_log_group_home_dir = /db/logs/5.0 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120

Also MPSS and libumem libraries were pre-loaded by making changes to the mysqld_safe script. More information regarding MySQL performance tuning can be found here: http://blogs.sun.com/yufei/entry/mysql_innodb_performance_tuning_on

 

#mysqld_safe file modified to pre-load MPSS and libumem libraries  MPSSHEAP=256M export MPSSHEAP LD_PRELOAD_64=»/usr/lib/sparcv9/mpss.so.1 /usr/lib/sparcv9/libumem.so.1″ export LD_PRELOAD_64

PHP Tuning

Some of these settings are pretty standard except however we did make changes to the default apc settings.

 

[PHP]  cgi.fix_pathinfo = 1 memory_limit = 320M ; Maximum amount of memory a script may consume (8MB) default_socket_timeout = 1800 safe_mode = 0 post_max_size = 20M upload_max_filesize = 20M [Session] session.use_cookies = 1 session.cookie_lifetime = 0 session.gc_probability = 1 session.gc_divisor = 5000 session.gc_maxlifetime = 6000 session.entropy_file = «/dev/urandom» ;session.cache_expire = 300 ;session.cache_limiter = nocache session.save_path = «/tmp/sessions» include_path=/opt/coolstack/php5/lib/php:.: extension_dir=/opt/coolstack/php5/lib/php/extensions/no-debug-non-zts-20060613 extension=»mysql.so» extension=»mysqli.so» extension=»curl.so» extension=»zlib.so» #APC 3.0.16 tuning below extension=»apc.so» apc.enabled=1 apc.shm_segments=1 apc.shm_size=32 apc.optimization=0 apc.num_files_hint=2048 apc.user_entries_hint=4096 apc.ttl=0 apc.user_ttl=0 apc.gc_ttl=3600 apc.cache_by_default=1 apc.mmap_file_mask=/dev/zero apc.slam_defense=0 apc.file_update_protection=2 apc.enable_cli=0 apc.max_file_size=1M apc.stat=0 apc.write_lock=0 apc.report_autofilter=0 apc.include_once_override=1 apc.localcache=0 apc.localcache.size=512

SugarCRM Tuning

We applied the standard tuning recommended for SugarCRM along with some of the suggestions made here http://www.sugarcrm.com/wiki/index.php?title=Performance_Tweaks_for_Large_Systems. It would be interesting to see how SugarCRM performs with memcached when it supports but that’s probably for another day.

Conclusion

In our tests we found that the Sun’s Coolthreads server can scale to large number of concurrent users on a single server running both SugarCRM and MySQL. There is not much performance impact if the MySQL is run inside Solaris Containers vs running it in the global zone along with SugarCRM. Sun’s Coolthreads server does scale well with the SAMP stack based applications more specifically SugarCRM and MySQL database.

Posted at 12:57AM Feb 26, 2008 by Satish Vanga in Sun |  Comments[9]