Mysql在执行update select时超出系统内存

Question

我在mac pro上运行mysql服务器，64GB ram, 6核。我的模式中的表1有3.3亿行。表2有65,000行。(我还有其他几个表，总共有大约15亿行，但它们没有在我尝试的操作中使用，所以我认为它们不相关)。

我试图做我认为是一个相对简单的更新语句(见下文)，把一些数据从表2到表1。然而，我有一个可怕的时间mysql吹满了我的系统内存，迫使我进行交换，并最终冻结了整个系统，使mysql变得无响应，我需要重新启动我的计算机。我的更新语句如下:

UPDATE Table1, Table2 
SET 
Table1.Column1 = Table2.Column1, 
Table1.Column2 = Table2.Column2, 
Table1.Column3 = Table2.Column3, 
Table1.Column4 = Table2.Column4 
WHERE 
(Table1.Column5 = Table2.Column5) AND 
(Table1.Column6 = Table2.Column6) AND 
(Table1.Column7 = Table2.Column7) AND 
(Table1.id between 0 AND 5000000);

最终，我想对表1中的所有3.3亿行执行此更新。我决定把它分成几批，每批500万行，因为

• (a)我遇到了超过锁大小和
的问题
• (b)我想它可能有助于我吹过ram的问题。

以下是有关情况的更多相关细节:

• 我已经在Column5, Column6, Column7(我正在匹配的列的值)的组合上为表1和表2创建了索引。
• 表1有50列，总共约60gb。
• 表2有8列，总共3.5 MB。
• 我知道在这种情况下，有些人可能会推荐外键，而不是用表2中的信息更新表1，但是(a)我有足够的磁盘空间，并不真正关心如何使用它来提高效率(b)这些表中的任何值都不会随着时间的推移而改变(c)我最关心的是在表1上运行的查询速度，如果从表2到表1获取信息需要这么长时间，我当然不希望每次查询都需要重复这个过程。
• 为了解决超过最大锁表大小的问题，我尝试增加innodb_buffer_pool_size。我尝试了一些值。即使在低至8 GB(即1/8我的计算机的ram，我几乎没有运行其他任何东西，而这样做)，我仍然有这个问题的mysqld进程使用基本上所有可用的ram在系统上，然后开始从操作系统拉ram分配(即我的kernel_task开始显示为使用30GB的ram，而它通常使用约2GB)。最大锁的问题似乎已经很大程度上解决了;我不再得到这个错误，虽然也许这只是因为现在我吹过我的记忆和崩溃之前，我可以到达那里。
• 我尝试过较小的批处理大小(100万行，100,000行)。这些似乎工作可能比500万行批次好一点，但它们通常仍然有同样的问题，可能只是发展得慢一点。而且，性能似乎很糟糕-例如，以我进行100,000批处理的速度，执行此更新需要大约7天。
• 表都使用InnoDB
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;虽然我不知道它是否真的有帮助(我是唯一一个以任何方式访问这个数据库的用户，所以我真的不关心锁，如果可以的话，我会完全取消它)
• 我注意到批量运行所需的时间有很多变化。例如，在100万行批处理中，我会观察到时间在45秒到20分钟之间。
• 当我尝试运行一些东西，只是找到匹配的行，然后只把两个列值的这些到一个新的表，我得到了更一致的时间(大约2.5分钟每百万行)。因此，似乎我的问题可能以某种方式源于这样一个事实，也许我正在更新我正在进行匹配的表中的值，即使我正在更新的列与我正在匹配的列不同。
• 我正在匹配和更新的列只包含INT和CHAR类型，最大不超过7个字符。
我运行了CHECK TABLE诊断，它返回正常。总的来说，我非常困惑为什么这将是如此困难。我是mysql和数据库的新手。由于表2非常小，我可以用python使用字典查找来完成同样的任务，我相信速度会快得多。我本以为数据库能够更好地处理这个问题，因为处理和更新大数据集是它们的设计目的。
• 我使用Mysql工作台对查询进行了一些诊断，并确认没有执行全表扫描。不过，这里似乎真的出了什么问题。如果系统有64gb的ram，这比两个表的总大小还要大(尽管计算索引大小，这两个表的大小略大于64gb)，并且如果操作一次只应用于3.3亿行中的500万行，那么它应该耗尽ram是没有意义的。

因此，我想知道:

1. 我写这个更新语句的语法是不是非常糟糕和低效，以至于它会解释可怕的性能和问题?
2. 是否有某种类型的参数超出innodb_buffer_pool_size，我应该配置，要么把一个更牢固的上限ram mysql使用或让它更有效地使用资源?
3. 是否有其他类型的诊断，我应该运行，以尝试检测问题与我的表，模式等?
4. 期待这样的更新需要多少"合理"的时间?

Answer 1

因此，在咨询了几位熟悉这些问题的人之后，我想出了以下解决方案:

1. 我将innodb_buffer_pool_size降低到4GB，即系统总内存的1/16。这似乎终于足够可靠地阻止MySQL耗尽我的64GB内存。

2. 我简化了我的索引，以便它们只包含我需要的列，并确保我使用的所有索引都足够小，可以容纳RAM(还有足够的空间用于MySQL的其他RAM用途)。

3. 我学会了接受MySQL似乎不是为特别大的数据集而构建的(或者，至少不是在一台机器上，即使是像我这样的一台相对较大的机器)。因此，我接受手动将我的作业分解成批通常是必要的，因为显然MySQL的机制不具备做出正确决定的能力，即如何自行分解作业，以便对系统资源(如RAM)负责。

4. 有时候，当我在做这样的工作时，或者一般来说，在我的中等规模的数据集上，我会使用MySQL来进行更新和连接。其他时候，我只是将数据分解成块，然后在另一个程序中进行连接或其他类似的操作，例如R(通常使用像data这样的包)。

5. 我还被建议，作为替代，我可以在Hadoop集群上使用Pig之类的东西，它应该能够更好地处理这种大小的数据。