List:General Discussion« Previous MessageNext Message »
From:Joseph Cochran Date:May 4 2005 7:44pm
Subject:Re: Slow queries, why?
View as plain text  
Thanks! Explain and InnoDB monitor were exactly what I needed to
diagnose and fix the problem! In case you were curious, the issue was
that the statement I was expecting to run was not the statement that
was running, but the first hundred and some-odd characters in both
were the same. Using the monitor I was able to see that the wrong
thing was running.

Some SELECTs are still taking longer than they should, but I have some
new tools at my disposal, which makes me very happy.

-- Joe

On 5/4/05, Gleb Paharenko <gleb.paharenko@stripped> wrote:
> Hello.
> 
> 
> > We're running MySQL 4.11 on a machine with 2GB memory, the table is
> 
> > InnoDB with a compound primary key, and additional indexes on all rows
> 
> > with searchable options in the API. Any generic advice or admin tools
> 
> > would be great.....
> 
> Use EXPLAIN to determine how efficient your indexes are. Using a lot of
> 
> keys could slow down the INSERT operations but fasten the SELECTs.
> 
> InnoDB monitors might be helpful in your case as well. See:
> 
>   http://dev.mysql.com/doc/mysql/en/explain.html
> 
>   http://dev.mysql.com/doc/mysql/en/innodb-monitor.html
> 
> 
> Joseph Cochran <g.jsciv@stripped> wrote:
> 
> > So here's my situation: we have a database that has a table of about 5
> 
> > million rows. To put a new row into the table, I do an INSERT ...
> 
> > SELECT, pulling data from one row in the table to seed the data for
> 
> > the new row. When there are no active connections to the DB other than
> 
> > the one making the INSERT, it runs like a charm. But during normal
> 
> > daytime operation, when we run around 50 connections (most sleeping at
> 
> > any one time), it takes up to two minutes to do, and ends up locking
> 
> > any other inserts or updates against that table for the entire time.
> 
> >
> 
> > I'll get into more specifics if they're required, but I wanted to ask
> 
> > in general if MySQL has tools to diagnose this, or if anyone has had
> 
> > general situations like this. In SQL Server (which is where I have
> 
> > most of my experience) I could use the trace tool and the Query
> 
> > Analyzer to tell what the execution plan for the query was and thus
> 
> > what's stalling it (an index gone bad, a weird locking situation,
> 
> > etc).
> 
> >
> 
> > We're running MySQL 4.11 on a machine with 2GB memory, the table is
> 
> > InnoDB with a compound primary key, and additional indexes on all rows
> 
> > with searchable options in the API. Any generic advice or admin tools
> 
> > would be great.....
> 
> >
> 
> > -- Joe
> 
> >
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
>        <___/   www.mysql.com
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
>
Thread
Slow queries, why?Joseph Cochran4 May
  • Re: Slow queries, why?Scott Haneda4 May
  • Re: Slow queries, why?Gleb Paharenko4 May
    • Re: Slow queries, why?David Griffiths4 May
    • Re: Slow queries, why?Joseph Cochran4 May
RE: Slow queries, why?lakshmi.narasimharao4 May
  • Re: Slow queries, why?Gleb Paharenko5 May