Databases
StackOverflow2013
Postsdbo
POTornado Web Framework Mysql connection handling
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTornado Web Framework Mysql connection handling
    text
    copied!<p>I have recently been exploring the Tornado web framework to serve a lot of consistent connections by lots of different clients.</p> <p>I have a request handler that basically takes an RSA encrypted string and decrypts it. The decrypted text is an XML string that gets parsed by a SAX document handler that I have written. Everything works perfectly fine and the execution time (per HTTP request) was roughly 100 milliseconds (with decryption and parsing).</p> <p>The XML contains the Username and Password hash of the user. I want to connect to a MySQL server to verify that the username matches the password hash supplied by the application.</p> <p>When I add basically the following code:</p> <pre><code>conn = MySQLdb.connect (host = "192.168.1.12", user = "&lt;useraccount&gt;", passwd = "&lt;Password&gt;", db = "&lt;dbname&gt;") cursor = conn.cursor() safe_username = MySQLdb.escape_string(XMLLoginMessage.username) safe_pass_hash = MySQLdb.escape_string(XMLLoginMessage.pass_hash) sql = "SELECT * FROM `mrad`.`users` WHERE `username` = '" + safe_username + "' AND `password` = '" + safe_pass_hash + "' LIMIT 1;" cursor.execute(sql) cursor.close() conn.close() </code></pre> <p>The time it takes to execute the HTTP request shoots up to 4 - 5 seconds! I believe this is incurred in the time it takes to connect to the MySql database server itself.</p> <p>My question is how can I speed this up? Can I declare the MySQL connection in the global scope and access it in the request handlers by creating a new cursor, or will that run into concurrency issues because of the asynchronous design of Tornado?</p> <p>Basically, how can I not have to incur a new connection to a MySQL server EVERY Http request, so it only takes a fraction of a second instead of multiple seconds to implement.</p> <p>Also, please note, the SQL server is actually on the same physical machine as the Tornado Web Server instance</p> <h1>Update</h1> <p>I just ran a simple MySQL query through a profiler, the same code below.</p> <p>The call to 'connections.py' <strong>init</strong> function took 4.944 seconds to execute alone. That doesn't seem right, does it?</p> <h1>Update 2</h1> <p>I think that running with one connection (or even a few with a very simple DB conn pool) will be fast enough to handle the throughput I'm expecting per tornado web server instance.</p> <p>If 1,000 clients need to access a query, typical query times being in the thousands of seconds, the unluckiest client would only have to wait one second to retrieve the data.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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