使用所有内存的MySQL

Question

我试图用1GBRAM和MySQL 5.7.21在Ubuntu16.04VM上恢复大约750 MB的SQL文件。

我配置了innodb_buffer_pool_size = 768MB、innodb_buffer_pool_chunk_size = 128M和innodb_buffer_pool_instances = 1。

为了停止来自OOM杀手的杀戮，OOMScoreAdjust=-1000被添加到mysql.sqevice文件中。

启动恢复时，mysqld使用了所有内存。

root@IA6-OA-DB-01:~# free -m
              total        used        free      shared  buff/cache   available
Mem:            987         925          11           0          50          19
Swap:           632         621          11

它开始扼杀所有的进程。

root@IA6-OA-DB-01:~# mysql -uroot -proot -D OA2017 < OA2017.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
Killed

赛斯洛

Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.058935] Out of memory: Kill process 24895 (mysql) score 17 or sacrifice child
Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.477043] Out of memory: Kill process 2226 (sssd_be) score 3 or sacrifice child
Apr 14 13:13:35 IA6-OA-DB-01 kernel: [84990.747574] Out of memory: Kill process 2283 (sssd_be) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84990.903214] Out of memory: Kill process 1587 (smbd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84990.971431] Out of memory: Kill process 1587 (smbd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84991.208088] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:36 IA6-OA-DB-01 kernel: [84991.329594] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:38 IA6-OA-DB-01 kernel: [84993.369239] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:39 IA6-OA-DB-01 kernel: [84994.124215] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:41 IA6-OA-DB-01 kernel: [84995.928253] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:44 IA6-OA-DB-01 kernel: [84999.295787] Out of memory: Kill process 1587 (smbd) score 2 or sacrifice child
Apr 14 13:13:48 IA6-OA-DB-01 kernel: [85003.091554] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:13:49 IA6-OA-DB-01 kernel: [85003.498436] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:50 IA6-OA-DB-01 kernel: [85005.624213] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:13:55 IA6-OA-DB-01 kernel: [85007.717901] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85014.623037] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85016.130481] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:02 IA6-OA-DB-01 kernel: [85017.218094] Out of memory: Kill process 858 (sssd) score 2 or sacrifice child
Apr 14 13:14:09 IA6-OA-DB-01 kernel: [85024.577427] Out of memory: Kill process 858 (sssd) score 3 or sacrifice child
Apr 14 13:14:09 IA6-OA-DB-01 kernel: [85024.857977] Out of memory: Kill process 321 (systemd-journal) score 2 or sacrifice child
Apr 14 13:14:11 IA6-OA-DB-01 kernel: [85025.128881] Out of memory: Kill process 14831 (bash) score 2 or sacrifice child
Apr 14 13:14:11 IA6-OA-DB-01 kernel: [85025.173233] Out of memory: Kill process 14831 (bash) score 2 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85048.414654] Out of memory: Kill process 15546 (bash) score 2 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85048.545329] Out of memory: Kill process 1057 (nginx) score 1 or sacrifice child
Apr 14 13:14:34 IA6-OA-DB-01 kernel: [85049.430812] Out of memory: Kill process 15513 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85060.595291] Out of memory: Kill process 1629 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85060.884189] Out of memory: Kill process 1629 (sshd) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85061.268085] Out of memory: Kill process 1061 (nginx) score 1 or sacrifice child
Apr 14 13:14:46 IA6-OA-DB-01 kernel: [85061.470392] Out of memory: Kill process 1280 ((sd-pam)) score 1 or sacrifice child

在这个VM重新启动之后。有什么问题吗？我需要更改哪些变量才能使其运行？

Answer 1

使用所有内存的MySQL

根据MySQL文档，这里缓冲池大小必须始终等于或乘以诺姆b_缓冲器_池子_块块_大小 * 诺姆b_缓冲器_池子_实例。如果将诺姆b_缓冲器_池子_大小配置为不等于诺姆b_缓冲器_池子_块块_大小 * 诺姆b_缓冲器_池子_实例或其倍数的值，缓冲池大小将自动调整为等于或等于诺姆b_缓冲器_池子_块块_大小 * 诺姆b_缓冲器_池子_实例倍数的值。

innodb_buffer_pool_size

InnoDB与MyISAM不同，它使用缓冲池缓存索引和行数据。设置的越大，访问表中的数据所需的磁盘I/O就越少。在专用数据库服务器上，可以将此参数设置为机器物理内存大小的80%。但是，不要将其设置得太大，因为物理内存的竞争可能会导致操作系统中的分页。请注意，在32bit系统中，您可能仅限于每个进程的用户级内存的2-3.5G，因此不要将其设置得太高。

innodb_buffer_pool_instances

InnoDB缓冲池被划分为的区域数。对于具有多千兆字节范围内缓冲池的系统，将缓冲池划分为单独的实例可以提高并发性，方法是在不同线程读取和写入缓存页时减少争用。

例如，innodb_buffer_pool_size设置为8G，innodb_buffer_pool_instances设置为16。innodb_buffer_pool_chunk_size是128M，它是默认值。

8G是一个有效的innodb_buffer_pool_size值，因为8G是innodb_buffer_pool_instances=16 *innodb_buffer_pool_chunk_size=128 m的倍数，即2G。

shell> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+

innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size为调整InnoDB缓冲池大小的操作定义了块大小。诺姆b_缓冲器_池子_大小参数是动态的，它允许您在不重新启动服务器的情况下调整缓冲池的大小。

为了避免在调整大小的操作期间复制所有缓冲池页，该操作将在chunks中执行。默认情况下，innodb_buffer_pool_chunk_size是128MB (134217728字节)。块中包含的页面数取决于诺姆b_页面_大小的值。innodb_buffer_pool_chunk_size可以增加或减少，单位为1MB (1048576字节)。

注意:在更改诺姆b_缓冲器_池子_块块_大小时要小心，因为更改此值会自动增加缓冲池的大小。在更改innodb_buffer_pool_chunk_size之前，请计算它对诺姆b_缓冲器_池子_大小的影响，以确保得到的缓冲池大小是可接受的。

关于您的参考资料这里

Answer 2

一个大问题是:在1GB的机器上，innodb_buffer_pool_size = 768MB太大了。这会导致交换(糟糕的性能)或OOM (更糟)。200米可能没问题。

每个连接都使用一定的空间；我不会将max_connections设置为大于20。

您还提出了哪些其他设置？如果有的话，你应该降低设置-- MySQL，开箱即用，不是为1GB的微型机器(或VM)设计的。

而对于innodb_buffer_pool_instances，则不超过1。

更改[mysqld]部分的my.cnf并重新启动(如果它没有再次崩溃)。

Answer 3

这个问题可以在不重新配置的情况下得到解决:使用--skip-extended-insert重新创建mysqldump。这将导致转储中的每一行逐个插入。

这将控制专门用于在不展开MySQL数据包的情况下执行插入的内存量。

有一次，我在我以前的工作中做了这件事，用的是一台光秃秃的PC (真正的旧工作)，它有1GB的内存。我不得不在Windows上加载一个MySQL数据库。由于执行mysqldumps通常所做的大容量插入，它会反复崩溃。我不得不用--skip-extended-insert重新创建转储。然后，我能够在不崩溃的情况下加载数据库。

这有两个不利的方面：

1. 转储的大小是原来的10倍(这是插入到作为文本的每一行中)。
2. 加载时间长4倍

除了更大的VM之外，重做mysqldump是最好的选择。

我以前也提过这个

• Jul 10, 2015：Windows在执行sql转储时崩溃。
• Apr 15, 2015：mysqldump错误2013年
• Feb 13, 2015：什么时候用mysqldump创建.sql备份变得太大了？