Scaling SugarCRM and MySQL on Sun’s Coolthreads Server and Solaris Operating System
Scaling SugarCRM with MySQL on Sun’s Coolthreads server
Scaling SugarCRM and MySQL on Sun’s Coolthreads Server and Solaris Operating System
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.
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.
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/
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)
Updated PATH environment variable
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
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
made sure that data dir. is owned by mysql. created the mysql db instanc and started mysql smf service
Now rest of the configuration using the web browser
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|
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
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.
Number of Records
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.
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
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.
Create / Delete
(List View, Detail View)
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|
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)||
|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
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:
#./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
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
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.
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
AddType application/x-httpd-php .php .phtml
AddType application/x-httpd-php-source .phps
CacheEnable mem /
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.
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
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
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
Some of these settings are pretty standard except however we did make changes to the default apc settings.
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.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”
#APC 3.0.16 tuning below
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.
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.