PHP系统()使用MySQL在多个数据库上运行查询

Question

以下是我的脚本，它遍历了380个MySQL InnoDB数据库并运行各种创建表格、插入、更新等操作以迁移模式。它从连接到云数据库服务器的Web服务器运行。我将迁移脚本排除在本问题之外，因为我认为这与问题无关。

我遇到了一个问题，正在尝试找到解决方法。

我有一个运行MySQL 5.6的4GB RAM云数据库服务器。我将380个具有40个表格的数据库迁移到了59个表格。在进行到70%左右时，我遇到了以下错误。它在一次迁移中间就停止了，服务器崩溃了。我观察了内存使用情况，发现它耗尽了内存。它是作为数据库服务运行的，所以我没有对服务器进行根访问，因此我不知道所有的细节。

在phppoint_smg上运行查询

---

Warning: Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 355: Lost connection to MySQL server during query

在 phppoint_soulofhalloween 数据库上运行查询

---

Warning: Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

在phppoint_srvais上运行查询

---

Warning: Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

这是 PHP 脚本的简化版本。

db_host = escapeshellarg($db_host);
$db_user = escapeshellarg($db_user);
$db_password = escapeshellarg($db_password);
foreach($databases as $database)
{
    echo "Running queries on $database\n***********************************\n";
    system("mysql --host=$db_host --user=$db_user --password=$db_password --port=3306 $database < ../update.sql"); 
    echo "\n\n";
}

我的问题:

在进行迁移时，有没有办法避免内存使用量增加？我一个数据库一个数据库地进行迁移。或者是表和数据的增加导致了内存使用量增加吗？

迁移后我能够使用服务器，并删除了80个数据库并完成了迁移。它有800mb的空闲空间；我预计它会下降到600mb。 在迁移之前，它是500mb。

- Chris Muench

---

鉴于似乎没有时间限制：我的理解是否正确，这不是一个生产服务器？ - DaSourcerer

我不确定你所说的时间限制是什么意思？在错误发生后，我通过提供更多可用内存来完成脚本以解决问题。但我想为将来解决这个问题。 - Chris Muench

你使用的引擎是什么？你有多少RAM？SHOW VARIABLES LIKE "%buffer%"; 也许你配置MySQL的方式不太好。 - Rick James

这是InnoDB的配置。我在考虑将InnoDB缓冲池减半。https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984 - Chris Muench

当我准备回答自己的问题时：Web服务器具体扮演什么角色？除了更改MySQL设置的能力外，您对数据库服务器有多少访问权限？您是否拥有shell（ssh）访问权限并且可以运行自己的脚本？ - DaSourcerer

Answer 1

很明显，您的迁移 SQL 查询会拖垮服务器。看起来数据库在这类操作中有太少的空闲 RAM。根据数据库文件大小和查询，它确实会增加 RAM 使用量。由于不知道确切的服务器规格、数据库中的数据以及您发出的查询，因此没有确切的答案可以帮助您。

- Brain Foo Long

---

好的，谢谢。我有大约17GB的数据库数据。我想我很快就要升级了。你觉得如果我做迁移时选择冷迁移或分组进行是否有所帮助？这是一个异常大的迁移过程。 - Chris Muench

我不明白的一件事是，我逐个运行它们，但内存线性下降。我原以为在迁移完一个数据库后会释放一些内存。 - Chris Muench

我甚至不知道数据库的具体机制，但所有这些听起来都合理，随着更多语句的执行，内存会下降。如果它被（误）配置为在RAM中保存（过多）信息，那么您会看到这样的特征。这一切取决于很多因素，由于缺乏信息，我们无法深入探讨，抱歉。只有您的服务器管理员才能真正提供帮助。 - Brain Foo Long

好的，我们将尝试更改一些缓冲区和缓存设置，以使用更少的RAM。 - Chris Muench

很明显...不！杀死服务器的可能不是SQL查询。它们只是促使DBMS缓存其配置所承诺的内存 - 这些内存操作系统无法提供（或服务器存在一些有问题的RAM）。 - symcbean

@symcbean，我从未说过这是一种常见的行为。我的答案特别依赖于这个情况，因为显然这是唯一一个会导致服务器崩溃的原因。如果我的回答有点让人困惑，对此我表示抱歉。 - Brain Foo Long

Answer 2

你的PHP示例不会占用太多内存，也没有在数据库服务器上运行，而正是这个服务器出了问题，对吗？所以问题可能出在你配置的MySQL参数上。

根据你的Gist和使用简单的MySQL内存计算器，我们可以看到你的MySQL服务最多可以使用3817MB的内存。如果服务器在错误发生时只有4GB，那么很可能就是这个原因（你需要为操作系统和运行应用程序保留一些额外的内存）。增加内存或微调服务器将解决此问题。请查看MySQL服务器变量的文档页面，以更好地理解每个值。

然而，这可能不是断开连接/超时的唯一原因（但它似乎是你的情况，因为增加内存解决了问题）。另一个常见问题是低估max_allowed_packet值（在你的配置中为16MB），因为这样的脚本很容易具有超过此值的查询（例如，如果你为单个INSERT INTO table ...设置了多个值）。

请记住，max_allowed_packet应该大于你向数据库发出的最大命令（它不是SQL文件，而是其中每个命令，在;之间的块）。

但请考虑更加谨慎地调整，因为糟糕的配置可能会突然崩溃或变得无响应，而不需要增加更多内存也可以完美运行。我建议运行性能调整脚本，如MySQLTuner-perl，它将分析你的数据、索引使用情况、慢查询甚至提供你需要优化服务器的调整建议。

- Capilé

---

我使用了MySQL调整器，它显示最大使用量为3400 MB。这个结果好吗？我已经更新了要点以获取最新的设置。此外，这是MySQL调整器的结果：https://gist.github.com/blasto333/669717ceec7e2d9f314967f7fa525b0d - Chris Muench

这里还有最新的my.cnf设置 https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984 - Chris Muench

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Capilé

我认为在4GB的系统中使用3.4GB有些冒险...您还应考虑到可以连接到服务器的最大客户端数量——例如，如果您有100个PHP Web服务器进程的限制，则不建议在数据库中使用410个最大连接数。 - Capilé

Answer 3

不要生成大量的进程，生成一个文件，然后运行它。可以像以下方式生成该文件：

$out = fopen('tmp_script.sql', 'w');
foreach($databases as $database)
{
    fwrite($out, "USE $database;\n");
    fwrite($out, "source ../update.sql;\n");
}
fclose($out);

然后，手动或以编程的方式执行以下步骤：

mysql ... < tmp_script.sql

可能手动操作会更安全，这样PHP就不会干扰。

- Rick James

---

我不确定 PHP 是否与此有关，因为 Web 服务器正常运行；问题出在数据库服务器上。除非你是在说 MySQL 进程有某种影响？ - Chris Muench

我无法确定是什么原因导致了问题。我的建议将排除PHP的影响并加快整个过程的速度。 - Rick James

Answer 4

如果你的数据库服务器崩溃了（或被oom killer杀死），那么原因是它被配置为使用比设备上可用的内存更多的内存。你忘记告诉我们数据库节点运行的操作系统是什么。如果您没有root访问权限，则这是配置它的人的错误。内存超额提交应该被禁用（需要root访问权限）。像mysqltuner这样的工具将向您显示DBMS配置使用多少内存（需要管理员特权）。另请参见此percona帖子。

- symcbean

---

这是一个通过一个知名提供商提供的数据库服务，我大约有99.9999%的把握它是某种变体的Linux（CentOS / Redhat）。我将询问他们关于内存超额分配的问题，并了解一下该工具的情况。 - Chris Muench

为了对那些具有管理员访问权限的人有类似问题的受益者，我已经包括了可能原因的详细信息。如果您正在付费使用数据库服务，那么我对您声称数据库崩溃表示怀疑。您确定数据库崩溃而不仅仅是您的会话失败了吗？无论如何，您支付的服务的部分包括解决此类问题的支持。可能只是服务器配置了非常低的超时 / mysql数据包大小。 - symcbean

Answer 5

我认为他们关于RAM的说法是正确的，但值得注意的是你使用的工具很重要。

如果你尝试过http://www.mysqldumper.net/，使用它（php脚本）时请检查php内存限制的设置并让它自动检测。

我曾经使用http://www.ozerov.de/bigdump/，但它太慢了，所以我不再使用它。

另一方面，mysqldumper备份和恢复都非常快速，不会崩溃（如果你设置了内存限制）。

我发现这个工具非常出色。

- iGNEOS

Answer 6

如果您的服务器显然缺乏RAM，有一件事可以尝试以减轻RAM压力，即在循环完成后对大型数组进行unset并强制进行垃圾回收。

我曾经在PHP7下使用PTHREADS（拥有512Go RAM）处理1024个异步连接到MariaDB和Postgresql时遇到类似的问题，这些连接都是在一个庞大的服务器上运行的。

请尝试在每个循环中执行此操作。

//first unset main immediately at loop start:
unset($databases[$key]);

// second unset process and purge immediately
unset($database);
gc_collect_cycles();

此外，设置一个控件来不断监测负载下的RAM使用情况，以查看是否在特定的$database上发生这种情况。如果你的RAM太低，将控件设置为分块处理$database并进行多次插入批处理，并在完成时取消设置。这将清除更多的RAM并避免子插入循环之前过大的数组复制。特别是当你使用具有构造函数的类时。对于4Go，我倾向于设置400到500个异步插入最大批次，取决于你的插入全局长度。

- cpugourou

---

当内存耗尽是PHP端的问题，而且/因为MySQL服务器运行在同一主机上时，这是一个有价值的建议。但从OP的问题来看，我认为这不是这种情况。 - LSerni

Answer 7

更新：

你的评论完全改变了情况。
这是我的更新答案：

由于您无法访问MySQL服务器，因此需要采取一些替代方法。

强制从导入文件中删除所有特殊的“东西”，例如封闭事务、插入延迟/忽略等。

强制使用单个语句执行SQL - 我不知道插入语句的样子，但请使用单个语句 - 单个插入 - 不要在单个语句中捆绑多行，

例如：

而不是

insert into x
             (...fields...)values(...single row...),
             (...fields...)values(...single row...), 
             (...fields...)values(...single row...), 
             (...fields...)values(...single row...)
;

做

insert into x(...fields...)values(...single row...); 
insert into x(...fields...)values(...single row...); 
insert into x(...fields...)values(...single row...); 
insert into x(...fields...)values(...single row...); 

然后试试这些：

• 你可以尝试使用具有大缓冲区等功能的 my.ini 进行“上传”。这样，MySQL服务器的提供者可能会为您提供更多的RAM。毕竟这是服务 :)

• 您可以尝试生成包含模式和数据文件的文件。然后导入模式，逐个表格地开始导入并查看其崩溃位置，并恢复已崩溃的文件。

• 您可以使用 MyISAM 表格导入所有内容，然后将这些转换为 InnoDB。 alter table x engine=innodb 。但是，这样做将丢失所有参照完整性，并且您需要稍后强制执行。

• 您可以使用 MyISAM 表格导入所有内容，然后不转换它们，可以每个表格执行以下操作：

像这样：

alter table x rename to x_myisam;
create table x(...);
insert into x select * from x_myisam;

我相信有一个单一的表格破坏了这个过程。如果你找到了它，你可以手动进行。例如，一次导入10000行或其他。

替代方法

如果你的服务器在亚马逊AWS或类似服务中，你可以尝试“扩展”（“放大”）服务器进行导入，然后在导入完成后“缩小”。

旧答案

why do you use php script? try create or generate via php a shell script. then run shell script.

also is very important to create huge swap file on the system. here is one way to do it. It might not work on older systems:

sudo su # became root
cd /somewhere
fallocate -l 16gb file001
mkswap file001
chmod 0 file001
swapon file001

Then execute the php or shell script.

Once is done, you can swapoff and remove the file or make it permanent in fstab.

Let me know if i need to clarify anything.

- Nick

---

Web服务器上没有内存使用问题；只有数据库服务器上的问题。这是一个无交换空间的数据库服务，我只能调整my.cnf设置。我需要一个PHP脚本，因为有很多逻辑需要确定哪些数据库需要升级。 - Chris Muench

哦，我看到你没有访问数据库服务器的权限。那么，你什么也做不了。可能你可以使用myisam导入数据，然后使用alter table xxx engine=innodb更改为innodb。这将破坏你可能拥有的任何引用完整性，但这是你唯一能做的事情。这是AWS吗？你能只为数据负载购买更大的实例吗？ - Nick

我将仅为导入而进行扩展，或找出另一种减少内存使用的方法。 - Chris Muench

我很好奇这样做是否可以 - 请告诉我。 - Nick