MySQL and Apache tuning - my approach - what's yours?

[CiviTeacher.com]

I have run into some resource issues.  Let's talk about my environment first and then I'll show you what I did.  I run almost all my instances of CiviCRM on CentOS VPS systems with 2.4gz processors (it's a quad-core split 4 ways) and 1GB ram.

I use 128M to 192M PHP memory.  Sometimes I get servers bogging down, especially in big Search queries (Contacts or Contributions) and Manage Events, in particular.

I've tuned MySQL as follows, and my server admin developed these settings and we've seen improvement.  I'm going to share them here.  If you have ideas of your own (for my environment or for yours) please share.

Code: [Select]

#### my.cnf (mysql tuning)
thread_cache_size = 4
query_cache_size = 16777216
tmp_table_size = 33554432
max_heap_table_size = 33554432
table_cache = 192
innodb_buffer_pool_size = 83886080
query_cache_limit = 16777216
key_buffer_size = 67108864

#### httpd.conf apache settings
StartServers       2
MinSpareServers    2
MaxSpareServers    5
ServerLimit      100
MaxClients       100
MaxRequestsPerChild  100


If we get a lot of good ideas here I'll compile them in to a wiki page.

[demeritcowboy]

I find this is a good resource. I also saw a presentation by him that was good too - not sure if it's on the web.
http://2bits.com/articles/drupal-performance-tuning-and-optimization-for-large-web-sites.html

[Eileen]

Thanks Stoob - be interested to see what gets posted

we found we had to add these lines

tmp_table_size = 33554432
max_heap_table_size = 33554432

to get walklists to work - although I think we went for 64MB & you've got 32MB (approx)

[Michael McAndrew]

Am hoping more will post here.  Shock horror I tend to use default ubuntu (10.10) config.

Any appetite for sharing this kind of stuff via puppet or chef?

[mcarson]

Looking at your settings, I wonder why the settings are so low. Most people use low settings and adjust them higher; I have set high and start trimming memory settings until performance IS impacted. I am currently at:

Code: [Select]

thread_cache_size = 4
query_cache_size = 56M
tmp_table_size = 96M
max_heap_table_size = 96M
table_cache = 4096
innodb_buffer_pool_size = 83M
query_cache_limit = 96M
key_buffer_size = 32M

PHP Memory: 128MB - reduced from 256MB with some impact but not much...

As a result MySQL is running like "s**t off a shovel".

My apache settings close to default - however I have mod_fcgid in use, which speeds things up somewhat. Combined memory usage for Apache/MySQL/PHP is around 600MB during work hours.

It would be interesting to discover settings that have a 'perfect' balance of memory usage / performance.

[Eileen]

mcarson thanks for this - your approach makes sense and I suspect you are probably benchmarking in a more systematic way then those of use who just increase our settings when we notice a bottleneck (or our users do)

[mcarson]

Adding again to this topic...

Apache Main Configuration:

Code: [Select]


# prefork MPM (CiviCRM uses worker.c)

<IfModule prefork.c>
    StartServers  20
    MinSpareServers  20
    MaxSpareServers   20
    ServerLimit  100
    MaxClients  100
    MaxRequestsPerChild  4000
    KeepAlive  On
    MaxKeepAliveRequests  80
    KeepAliveTimeout  2
</IfModule>

<IfModule worker.c>
    StartServers  20
    MaxClients  500
    MinSpareThreads  8
    MaxSpareThreads  50
    ThreadsPerChild  50
    MaxRequestsPerChild  5000
    KeepAlive  On
    MaxKeepAliveRequests  80
    KeepAliveTimeout   15
</IfModule>


mod_fcgi configuration: (timeout is high to assist with imports)

Code: [Select]

<IfModule mod_fcgid.c>
IdleTimeout   300
ProcessLifeTime  3600
MaxProcessCount  32
DefaultMaxClassProcessCount  6
IPCConnectTimeout  60
IPCCommTimeout  600
BusyTimeout  600
</IfModule>

MySQL (my.cnf)

Code: [Select]


local-infile = 0
symbolic_links = 0
max_connections = 100

max_write_lock_count = 1
flush_time = 3600
thread_cache_size = 16
thread_stack = 256K
query_cache_size = 64M
query_cache_limit = 96M
net_buffer_length = 2K
read_buffer_size = 384K
read_rnd_buffer_size = 1M
sort_buffer_size = 256K
bulk_insert_buffer_size = 8M
max_allowed_packet = 4M
max_heap_table_size = 96M
tmp_table_size = 96M
table_cache = 512
key_buffer_size = 16M

innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 40M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 2M
innodb_thread_concurrency = 8


This setup is quite different to my previous post, but my plan is to 'open-up' our CiviCRM to about 100 users (916 max). I have one helluva task ahead.

The above config has the following impact:

• Both Apache and MySQL performance has increased allowing more simultaneous users
• The resource usage has increased significantly.
• 411MB used by MySQL and it's child processes
• 27MB max used by mod_fcgi for each spawned process
• 14MB max used by apache for each spawned process

Any advice on the above would be good. My server is going to bite the dust in about 3 weeks.

[dharmatech]

We use several tools to tweak performance: munin, devel module, xdebug/webcachegrind, mysqltuner.pl, tuning-primer.sh to name a few. We generally run Drupal/CiviCRM in a Linode 768 or 1024 container. That container gets built with a default template (we developed) and gets adjusted incrementally weeks/months after a site goes live.

A client we're currently working with, seems to enjoy the following my.cnf:
key_buffer = 8M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
max_connections = 60
table_cache = 1024
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 64M
innodb_buffer_pool_size = 145M
join_buffer_size = 2M
query_cache_min_res_unit = 2048
tmp_table_size = 128M
max_heap_table_size = 128M

and php-fcgid.conf
MaxRequestsPerProcess  1000
MaxProcessCount 20
IPCCommTimeout 240
IdleTimeout 240
ServerLimit 750
StartServers 5
MinSpareThreads 5
MaxSpareThreads 20
ThreadsPerChild 32
MaxClients 480
MaxRequestsPerChild 1200

That's not to say these settings couldn't be improved... but over the last month or two we've been tweaking and have settled on these tuning parameters. The last piece we're trying to figure out is fragmented tables and query cache prunes.

2bits has some great information on their site for tuning large Drupal sites. Khalid really knows what's he's talking about and happy to share his findings. The High Performance Drupal group is also wealth of information. Dalin chimes in frequently and is well versed on the topic.

[mcarson]

Thanks for your feedback, I need to do a bit more research. (linode looks interesting)

Fragmented tables...
This has also been bugging me, particularly since CiviCRM uses INNODB and on my server, this happens to be a single file. However, I did read somewhere that configuring mysql with innodb_file_per_table helps with the fragmentation aspect, but doesn't cure it. Perhaps I can look at this for a new server; I am too much of a newbie to mess about with this on a live server.

Another point; each time I run mysqltuner.pl (I also use Cacti), it advises massive table_cache values (I have had this over 10000 at one point). I only use this now for a quick reporting tool.

It would be helpful to know what the memory footprint of your settings are, to get a good balance of performance/memory.