One WP-CLI command that I’ve found handy is this db-size command. It allows you to output a site’s registered database tables along with the data and index size in any format that WP-CLI natively supports, with multiple sort options:
/**
* Gets size of database tables for the current site.
*
* ## OPTIONS
*
* [--raw]
* : Outputs full size in bytes instead of human readable sizes.
*
* [--order_by=<Total><total>]
* : Allows custom ordering of the data.
* ---
* default: Total
* options:
* - Table
* - Data Size
* - Index Size
* - Total
* ---
*
* [--order=<asc><asc>]
* : Allows custom ordering direction of the data.
* ---
* default: asc
* options:
* - asc
* - desc
* ---
*
* [--format=<format><format>]
* : Render output in a particular format.
* ---
* default: table
* options:
* - table
* - csv
* - json
* - count
* - yaml
* ---
*
* @subcommand db-size
*/
public function db_size( $args, $assoc_args ) {
global $wpdb;
$output = array();
$db_size = array();
$order_by = WP_CLI\Utils\get_flag_value( $assoc_args, 'order_by', 'Total' );
$order = WP_CLI\Utils\get_flag_value( $assoc_args, 'order', 'asc' );
$format = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
$raw = (bool) WP_CLI\Utils\get_flag_value( $assoc_args, 'raw', false );
// Fetch list of tables from database.
$tables = array_map(
function( $val ) {
return $val[0];
},
$wpdb->get_results( 'SHOW TABLES;', ARRAY_N ) // phpcs:ignore WordPress.DB.DirectDatabaseQuery
);
// Fetch table information from database.
$report = array_map(
function( $table ) use ( $wpdb ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery
return $wpdb->get_row( "SHOW TABLE STATUS LIKE '$table'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
},
$tables
);
foreach ( $report as $table ) {
// Keep a running total of sizes.
$db_size['data'] += $table->Data_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
$db_size['index'] += $table->Index_length; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
// Format output for WP-CLI's format_items function.
$output[] = array(
'Table' => $table->Name, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
'Data Size' => $table->Data_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
'Index Size' => $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
'Total' => $table->Data_length + $table->Index_length, // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
);
}
// Sort table data.
self::sort_table_by( $order_by, $output, $order );
if ( ! $raw ) {
// Make data human readable.
foreach ( array_keys( $output ) as $key ) {
$output[ $key ]['Data Size'] = size_format( $output[ $key ]['Data Size'] );
$output[ $key ]['Index Size'] = size_format( $output[ $key ]['Index Size'] );
$output[ $key ]['Total'] = size_format( $output[ $key ]['Total'] );
}
}
// Output data.
WP_CLI\Utils\format_items( $format, $output, array( 'Table', 'Data Size', 'Index Size', 'Total' ) );
// Output totals if we're not piping somewhere.
if ( ! WP_CLI\Utils\isPiped() ) {
WP_CLI::success(
sprintf(
'Total size of the database for %s is %s. Data: %s; Index: %s',
home_url(),
WP_CLI::colorize( '%g' . size_format( $db_size['data'] + $db_size['index'] ) . '%n' ),
WP_CLI::colorize( '%g' . size_format( $db_size['data'] ) . '%n' ),
WP_CLI::colorize( '%g' . size_format( $db_size['index'] ) . '%n' )
)
);
}
}
/**
* Sorts a table by a specific field and direction.
*
* @param string $field The field to order by.
* @param array &$array The table array to sort.
* @param string $direction The direction to sort. Ascending ('asc') or descending ('desc').
*/
private function sort_table_by( $field, &$array, $direction ) {
// Taken from https://joshtronic.com/2013/09/23/sorting-associative-array-specific-key/ Thanks!
usort(
$array,
function ( $a, $b ) use ( $field, $direction ) {
$a = $a[ $field ];
$b = $b[ $field ];
if ( $a === $b ) {
return 0;
}
switch ( $direction ) {
case 'asc':
return ( $a < $b ) ? -1 : 1;
case 'desc':
return ( $a > $b ) ? -1 : 1;
default:
return 0;
}
}
);
return true;
}</format></asc></total>Code language: PHP (php)Here’s some example output from one of my test sites:
$ wp test db-size
+----------------------------+-----------+------------+-------+
| Table | Data Size | Index Size | Total |
+----------------------------+-----------+------------+-------+
| wp_mlp_relationships | 16KB | 0B | 16KB |
| wp_redacted_table | 16KB | 0B | 16KB |
| wp_redacted_table | 16KB | 0B | 16KB |
| wp_3_links | 16KB | 16KB | 32KB |
| wp_term_relationships | 16KB | 16KB | 32KB |
| wp_site | 16KB | 16KB | 32KB |
| wp_registration_log | 16KB | 16KB | 32KB |
| wp_mlp_site_relations | 16KB | 16KB | 32KB |
| wp_mlp_languages | 16KB | 16KB | 32KB |
| wp_mlp_content_relations | 16KB | 16KB | 32KB |
| wp_links | 16KB | 16KB | 32KB |
| wp_redacted_table | 16KB | 16KB | 32KB |
| wp_3_term_relationships | 16KB | 16KB | 32KB |
| wp_blog_versions | 16KB | 16KB | 32KB |
| wp_2_links | 16KB | 16KB | 32KB |
| wp_2_term_relationships | 16KB | 16KB | 32KB |
| wp_2_term_taxonomy | 16KB | 32KB | 48KB |
| wp_2_commentmeta | 16KB | 32KB | 48KB |
| wp_2_postmeta | 16KB | 32KB | 48KB |
| wp_term_taxonomy | 16KB | 32KB | 48KB |
| wp_3_commentmeta | 16KB | 32KB | 48KB |
| wp_2_termmeta | 16KB | 32KB | 48KB |
| wp_2_terms | 16KB | 32KB | 48KB |
| wp_termmeta | 16KB | 32KB | 48KB |
| wp_commentmeta | 16KB | 32KB | 48KB |
| wp_blogmeta | 16KB | 32KB | 48KB |
| wp_blogs | 16KB | 32KB | 48KB |
| wp_2_a8c_cron_control_jobs | 16KB | 32KB | 48KB |
| wp_redacted_table | 16KB | 32KB | 48KB |
| wp_3_terms | 16KB | 32KB | 48KB |
| wp_3_termmeta | 16KB | 32KB | 48KB |
| wp_3_term_taxonomy | 16KB | 32KB | 48KB |
| wp_redacted_table | 16KB | 32KB | 48KB |
| wp_terms | 16KB | 32KB | 48KB |
| wp_3_postmeta | 16KB | 32KB | 48KB |
| wp_usermeta | 16KB | 32KB | 48KB |
| wp_sitemeta | 16KB | 32KB | 48KB |
| wp_users | 16KB | 48KB | 64KB |
| wp_postmeta | 16KB | 48KB | 64KB |
| wp_posts | 16KB | 64KB | 80KB |
| wp_signups | 16KB | 64KB | 80KB |
| wp_2_posts | 16KB | 64KB | 80KB |
| wp_3_posts | 16KB | 64KB | 80KB |
| wp_2_comments | 16KB | 80KB | 96KB |
| wp_comments | 16KB | 80KB | 96KB |
| wp_3_comments | 16KB | 80KB | 96KB |
| wp_2_options | 80KB | 32KB | 112KB |
| wp_3_options | 80KB | 32KB | 112KB |
| wp_options | 176KB | 32KB | 208KB |
+----------------------------+-----------+------------+-------+
Success: Total size of the database for https://www.example.com is 2.6MB. Data: 1MB; Index: 1.5MBCode language: JavaScript (javascript)Enjoy!
Leave a Reply