Fixing Magento warning MySQL innodb_buffer_pool_size

While checking the Magento log files of a Magento instance we recently migrated, I came across the following MySQL warning:

Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size. Please update innodb_buffer_pool_size 
or decrease batch size value (which decreases memory usages for the temporary table). 
Current batch size: 417; Allocated memory size: 40032000 bytes; InnoDB buffer pool size: 134217728 bytes.

Luckily StackOverflow has some guidance on how to get rid of the warning.

First, let's identify the recommended InnoDB buffer pool size, which includes all InnoDB data and indexes plus a 60% buffer:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM 
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

In our case, the query returned 1 (GB). To configure the innodb_buffer_pool_size in MySQL you have to edit the my.cnf and run an SQL query to avoid not restarting the MySQL server.

First, edit the my.cnf file and add one line in the [mysqld] section:

innodb_buffer_pool_size=1G

Since 1GB is 1073741824 bytes, the SQL query to run looks like this:

SET GLOBAL innodb_buffer_pool_size=1073741824;

After I updated the innodb_buffer_pool_size, the warnings in Magento's log files are gone.

EDIT 2024-09-24: Thanks to Tim Düsterhus for pointing out that both the MySQL server config file needs to be changed and the SQL query should be run to avoid losing the config change after a MySQL server restart.