Mysql – Assign different RAM usage (buffer pool size) per db in thesql

buffer-poolinnodbMySQLperformance

I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb.

However, the InnoDB Buffer Pool is only 16Go, meaning that often my indexes need to be loaded from disk, which destroys performance of my requests.

Index size of my production database is 16Go. Is there somehow I can tell mysql to keep my production db indexes in RAM ? E.g., by assigning buffer pool sizes per db.

Any other recommended strategy ?

Best Answer

I have written a few posts on loading index pages into the InnoDB Buffer Pool

Here is an example of a SELECT that will generate all possible SELECTs you would want to run to read InnoDB Index Pages:

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

The main idea with these posts is this: Run SELECT queries whose columns are indexed. This will force index pages into the InnoDB Buffer Pool. Unfortunately, when doing SELECTs against non-indexed columns will push out old index pages out of the InnoDB Buffer Pool. There is no perfect solution for this but to get as many InnoDB index pages as possible.

You may want to limit the tables in the WHERE clause to a specific list of tables.

UPDATE 2021-02-02 10:13 EST

I have some additional advice: If the InnoDB tables don't change all that often, you may want to take a snapshot of the InnoDB Buffer Pool. This is on by default in MySQL 8.0.

For those using MySQL 5.7 and back, please do the following:

STEP 01 : Configure MySQL to Dump the InnoDB Buffer Pool on Shutdown and Load the InnoDB Buffer Pool on Startup

Add these two lines to my.cnf under the [mysqld] group header

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

STEP 02 : Configure Running Instance to Dump the Buffer on Shutdown

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

STEP 03 : Dump the Buffer Pool Now

SET GLOBAL innodb_buffer_pool_dump_now = 1;

That's it. The dump of the Buffer Pool is just a binary file with a list of tablespace IDs. The size of such a file is usually in the 100K-120K range and dumps in seconds.

Related Question