[TriLUG] need consulting for LAMP server

[David Brain via TriLUG]

Hi,

Take a look at the tuning for innodb - this seems to be a decent guide
(https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/)
-  mysql's defaults are more suitable for a small/test DB.

A 'show full processlist \G' in mysql will give you a list of what's
currently running, which may reveal lock issues etc.  You can also set
up slow query logging
(http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html) which
will log specific queries that are taking 'too long'.

David.

On Sat, Jun 4, 2016 at 12:25 PM, Cristóbal Palmer <trilug at trilug.org> wrote:
> I got to this: "I'll try adding auto_increment primary keys and see if that
> improves things," and had to respond.
>
> Many things can impact query response time, but to oversimplify: you want
> fewer joins and more index-only queries. Adding an index only helps if
> you're actually going to query that index. Don't add indexes in scattershot
> fashion. An auto-increment primary key can ease and speed up indexing, and
> can produce a small index, but if you can't query that index (because your
> app doesn't use it)....
>
> -CMP
>
>
> On Fri, Jun 3, 2016 at 4:33 PM Wes Garrison via TriLUG <trilug at trilug.org>
> 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.
>> >
>> --
>> This message was sent to: Cristóbal Palmer <cmp at cmpalmer.org>
>> 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/cmp%40cmpalmer.org
>> Welcome to TriLUG: http://trilug.org/welcome
> --
> This message was sent to: dbrain at gmail.com <dbrain 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/dbrain%40gmail.com
> Welcome to TriLUG: http://trilug.org/welcome