List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 16 2013 3:21pm
Subject:RE: Mesaure query speed and InnoDB pool
View as plain text  
Run your query twice; take the second time.  For most queries the first run brings
everything into cache, then the second gives you a repeatable, though cached, timing.

Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will critique your indexes and
query plan.

Handler* is another way to get consistent values.  These numbers are unaffected by
caching.

1GB buffer_pool?  You have only 2GB of available RAM?  Normally, if you are running only
InnoDB, the buffer_pool should be set to about 70% of available RAM.

> -----Original Message-----
> From: Ananda Kumar [mailto:anandkl@stripped]
> Sent: Tuesday, April 16, 2013 2:06 AM
> To: Ilya Kazakevich
> Cc: MySQL
> Subject: Re: Mesaure query speed and InnoDB pool
> 
> Does your query use proper indexes.
> Does your query scan less number blocks/rows can you share the explain
> plan of the sql
> 
> 
> On Tue, Apr 16, 2013 at 2:23 PM, Ilya Kazakevich <
> Ilya.Kazakevich@stripped> wrote:
> 
> > Hello,
> >
> > I have 12Gb DB and 1Gb InnoDB pool. My query takes 50 seconds when it
> > reads data from disk and about 2 seconds when data already exists in
> > pool. And it may take 10 seconds when _some_ pages are on disk and
> some are in pool.
> > So, what is the best way to test query performance? I have several
> ideas:
> > * Count 'Innodb_rows_read' or 'Innodb_pages_read' instead of actual
> > time
> > * Set pool as small as possible to reduce its effect on query speed
> > * Set pool larger than my db and run query to load all data into pool
> > and measure speed then
> >
> > How do you measure your queries' speed?
> >
> > Ilya Kazakevich
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
> >
> >
Thread
Mesaure query speed and InnoDB poolIlya Kazakevich16 Apr
  • Re: Mesaure query speed and InnoDB poolAnanda Kumar16 Apr
    • RE: Mesaure query speed and InnoDB poolRick James16 Apr
      • Re: Mesaure query speed and InnoDB poolIgor Shevtsov16 Apr
        • RE: Mesaure query speed and InnoDB poolRick James16 Apr
      • RE: Mesaure query speed and InnoDB poolIlya Kazakevich17 Apr
        • RE: Mesaure query speed and InnoDB poolRick James17 Apr