Python使用MySQL连接器挂在fetchall上 [英] Python hangs on fetchall using MySQL connector
问题描述
我对Python和MySQL还是很陌生.我正在编写代码，以查询60个不同的表，每个表在五分钟内每秒包含一次记录.该代码每五分钟执行一次.一些查询可以达到1/2 MB的数据，但大多数在50 KB的范围内.我正在使用MySQL Connector/Python在运行Windows 7,64位的工作站上运行.我正在使用PowerShell窗口测试我的代码，但是该代码最终将作为计划任务运行.工作站有足够的RAM(8 GB).其他进程正在运行，但是根据任务管理器，仅使用了一半的内存.大多数情况下，所有操作均按预期执行，但有时处理会挂起.我在代码中插入了打印语句(我也使用了调试器跟踪)来确定挂起的位置.它是在调用fetchall时发生的.以下是代码的相关部分.所有CAPS都是(伪)常量.
I am fairly new to Python and MySQL. I am writing code that queries 60 different tables each containing records for each second in a five minute period. The code executes every five minutes. A few of the queries can reach 1/2 MB of data but most are in the 50 KB range. I am running on a workstation running Windows 7,64-bit using MySQL Connector/Python. I am testing my code using PowerShell windows but the code will eventually run as a scheduled task. The workstation has plenty of RAM (8 GB). Other processes are running but according to the Task Manager only half of memory is being used. Mostly everything performs as expected but sometimes processing hangs. I have inserted print statements in the code (I've also used debugger tracing) to determine where the hang occurs. It is occurring on a call to fetchall. Below is the germane parts of the code. All CAPS are (pseudo)constants.
mncdb = mysql.connector.connect(
option_files=ENV_MCG_MYSQL_OPTION_FILE,
option_groups=ENV_MCG_MYSQL_OPTION_GROUP,
host=ut_get_workstation_hostname(),
database=ENV_MNC_DATABASE_NAME
)
for generic_table_id in DBR_TABLE_INDEX:
site_table_id = DBR_SITE_TABLE_NAMES[site_id][generic_table_id]
db_cursor = mncdb.cursor()
db_command = (
"SELECT *"
+" FROM "
+site_table_id
+" WHERE "
+DBR_DATETIME_FIELD
+" >= '"
+query_start_time+"'"
+" AND "
+DBR_DATETIME_FIELD
+" < '"
+query_end_time+"'"
)
try:
db_cursor.execute(db_command)
print "selected data for table "+site_table_id
try:
table_info = db_cursor.fetchall()
print "extracted data for table "+site_table_id
except:
print "DB exception "+formatExceptionInfo()
print "FETCH failed to return any rows..."
table_info = []
raise
except:
print "uncaught DB error "+formatExceptionInfo()
raise
. . . 使用数据的其他处理 . . . db_cursor.close() mncdb.close() . . . 没有例外.在单独的PowerShell窗口中，我可以访问代码正在处理的数据.对于我的测试，在执行代码之前已加载数据库中的所有数据.测试代码时，没有进程在更新数据库.挂起可能在第一次执行代码时或执行几个小时后发生.
. . . other processing that uses the data . . . db_cursor.close() mncdb.close() . . . No exceptions are being raised. In a separate PowerShell window I can access the data being processed by the code. For my testing all data in the database is loaded before the code is executed. No processes are updating the database while the code is being tested. The hanging can occur on the first execution of the code or after several hours of execution.
我的问题是什么会导致代码挂在fetchall语句上?
My question is what could be causing the code to hang on the fetchall statement?
推荐答案
挂起可能涉及MySQL表本身，而不涉及Python代码.它们包含很多记录吗?他们的桌子很宽吗?它们是否在datetime_field上建立索引?
Hanging could involve the MySQL tables themselves and not specifically the Python code. Do they contain many records? Are they very wide tables? Are they indexed on the datetime_field?
考虑各种策略:
-
具体选择所需的列而不是星号，从而调用所有列.
Specifically select the needed columns instead of the asterisk, calling all columns.
DBR_DATETIME_FIELD上的索引，用于where子句(即隐式连接).
Index on the DBR_DATETIME_FIELD being used in the where clause (i.e., implicit join).
使用打印的计时器print(datetime.datetime.now())进一步诊断，以查看哪些是瓶颈表.为此，请确保导入datetime模块.
Diagnose further with printed timers print(datetime.datetime.now()) to see which are the bottleneck tables. In doing so, be sure to import the datetime module.
这篇关于Python使用MySQL连接器挂在fetchall上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
