Persistent metadata in hs_history table after run deletion

[erhlloyd]

hi W&B - in our local instance, we’ve noticed that the main parameter logging table in our MySQL db remains at capacity despite manual cleanup of old runs (done through the UI) which we had hoped would free up some space. We thought perhaps it’s keeping those old steps around to be able to perform undo operations, but we don’t need steps from months ago or from runs that have already been deleted.

Is there a better way we can prune the metadata from the history table? Or perhaps is there an admin setting such that everything older than x months could be automatically removed? Thanks!

[MBakirWB]

Hi @erhlloyd, happy to help and we appreciate you writing in with your question. As per our email response from earlier today, here's a SQL procedure that will delete the metrics and logs from the sql database for any runs that have been deleted in the UI. We're planning to integrate this logic into our application soon, but this can be used from a mysql shell to clear up data in the mean time. Please let us know if you have any questions.

[erhlloyd]

hi @MBakirWB - this is great, thank you so much! We're testing it now and will let you know how it goes if there are any problems. Very excited to hear that this will be integrated in W&B soon.

[erhlloyd]

Just to update with some feedback: We tested this on our staging instance and it worked instantaneously, but on the production side which is a much bigger database it gives some timeout and locks issues (Error code 1206: The number of locks exceeds the lock table size.). We've tried increasing database memory, and innodb_buffer_pool_size and sort_buffer_size properties to account for the ORDER BY operations, but it could be the logic could use some optimisation there for larger databases.

[MBakirWB]

Thank you for the update @erhlloyd, looping in @vanpelt for consideration/review of feedback to the pruning script.

[vanpelt]

Hey @erhlloyd have you tried using a smaller batch size? The default in the example uses 100, i.e. CALL batch_delete(100, 1); reducing this to 10 or even lower should reduce the number of locks taken.

[MBakirWB]

Hey @erhlloyd , since we have not heard back from you we are going to close this request. If you would like to re-open the conversation, please let us know!