How to Tuning Apache and Mysql

Question

top - 12:24:22 up 1 day, 18:37,  2 users,  load average: 1.19, 1.77, 1.59
Tasks: 166 total,   4 running, 162 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.1%us,  5.8%sy,  0.0%ni, 62.4%id, 10.8%wa,  0.0%hi,  1.0%si,  0.0%st
Mem:    987780k total,   979052k used,     8728k free,    17240k buffers
Swap:  2104432k total,   106760k used,  1997672k free,   174100k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4116 mysql     15   0  356m 105m 4176 S 12.0 11.0 139:58.69 mysqld
 5722 apache    15   0  160m  17m 4224 S 10.0  1.8   0:00.78 httpd
 5741 apache    15   0  161m  17m 4220 S  9.3  1.8   0:00.37 httpd
 5840 apache    15   0  161m  17m 4148 S  8.3  1.8   0:00.52 httpd
 5846 apache    15   0  161m  17m 4132 S  6.3  1.8   0:00.47 httpd
 5744 apache    15   0  162m  18m 4224 S  2.0  1.9   0:00.37 httpd
 5725 apache    15   0  161m  17m 4424 S  1.3  1.8   0:00.34 httpd
 5755 apache    15   0  105m  14m 4248 R  1.3  1.5   0:00.17 httpd
 5564 apache    15   0  163m  19m 4360 S  1.0  2.0   0:00.65 httpd
 5322 apache    16   0  162m  19m 4456 S  0.7  2.0   0:02.26 httpd
 5586 apache    15   0  161m  18m 4468 S  0.7  1.9   0:01.77 httpd
 5852 apache    16   0 99.9m  11m 3424 S  0.7  1.2   0:00.02 httpd
 5121 root      18   0 98.3m  10m 4320 S  0.3  1.1   0:00.07 httpd
 5723 apache    15   0  161m  17m 4240 S  0.3  1.8   0:00.31 httpd
 5833 apeadm    15   0 12740 1128  808 R  0.3  0.1   0:00.03 top
 5834 apache    15   0  160m  16m 4172 S  0.3  1.7   0:00.20 httpd
 5836 apache    15   0 98.5m 9388 2912 S  0.3  1.0   0:00.01 httpd
    1 root      15   0 10348  592  560 S  0.0  0.1   0:00.72 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/0

Server Spec: CENTOS CPU:E5200 RAM:1G

Software: Zencart X 3, Piwik x1

Web server always down,How to tuning apache and mysql?

Thanks a lot.

httpd.conf

<IfModule mpm_prefork_module>
    StartServers          8
    MinSpareServers       8
    MaxSpareServers      15
    ServerLimit         450
    MaxClients          450
    MaxRequestsPerChild   10000
</IfModule>


<IfModule mpm_worker_module>
    StartServers          2
    ServerLimit         450
    MaxClients          450
    MinSpareThreads      25
    MaxSpareThreads      75
    ThreadsPerChild      25
    MaxRequestsPerChild   10000
</IfModule>

<IfModule mpm_beos_module>
    StartThreads            10
    MaxClients              50
    MaxRequestsPerThread 10000
</IfModule>


<IfModule mpm_netware_module>
    ThreadStackSize      65536
    StartThreads           250
    MinSpareThreads         25
    MaxSpareThreads        250
    MaxThreads            1000
    MaxRequestsPerChild      10000
    MaxMemFree             100
</IfModule>

<IfModule mpm_mpmt_os2_module>
    StartServers           2
    MinSpareThreads        5
    MaxSpareThreads       10
    MaxRequestsPerChild    10000
</IfModule>

my.cnf

[mysqld]
set-variable = query_cache_limit=1M
set-variable = query_cache_size=16M
set-variable = query_cache_type=1
set-variable =  max_connections=400
set-variable = interactive_timeout=100
set-variable = wait_timeout=100
set-variable = connect_timeout=100
set-variable = thread_cache_size=16
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
set-variable = key_buffer=32M
set-variable = join_buffer=1M
set-variable = max_allowed_packet=8M
set-variable = table_cache=1024
set-variable = record_buffer=1M
set-variable = sort_buffer_size=2M
set-variable = read_buffer_size=2M
set-variable = max_connect_errors=10
set-variable = myisam_sort_buffer_size=16M

#Useful for SMP
set-variable = thread_concurrency=8

Answer 1

Until you add more RAM or more servers, try:

• Removing all unneeded apache modules to get down memory for each httpd process

• Your ServerLimit 450 is very high for your ram, more requests, more swapping.

• Set lower values for all MySQL buffers (maybe let them as defaults?)

• Also check for the max used connections of mysql (show global status;)... because I don't agree to be able to reach your 400 limit, with your ram and values, as you can see in top, you have I/O percentage in "wa" (waiting). If your server does not reach... say 15 connections, you don't need to reserve memory for 450.

Also, you can try switching in the same hardware to a less-memory-waster httpd, like lighttpd or nginx, and use mysqltuner, that will alert you about your optimistic (for your ram) settings.

Answer 2

Look at your memory. Add more RAM - this will help you a lot.

Answer 3

Look at using Apaches modules mod_expires and mod_deflate.

For MySQL. Start looking at tightening up your queries and if need be use indexes (Suggest you read and understand MySQL by reading High Performace MySQL).

What you can also do is look at using a loadbalancer (e.g. LVS) and add another webserver.