[TriLUG] need consulting for LAMP server

[Michael Kimsal via TriLUG]

Apologies for the bad reply - I don't have the original email to reply to.

Wes, you mentioned 15-20 second page load times, and have since been
talking about MySQL optimization, and then pointed out in another reply
that there's "nothing of consequence" in the slow query log.

If you verify that the slow query log is, in fact, working properly, and
this was a DB problem, you'd see 15 second times in the slow query log.

I would submit the problem is not in the querying, but the processing of
those results by whatever app is processing the results in to a page.

Log all your queries for a particularly slow page request.

Run those queries again by hand, outside the app - is anything egregiously
slow?



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: mgkimsal at gmail.com <mgkimsal 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/mgkimsal%40gmail.com
> Welcome to TriLUG: http://trilug.org/welcome




-- 

Michael Kimsal
http://kims.al <http://michaelkimsal.com>
919.827.4724