Python 的 db-query 很慢，但 Perl 没有

Question

我的网上商店使用 python (Django)。

当我测试高负载(数据库访问)时得到了有趣的结果:

python 10 process = 200sec / 100% CPU utilisation
perl 10 process  = 65sec / 35% CPU utilisation

Centos 6、python 2.6、mysql 5.5、标准库、其他服务器上的 mysql-server。表 product_cars 有 70 000 000 条记录。

为什么 python 程序这么慢？

Python 程序:

#!/usr/bin/python
import MySQLdb
import re
from MySQLdb import cursors
import shutil
import datetime
import random

db0 = MySQLdb.connect(user="X", passwd="X", db="parts")
cursor0 = db0.cursor()
cursor0.execute('SET NAMES utf8')

now = datetime.datetime.now()
for x in xrange(1, 100000):
    id = random.randint(10, 50000)
    cursor0.execute("SELECT * FROM product_cars WHERE car_id=%s LIMIT 500", [id])
    cursor0.fetchone()

Perl 程序:

#!/usr/bin/perl
use DBI;
my $INSTANCE=$ARGV[0];
my $user = "x";
my $pw = "x";
my $db = DBI->connect( "dbi:mysql:parts", "x", "x");
my $sql= "SELECT * FROM product_cars WHERE car_id=? LIMIT 500";
foreach $_ ( 1 .. 100000 )
{
 $random = int(rand(50000));
 $cursor = $db->prepare($sql);
 $cursor->execute($random) || die $cursor->errstr;
 @Data= $cursor->fetchrow_array();
}

$cursor->finish;
$db->disconnect;

---

更新1

有趣的事情:

始终选择 id=1 的行:

很明显，MYSQL 使用缓存和查询会非常快，但同样会很慢，而且 CPU 使用率为 100%。但相同的 perl 或 ruby​​ 代码运行速度很快。

如果在python代码中替换字符串:

# remove "SET NAMES utf8" string - this has no impact
# python-mysql use "%s", but not "?" as parameter marker
id = 1
for x in xrange(1, 100000):
    id = 1
    cursor0.execute("SELECT * FROM product_cars WHERE car_id=%s LIMIT 500", [id])
    cursor0.fetchone()

perl 中的相同代码:

foreach $_ ( 1 .. 20000 )
{
 $cursor = $db->prepare( "SELECT * FROM product_cars WHERE car_id=? LIMIT 500";);
 $cursor->execute(1);
#    while (my @Data= $cursor->fetchrow_array())
 if ($_ % 1000 == 0) { print "$_\n" };.
 @Data= $cursor->fetchrow_array();
# print "$_\n";
}

ruby 代码:

pk=2
20000.times do |i|
    if i % 1000 == 0
        print i, "\n"
    end
    res = my.query("SELECT * FROM product_cars WHERE car_id='#{pk}' LIMIT 500")
    res.fetch_row
end

---

更新 2

Exec SQL "SELECT * FROM product WHERE id=1" (string without params) 100000 times
Python: ~15 sec 100% CPU 100%
Perl:   ~9 sec CPU 70-90%
Ruby:   ~6 sec CPU 60-80%

其他机器上的 MySQL 服务器。

---

更新 3

尝试使用 oursql 和 pymysql - 结果更差。

Answer 1

正如人们所指出的，您在两者之间准备和执行语句的方式是不一样的，这不是推荐的做法。两者都应该利用准备好的语句，并且都应该在循环之外进行准备。

但是，看起来 Python MySQL 驱动程序没有利用 server side prepared statements根本。这可能是性能不佳的原因。

服务器端prepared statements 是在MySQL 4.1 中加入的，但是一些驱动适应的很慢。 MySQLdb users guide没有提到准备好的语句，并认为“MySQL 中没有游标，也没有参数替换”，这从 MySQL 4.1 开始就不是这样了。它还说“MySQLdb 的连接和游标对象是用 Python 编写的”而不是利用 MySQL API。

您可能想查看 oursql driver .看起来它是为了利用"new"MySQL API 而编写的，让数据库进行 self 优化。

DBD::mysql(Perl MySQL 驱动程序)可以利用准备好的语句，但它 does not by default根据文档。您必须通过将 mysql_server_prepare=1 添加到您的 dsn 来打开它。这将使 Perl 示例运行得更快。或者文档在撒谎，默认情况下它们是打开的。

顺便说一句，虽然不能解释 2 分钟的差异，但会偏离基准的一件事是生成随机数。它们的成本很高。

Python代码

#!/usr/bin/python
import random

for x in xrange(1, 100000):
    id = random.randint(0, 50000)

Perl 代码

#!/usr/bin/perl
foreach $_ ( 1 .. 100000 )
{
 $random = int(rand(50000));
}

Python时间

real    0m0.194s
user    0m0.184s
sys     0m0.008s

Perl 时间

real    0m0.019s
user    0m0.015s
sys     0m0.003s

为了防止这成为更敏感的基准测试中的问题，请增加一个计数器。