How to preload tables into INNODB buffer pool with MySQL?

Question

I have an e-commerce application that uses MySQL, and I'd like it to be faster. When a part # is accessed on the website that has been accessed before, the part loads quickly because all the necessary data is already in the INNODB buffer pool. However, if the part # has never been loaded before, that data isn't in the buffer pool yet, so it needs to be read from disk, and that is slow. I set my INNODB buffer pool to be 2GB, and this entire database is only about 350MB, so there is plenty of room to load the entire database in the buffer pool. I can see from the INNODB statistics that only about half the buffer pool is used right now.

I've found references to pre-loading the data, also known as "warming up" the buffer pool, such as Quickly preloading Innodb tables in the buffer pool or mysqldump.azundris.com/archives/70-Innodb-cache-preloading-using-blackhole.html. The strategy basically involves forcing a table scan on each table since MySQL doesn't have a native way for preloading the data.

I don't want to manually create a script that lists every single table in my database and have to do this. How can I create a script that goes through and does a select for each table automatically, and automatically picks out a non-indexed column so that a table scan is performed?

Flinch

Answer 1

This should give you a list of queries to run ;)

SELECT 
  CONCAT('SELECT ',MIN(c.COLUMN_NAME),' FROM ',c.TABLE_NAME,' WHERE ',MIN(c.COLUMN_NAME),' IS NOT NULL')
FROM
  information_schema.COLUMNS AS c
LEFT JOIN (
  SELECT DISTINCT
    TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
  FROM
    information_schema.KEY_COLUMN_USAGE
) AS k
USING
  (TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
  c.TABLE_SCHEMA = 'yourDatabase'
  AND k.COLUMN_NAME IS NULL
GROUP BY
  c.TABLE_NAME

You can put it into stored procedure, and go over the resultset with cursor. Create a prepared statement from each row, and execute.

Duala

this algorithm is simply concise and brilliant. This will cache the innodb data. I'm not sure if will this cache all innodb index pages, but your algorithm can be quickly applied to do so. I believe MyISAM only caches MYI index pages. Again, your algorithm forms the basis to pass through tables and indexes. Bravo (and +1) !!!! – Intelligence

Mine is only implementation to get a list of queries. The idea comes from Mysql Performance Blog linked in the Question. – Duala

Answer 2

This query will return table names in your database, so you don't have to input them manually:

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database name'

Then for each table name force table scan.

Vaporific

Answer 3

Here is a stored routine that runs in a loop and loads specified tables into the buffer pool. You can insert all your tables using an INSERT...SELECT statement that reads from the INFORMATION_SCHEMA.TABLES table and inserts into pin_buffer_pool_config.

CREATE DATABASE IF NOT EXISTS `swanhart`;

use swanhart;

CREATE TABLE IF NOT EXISTS `pin_buffer_pool_config` (
  `schema_name` varchar(50) DEFAULT NULL,
  `table_name` varchar(50) DEFAULT NULL,
  `index_name` varchar(255) DEFAULT 'PRIMARY' comment 'null for all indexes',
  `where_clause` varchar(255) DEFAULT NULL comment 'do not include the WHERE keyword'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter ;;

DROP PROCEDURE IF EXISTS pin_buffer_pool;

-- load all the columns for the given index
-- into the buffer pool
CREATE PROCEDURE pin_buffer_pool()
BEGIN
DECLARE v_done boolean default false;
DECLARE v_stmt TEXT default null;
DECLARE v_got_lock tinyint default 0;
DECLARE v_cursor CURSOR FOR
SELECT CONCAT('SELECT COUNT(CONCAT(', 
              GROUP_CONCAT(column_name ORDER BY seq_in_index), ')) INTO @discard 
              FROM `', s.table_schema, '`.`', s.table_name,
              '` FORCE INDEX(`', s.index_name, '`)', 
              IF(where_clause IS NOT NULL, CONCAT(' WHERE ', where_clause), '')
         ) AS stmt 
   FROM information_schema.statistics s 
   JOIN percona.pin_buffer_pool_config pbpc
     ON pbpc.table_name = s.table_name 
    AND pbpc.schema_name = s.table_schema 
    -- when the index_name is null, it means warm all indexes for the table
    AND s.index_name = ifnull(pbpc.index_name, s.index_name) 
  GROUP BY s.index_name;

  DECLARE CONTINUE HANDLER FOR
  SQLSTATE '02000'
    SET v_done = TRUE;

  -- DON'T HOLD A LONG TRANSACTION, START A NEW SNAPSHOT FOR EACH READ
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  -- abort if the last pin is still running
  SELECT GET_LOCK('BUFFER_POOL_PIN', 0) INTO v_got_lock;
  IF v_got_lock = 1 THEN

    OPEN v_cursor;
    cursorLoop: LOOP
      FETCH v_cursor INTO v_stmt;

      IF v_done THEN
        CLOSE v_cursor;
        LEAVE cursorLoop;
      END IF;

      set @v_stmt = v_stmt;
      prepare v_bp_pin from @v_stmt;
      execute v_bp_pin;
      deallocate prepare v_bp_pin;
      
    END LOOP;

    SELECT RELEASE_LOCK('BUFFER_POOL_PIN') INTO @discard;
 
  END IF;

END;;

DROP PROCEDURE IF EXISTS pin_buffer_pool_loop;;

CREATE PROCEDURE pin_buffer_pool_loop()
BEGIN
  -- This procedure can be scheduled to start
  -- every second with no harm done.  It will
  -- simply exit if more than one copy tries
  -- to run.  This means that an event can
  -- be used to ensure the warming function is
  -- always on and that it is looping faster
  -- than innodb_old_blocks_time (default 1000 in 5.6)
  SELECT GET_LOCK('BUFFER_POOL_PIN_LOOP_LOCK', 0) INTO @got_lock;
  IF @got_lock = 1 THEN
    LOOP
      CALL pin_buffer_pool();
      select sleep(.25) into @discard;
    END LOOP;
    SELECT RELEASE_LOCK('BUFFER_POOL_PIN_LOOP_LOCK') INTO @discard;
  END IF;
END;;

delimiter ;

Pejorative

You can use CREATE EVENT to schedule the loop procedure to run every 1 second safely. it won't stack up. – Pejorative