Fixing Magento warning MySQL innodb_buffer_pool_size
· One min read
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 can either edit the my.cnf
or run an SQL query. I decided to use the SQL query.
Since 1GB is 1073741824 bytes, the query 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.