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
