Best Apache & MySQL config for 16GB and 8 Cores

Question

I'm having some trouble with my servers. I'm having a peak and my servers load are very high.

I have 3 servers, one for frontend, one for database (mysql) and one for delivery images (no problem with this one)

Here is my current configurations and load average for 1500 visitors:

Server 1 (frontend):

load average: 143.08, 120.50, 66.99
CentOS 6.3
Apache/2.2.15
8 x Intel(R) Xeon(R) CPU E5645  @ 2.40GHz
16GB RAM

httpd.conf

PidFile run/httpd.pid
Timeout 60
KeepAlive Off
MaxKeepAliveRequests 1000
KeepAliveTimeout 15

<IfModule prefork.c>
  ServerLimit       520

  StartServers           16
  MinSpareServers         5
  MaxSpareServers        20
  MaxClients            500
  MaxRequestsPerChild  1000
</IfModule>

<IfModule worker.c>
  ServerLimit           520

  StartServers           16 
  MaxClients            500
  MinSpareThreads        25
  MaxSpareThreads        75 
  ThreadsPerChild        25
  MaxRequestsPerChild     0
</IfModule>

ExtendedStatus On
UseCanonicalName Off
HostnameLookups Off

ErrorLog logs/error_log
LogLevel warn

ServerSignature On


IndexOptions FancyIndexing VersionSort NameWidth=* HTMLTable Charset=UTF-8
BrowserMatch "Mozilla/2" nokeepalive
BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0
BrowserMatch "RealPlayer 4\.0" force-response-1.0
BrowserMatch "Java/1\.0" force-response-1.0
BrowserMatch "JDK/1\.0" force-response-1.0

BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully
BrowserMatch "MS FrontPage" redirect-carefully
BrowserMatch "^WebDrive" redirect-carefully
BrowserMatch "^WebDAVFS/1.[0123]" redirect-carefully
BrowserMatch "^gnome-vfs/1.0" redirect-carefully
BrowserMatch "^XML Spy" redirect-carefully
BrowserMatch "^Dreamweaver-WebDAV-SCM1" redirect-carefully

Server 2 (MySQL my.cnf)

load average: 12.84, 23.39, 22.85
CentOS 6.4
MYSQL 5.5.34
8 x Intel(R) Xeon(R) CPU E5645  @ 2.40GHz
16GB RAM

my.cnf

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_connections = 2000

key_buffer = 384M
query_cache_type=1
query_cache_size=512M
query_cache_limit=4M

innodb_buffer_pool_size = 8G
slow_query_log=1
slow_query_log_file=log-slow-queries.log
thread_cache_size=8
table_cache=128k
tmp_table_size=256M
max_heap_table_size=256M

init_connect='SET collation_connection = latin1_swedish_ci'
init_connect='SET NAMES latin1'
character-set-server=latin1
collation-server=latin1_swedish_ci
skip-character-set-client-handshake

log-error=/var/log/mysqld.error.log

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

mysqltuner output

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 3G (Tables: 57)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 57

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 11m 15s (4M q [241.514 qps], 537K conn, TX: 11B, RX: 2B)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 9.1G global + 2.8M per thread (2000 max threads)
[OK] Maximum possible memory usage: 14.5G (46% of installed RAM)
[OK] Slow queries: 1% (55K/4M)
[OK] Highest usage of available connections: 37% (756/2000)
[OK] Key buffer size / total MyISAM indexes: 384.0M/96.0K
[OK] Key buffer hit rate: 100.0% (46K cached / 0 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 480K sorts)
[OK] Temporary tables created on disk: 16% (112K on disk / 684K total)
[OK] Thread cache hit rate: 83% (87K created / 537K connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 0% (19/264K)
[OK] Table locks acquired immediately: 100% (7M immediate / 7M locks)
[OK] InnoDB data size / buffer pool: 3.6G/8.0G

Application details

php 5.4 Symfony 1.2 Database size: 6GB

Can you help me?

Solution

The main problem I see here is high load average on frontend server. MySQL server could do better but it's not that bad. Apache servers become a bottleneck quite often. Without knowing what your application is doing, it's quite difficult to solve the problem. General recommendations could be the following:

1. Set up Munin on frontend server which would include metrics for Apache. Having this information it would be much easier for you to tune Apache. Munin would also help you identify how often load average increases and when.
2. Analyze Apache and your application logs. What the most heaviest and most frequent requests? Probably there's some application related issue (like serving static files using PHP).
3. Consider switching to nginx/php-fpm instead of Apache. Apache is very good webserver, but nginx became kind of a standard for a lot of modern setups which don't need some specific Apache features.

Considering MySQL optimization. You have only 96.0K of MyISAM indexes but 384.0M of key buffer pool. You could safely decrease this parameter. You could also check your slow queries using Percona's pt-query-digest, it's very handy.

Comments

[Admin]

A high load average is usually an indicator of a problem but it's not an actual problem itself. Are you actually seeing any performance problems? That's what would have to be addressed. If you are seeing performance problems, where are you seeing them? Have you looked at your logs for any errors?

[Bert]

Really need a lot more information. How big is your database? Have you ran mysqltuner.pl? What programming language does your web server run the application in (php, ruby, python...)?

[Nuno Rafael Rocha]

Just added application details and mysqltuner output

[lsmooth]

Try this SHOW VARIABLES LIKE 'query_cache_size'; on the mysql server. What's the output? Your mysql server isn't using the query cache, I'd try to find out why.

[Nuno Rafael Rocha]

mysql> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+-----------+ | Variable_name | Value | +------------------+-----------+ | query_cache_size | 536870912 | +------------------+-----------+

[Nuno Rafael Rocha]

Thank you for your answer. I have already installed Munin. The real problem is that I dont now how to analise the data that I have and change the apache settings according to that... :/

[user3120146]

Do you use some specific Apache features? I'd say nginx could be an option if you don't. Often it performs better due to its internal model which differs from Apache's. Besides, I would try to understand why your application performs this way. It's quite frequent situation when you have few issues, and if you fix them, the performance will be much better.