MySQL Racing PHP against Python with Gevent and SQLAlchemy

query_test.php

<?php
//read in file
$queries = file('homepage_queries.txt');

$conns = array();
$connList = '';
$queryCount = 0;

//start timer
$start = microtime(true);
//loop queries
foreach($queries as $queryRaw) {
    $queryArr = explode('|', $queryRaw);
    $dsn = $queryArr[0];
    $query = $queryArr[1];
    if(!$conns[$dsn] || !$conns[$dsn]->ping()) {
        $conns[$dsn] = new mysqli(
            'localhost',
            'user',
            '',
            $dsn,
            '3306'
        );
        $connList .= $dsn . ', ';
        if (mysqli_connect_error()) {
            die('Connect Error (' . mysqli_connect_errno() . ') '
                    . mysqli_connect_error());
        }

    }
    if($query) {
        $data = $conns[$dsn]->query($query);
        $queryCount++;
    }

}
$time = microtime(true) - $start;
//end timer

//display output
echo "<pre>";
echo "duration: " . $time . "\n";
echo "connections: " . $connList . "\n";
echo "queries: " . $queryCount;

query_test.py

import time
from flask import Flask
from sqlalchemy import create_engine, text
import gevent
import gevent.monkey
gevent.monkey.patch_all()

app = Flask(__name__)
conns = {}
connList = []
queries = 0


def run_query(query, db_name):
    global conns, connList, queries
    q = text(query)
    con = conns[db_name].connect()
    con.execute(q)
    con.close()

    queries = queries + 1


@app.route("/")
def test_queries():
    """
    queries.txt contains queries in the format of:
    dbname1|select foo ...
    dbname2|select bar ...
    ...
    """
    global queries
    f = open('homepage_queries.txt', 'r')
    queries = 0
    threads = []
    output = '<pre>'
    start = time.time()
    for line in f:
        parsed = line.split('|')
        #is dsn connected?
        db_name = parsed[0]
        query = 'select 1'  # parsed[1].strip()
        if db_name not in conns:
            conns[db_name] = create_engine(
                'mysql+mysqlconnector://[email protected]:3306/' + db_name,
                max_overflow=-1
            )
            connList.append(db_name)
        threads.append(gevent.spawn(run_query, query, db_name))

    print 'joining...'
    gevent.joinall(threads)
    #end timer
    end = time.time()
    duration = end - start
    print duration
    output += "duration: " + repr(duration) + "\n"
    output += "connections: " + repr(connList) + "\n"
    output += "queries: " + repr(queries) + "\n"

    return output

if __name__ == "__main__":
    host = "0.0.0.0"
    #app.debug = True
    app.run(host)

I'm trying to prove that using gevent with SQLAlchemy will be a performant solution, but I can't get it to be faster than regular PHP. I'm hoping that there's something amiss in my Python code (I'm learning python, have written PHP for years).

The python is being run with the Flask server (% python query_test.py), php is just running under normal Apache. I had to set max_overflow=-1 to get SQLAlchemy to run under gevent (as recommended elsewhere in the gevent google group) otherwise it'd just hang after about 50 queries.

The reason I think I'm missing something, is that using gevent improved the time of the script only very slightly vs a non gevent python test, and is still slower than the PHP version. I can go faster with gevent-mysql but I'd like to try and use SQLAlchemy as it'd be useful for the larger project that this is a test for.

Any advice would be greatly appreciated.

update fixes the issue. Had to do with how I was making connections - needed to define them before I made the threads, not within the thread itself.