1

So we have a database server (MySQL) that has 40 databases (20 database As and 20 database Bs). The As are named db_a_01, db_a_02.. and so on (Bs are db_b_01.. and so on). The As have the same structure and so have the Bs, but the As and the Bs have different structures.

Now, the As have a procedure (TransferItemsData()) that selects from a table, processes the data, then inserts the processed data to their respective B database table (db_a_08 to db_b_08). All also had an event (ScheduleTransfer) that calls the procedure TransferItemsData() and was set to execute at 11:30 PM.

So 20 databases were set to transfer data at 11:30 PM at the same time. Upon checking the logs table this morning though, I found out that only the databases that had less than 200k items to transfer were able to finish the transfer, with the longest taking 29 mins. Those databases with more than 200k rows to transfer weren't able to transfer all items. They also did not log a date_completed, so I'm sure either the procedures or the events were stopped.

Is there an execution time limit for either procedures or events? They seemed to have stopped at about 30 mins in. Or are they stopped at 12am? The transfers started at 11:30 PM. Why were the procedures / events stopped?

xtan
  • 41
  • 1
  • 3
  • 1
    This may help you troubleshoot. https://dba.stackexchange.com/questions/51510/where-events-history-are-logged-recorded-in-mysql I've done tonnage of this kind of thing, and found it necessary to write the stored procs so they loop and do their work in batches of a few hundred or thousand rows each. Otherwise the transaction redo logs may get too big. AND. optimize those queries with the right indexes. – O. Jones May 20 '20 at 09:59

0 Answers0