I have another issue that may not be related to Pr...
# ask-communityj
Jarvis Stubblefield
12/16/2022, 3:59 PMI have another issue that may not be related to Prefect, but thought I would post here in the off chance someone has had this issue when using Prefect. I have a task that looks at about 30-50,000 records total if it looks at all of the records in the table. Transactions are to be committed in blocks of 1,000 records checked. For each 1k there are less than that being updated. Previously at times this task would take a few hours when it really had to go over 3-5,000 records. The first three days I started this task it took a while which was to be expected as I stopped manually running the task. However, it is now again taking a LONG time and I’m ending up with MySQL Lock Table Timeouts
MySQLdb.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')Jarvis Stubblefield
12/16/2022, 4:02 PMSo to further explain “LONG” time … the task from yesterday has been running for over 19 hours. These are daily tasks… this lock has so far affected one user being able to log into the system and also affected my running a different one off task to update some records.
Jarvis Stubblefield
12/16/2022, 4:03 PMThis is the long running task flow…
Copy code
@flow
def update_and_alert_about_users_who_need_logs():
logger = get_run_logger()
# Update any users who need new auto logs.
transaction.set_autocommit(False)
num_updates = 0
num_last_log_alerts = 0
num_changes = 0
active = HREUser.active.all()
active.filter(Q(org__in=Organization.active.all()))
for user in active:
try:
if LivePerformance.maybe_update(user):
user.save()
num_updates += 1
num_changes += 1
if user.logs_needed_alert():
num_last_log_alerts += 1
num_changes += 1
except KeyboardInterrupt:
raise
except:
traceback.print_exc()
if num_changes > 1000:
<http://logger.info|logger.info>("Committing progress...")
num_changes = 0
transaction.commit()
<http://logger.info|logger.info>("Committing changes.")
transaction.commit()
<http://logger.info|logger.info>(f"{num_updates} users needed a score update.")
<http://logger.info|logger.info>(f"{num_last_log_alerts} users needed a last log alert.")Jarvis Stubblefield
12/16/2022, 4:23 PMSo I’m trying to figure out what’s going on … and it seems that the database isn’t doing anything … not sure how to debug this.
Copy code
mysql> show full processlist;
+--------+------------+--------------------+-----------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------------+--------------------+-----------------+---------+------+----------+-----------------------+
| 884310 | xxxxxxxxxx | localhost | NULL | Sleep | 3 | | NULL |
| 890453 | xxxxxxxxxx | xxx.xx.xx.75:35920 | tenzinga_django | Sleep | 0 | | NULL |
| 894731 | xxxxxxxxxx | xxx.xx.xx.31:53302 | tenzinga_django | Sleep | 1771 | | NULL |
| 894732 | xxxxxxxxxx | xxx.xx.xx.31:53304 | tenzinga_django | Query | 0 | starting | show full processlist |
+--------+------------+--------------------+-----------------+---------+------+----------+-----------------------+
4 rows in set (0.00 sec)Jarvis Stubblefield
12/16/2022, 4:43 PMI honestly don’t know how to really read the
show engine innodb status;Jarvis Stubblefield
12/16/2022, 4:43 PMI’m tempted to kill the process that seems to be sleeping.
Jarvis Stubblefield
12/16/2022, 5:00 PMWell I checked the agent status … it is still outputting print statements … so it is still running.
3 Views