[TriLUG] need consulting for LAMP server

[Wes Garrison via TriLUG]

Folks,

I need help figuring out why my page load times atrocious during busy times
- we're talking 15 - 20 seconds for our internal pages *over our LAN* during
peak hours.

Here are the basics:
Debian 8 "Jessie" up to date as of this very minute
Apache 2.4 running mpm_prefork
MySql 5.5 using mostly InnoDB tables
Perl 5.20 via mod_perl

Server is a real machine with 16 cores at 1.8Ghz, 128GiB ECC RAM, and dual
15k RPM SAS drives.  It is hosted on a dedicated symmetric 100Mbps fiber
optic line.  It should be fast.

I'm only using about 17 GiB of RAM during peak times, so that doesn't seem
to be the issue.

I have noticed that when it's busy, *top *shows *mysqld *using 700% - 1200%
of CPU, which seems like a big problem to me.

*apache2.conf* config is:
KeepAlive On
MaxKeepAliveRequests 0  # unlimited keep alive requests
KeepAliveTimeout 5

Apache *mpm_prefork *config is:
StartServers            5
MinSpareServers         5
MaxSpareServers         10
MaxRequestWorkers       500  # 500 processes!!!
ServerLimit             500
MaxConnectionsPerChild  0     # unlimited connections per child process

running apache2 mod_status shows that the number of connections is rarely
above 70.

*my.cnf* config is:
bind-address = 127.0.0.1  # only allow connections from local machine
innodb_buffer_pool_size = 15G  # most tables are innodb, and 15G is more
than enough
key_buffer              = 1G
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections        = 500
table_cache            = 1200
query_cache_limit       = 1M
query_cache_size        = 500M

mysqltuner stats look decent:
-------- Storage Engine Statistics
-------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: *577M* (Tables: 32)
[--] Data in InnoDB tables: *4G* (Tables: 45)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 55

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

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 4d 22h 57m 22s (37M q [87.054 qps], 1M conn, TX: 32B, RX: 5B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 16.5G global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 17.8G (14% of installed RAM)
[OK] Slow queries: 0% (14/37M)
[OK] Highest usage of available connections: 12% (61/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/280.0M
[!!] Key buffer hit rate: 91.7% (7K cached / 617 reads)
[OK] Query cache efficiency: 71.1% (24M cached / 35M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (10K temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 0% (9K on disk / 1M total)
[OK] Thread cache hit rate: 95% (55K created / 1M connections)
[OK] Table cache hit rate: 97% (243 open / 250 opened)
[OK] Open file limit used: 3% (113/2K)
[OK] Table locks acquired immediately: 99% (14M immediate / 14M locks)
[OK] InnoDB buffer pool / data size: 15.0G/4.2G
[OK] InnoDB log waits: 0

To my eyes, everything looks good.

Is there anything that seems obviously wrong that I'm missing?

This is for business, so I'm happy to do a paid consultation if this gets
outside the scope of the TriLUG list.

-Wes

_________________________________
Wesley S. Garrison
Network Engineer
Xitech Communications, Inc.
phone:  (919) 260-0803
fax:       (919) 932-5051
__________________________________
"Lead us not into temptation, but deliver us from email."