List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 17 2013 3:30pm
Subject:RE: Mesaure query speed and InnoDB pool
View as plain text  
>Run your query twice; take the second time.  

One more thing -- avoid the Query cache.  That could lead to really bogus timings.

> Yes, but  I need cache to be > my database size to prevent other pages from
> pushing out pages for my query, right?

Well, yes and no.  If the cache is big enough, there won't be any thrashing.
If the "working set" is smaller than the cache, then there won't be any thrashing.  That
is, if you don't access all the data/index blocks, there could be room for everything
that is needed (the "working set").
I often see, say, 100GB on disk and only 5GB of cache, yet the system is humming along
fine -- the working set is < 5GB and/or the accesses to other blocks is infrequent
enough so that it is not a big issue.

> I speak about query optimization in general

General tips, many relating to optimization:
http://mysql.rjweb.org/doc.php/ricksrots
Quick lesson in "compound indexes" (something that novices don't understand -- and a
significant optimization principle):
http://mysql.rjweb.org/doc.php/index1

> I will increase it now.

I hope you are referring to increasing RAM.

> But I will need to disable swapping also to prevent my OS from swapping out InnoDB
> pages.

NO!  Don't "disable" swapping; "avoid" swapping.  Disabling could cause a crash or other
nasties.  "Avoiding" means decreasing the tunables so that mysqld does not need to be
swapped.
This gives the main tunables:
http://mysql.rjweb.org/doc.php/memory
Decrease each as much as practical for your situation.  (For example, change
max_connections from 200 down to 5 -- assuming you don't need more than 5 simultaneous
connections.)  innodb_buffer_pool_size is probably the biggest memory consumer, so it is
the easiest way to shrink mysqld's footprint.


> -----Original Message-----
> From: Ilya Kazakevich [mailto:Ilya.Kazakevich@stripped]
> Sent: Wednesday, April 17, 2013 8:05 AM
> To: Rick James
> Cc: 'MySQL'
> Subject: RE: Mesaure query speed and InnoDB pool
> 
> 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