Last week I installed SugarCRM for arsenic.ph to try it out. While I was playing around with adding new users and roles, I noticed that it took over a minute for SugarCRM to finish creating one user. I began investigating and slow queries notwithstanding, MySQL turned out to be the performance bottleneck. This server is still new and I still have not come around to tuning MySQL as I have already begun moving most of my work to Postgres. Unfortunately there are still a large number of good software packages out there that require MySQL so I was left with no choice but to tune it.

Running sql-bench/run-all-tests from the mysql-benchmark suite I got the following:

alter-table: 
Total time: 14 wallclock secs ( 0.01 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.02 CPU)
ATIS: Total time:  3 wallclock secs ( 2.58 usr  0.10 sys +  0.00 cusr  0.00 csys =  2.68 CPU)
big-tables: Total time:  5 wallclock secs ( 2.33 usr  0.17 sys +  0.00 cusr  0.00 csys =  2.50 CPU)
connect: Total time: 49 wallclock secs (12.93 usr  7.55 sys +  0.00 cusr  0.00 csys = 20.48 CPU)
create: Total time: 59 wallclock secs ( 1.06 usr  0.50 sys +  0.00 cusr  0.00 csys =  1.56 CPU)
insert: Total time: 853 wallclock secs (151.16 usr 20.44 sys +  0.00 cusr  0.00 csys = 171.60 CPU)
select: Total time: 42 wallclock secs (13.51 usr  1.32 sys +  0.00 cusr  0.00 csys = 14.83 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time:  3 wallclock secs ( 1.18 usr  0.27 sys +  0.00 cusr  0.00 csys =  1.45 CPU)

As I had suspected, insert performance was way too slow. In this case it took over 14 minutes to run the entire test-insert suite which runs about 500,000+ queries. Normally this would not be an issue but SugarCRM has a lot of inserts to do when it creates a new user or a new role. Clearly something needs to be done about slow inserts. Turn out MyISAM is not really good when it comes to inserts so MySQL needs a little help on the configuration side to improve things.

After about half an hour of playing around with /etc/my.cnf I finally managed to cut down execution time for inserts while keeping the other operations at almost the same level of performance. The results:

alter-table: Total time: 14 wallclock secs ( 0.01 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.02 CPU)
ATIS: Total time:  3 wallclock secs ( 2.58 usr  0.08 sys +  0.00 cusr  0.00 csys =  2.66 CPU)
big-tables: Total time:  5 wallclock secs ( 2.32 usr  0.21 sys +  0.00 cusr  0.00 csys =  2.53 CPU)
connect: Total time: 46 wallclock secs (13.42 usr  6.37 sys +  0.00 cusr  0.00 csys = 19.79 CPU)
create: Total time: 60 wallclock secs ( 1.07 usr  0.59 sys +  0.00 cusr  0.00 csys =  1.66 CPU)
insert: Total time: 439 wallclock secs (141.66 usr 21.48 sys +  0.00 cusr  0.00 csys = 163.14 CPU)
select: Total time: 43 wallclock secs (13.57 usr  1.40 sys +  0.00 cusr  0.00 csys = 14.97 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time:  3 wallclock secs ( 1.21 usr  0.24 sys +  0.00 cusr  0.00 csys =  1.45 CPU)

That’s a 64% improvement in the insert benchmark, the rest of the benchmarks are largely unchanged. The whole shebang of settings are as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
back_log = 75
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_min_res = 2K
query_cache_limit = 4M
query_cache_size = 32M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072

Most of these settings came from here: http://www.debianhelp.co.uk/mysqlperformance.htm. I played with some of the settings based on sql-bench results in addition to using mysqlreport to further fine-tune the settings. You might need to adjust your own settings to match your needs though. As for me, I’m happy with the results although I know things can be further tuned for performance using OS-level tweaks. But as far as I can tell, OS-level tweaks will only cause marginal performance improvements anyway.

Oh, and as for SugarCRM I removed it and used vTiger instead,

This entry was posted on Saturday, May 17th, 2008 at 10:12 pm and is filed under CentOS/RHEL, MySQL, System Administration. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.