Server tuning/optimization request

[dethfire]

Hi all! Looking for some suggestions on how I can best tune Apache and MySQL etc for my needs.

CentOS / 6.3-64
Dual Xeon-SandyBridge E5-2620-HexCore [2GHz]
Seagate Cheetah / X25-E Extreme SSD
16GB of RAM
PHP 5.4.33
MySQL 5.6.17
Apache 2.4
Memcached / opcache

top - 14:47:08 up 6 days, 19 min,  1 user,  load average: 1.84, 1.50, 1.36
Tasks: 574 total,   2 running, 572 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.4%us,  2.8%sy,  0.0%ni, 89.7%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  16302892k total, 11323396k used,  4979496k free,   105684k buffers
Swap:  1048572k total,        0k used,  1048572k free,  1714056k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
12833 mysql     20   0 19.6g 5.9g 7256 S  8.9 37.8 123:12.93 mysqld
10569 nobody    20   0  567m  42m  29m S  8.6  0.3   0:05.83 httpd
28826 nobody    20   0  566m  47m  32m S  6.0  0.3   0:09.88 httpd
28827 nobody    20   0  567m  55m  41m S  5.6  0.3   0:09.95 httpd
28540 nobody    20   0  564m  54m  41m S  5.0  0.3   0:10.33 httpd
7598 nobody    20   0  567m  63m  49m S  4.6  0.4   0:28.16 httpd
20715 nobody    20   0  564m  41m  28m S  4.6  0.3   0:04.05 httpd
23849 nobody    20   0  569m  54m  41m S  4.6  0.3   0:13.40 httpd
12943 nobody    20   0  566m  31m  16m R  4.3  0.2   0:01.01 httpd
15174 nobody    20   0  566m  32m  18m S  4.0  0.2   0:00.59 httpd
18962 nobody    20   0  568m  59m  43m S  4.0  0.4   0:11.32 httpd
10594 nobody    20   0  567m  51m  38m S  3.6  0.3   0:05.61 httpd

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 584M (Tables: 652)
[--] Data in InnoDB tables: 4G (Tables: 287)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 6M (Tables: 9)
[!!] Total fragmented tables: 315

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 56m 21s (21M q [220.530 qps], 2M conn, TX: 171B, RX: 6B)
[--] Reads / Writes: 51% / 49%
[--] Total buffers: 6.8G global + 2.9M per thread (450 max threads)
[OK] Maximum possible memory usage: 8.0G (51% of installed RAM)
[OK] Slow queries: 0% (2/21M)
[OK] Highest usage of available connections: 54% (245/450)
[OK] Key buffer size / total MyISAM indexes: 8.0M/167.9M
[OK] Key buffer hit rate: 96.9% (113M cached / 3M reads)
[OK] Query cache efficiency: 61.1% (6M cached / 10M selects)
[!!] Query cache prunes per day: 318008
[OK] Sorts requiring temporary tables: 0% (411 temp sorts / 906K sorts)
[!!] Temporary tables created on disk: 26% (26K on disk / 101K total)
[OK] Thread cache hit rate: 99% (2K created / 2M connections)
[OK] Table cache hit rate: 83% (1K open / 2K opened)
[OK] Open file limit used: 37% (1K/5K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
[OK] InnoDB data size / buffer pool: 4.6G/6.2G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 256M) [see warning above]

[mysqld]
datadir="xxxx"
tmpdir="xxxx"

max_connections = 450
innodb_buffer_pool_size=6400M
innodb_log_file_size=5M
innodb_log_buffer_size=8M
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
join_buffer_size=2M
wait_timeout=360
interactive_timeout=60
connect_timeout=10
tmp_table_size=256M
max_heap_table_size=256M

Start Servers 25
Minimum Spare Servers 25
Maximum Spare Servers 50
Server Limit 250
Max Request Workers 250
Max Request Workers 250
Max Connections Per Child 10000
Keep-Alive On
Keep-Alive Timeout 2
Max Keep-Alive Requests 100
Timeout 100

Thanks!! @MattW

 

[Russ]

We use and trust @MattW for all of our stuff

 

[dethfire]

We use and trust @MattW for all of our stuff

Paid service?

 

[TPerry]

Paid service?

Yes... and you get what you pay for with him.

 

[Russ]

Paid service?

Ya he's very reasonable though, we've hired him a few times over the past year.

 

[Steve F]

Paid service?

https://mattwservices.co.uk/resources/hourly-server-work.1/

Not sure what you are looking for, we usually get the above and he is able to do most requested work in that allotted time. He also offers other paid server/XenForo services.

https://mattwservices.co.uk/resources/categories/server-administration-and-configuration.1/

 

[Brent W]

I would move to a nginx stack. I use http://www.centminmod.com on all my servers.

 

[TPerry]

I would move to a nginx stack. I use http://www.centminmod.com on all my servers.

From a cursory look I'm almost willing to bet he's running cPanel.

 

[dethfire]

From a cursory look I'm almost willing to bet he's running cPanel.

Yes cpanel and have nginx as reverse proxy

 

[TPerry]

Yes cpanel and have nginx as reverse proxy

Figured as much.... I really wish there as a plugin that would totally replace Apache with nginx in cPanel. I know you can use LiteSpeed but for sites like what I do (where I'm offering some free hosting) it's not worth it to me.