List:General Discussion« Previous MessageNext Message »
From:Igor Shevtsov Date:April 16 2013 3:37pm
Subject:Re: Mesaure query speed and InnoDB pool
View as plain text  
Hi Rick,
I thought you have to dedicate 70-80% of available RAM not a total RAM.
Saying if I have 2 gig of RAM on my exclusively innodb box, and I 
dedicate 1.4Gig to innodb pool, my 64-bit linux machine will start 
swapping heavily.
If I set it to 800-900M, it just fine and I have like 100M of RAM left 
for some occasional process. I did try it.
Thanks,
Igor

On 16/04/13 16:21, Rick James wrote:
> 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