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