[TriLUG] need consulting for LAMP server

Fri Jun 3 07:09:39 EDT 2016

Is your DB setup for replication?  That could cause some issues during heavy I/O.

Couple suggestions:

During the busy time, run “iotop”, “htop”, and “nmon” in separate windows so you can see them together at one time.  These should help pinpoint where the contention lies.  Also, suggest you creating a non-DB related HTML page to see if you can load just a text page during the busy time.  

Try to narrow down the issue to CPU, RAM, Disk, or Network.  While 2x SAS drives sounds good, you might be hitting some sort of disk IO wait issue causing the DB to slow down.

On Jun 3, 2016, at 12:35 AM, Wes Garrison via TriLUG <trilug at trilug.org> wrote:

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."
-- 
This message was sent to: Ron Kelley <rkelleyrtp at gmail.com>
To unsubscribe, send a blank message to trilug-leave at trilug.org from that address.
TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
Unsubscribe or edit options on the web	: http://www.trilug.org/mailman/options/trilug/rkelleyrtp%40gmail.com
Welcome to TriLUG: http://trilug.org/welcome