0

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 ?

2
  • You could run multiple instances of MySQL with the one with the production databases given more resources / priority over the ones with the other databases. This would also let you run different versions / patch levels in dev/test/staging before updating production.
    – Brian
    Feb 1, 2021 at 14:12
  • Instead of that approache, let's look at the ram-hungry queries and try to improve them. Turn on the slowlog; Any such 'big' query is likely to also be "slow". That will help identify the naughty ones. Note especially those with the largest "Rows examined".
    – Rick James
    Feb 2, 2021 at 4:45

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.