[TriLUG] need consulting for LAMP server

[Cristóbal Palmer via TriLUG]

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