[GLLUG] mysql slow

Rocky Lichen rockylichen at yahoo.com
Sun Nov 18 11:02:05 EST 2007



If the problem query updates many records at a time, and if many of the six fields you are updating are indexed (or part of other table constraints such as foreign keys), it may be the the maintenance of those constraints that's slowing things down.

To see if this is the case, modify your function to drop any indexes on the fields to be updated, do the updates, then rebuild the indexes.


--- On Tue, 11/13/07, Mike Rambo <mrambo at lsd.k12.mi.us> wrote:

> From: Mike Rambo <mrambo at lsd.k12.mi.us>
> Subject: [GLLUG] mysql slow
> To: "Gllug List" <linux-user at EGR.MSU.EDU>
> Date: Tuesday, November 13, 2007, 6:26 AM
> Hey all,
> 
> Does anyone have any ideas on what to look for when mysql
> queries are a 
> bit slow? We are using a home grown work order tracking
> system 
> (php/mysql) and have noticed latencies increasing of late.
> Actually, I 
> think some of it may have coincided with a change made to
> one of the 
> main tables a short while back when I added some additional
> function. 
> I've ran table checks and re-indexed them but it
> hasn't helped. The 
> largest table is about 21MB in size with a little under 40k
> records.
> 
> Some background...
> 
> The db runs on an old Proliant 3000 running an ancient RH
> Linux (7.3 I 
> think) but it's worked well for years and the box
> isn't exposed 
> externally. There is no trouble signs in the logs (that
> I've found 
> anyway). The hardware is 300MHz PII with 384MB. Lots of HDD
> and we're 
> not swapping.
> 
> Most of my selects are still simple selects from a single
> table though 
> there are are increasing numbers that join two or three
> tables as this 
> thing increases in complexity. I'm not a sql guru so I
> try to keep 
> things simple. The specific function that has caused this
> thing to come 
> to a head is one where I update about six of the 26 fields
> in the 
> primary table in a single sql udpate. It is slow returning
> and there 
> isn't much php code behind it either (about two dozen
> lines of code 
> which include six conditionals (if statements in this
> case)).
> 
> I think we're going to be getting a new box to run this
> on. I'm sure 
> that will help since more cycles processed each second can
> only improve 
> things but since I'm not sure that is really the root
> cause of the 
> problem I'd prefer to sort out what the problem really
> is first.
> 
> Perhaps adding fields to an existing table adds overhead
> that a re-index 
> will not resolve??? I don't know.
> 
> 
> -- 
> Mike Rambo
> 
> 
> To mess up a Linux box, you need to work at it; to mess up
> your Windows box, you just have to work on it.
>      -Scott Granneman
> _______________________________________________
> linux-user mailing list
> linux-user at egr.msu.edu
> http://mailman.egr.msu.edu/mailman/listinfo/linux-user


      ____________________________________________________________________________________
Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ


More information about the linux-user mailing list