>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.kazakev...@jetbrains.com] > 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:anan...@gmail.com] > >> 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.kazakev...@jetbrains.com> 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
