Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to preload tables into INNODB buffer pool with MySQL?

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?

like image 499
Nick Avatar asked Aug 07 '10 15:08

Nick


People also ask

What is InnoDB buffer pool in MySQL?

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

What is InnoDB buffer pool instances?

The InnoDB buffer pool is a key component for optimizing MariaDB. It stores data and indexes, and you usually want it as large as possible so as to keep as much of the data and indexes in memory, reducing disk IO, as main bottleneck.

Can we change innodb_buffer_pool_size in MySQL?

MySQL :: Resizing the InnoDB Buffer Pool Online. As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7. 5. You can now use the "SET GLOBAL innodb_buffer_pool_size = xxxx" command which causes a resizing job to begin in background.


1 Answers

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.

like image 66
Mchl Avatar answered Oct 08 '22 11:10

Mchl