List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 16 2013 4:26pm
Subject:RE: Mesaure query speed and InnoDB pool
View as plain text  
Swapping is really bad.  Shrink buffer_pool as needed to avoid swapping.  The 70-80%
'rule' works for 'most' machines today, because most machines have a lot more than 2GB
'available' to MySQL.  As you observed, 2GB box would probably swap if buffer_pool were
1.4GB, so 800-900M is better.  Meanwhile, a 20GB box would be fine with 14GB, even 16GB. 
The best formula would be something more complex than a simple percent.  I recommend "70%
of available ram" because it safely covers most machines today.  Then I backpeddle like
this when I see that the machine is smaller.  (Sorry, I was assuming you had a bigger
machine, and had not allocated as much as you could.)  The old default of 8M is terrible.
 Even the new default is puny (most of the time).

> -----Original Message-----
> From: Igor Shevtsov [mailto:nixofortune@stripped]
> Sent: Tuesday, April 16, 2013 8:38 AM
> To: mysql@stripped
> Subject: Re: Mesaure query speed and InnoDB pool
> 
> 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
> >>>
> >>>
> 
> 
> --
> 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