Clearing out table rows across databases in WordPress Multisite

I've been doing some optimizing of databases on a very large (32k site) WordPress Multisite that currently is split using MultiDB into 256 databases. The size of the databases seemed abnormally large and upon closer inspection I found a lot of rows in the options table for each site that started with jpsq_sync, jpsq_full_sync, and jetpack_sync. Jetpack had been network activated and clearly hadn't been doing the garbage cleanup in the database it was supposed to and between that and _transient rows the overall space MySQL was using was up to 445 GB. A plan was made to deactivate Jetpack network-wide and clean out these rows.

I had some trouble finding good information on how I might do something like that with multiple databases and many options tables within each database. I did find some basic information on running SQL queries on each site using a for loop though and figured a script could be written. Combined with information on how to stage WordPress in a single PHP file I created this script:

<?php
  
define( 'SHORTINIT', true );

require( '/home/username/public_html/wp-load.php' );

// so get all the blog ids from the blogs table
$blogs = $wpdb->get_results("SELECT blog_id FROM {$wpdb->blogs}", ARRAY_A);

// build a sql statement for each blog options table, adding in the blog id for each row
foreach ($blogs as $blog_row) {
 $wpdb->query( "DELETE FROM ".$wpdb->get_blog_prefix($blog_row['blog_id'])."options WHERE option_name LIKE '%jpsq_sync%'" );
 $lastquery = print_r($wpdb->last_query, true);
 error_log($lastquery);
}

?>

The script calls the path to wp-load.php which then lets you make standard WP commands and then gets all blog IDs in the Multisite as an array and loops through them to call a SQL query of our choice. The last few lines log the command which made using tail on the error_log file an easy way to track progress. I did have to max out the PHP max_execution_time in order to make it work given it had over 32,000 options tables to run the command on.

The last step was to run optimize on all tables, we had cleared out a bunch of stuff but MySQL doesn't automatically clear out the space so I ran mysqlcheck -o --all-databases to do that. The overall size of MySQL was brought down to 125 GB and now I have another tool in my belt when I need to run bulk queries on every site in a WordPress Multisite across all databases.