[TriLUG] need consulting for LAMP server

[Wes Garrison via TriLUG]

Lots of great info here, thanks for all the suggestions.

I think Peter Neilson may have put his finger on it.  I was searching for
various combinations of "mysql high cpu usage" and forgot about InnoDB.

I changed the engine for a lot of our tables a few months ago from MyISAM
to InnoDB and we do indeed have a lot of random primary keys.

I'll try adding auto_increment primary keys and see if that improves things.

There isn't really anything of consequence in the slow queries log, I don't
do a lot of large table joins, not using replication, and we're nowhere
near the max concurrent users.

I'll try to isolate the issue to CPU, Disk, or Network as Ronald suggested
using “iotop”, “htop”, and “nmon”.

I'll also check out InnoTop, MyTop, MonYog and sqlYog, and glances.

First I'm going to try changing the keys to be non-random and see how that
works out.

-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."

On Fri, Jun 3, 2016 at 4:52 AM, Peter Neilson <neilson at windstream.net>
wrote:

>
>>
> I'd think about InnoDB first. If for some reason it thinks that a random
> key needs to be inserted it has to reshuffle everything, moving large
> chunks of stuff around in tables.
>
> Here's a stackoverflow about the difficulty:
>
> http://stackoverflow.com/questions/9819271/why-is-mysql-innodb-insert-so-slow
>
> This analysis is based on nothing more than about five minutes of poking
> around with Google and favoring stackoverflow results against other
> opinions. I am not an authority on any DB stuff. I've just occasionally
> noticed that page sloth seems to result from DB server problems as opposed
> to clogging in the network or in the rendering engines.
>