分配给临时表的内存大小大于innodb_buffer_pool_size的20%

Question

我在mysql中有一个问题，因为我的mgento 2网站总是崩溃。请查看此错误。我怎样才能解决这个问题呢?网站托管于AWS .

[2021-07-19 15:42:32] main.ERROR: SQLSTATE[08004] [1040] Too many connections [] []
[2021-07-19 15:42:34] main.ERROR: SQLSTATE[08004] [1040] Too many connections [] []
[2021-07-19 15:42:54] main.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: 100000; Allocated memory size: 500000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] []
[2021-07-19 15:43:05] main.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: 100000; Allocated memory size: 500000000 bytes; InnoDB buffer pool size: 134217728 bytes. [] [] 

Answer 1

要确定最优的innodb_buffer_pool_size，运行由@RolandoMySQLDBA编写的查询

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;

这将给你RIBPS，建议InnoDB缓冲池大小，基于所有InnoDB数据和索引，额外的60%。

在我的例子中，值是1(GB)，所以您有2个选项:

A)使用配置文件配置InnoDB缓冲池大小

1. 编辑my.cnf(MySQL)或mariadb-server.cnf(MariaDB)，并在[mysqld]部分下添加一行，例如:

[mysqld]
innodb_buffer_pool_size=1G

2. 重启MySQL数据库

或

B) Configure InnoDB Buffer Pool Size Online

1GB为1073741824字节。以root身份登录时运行以下命令:

mysqld> SET GLOBAL innodb_buffer_pool_size=1073741824;

确认新值:

mysqld> SELECT @@innodb_buffer_pool_size/1024/1024/1024;

Answer 2

Magento可以通过使用内存引擎来代替InnoDB来增加处理大量数据的内存。该算法增加了max_heap_table_size和tmp_table_size MySQL参数的内存值。

当为临时表分配的内存大小大于innodb_buffer_pool_size的20%时，错误消息将被写入Magento日志。

要防止此错误消息，您需要更新catalog_category_product (Category Products)索引器的默认批处理配置，因为"当前批处理大小:1000000 "

注意:减少batchRowsCount来解决这个错误。