我使用
python(Django)作为我的网店.
当我测试高负载(db访问)得到有趣的结果:
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;
UPDATE1
有趣的事情
选择始终为id = 1的行:
唉,MYSQL使用缓存和查询会非常快,但再次缓慢和100%的CPU利用率.但是同样的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";
}
红宝石代码:
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
尝试使用ourql和pymysql – 更糟的结果.