Tag: php

  • Getting WordPress Database Size via WP-CLI

    Getting WordPress Database Size via WP-CLI

    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!

  • Quick Tip: Export WordPress SQL output via WP-CLI

    Quick Tip: Export WordPress SQL output via WP-CLI

    If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:

    /**
     * Runs a SQL query against the site database.
     *
     * ## OPTIONS
     *
     * 
     * : SQL Query to run.
     *
     * [--format=]
     * : Render output in a particular format.
     * ---
     * default: table
     * options:
     * - table
     * - csv
     * - json
     * - count
     * - yaml
     * ---
     *
     * [--dry-run=]
     * : Performa a dry run
     *
     * @subcommand sql
     */
    public function sql( $args, $assoc_args ) {
         global $wpdb;
     
         $sql     = $args[0];
         $format  = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
         $dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
     
         // Just some precautions.
         if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
             WP_CLI::error( 'Please do not modify the database with this command.' );
         }
     
         if ( 'false' !== $dry_run ) {
             WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: <code>EXPLAIN</code> of the query is below: https://mariadb.com/kb/en/explain/' ) );
             $sql = 'EXPLAIN ' . $sql;
         }
     
         // Fetch results from database.
         $results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB
     
         // Output data.
         WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
    }Code language: PHP (php)

    I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)

  • Converting CSV to SQL

    Converting CSV to SQL

    I was recently working on an issue that required me to dig through gigabytes of CSV files to look for patterns and data points. I had no intention of bringing all of this data in to Excel, because there’s not enough RAM on earth for that to work. Instead I thought it would be easier to dump the data into MariaDB (MySQL would be fine as well) and just write some simple SQL statements.

    There were a few things I tried, such as LOAD DATA INFILE and other SQL type things. Nothing really worked properly, so instead I decided to just convert the CSV files into SQL statements I could import.

    This is a bad idea! Don’t ever let this touch a live production database!

    I wrote this little PHP script to convert all of the CSV files in a directory to SQL:

    <?php
    $files    = glob( './*.{csv}', GLOB_BRACE );
    $db_table = 'csv_table';
    
    foreach ( $files as $file ) {
    	$row       = 0;
    	$handle    = fopen( $file, 'rb' );
    	$row_names = '';
    
    	if ( false !== $handle ) {
    		while ( false !== ( $data = fgetcsv( $handle ) ) ) {
    			$row++;
    			$values = '';
    
    			if ( 1 === $row ) {
    				// Do Header things.
    				$index  = 0;
    
    				echo sprintf( 'DROP TABLE IF EXISTS `%s`;' . PHP_EOL, $db_table );
    				echo sprintf( 'CREATE TABLE `%s` (' . PHP_EOL, $db_table );
    
    				foreach ( $data as $key => $value ) {
    					$index++;
    					$data[ $key ] = strtolower( preg_replace( '/[^A-Za-z0-9]/', '_', $value ) );
    
    					// Add a comma if we're not at the end of the array.
    					if ( count( $data ) !== $index ) {
    						echo sprintf( '	`%s` text NOT NULL,' . PHP_EOL, $data[ $key ] );
    					} else {
    						echo sprintf( '	`%s` text NOT NULL' . PHP_EOL, $data[ $key ] );
    					}
    				}
    
    				echo ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;' . PHP_EOL;
    				$row_names = '(`' . implode( '`,`', $data ) . '`)';
    				continue;
    			}
    
    			foreach ( $data as $key => $value ) {
    				$data[ $key ] = fake_sql_escape( $value );
    			}
    
    			$values = '(\'' . implode( '\',\'', $data ) . '\')';
    
    			echo sprintf( 'INSERT INTO `%s` %s VALUES %s;' . PHP_EOL, $db_table, $row_names, $values );
    		}
    		fclose( $handle );
    	}
    }
    
    function fake_sql_escape( $value ) {
    	// https://stackoverflow.com/questions/1162491/alternative-to-mysql-real-escape-string-without-connecting-to-db/1163091#1163091
    	// Totaly not safe for production!
    	$return = '';
    	$length = strlen( $value );
    
    	for ( $i = 0; $i < $length; ++$i ) {
    		$char = $value[ $i ];
    		$ord  = ord( $char );
    		if (
    			"'" !== $char &&
    			'"' !== $char &&
    			'\\' !== $char &&
    			$ord >= 32 &&
    			$ord <= 126
    		) {
    			$return .= $char;
    		} else {
    			$return .= '\\x' . dechex( $ord );
    		}
    	}
    	return $return;
    }Code language: PHP (php)

    Borrowing the example airtravel.csv from https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html this is what the output looks like:

    DROP TABLE IF EXISTS `csv_table`;
    CREATE TABLE `csv_table` (
    	`month` text NOT NULL,
    	`1958` text NOT NULL,
    	`1959` text NOT NULL,
    	`1960` text NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JAN','  340','  360','  417');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('FEB','  318','  342','  391');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAR','  362','  406','  419');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('APR','  348','  396','  461');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('MAY','  363','  420','  472');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUN','  435','  472','  535');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('JUL','  491','  548','  622');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('AUG','  505','  559','  606');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('SEP','  404','  463','  508');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('OCT','  359','  407','  461');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('NOV','  310','  362','  390');
    INSERT INTO `csv_table` (`month`,`1958`,`1959`,`1960`,`type`) VALUES ('DEC','  337','  405','  432');Code language: JavaScript (javascript)

    And if you’re too lazy to see what the input CSV looks like (I would be) here it is, in its LGPL glory:

    "Month", "1958", "1959", "1960"
    "JAN",  340,  360,  417
    "FEB",  318,  342,  391
    "MAR",  362,  406,  419
    "APR",  348,  396,  461
    "MAY",  363,  420,  472
    "JUN",  435,  472,  535
    "JUL",  491,  548,  622
    "AUG",  505,  559,  606
    "SEP",  404,  463,  508
    "OCT",  359,  407,  461
    "NOV",  310,  362,  390
    "DEC",  337,  405,  432Code language: JavaScript (javascript)

    There’s a lot of ways that this could be made better, but it works in a quick pinch. If you need something similar, feel free to take this as a starting point.

  • Deleting Old Post Revisions in WordPress with WP-CLI

    Deleting Old Post Revisions in WordPress with WP-CLI

    Recently I’ve been working with a client who’s site we’re going to soon be migrating. To help with any downtime, we’ve been looking at reducing their database size, which is something around 50-60 gigabytes. After looking through the database, one easy win would be to purge as many post revisions as possible, since their wp_posts table was about 50% revisions 😱

    We couldn’t just mass delete anything with a revision post type though, because the client had some specific needs:

    • Keep all revisions for posts from the last 12 months.
    • Keep all revisions that were made after the post was published.
    • Keep a backup of all revisions deleted.

    To do this, I crafted a custom WP-CLI command to purge these revisions. I’ve accidentally deleted the final version of the script, since it was only a one-run thing, but here’s an earlier version that could be a good starting point for anyone else that has a similar need to prune revisions:

    /**
     * WP-CLI command that deletes pre-publish post revisions for posts older than 12 months.
     *
     * @subcommand prune-revisions [--live] [--verbose]
     */
    public function prune_revisions( $args, $assoc_args ) {
    	global $wpdb;
    
    	$live    = (bool) $assoc_args[ 'live' ];
    	$verbose = (bool) $assoc_args[ 'verbose' ];
    	$offset  = 0;
    	$limit   = 500;
    	$count   = 0;
    	$deletes = 0;
    
    	if ( $live ) {
    		$output_file = sanitize_file_name( sprintf( 'prune-revisions-backup_%d_%d.csv', get_bloginfo( 'name' ), current_time( 'timestamp', true ) ) );
    		$handle      = fopen( $output_file, 'wb' );
    
    		if ( false === $handle ) {
    			WP_CLI::error( sprintf( 'Error opening %s for writing!', $output_file ) );
    		}
    
    		// Headers.
    		$csv_headers = array(
    			'ID',
    			'post_author',
    			'post_date',
    			'post_date_gmt',
    			'post_content',
    			'post_title',
    			'post_excerpt',
    			'post_status',
    			'comment_status',
    			'ping_status',
    			'post_password',
    			'post_name',
    			'to_ping',
    			'pinged',
    			'post_modified',
    			'post_modified_gmt',
    			'post_content_filtered',
    			'post_parent',
    			'guid',
    			'menu_order',
    			'post_type',
    			'post_mime_type',
    			'comment_count',
    			'filter',
    		);
    
    		fputcsv(
    			$handle,
    			$csv_headers
    		);
    	}
    
    	$count_sql      = 'SELECT COUNT(ID) FROM ' . $wpdb->posts . ' WHERE post_type = "revision"';
    	$revision_count = (int) $wpdb->get_row( $count_sql, ARRAY_N )[0];
    	$progress       = \WP_CLI\Utils\make_progress_bar( sprintf( 'Checking %s revisions', number_format( $revision_count ) ), $revision_count );
    
    	do {
    		$sql       = $wpdb->prepare( 'SELECT ID FROM ' . $wpdb->posts . ' WHERE post_type = "revision" LIMIT %d,%d', $offset, $limit );
    		$revisions = $wpdb->get_results( $sql );
    
    		foreach ( $revisions as $revision ) {
    			$count++;
    			$post_parent_id = wp_get_post_parent_id( $revision->ID );
    
    			// Fail on either 0 or false.
    			if ( false == $post_parent_id ) {
    				WP_CLI::warning( sprintf( 'Revision %d does not have a parent!  Skipping!', $revision->ID ) );
    				continue;
    			}
    
    			$revision_modified   = get_post_modified_time( 'U', false, $revision->ID );
    			$parent_publish_time = get_post_time( 'U', false, $post_parent_id );
    
    			if ( $parent_publish_time < current_time( 'timestamp') - ( MONTH_IN_SECONDS * 12 ) ) {
    				// Post is older than 12 months, safe to delete pre-publish revisions.
    				if ( $revision_modified < $parent_publish_time ) {
    					if ( $live ) {
    						// We're doing it live!
    						WP_CLI::log( sprintf( 'Deleting revision %d for post %d. (%d%% done)', $revision->ID, $post_parent_id, ( $count / $revision_count ) * 100 ) );
    
    						// Backup data!
    						$output = [];
    						$data   = get_post( $revision->ID );
    
    						// Validate the field is set, just in case.  IDK how it couldn't be.
    						foreach ( $csv_headers as $field ) {
    							$output = isset( $data->$field ) ? $data->$field : '';
    						}
    
    						fputcsv( $handle, $output );
    
    						$did_delete = wp_delete_post_revision( $revision->ID );
    
    						// Something went wrong while deleting the revision?
    						if ( false === $did_delete || is_wp_error( $did_delete ) ) {
    							WP_CLI::warning( sprintf( 'Revision %d for post %d DID NOT DELETE! wp_delete_post_revision returned:', $revision->ID, $post_parent_id ) );
    						}
    						$deletes++;
    
    						// Pause after lots of db modifications.
    						if ( 0 === $deletes % 50 ) {
    							if ( $verbose ) {
    								WP_CLI::log( sprintf( 'Current Deletes: %d', $deletes ) );
    							}
    							sleep( 1 );
    						}
    					} else {
    						// Not live, just output info.
    						WP_CLI::log( sprintf( 'Will delete revision %d for post %d.', $revision->ID, $post_parent_id ) );
    					}
    				} else {
    					// Revision is after the post has been published.
    					if ( $verbose ) {
    						WP_CLI::log( sprintf( 'Post-Publish: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
    					}
    				}
    			} else {
    				// Post is too new to prune.
    				if ( $verbose ) {
    					WP_CLI::log( sprintf( 'Too-New: Will NOT delete Revision %d for post %d.', $revision->ID, $post_parent_id ) );
    				}
    			}
    		}
    
    		// Pause after lots of db reads.
    		if ( 0 === $count % 5000 ) {
    			if ( $verbose ) {
    				WP_CLI::log( sprintf( 'Current Count: %d', $count ) );
    			}
    			sleep( 1 );
    		}
    
    		// Free up memory.
    		$this->stop_the_insanity();
    
    		// Paginate.
    		if ( count( $revisions ) ) {
    			$offset += $limit;
    			$progress->tick( $limit );
    		} else {
    			WP_CLI::warning( 'Possible MySQL Error, retrying in 10 seconds!' );
    			sleep( 10 );
    		}
    
    	} while ( $count < $revision_count );
    
    	$progress->finish();
    
    	if ( $live ) {
    		fclose( $handle );
    		WP_CLI::success( sprintf( 'Deleted %d revisions', $deleted ) );
    	} else {
    		WP_CLI::success( sprintf( 'Processed %d revisions', $revision_count ) );
    	}
    }Code language: PHP (php)
  • Debugging WordPress Hooks: Speed

    Debugging WordPress Hooks: Speed

    If you google debugging WordPress hooks you’ll find a lot of information.

    About 1,180,000 results

    Let’s add another one.

    WordPress hooks are powerful, but also complex under the hood. There’s plenty of topics I could talk about here, but right now I’m only going to talk about speed. How long does it take for a hook to fire and return?

    Some cool work in this area has already been done thanks to Debug Bar Slow Actions and Query Monitor, but outside of something like Xdebug or New Relic, you’ll have a hard time figuring out how long each individual hook callback takes without modifying either WordPress core or each hook call.

    … or maybe not …

    While doing some client debugging for my job at WordPress VIP (did I mention we’re hiring?) I came across the need to do this exact thing. I’ve just finished the code that will make this happen, and I’m releasing it into the wild for everyone to benefit.

    What’s the problem we’re trying to solve? Well, this client has a sporadic issue where posts saving in the admin time out and sometimes fail. We’ve ruled out some potential issues, and are looking at a save_post hook going haywire.

    Now I can capture every single save_post action, what the callback was, and how long it took. Here’s an example for this exact post:

    START: 10:save_post, (Callback: `delete_get_calendar_cache`)
    STOP: 10:save_post, Taken 132.084μs (Callback: `delete_get_calendar_cache`)
    START: 10:save_post, (Callback: `sharing_meta_box_save`)
    STOP: 10:save_post, Taken 15.974μs (Callback: `sharing_meta_box_save`)
    START: 10:save_post, (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    STOP: 10:save_post, Taken 19.073μs (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    START: 10:save_post, (Callback: `SyntaxHighlighter::mark_as_encoded`)
    STOP: 10:save_post, Taken 19.073μs (Callback: `SyntaxHighlighter::mark_as_encoded`)
    START: 10:save_post, (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    STOP: 10:save_post, Taken 16.928μs (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    START: 20:save_post, (Callback: `Publicize::save_meta`)
    STOP: 20:save_post, Taken 428.915μs (Callback: `Publicize::save_meta`)
    START: 9000:save_post, (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    STOP: 9000:save_post, Taken 11.921μs (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    START: 1:save_post, (Callback: `The_SEO_Framework\Load::_update_post_meta`)
    STOP: 1:save_post, Taken 6.016ms (Callback: `The_SEO_Framework\Load::_update_post_meta`)
    START: 1:save_post, (Callback: `The_SEO_Framework\Load::_save_inpost_primary_term`)
    STOP: 1:save_post, Taken 1.535ms (Callback: `The_SEO_Framework\Load::_save_inpost_primary_term`)
    START: 10:save_post, (Callback: `delete_get_calendar_cache`)
    STOP: 10:save_post, Taken 179.052μs (Callback: `delete_get_calendar_cache`)
    START: 10:save_post, (Callback: `sharing_meta_box_save`)
    STOP: 10:save_post, Taken 247.002μs (Callback: `sharing_meta_box_save`)
    START: 10:save_post, (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    STOP: 10:save_post, Taken 25.988μs (Callback: `Jetpack_Likes_Settings::meta_box_save`)
    START: 10:save_post, (Callback: `The_SEO_Framework\Load::delete_excluded_ids_cache`)
    STOP: 10:save_post, Taken 185.966μs (Callback: `The_SEO_Framework\Load::delete_excluded_ids_cache`)
    START: 10:save_post, (Callback: `SyntaxHighlighter::mark_as_encoded`)
    STOP: 10:save_post, Taken 15.020μs (Callback: `SyntaxHighlighter::mark_as_encoded`)
    START: 10:save_post, (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    STOP: 10:save_post, Taken 12.875μs (Callback: `AMP_Post_Meta_Box::save_amp_status`)
    START: 20:save_post, (Callback: `Publicize::save_meta`)
    STOP: 20:save_post, Taken 377.893μs (Callback: `Publicize::save_meta`)
    START: 9000:save_post, (Callback: `Debug_Bar_Slow_Actions::time_stop`)
    STOP: 9000:save_post, Taken 14.067μs (Callback: `Debug_Bar_Slow_Actions::time_stop`)Code language: CSS (css)

    So how does it work?

    1. We add an all action that will fire for every other action.
    2. In the all callback, we make sure we’re looking for the correct hook.
    3. We then build an array to store some data, use the $wp_filter global to fill out information such as the priority and the callback, and store the start time.
    4. Next we have to add a new action to run for our hook right before the callback we want to time. We use the fact that, even though add_action() is supposed to use an int for the priority, it will also accept a string. We add new hooks, and re-prioritze all of the existing hooks with floats that are stringified.
    5. This allows us to capture the start time and end time of each individual callback, instead of the priority group as a whole.

    Of course, this does add a tiny bit of overhead, and could cause some problems if any other plugins use stringified hook priorities, or other odd issues–so be careful 🙂

    Finally, here’s the code:

    class VIP_Hook_Timeline {
    	public $hook;
    	public $callbacks     = [];
    	public $callback_mod  = 0.0001;
    	public $callback_mods = [];
    
    	public function __construct( $hook ) {
    		$this->hook = $hook;
    		add_action( 'all', array( $this, 'start' ) );
    	}
    
    	public function start() {
    		// We only want to get a timeline for one hook.
    		if ( $this->hook !== current_filter() ) {
    			return;
    		}
    
    		global $wp_filter;
    
    		// Iterate over each priority level and set up array.
    		foreach( $wp_filter[ $this->hook ] as $priority => $callback ) {
    			// Make the mod counter if not exists.
    			if ( ! isset( $this->callback_mods[ $priority ] ) ) {
    				$this->callback_mods[ $priority ] = $priority - $this->callback_mod;
    			}
    
    			// Make the array if not exists.
    			if ( ! is_array( $this->callbacks[ $priority ] ) ) {
    				$this->callbacks[ $priority ] = [];
    			}
    
    			// Iterate over each callback and set up array.
    			foreach( array_keys( $callback ) as $callback_func ) {
    				if ( ! is_array( $this->callbacks[ $priority ][ $callback_func ] ) ) {
    					$this->callbacks[ $priority ][ $callback_func ] = [ 'start' => 0, 'stop' => 0 ];
    				}
    			}
    		}
    
    		foreach( $this->callbacks as $priority => $callback ) {
    			foreach ( array_keys( $callback ) as $callback_func ) {
    
    				// Get data befmore we move things around.
    				$human_callback = $this->get_human_callback( $wp_filter[ $this->hook ][ $priority ][$callback_func] );
    
    				// Modify the priorities.
    				$pre_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				$new_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				$post_callback_priority = $this->callback_mods[ $priority ];
    				$this->callback_mods[ $priority ] = $this->callback_mods[ $priority ] + $this->callback_mod;
    
    				// Move the callback to our "new" priority.
    				if ( $new_callback_priority != $priority ) {
    					$wp_filter[ $this->hook ]->callbacks[ strval( $new_callback_priority ) ][ $callback_func ] = $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ];
    					unset( $wp_filter[ $this->hook ]->callbacks[ $priority ][ $callback_func ] );
    					if ( empty( $wp_filter[ $this->hook ]->callbacks[ $priority ] ) ) {
    						unset( $wp_filter[ $this->hook ]->callbacks[ $priority ] );
    					}
    				}
    
    				// Add a new action right before the one we want to debug to capture start time.
    				add_action( $this->hook, function( $value = null ) use ( $callback_func, $priority, $human_callback ) {
    					$this->callbacks[ $priority ][ $callback_func ]['start'] = microtime( true );
    
    					// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.
    					//$message = 'START: %d:%s, (Callback: `%s`)';
    					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
    					//error_log( sprintf( $message,
    					//	$priority,
    					//	$this->hook,
    					//	$human_callback,
    					//) );
    
    					// Just in case it's a filter, return.
    					return $value;
    				}, strval( $pre_callback_priority ) );
    
    				// Add a new action right after the one we want to debug to capture end time.
    				add_action( $this->hook, function( $value = null ) use ( $callback_func, $priority, $human_callback ) {
    					$this->callbacks[ $priority ][ $callback_func ]['stop'] = microtime( true );
    
    					// Uncomment this if you just want to dump data to the PHP error log, otherwise add your own logic.
    					//$message = 'STOP: %d:%s, Taken %s (Callback: `%s`)';
    					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
    					//error_log( sprintf( $message,
    					//	$priority,
    					//	$this->hook,
    					//	$this->get_human_diff( $priority, $callback_func ),
    					//	$human_callback,
    					//) );
    
    					// Just in case it's a filter, return.
    					return $value;
    				}, strval( $post_callback_priority ) );
    				
    			}
    		}
    	}
    
    	public function get_human_callback( $callback ) {
    		$human_callback = '[UNKNOWN HOOK]';
    		if ( is_array( $callback['function'] ) && count( $callback['function'] ) == 2 ) {
    			list( $object_or_class, $method ) = $callback['function'];
    			if ( is_object( $object_or_class ) ) {
    				$object_or_class = get_class( $object_or_class );
    			}
    			$human_callback =  sprintf( '%s::%s', $object_or_class, $method );
    		} elseif ( is_object( $callback['function'] ) ) {
    			// Probably an anonymous function.
    			$human_callback =  get_class( $callback['function'] );
    		} else {
    			$human_callback =  $callback['function'];
    		}
    		return $human_callback;
    	}
    
    	public function get_start( $priority, $callback_func ) {
    		return (float) $this->callbacks[ $priority ][ $callback_func ]['start'];
    	}
    
    	public function get_stop( $priority, $callback_func ) {
    		return (float) $this->callbacks[ $priority ][ $callback_func ]['stop'];
    	}
    
    	public function get_diff( $priority, $callback_func ) {
    		return (float) ( $this->get_stop( $priority, $callback_func ) - $this->get_start( $priority, $callback_func ) );
    	}
    
    	public function get_human_diff( $priority, $callback_func ) {
    		$seconds = $this->get_diff( $priority, $callback_func );
    
    		// Seconds.
    		if ( $seconds >= 1 || $seconds == 0 ) {
    			return number_format( $seconds, 3 ) . 's';
    		}
    
    		// Milliseconds.
    		if ( $seconds >= .001 ) {
    			return number_format( $seconds * 1000, 3 ) . 'ms';
    		}
    
    		// Microseconds.
    		if ( $seconds >= .000001 ) {
    			return number_format( $seconds * 1000000, 3 ) . 'μs';
    		}
    
    		// Nanoseconds.
    		if ( $seconds >= .000000001 ) {
    			// WOW THAT'S FAST!
    			return number_format( $seconds * 1000000000, 3 ) . 'ns';
    		}
    
    		return $seconds . 's?';
    	}
    }
    new VIP_Hook_Timeline( 'save_post' );Code language: PHP (php)

  • Better Caching in WordPress

    Better Caching in WordPress

    Caching data in WordPress is easy. Caching data in WordPress in a good and performant way takes a bit more work. For instance, many developers commonly use the Transients API to cache data. As the lowest common denominator in caching, this is okay. It’ll get the job done, even on a $10/year shared hosting plan. But what we should do instead is leverage the WP_Object_Cache functions to provide more functionality and better features.

    For instance, let’s say we want to cache the result of an external API request. One way we could do this would be this way:

    Note: These examples are terrible, but I hope they get the point across!

    function get_api( $value) {
    	$value = absint( $value );
    	$api_data = get_transient( 'example-api-data-' . $value );
    
    	if ( false === $api_data ) {
    		$api_data = file_get_contents( 'https://example.com/api/' . $value );
    		set_transient( 'example-api-data-' . $value, $api_data, HOUR_IN_SECONDS * 6 );
    	}
    
    	return json_decode( $api_data );
    }Code language: PHP (php)

    What’s one way we could make this better by using the WP_Object_Cache functions? Well, what happens if the API data structure changes, and you need to invalidate every cache value? It would be pretty hard to know the exact transient keys that you’d need to clear, and clearing the entire cache is a bit too nuclear for this (but it would work). Instead, wp_cache_*() could be used, which includes the ability to use a cache group that can be changed:

    function get_api( $value) {
    	$value = absint( $value );
    	$cache_group = 'example-api-data';
    
    	$api_data = wp_cache_get( $value, $cache_group );
    
    	if ( false === $api_data ) {
    		$api_data = file_get_contents( 'https://example.com/api/' . $value );
    		wp_cache_set( $value, $api_data, $cache_group, HOUR_IN_SECONDS * 6 );
    	}
    
    	return json_decode( $api_data );
    }Code language: PHP (php)

    With this, if we ever need to invalidate the cache for this API, we just need to change the $cache_group value, and all cache requests will be new.


    Another common theme I see is caching too much data. Let’s say you’re going to do a slow WP_Query, and want to cache the results for better performance:

    function get_new_posts() {
    	$posts = wp_cache_get( 'new-posts' );
    
    	if ( false === $posts ) {
    		$posts = new WP_Query( 'posts_per_page=5000' );
    		wp_cache_set( 'new-posts', $posts );
    	}
    
    	return $posts;
    }Code language: PHP (php)

    Sure, that’s fine and it’ll work but… the WP_Query object is huge!

    echo strlen( serialize( new WP_Query( 'posts_per_page=500' ) ) ); … 2,430,748

    That’s 2.5 megs of data needing to be transferred out of cache on every pageload. If your cache is accessed across the network on another server, this introduces more delay as it has to transfer. Also, some caching solutions might put a limit on the size of an individual cache object–which means that an object like this might never be cached!

    Instead, we can just grab the IDs of the posts, and do a second, much faster query:

    function get_new_posts() {
    	$post_ids = wp_cache_get( 'new-posts' );
    
    	if ( false === $posts ) {
    		$post_ids = new WP_Query( 'posts_per_page=5000&fields=ids' );
    		wp_cache_set( 'new-posts', $posts->posts );
    	}
    
    	$posts = new WP_Query( [ 'post__in' => $post_ids ] );
    
    	return $posts;
    }Code language: PHP (php)

    echo strlen( serialize( $posts->posts ) ); … only 88,838 bytes, that’s like a 96%-something difference!

    I had a few more ideas for this post, but it’s been sitting as a draft forever and I don’t remember. It’s possible this topic might be revisited some day 🙂

  • Quick Tip: DreamHost cron and WP-CLI

    Quick Tip: DreamHost cron and WP-CLI

    If you’re hosting your WordPress website on DreamHost, and use their cron system to offload your WordPress faux-cron for better reliability, be careful of what version of PHP you have in your code.

    I recently had an issue where my cron events weren’t firing, and after enabling email output, I ended up with something like this PHP error message:

    Parse error: syntax error, unexpected '?' in /path/to/file.php on line 123

    It turns out that WP-CLI was running PHP 5.x via the DreamHost cron system.  I had PHP 7.x specific code in my theme.
    To fix this, I had to set the WP_CLI_PHP environment variable in my cron job:

    export WP_CLI_PHP=/usr/local/php72/bin/php
    wp cron event run --due-now --path=/home/path/to/wp/ --url=https://example.com/Code language: JavaScript (javascript)