List:General Discussion« Previous MessageNext Message »
From:Ilya Kazakevich Date:April 17 2013 3:05pm
Subject:RE: Mesaure query speed and InnoDB pool
View as plain text  
Hello Rick,

>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.
Yes, but  I need cache to be > my database size to prevent other pages from
pushing out pages for my query, right?
Or I need to do at the dedicated server..

>Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will critique
>your indexes and query plan.
I speak about query optimization in general)


>Handler* is another way to get consistent values.  These numbers are
>unaffected by caching.
What variable exactly should I take?
Why can't I use" Innodb_pages_read"? That is number of page reads regardless
its source (pool or disk), is not it?


>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.
I will increase it now.
But I will need to disable swapping also to prevent my OS from swapping out
InnoDB pages.

Ilya.

>
>> -----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