Как предварительно загрузить таблицы в буферный пул INNODB с MySQL?

Question

У меня есть приложение электронной коммерции, которое использует MySQL, и я бы хотел, чтобы оно было быстрее. При обращении к части # на веб-сайте, к которому ранее обращались, эта часть быстро загружается, поскольку все необходимые данные уже находятся в пуле буферов INNODB. Однако, если part # никогда не загружался ранее, эти данные еще не находятся в пуле буферов, поэтому их нужно читать с диска, и это медленно. Я установил буферный пул INNODB на 2 ГБ, а вся эта база данных - всего около 350 МБ, поэтому есть достаточно места для загрузки всей базы данных в буферный пул. Из статистики INNODB я вижу, что сейчас используется только около половины пула буферов.

Я нашел ссылки на предварительную загрузку данных, также известную как "прогрев" пула буферов, такой как быстрая предварительная загрузка таблиц Innodb в пул буферов или mysqldump.azundris.com/archives/70-Innodb-cache-preloading- используя-blackhole.html. Стратегия в основном предусматривает принудительное сканирование таблицы для каждой таблицы, поскольку MySQL не имеет встроенного способа предварительной загрузки данных.

Я не хочу вручную создавать скрипт, который перечисляет каждую таблицу в моей базе данных и должен это делать. Как я могу создать сценарий, который проходит и выполняет выбор для каждой таблицы автоматически и автоматически выбирает неиндексированный столбец, чтобы выполнить сканирование таблицы?

Answer 1

Это должно дать вам список запросов для запуска;)

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

Вы можете поместить его в хранимую процедуру и просмотреть курсор с помощью курсора. Создайте подготовленный оператор из каждой строки и выполните.

Answer 2

Этот запрос вернет имена таблиц в вашей базе данных, поэтому вам не нужно вводить их вручную:

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

Затем для каждого имени таблицы форсируют сканирование таблицы.

Answer 3

Вот хранимая процедура, которая выполняется в цикле и загружает указанные таблицы в пул буферов. Вы можете вставить все свои таблицы с помощью инструкции INSERT...SELECT, которая считывает таблицу INFORMATION_SCHEMA.TABLES и вставляет ее в 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 ;