Server optimizations for apache and mysql with ez framework

Monday 19 May 2008 11:13:29 pm

Hi,

i need help to optimize apache and mysql on server with CentOS 5 OS. I have tried to configure the system, but i get high apache cpu usage and sometimes also mysql cpu usage. There is free memory, but the apache is using CPU time harder. These are configurations for apache and mysql. We are using innodb engine in mysql and we have a big database (more that 1 million records).

This is apache prefork MPM configuration:

These are configuration of apache for prefork MPM:

<IfModule prefork.c>
StartServers 10
MinSpareServers 10
MaxSpareServers 50
ServerLimit 100
MaxClients 100
MaxRequestsPerChild 4000
</IfModule>

These are configuration mysql.

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0
bind-address=127.0.0.1
max_connections=300
# Set MyISAM parameters
key_buffer_size=3M
tmp_table_size=16M
# Set InnoDB parameters
innodb_file_per_table
# Set buffer pool size to 50 - 80% of availiable memory.
innodb_buffer_pool_size=1000M
innodb_additional_mem_pool_size=50M
# Set the log file size to about 25% of the buffer pool size.
#innodb_log_file_size=5M
#innodb_log_buffer_size=1M
table_cache=1024
#sort_buffer_size=3M
query_cache_type=1
query_cache_limit=1M
query_cache_size=40M
net_read_timeout=120
#long_query_time=10
#log_warnings=2
#log_slow_queries=/var/log/mysql/slow-queries.log
collation_server=utf8_unicode_ci
character_set_server=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

This is additional info that i can give you:

sar:

15:20:01 runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
15:30:01 9 189 3.56 2.57 1.98
15:40:01 3 174 3.42 2.39 2.16
15:50:01 4 183 2.34 1.70 1.85
16:00:01 9 191 1.89 2.23 2.03
16:10:01 2 190 2.74 2.25 2.05
16:20:01 5 199 3.33 3.22 2.52
16:30:01 6 199 5.30 2.98 2.43
16:40:01 3 200 2.05 2.99 2.80
16:50:01 3 204 1.15 2.05 2.39
17:00:01 9 184 2.10 2.46 2.48
17:10:01 3 206 7.46 4.99 3.56
17:20:01 7 188 3.53 4.52 3.96
17:30:01 6 210 4.39 4.49 4.16
17:40:01 4 210 3.09 4.17 4.43
17:50:01 24 214 7.55 5.64 4.87
18:00:08 16 197 11.15 7.24 5.85
18:10:01 1 185 2.34 4.54 5.59
18:20:01 5 212 4.19 3.92 4.60
18:30:01 8 214 5.97 4.13 4.28
18:40:01 3 211 1.60 3.22 4.10
18:50:01 4 200 3.39 3.34 3.61
19:00:02 6 225 2.26 2.02 2.68
19:10:01 3 202 1.82 2.14 2.43

19:10:01 runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
19:20:01 6 220 2.13 1.97 2.10
19:30:02 7 225 2.09 2.27 2.19
19:40:01 3 216 1.92 2.52 2.48
19:50:01 4 217 2.60 2.75 2.47
20:00:01 7 223 1.29 2.29 2.40
20:10:01 2 200 0.56 1.33 1.84
20:20:01 4 197 0.92 1.29 1.61
20:30:01 5 199 2.80 2.36 1.84
20:40:02 13 197 1.27 1.49 1.63
20:50:01 2 198 1.82 1.96 1.94
21:00:01 5 209 0.93 1.72 1.84
21:10:01 2 212 2.02 1.53 1.64
21:20:02 7 204 3.47 3.37 2.62
21:30:01 7 181 2.62 2.99 2.75
21:40:01 7 202 1.85 2.84 3.05
21:50:01 5 174 1.35 1.84 2.45
22:00:01 14 185 2.41 2.66 2.60
22:10:01 3 196 2.46 1.87 2.08
22:20:01 9 194 0.84 1.26 1.64
22:30:01 6 208 2.97 1.93 1.76
22:40:01 5 209 1.11 1.64 1.71
22:50:01 5 189 2.76 2.07 1.85
23:00:01 8 194 1.77 2.32 2.11

23:00:01 runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
23:10:01 4 198 1.85 2.04 2.04
23:20:01 5 200 2.29 2.23 2.11
23:30:02 5 209 2.02 2.50 2.44
23:40:01 5 202 1.50 1.91 2.22
23:50:01 4 192 1.86 1.97 2.10
Average: 5 197 2.37 2.31 2.27

top:

top - 23:11:41 up 11:24, 6 users, load average: 2.04, 2.08, 2.06
Tasks: 130 total, 1 running, 129 sleeping, 0 stopped, 0 zombie
Cpu(s): 34.3%us, 6.8%sy, 0.0%ni, 52.1%id, 6.8%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4051316k total, 3745052k used, 306264k free, 151096k buffers
Swap: 6291448k total, 69640k used, 6221808k free, 1701868k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19301 mysql 15 0 2185m 531m 4424 S 2 13.4 0:42.53 mysqld
2174 root 19 0 1347m 440m 8960 S 0 11.1 5:32.76 java
19551 apache 15 0 435m 110m 41m S 0 2.8 0:30.54 httpd
19554 apache 15 0 411m 85m 41m S 0 2.2 0:37.34 httpd
19587 apache 15 0 412m 84m 40m S 0 2.1 0:42.91 httpd
19568 apache 16 0 409m 83m 41m D 82 2.1 0:29.80 httpd
19569 apache 15 0 408m 83m 41m S 0 2.1 0:49.39 httpd
19589 apache 15 0 409m 82m 40m S 0 2.1 0:17.50 httpd
19559 apache 15 0 409m 82m 40m S 0 2.1 0:34.86 httpd
19578 apache 15 0 409m 81m 40m S 0 2.1 0:34.18 httpd
19553 apache 15 0 407m 80m 40m S 0 2.0 0:08.89 httpd
19588 apache 15 0 406m 79m 40m S 0 2.0 0:13.91 httpd
19552 apache 15 0 405m 79m 40m S 0 2.0 0:27.38 httpd
19627 apache 15 0 403m 77m 41m S 0 2.0 0:09.87 httpd
19563 apache 15 0 404m 77m 41m S 0 2.0 0:34.15 httpd
19571 apache 15 0 399m 74m 42m S 0 1.9 0:11.61 httpd
19561 apache 15 0 398m 72m 42m S 0 1.8 0:45.43 httpd
19550 apache 15 0 398m 72m 40m S 0 1.8 0:13.68 httpd
19586 apache 15 0 399m 71m 40m S 0 1.8 0:30.43 httpd
19567 apache 15 0 399m 71m 40m S 0 1.8 0:31.72 httpd
19570 apache 15 0 396m 71m 41m S 0 1.8 0:16.42 httpd
19626 apache 15 0 396m 70m 40m S 0 1.8 0:18.85 httpd
2474 mailman 18 0 186m 47m 3164 S 0 1.2 0:10.98 python
2472 mailman 18 0 184m 46m 3148 S 0 1.2 0:06.39 python
2477 mailman 15 0 183m 45m 3156 S 0 1.2 0:10.07 python
2476 mailman 15 0 180m 42m 3220 S 0 1.1 0:04.82 python
18784 root 18 0 383m 26m 9008 S 0 0.7 0:00.54 httpd
19508 root 15 0 47980 16m 1832 S 0 0.4 0:01.47 awstats.pl
2475 mailman 18 0 147m 9860 3092 S 0 0.2 0:01.50 python
2473 mailman 18 0 147m 9824 3092 S 0 0.2 0:01.37 python

/proc/meminfo

MemTotal: 4051316 kB
MemFree: 114044 kB
Buffers: 30600 kB
Cached: 994564 kB
SwapCached: 58788 kB
Active: 2735744 kB
Inactive: 929172 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 4051316 kB
LowFree: 114044 kB
SwapTotal: 6291448 kB
SwapFree: 6224192 kB
Dirty: 2096 kB
Writeback: 0 kB
AnonPages: 2639828 kB
Mapped: 30624 kB
Slab: 221560 kB
PageTables: 28176 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 8317104 kB
Committed_AS: 4205424 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 265992 kB
VmallocChunk: 34359471135 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

/proc/cpuinfo

processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 35
model name : Dual Core AMD Opteron(tm) Processor 175
stepping : 2
cpu MHz : 2210.220
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni lahf_lm cmp_
legacy
bogomips : 4421.69
TLB size : 1024 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp

processor : 1
vendor_id : AuthenticAMD
cpu family : 15
model : 35
model name : Dual Core AMD Opteron(tm) Processor 175
stepping : 2
cpu MHz : 2210.220
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni lahf_lm cmp_
legacy
bogomips : 4419.77
TLB size : 1024 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: ts fid vid ttp

This is result from dstats tool:

6 21 1 0 0 0| 2.2 2.1 2.1|1847M 147M 1662M 300M|8087B 34k| 2 0 0| 68M 6076M|1055 805
49 9 42 1 0 0| 2.1 2.1 2.1|1847M 147M 1662M 300M|5231B 41k| 2 0 0| 68M 6076M|1075 2139
----total-cpu-usage---- ---load-avg--- ------memory-usage----- -net/total- ---procs--- -swp/total- ---system--
usr sys idl wai hiq siq| 1m 5m 15m | used buff cach free| recv send|run blk new| used free| int csw
22 6 72 1 0 0| 2.1 2.1 2.1|1847M 147M 1662M 300M|4303B 19k| 1 0 0| 68M 6076M|1074 953
0 0 100 0 0 0| 2.1 2.1 2.1|1847M 147M 1662M 300M|2094B 19k| 1 0 0| 68M 6076M|1024 131
33 9 55 3 0 0| 2.1 2.1 2.1|1848M 147M 1662M 299M|3208B 19k| 3 0 0| 68M 6076M|1062 863
83 16 0 0 0 0| 2.1 2.1 2.1|1852M 147M 1662M 295M|4198B 20k| 8 0 0| 68M 6076M|1049 1320
89 12 0 0 0 0| 2.3 2.1 2.1|1855M 147M 1662M 292M|3577B 21k| 6 0 0| 68M 6076M|1060 2542
80 20 0 0 0 1| 2.3 2.1 2.1|1853M 147M 1662M 294M|4650B 53k| 3 0 0| 68M 6076M|1067 2618
38 11 51 1 0 0| 2.3 2.1 2.1|1848M 147M 1662M 299M|9861B 76k| 4 0 0| 68M 6076M|1103 709
82 17 2 0 0 0| 2.3 2.1 2.1|1852M 147M 1662M 295M|4569B 30k| 3 0 0| 68M 6076M|1085 1581
57 14 29 0 0 0| 2.3 2.1 2.1|1853M 147M 1662M 294M|5086B 34k| 3 0 0| 68M 6076M|1061 2051
60 11 29 1 0 0| 2.2 2.1 2.1|1846M 148M 1662M 301M|6029B 50k| 2 0 0| 68M 6076M|1173 1377
50 12 37 0 0 0| 2.2 2.1 2.1|1846M 148M 1662M 301M|7440B 58k| 2 0 0| 68M 6076M|1067 1785
12 3 84 1 0 0| 2.2 2.1 2.1|1847M 148M 1662M 299M|6211B 67k| 0 0 0| 68M 6076M|1092 463
18 5 74 1 0 0| 2.2 2.1 2.1|1848M 148M 1662M 299M|5356B 27k| 0 0 0| 68M 6076M|1082 810
0 1 100 0 0 0| 2.2 2.1 2.1|1847M 148M 1662M 299M|6042B 70k| 0 0 0| 68M 6076M|1085 227
2 2 97 0 0 0| 2 2.1 2.1|1847M 148M 1662M 299M|6088B 53k| 2 0 0| 68M 6076M|1076 348
39 10 49 2 0 0| 2 2.1 2.1|1848M 148M 1662M 299M|8597B 51k| 1 0 0| 68M 6076M|1082 742
44 6 50 0 0 0| 2 2.1 2.1|1848M 148M 1662M 299M|7495B 68k| 1 0 0| 68M 6076M|1071 1449
30 8 61 2 0 0| 2 2.1 2.1|1848M 148M 1662M 299M|4438B 42k| 0 2 0| 68M 6076M|1128 1226
13 3 46 39 0 0| 2 2.1 2.1|1858M 148M 1662M 289M|8227B 37k| 1 0 0| 68M 6076M|1236 338

Kernel version: 2.6.18-53.1.19.el5 x86_64

I see with mtop tool that there are slow queries related with ezsession table also.

Please help me to configure the system better and getting it to work correctly.

What changes i need to do to optimize it?

If need more info tell me what you need.

Thanks in advanced!

Iguana Information Technologies, SL - http://www.iguanait.com

Tuesday 20 May 2008 4:01:17 am

Hi Ali,

1. How much traffic does your server receive?
2. Are you using APC or any other sort of PHP accelerator?
3. Try increasing innodb_buffer_pool_size to 2G if you have enough memory.
4. If you can you should move MySQL to a dedicated box and set innodb_buffer_pool_size to 70/80% of available memory.

Ole M.

Senior Software Engineer - Vision with Technology

http://www.visionwt.com
http://www.omh.cc
http://www.twitter.com/omh

eZ Certified Developer
http://ez.no/certification/verify/358441
http://ez.no/certification/verify/272578

Tuesday 20 May 2008 1:49:01 pm

The Apache settings for min/max/spare servers are quite useless if you do not know how many concurrent http connections you have.
To size those appropriately, a simple process is this: enable mod_status, turn it on to full info, then get one of the tools that periodically poll the server-status page and display nice graphs out of the data.
Enabling mod-status will increase Apache mem usage, but after you have profiled you can turn it off.

There are some other tools that can graph also the number of connections to your db, which is a very important information for tuning, too. Cacti, collectd and rrd are your friends.

Having said that, some very general tips (seeing the stats you reported, you probably already know about these, but anyway) - mostly unrelated to cpu usage, unfortunately:

- disable .htaccess files in apache conf
- give enough shmem to your opcoded cache
- MaxRequestsPerChild is a kind of protection against memleaks/segfaults in apache+php. If you have lotsa traffic, 4000 can be low. Check out average age of your Apache process, if it is less than an hour you can raise it
- recompile apache + php as a full static app, no modules. Make sure you have the complete list of apache and php modules needed. Eliminate any you do not need.
- same goes for mysql: recompile statically, include only utf8 charset
- use unix domain socket for connecting to mysql
- You can test if using the intel ICC (not free!) gives faster code
- disable atime registration on the fs partition holding your website
- disable all unnecessary services on the server
- move awstats to another server and ship apache logs to it for processing: when it is parsing the log files is a complete cpu hog!
- configure apache to avoid unnecessary requests from browsers, eg. set Expires headers on css/js/images
- as a last resort, enable eZ static cache
- move db to a different server

Principal Consultant International Business
Member of the Community Project Board