Databases
StackOverflow2013
Postsdbo
POPython have slow db-query, but Perl not
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPython have slow db-query, but Perl not
    primarykey
    data
    text
    <p>I use python (Django) for my web-shop. </p> <p>When I tested high loading (db access) got interesting results:</p> <pre><code>python 10 process = 200sec / 100% CPU utilisation perl 10 process = 65sec / 35% CPU utilisation </code></pre> <p>Centos 6, python 2.6, mysql 5.5, standard libraries, mysql-server on other server. Table product_cars have 70 000 000 records.</p> <p><strong>Why python-program so slow?</strong></p> <p>Python program:</p> <pre><code>#!/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() </code></pre> <p>Perl program:</p> <pre><code>#!/usr/bin/perl use DBI; my $INSTANCE=$ARGV[0]; my $user = "x"; my $pw = "x"; my $db = DBI-&gt;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-&gt;prepare($sql); $cursor-&gt;execute($random) || die $cursor-&gt;errstr; @Data= $cursor-&gt;fetchrow_array(); } $cursor-&gt;finish; $db-&gt;disconnect; </code></pre> <hr> <p><strong>update1</strong></p> <p><strong>Interesting thing:</strong> </p> <p><strong>select always row with id=1:</strong></p> <p>Сlear that MYSQL use cache and query will be very fast, but again slow and 100% CPU utilisation. But same perl or ruby code work quick.</p> <p>if replace string in python code:</p> <pre><code># 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() </code></pre> <p>Same code in perl:</p> <pre><code>foreach $_ ( 1 .. 20000 ) { $cursor = $db-&gt;prepare( "SELECT * FROM product_cars WHERE car_id=? LIMIT 500";); $cursor-&gt;execute(1); # while (my @Data= $cursor-&gt;fetchrow_array()) if ($_ % 1000 == 0) { print "$_\n" };. @Data= $cursor-&gt;fetchrow_array(); # print "$_\n"; } </code></pre> <p>Code in ruby:</p> <pre><code>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 </code></pre> <hr> <p><strong>update 2</strong></p> <pre><code>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% </code></pre> <p>MySQL-server on other machine.</p> <hr> <p><strong>update 3</strong></p> <p>Tried use oursql and pymysql - worse results.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew G
    1. USAndrew G
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPython have slow db-query, but Perl not
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPython have slow db-query, but Perl not
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPython have slow db-query, but Perl not
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI also notice you haven't executed 'SET NAMES utf8' in the Perl version.
      singulars
      1. POPython have slow db-query, but Perl not
      1. USDerek Litz
    2. CO@Derek: I don't use MySQLdb but AFAIK it uses `%s` as the parameter marker, and supplying the actual args as a list instead of a tuple is unconventional rather than incorrect.
      singulars
      1. POPython have slow db-query, but Perl not
      1. USJohn Machin
    3. CO@Derek: see stackoverflow.com/questions/825042
      singulars
      1. POPython have slow db-query, but Perl not
      1. USJohn Machin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload