MySQL Forums
Forum List  »  InnoDB

Re: A very slow query on InnoDb 5.1.67
Posted by: Peter Brawley
Date: April 18, 2013 11:36AM

You might get a temporary improvement by advancing innodb_buffer_pool_size to 750M.

> benchmarking this is a pain since results vary greatly

Automate unsetting the query cache ...

CREATE PROCEDURE `setquerycaching`( pcache bool )
BEGIN
  IF NOT pcache THEN
    IF @prev_query_cache_type IS NOT NULL THEN
      SET @prev_query_cache_type = @@LOCAL.query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NULL THEN
      SET @prev_query_cache_size = @@GLOBAL.query_cache_size;
    END IF;
    SET LOCAL query_cache_type = 0;
    SET GLOBAL query_cache_size = 0;
    RESET QUERY CACHE;
  ELSE
    IF @prev_query_cache_type IS NOT NULL THEN
      SET LOCAL query_cache_type= @prev_query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NOT NULL THEN
      SET GLOBAL query_cache_size= @prev_query_cache_size;
    END IF;
  END IF;
  SELECT
    pcache AS 'Query Caching',
    @@LOCAL.query_cache_type AS QCacheType, @prev_query_cache_type AS 'PrevType',
    @@GLOBAL.query_cache_size AS QCacheSize,@prev_query_cache_size AS 'PrevSize';
END;

If you have a Windows box, you could install 5.1 and 5.6 on it, optimise the 5.6 installation, and benchmark.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: A very slow query on InnoDb 5.1.67
1306
April 18, 2013 11:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.