Tornado Web 框架 Mysql 连接处理
我最近一直在探索 Tornado Web 框架，以便为许多不同的客户端提供大量一致的连接。
我有一个请求处理程序，它基本上采用 RSA 加密字符串并对其进行解密。解密后的文本是一个 XML 字符串，由我编写的 SAX 文档处理程序进行解析。一切都工作得很好，执行时间（每个 HTTP 请求）大约为 100 毫秒（包括解密和解析）。
XML 包含用户的用户名和密码哈希。我想连接到 MySQL 服务器以验证用户名是否与应用程序提供的密码哈希匹配。
当我基本上添加以下代码时：
conn = MySQLdb.connect (host = "192.168.1.12",
user = "<useraccount>",
passwd = "<Password>",
db = "<dbname>")
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()
执行 HTTP 请求所需的时间猛增至 4 - 5 秒！我相信这是由于连接到 MySql 数据库服务器本身所花费的时间而引起的。
我的问题是如何加快速度？我可以在全局范围内声明 MySQL 连接并通过创建新游标在请求处理程序中访问它，还是会因为 Tornado 的异步设计而遇到并发问题？
基本上，我怎样才能不必在每个 Http 请求时都产生与 MySQL 服务器的新连接，这样只需要几分之一秒而不是几秒钟的时间来实现。
另外，请注意，SQL 服务器实际上与 Tornado Web Server 实例位于同一台物理计算机上
更新
我只是通过探查器运行了一个简单的 MySQL 查询，代码如下。
对“connections.py”init 函数的调用单独执行需要 4.944 秒。这看起来不对，是吗？
更新 2
我认为使用一个连接（或者甚至使用一个非常简单的数据库连接池）运行几个连接将足够快，足以处理我期望的每个龙卷风 Web 服务器实例的吞吐量。
如果 1,000 个客户端需要访问查询，典型的查询时间为数千秒，最不幸的客户端只需等待一秒钟即可检索数据。
I have recently been exploring the Tornado web framework to serve a lot of consistent connections by lots of different clients.
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).
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.
When I add basically the following code:
conn = MySQLdb.connect (host = "192.168.1.12",
user = "<useraccount>",
passwd = "<Password>",
db = "<dbname>")
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()
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.
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?
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.
Also, please note, the SQL server is actually on the same physical machine as the Tornado Web Server instance
Update
I just ran a simple MySQL query through a profiler, the same code below.
The call to 'connections.py' init function took 4.944 seconds to execute alone. That doesn't seem right, does it?
Update 2
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
考虑 SQLAlchemy，它提供了对 DBAPI 更好的抽象，还提供连接池等。（您可以愉快地忽略它的 ORM 并只使用 SQL 工具包）
（此外，您没有在异步请求处理程序中阻塞数据库调用？）
Consider SQLAlchemy, which provides a nicer abstraction over DBAPI and also provides connection pooling, etc. (You can happily ignore its ORM and just use the SQL-toolkit)
(Also, you're not doing blocking database calls in the asynchronous request handlers?)
SQL 连接不应花费 5 秒。尝试不发出查询，看看这是否会提高您的性能 - 它应该如此。
Mysqldb 模块的线程安全性为“1”，这意味着该模块是线程安全的，但连接不能在线程之间共享。您可以实现连接池作为替代方案。
最后，DB-API 有一个用于查询的参数替换形式，不需要手动连接查询和转义参数：
An SQL connection should not take 5 seconds. Try to not issue a query and see if that improves your performance - which it should.
The Mysqldb module has a threadsafety of "1", which means the module is thread safe, but connections cannot be shared amongst threads. You can implement a connection pool as an alternative.
Lastly, the DB-API has a parameter replacement form for queries which would not require manually concatenating a query and escaping parameters:
在基本处理程序中声明它，每个应用程序都会调用一次。
Declare it in the base handler, it will be called once per application.