[TriLUG] need consulting for LAMP server

[Peter Neilson via TriLUG]

Thank you for asking this question, Wes, because it helped me discover a  
strange e-mail bug. Apparently my e-mail to trilug at trilug.org goes into a  
cosmic bit-bucket!

I looked for my posting to appear, and it never did. I sent it a second  
time with an apology for duplication. Ditto nada. I sent a test message.  
Again, nada.

Now I have Brian Gerard and the Steering Committee looking into it.

Anyway, I'm glad I seem to have helped. Sometimes it's just having the  
right attitude, a bit of off-the-wall knowledge or experience, some  
half-baked google-fu, and raw good luck.

I'm CC-ing this note to the list. If it does not show up there, feel free  
to forward it there.

-Peter

On Fri, 03 Jun 2016 16:32:04 -0400, Wes Garrison <wes at xitechusa.com> wrote:

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