mysql - "show full processlist"显示没有耗时的进程，但我的脚本耗时太长

Question

我有一个驻留在我的网络服务器的 cron 上的脚本，它应该每晚运行。由于超过了网络服务器在 cron 作业上设置的时间限制，它最近已停止运行。它曾经运行良好。任何时候我手动运行它，它都非常快(不到 5 分钟)。一下子就过了半个多小时。

该脚本主要更新 MySQL 数据库。根据他们的说法，数据库大约为 60mb。我似乎找不到这个信息，但它似乎是合理的(虽然我每晚传输到服务器的文件只有 2mb 左右)。

我已经采取了他们建议的步骤来优化我的数据库，但没有任何结果。脚本运行仍然需要很长时间。脚本所做的就是从数据库中删除所有内容，然后用我们更新的 list 再次填充它。

所以现在我在一个 Putty 窗口中运行“显示完整进程列表”，同时在另一个窗口中运行脚本。 “show full processlist”只显示了几个项目，这两个项目都暂时显示为 0。

mysql> show full processlist;
+-----------+--------------+--------------------+-------------------------+---------+------+-------+-----------------------+
| Id        | User         | Host               | db                      | Command | Time | State | Info                  |
+-----------+--------------+--------------------+-------------------------+---------+------+-------+-----------------------+
| 142841868 | purposely omitted | purposely omitted | purposely omitted_net_-_main | Sleep   |    0 |       | NULL                  |
| 142857238 | purposely omitted | purposely omitted | NULL                    | Query   |    0 | NULL  | show full processlist |
+-----------+--------------+--------------------+-------------------------+---------+------+-------+-----------------------+
2 rows in set (0.05 sec)

如果我一直非常快速地使用 show full processlist 命令，偶尔我可以捕捉到此表中列出的其他内容，但在我下次运行它时它们就会消失。这向我表明它们的处理速度非常快!

那么有人知道哪里出了问题吗？我对此很陌生:(

谢谢!!

PS 这是我的代码

#!/usr/bin/perl
use strict;
use DBI;
     my $host = 'PURPOSLEY OMITTED';
     my $db = 'PURPOSLEY OMITTED';
     my $db_user = 'PURPOSLEY OMITTED';
     my $db_password = "PURPOSLEY OMITTED";

my $dbh = DBI->connect("dbi:mysql:$db:$host", "$db_user", "$db_password");
$dbh->do("DELETE FROM main");
$dbh->do("DELETE FROM keywords");

open FH, "PURPOSLEY OMITTED" or die;

while (my $line = <FH>) {

my @rec = split(/\|/, $line);

print $rec[1].' : '.$rec[2].' : '.$rec[3].' : '.$rec[4].' : '.$rec[5].' : '.$rec[6].' : '.$rec[7];

$rec[16] =~ s/"//g;
$rec[17] =~ s/"//g;
$rec[13] =~ chomp($rec[13]);

my $myquery = "INSERT INTO main (medium, title, artist, label, genre, price, qty, catno,barcode,created,received,tickler,blurb,stockid) values (\"$rec[0]\",\"$rec[1]\",\"$rec[2]\",\"$rec[3]\",\"$rec[4]\",\"$rec[5]\",\"$rec[6]\",\"$rec[7]\",\"$rec[8]\",\"$rec[9]\",\"$rec[10]\",\"$rec[11]\",\"$rec[12]\",\"$rec[13]\")"; 

$dbh->do($myquery);

$dbh->do("INSERT IGNORE INTO keywords VALUES (0, '$rec[2]','$rec[13]')");
$dbh->do("INSERT LOW_PRIORITY IGNORE INTO keywords VALUES (0, \"$rec[1]\", \"$rec[13]\")");

print "\n";
}
close FH;

$dbh->disconnect();

Answer 1

我有两个建议:

1. (影响较小)使用 TRUNCATE 而不是 DELETE，它明显更快，并且在您不需要担心自动 -增值。
2. 稍微重组以分批处理插入内容。通常我通过保持给定大小的堆栈变量(从 20 行开始)来做到这一点，并且对于前 20 行，它只是填充堆栈；但在第 20 行，它实际上也执行插入并重置堆栈。这可能会令您大吃一惊:-)

伪代码:

const buffer_size = 20
while(row) {
  stack.addvalues(row.values)
  if(stack.size >= buffer_size) {
    // INSERT INTO mytable (fields) VALUES stack.all_values()
    stack.empty()
  }

然后使用“缓冲区”大小。我看到过将缓冲区一次调整到 100-200 行以上的脚本，将大量导入速度提高了几乎同样多的倍数(即，在执行单个 INSERT 的“开销”(网络，网络，等等)