Search – Server – Issue

I’m assuming it is a DB issue but it is actually a php fpm processes causing issues … :/

get_posts() ends up using WP_Query class, so you’re not gaining anything. The problem is the post meta schema is inherently inefficient. There might be some tweaks that might help with DB performance, but they generally would not gain a significant improvement. The simplest solution is to pay for greater server resources to avoid hitting imposed limits.

The best way to optimize meta queries is to not use the post meta schema. Instead build a custom table where each filter criteria is in its own column. But completely restructuring a large DB is not something to be taken lightly.

By using a custom table, you cannot use WP_Query or get_posts() anymore. Instead you’d need to make custom SQL queries using the global $wpdb object.

@bcworkz tks for the suggestions.

@bcworkz I just received a report from the server guy and the issue was being caused by another function that imports vehicles by reading a CSV file every 3 hours… this CSV is being run via CRON and sometimes it hits the server limits… I’m putting the code here to see if you or someone has a better idea of how to accomplish this task:

//import CSV
add_action("wp_ajax_fn_import_csv", "fn_import_csv");
add_action( 'wp_ajax_nopriv_fn_import_csv', 'fn_import_csv' );

$row = false;
function fn_import_csv(){
    define('EXECUTION', uniqid());
    echo "Execution ".EXECUTION."<br><br>";
    update_option('inventory_update', current_time('m/d/Y h:i A')); //update settings field
    $file = fopen(ABSPATH."inventory/MP7220_website.csv", "r");
    while (($data = fgetcsv($file)) !== FALSE){
        if($row == true){
            //check if INSERT or UPDATE
            $rsCheck = array('posts_per_page' => 1, 'post_type' => 'inventory', 'post_status' => 'any', 'meta_query' => array('relation' => 'AND', array('key' => 'stock','value' => $data[1], 'compare' => '=')));
            $arCheck = get_posts($rsCheck);

            $title = $data[4]." ".$data[5]." ".$data[6]." ".$data[10]." ".$data[16];
            
            if(count($arCheck) == 0){ //insert inventory
                echo "<b style='color:#2271b1'>INSERT</b> <i>".$data[1]."</i> - ".$title."<br>";
                $id = wp_insert_post(array('post_title' => $title, 'post_type'=> 'inventory', 'post_status' => 'publish'));
                $photosPieces = explode(";", $data[28]);

                $photos = "";
                for($i = 0; $i < count($photosPieces); $i++){
                    $photos .= "<img src='".$photosPieces[$i]."' style='height:100px!important'>";
                }
                add_post_meta($id, 'photos', $photos);
                add_post_meta($id, 'photos_urls', $data[28]);
                add_post_meta($id, 'has_photos', (count($photosPieces) == "1") ? "N" : "Y");
                add_post_meta($id, 'stock', $data[1]);
                add_post_meta($id, 'price', $data[19]);
                add_post_meta($id, 'vin', $data[2]);
                add_post_meta($id, 'newused', $data[3]);
                add_post_meta($id, 'year', $data[4]);
                add_post_meta($id, 'make', $data[5]);
                add_post_meta($id, 'model', $data[6]);
                add_post_meta($id, 'model_code', $data[7]);
                add_post_meta($id, 'body', $data[8]);
                add_post_meta($id, 'transmission', $data[9]);
                add_post_meta($id, 'series', $data[10]);
                add_post_meta($id, 'series_detail', $data[11]);
                add_post_meta($id, 'door_count', $data[12]);
                add_post_meta($id, 'odometer', $data[13]);
                add_post_meta($id, 'engine_cylinder_ct', $data[14]);
                add_post_meta($id, 'engine_displacement', $data[15]);
                add_post_meta($id, 'drivetrain_desc', $data[16]);
                add_post_meta($id, 'color', $data[17]);
                add_post_meta($id, 'interior_color', $data[18]);
                add_post_meta($id, 'description', $data[23]);
                add_post_meta($id, 'features', $data[24]);
                add_post_meta($id, 'city_mpg', $data[25]);
                add_post_meta($id, 'highway_mpg', $data[26]);
                add_post_meta($id, 'engine', $data[31]);
                add_post_meta($id, 'fuel', $data[32]);
                add_post_meta($id, 'execution', EXECUTION);
                add_post_meta($id, 'lock_price_field', "No");
                add_post_meta($id, 'pre_owned', "No");

                $description = $title." ".$data[17]." for sale. Visit Major World in Long Island City #NY serving Queens, Brooklyn and Bronx #".$data[2];
                add_post_meta($id, '_yoast_wpseo_metadesc', $description);
            }else{ //update inventory
                $ar = json_decode(json_encode($arCheck), true);
                echo "<b style='color:#4fb347'>UPDATE</b> <i>".$ar[0]["ID"]." - ".$data[1]."</i> - ".$title."<br>";
                $id = $ar[0]["ID"];
                $photosPieces = explode(";", $data[28]);

                $photos = "";
                for($i = 0; $i < count($photosPieces); $i++){
                    $photos .= "<img src='".$photosPieces[$i]."' style='height:100px!important'>";
                }
                wp_update_post(array('ID' => $id, 'post_title' => $title));
                update_post_meta($id, 'photos', $photos);
                update_post_meta($id, 'photos_urls', $data[28]);
                update_post_meta($id, 'has_photos', (count($photosPieces) == "1") ? "N" : "Y");
                update_post_meta($id, 'stock', $data[1]);

                if(get_field("lock_price_field", $id) == "No" || get_field("lock_price_field", $id) == ""){ //only update the price if it is not locked
                    update_post_meta($id, 'price', $data[19]);
                }

                update_post_meta($id, 'vin', $data[2]);
                update_post_meta($id, 'newused', $data[3]);
                update_post_meta($id, 'year', $data[4]);
                update_post_meta($id, 'make', $data[5]);
                update_post_meta($id, 'model', $data[6]);
                update_post_meta($id, 'model_code', $data[7]);
                update_post_meta($id, 'body', $data[8]);
                update_post_meta($id, 'transmission', $data[9]);
                update_post_meta($id, 'series', $data[10]);
                update_post_meta($id, 'series_detail', $data[11]);
                update_post_meta($id, 'door_count', $data[12]);
                update_post_meta($id, 'odometer', $data[13]);
                update_post_meta($id, 'engine_cylinder_ct', $data[14]);
                update_post_meta($id, 'engine_displacement', $data[15]);
                update_post_meta($id, 'drivetrain_desc', $data[16]);
                update_post_meta($id, 'color', $data[17]);
                update_post_meta($id, 'interior_color', $data[18]);
                update_post_meta($id, 'description', $data[23]);
                update_post_meta($id, 'features', $data[24]);
                update_post_meta($id, 'city_mpg', $data[25]);
                update_post_meta($id, 'highway_mpg', $data[26]);
                update_post_meta($id, 'engine', $data[31]);
                update_post_meta($id, 'fuel', $data[32]);

                $description = $title." ".$data[17]." for sale. Visit Major World in Long Island City #NY serving Queens, Brooklyn and Bronx #".$data[2];
                update_post_meta($id, '_yoast_wpseo_metadesc', $description);
                update_post_meta($id, 'execution', EXECUTION);
            }
        }else{
            $row = true;
        }
    }//end while

    //remove vehicles
    $argsD = array(
            'posts_per_page'  => -1,
            'post_type'       => 'inventory',
            'post_status'     => 'any',
            'suppress_filters' => false,
            'meta_query'    => array(
                'relation'      => 'AND',
                array(
                    'key'       => 'execution',
                    'value'     => EXECUTION,
                    'compare'   => '!='
                )
            ),
    ); 
    $postsDelete = get_posts($argsD);
    echo "<br><br><h4>TO BE DELETED (".count($postsDelete).") - <i>".EXECUTION."</i></h4>";
    foreach ($postsDelete as $arD){
        echo "- ".get_the_title($arD->ID)." ".$arD->ID." <i>".get_field("stock", $arD->ID)."</i><br>";
        wp_delete_post($arD->ID);
    }
    //end remove vehicles
    wp_die(); //necessary to prevent 0 from being printed
}//end import CSV

the weird thing about this import is that it works most of the time but when it doesn’t work well it shuts down the whole website lol

If the entire inventory is updated every 3 hours anyway, maybe a custom table isn’t as extreme as I thought. If each meta key was instead its own column you could update all of a vehicle’s meta data in a single query instead of with dozens of separate queries.

But it’s still a huge departure from what you have code-wise. All vehicle meta data code would need to be significantly changed since you would no longer be able to use handy WP functions like update_post_meta(). You’d want to replace such calls with a custom equivalent that uses custom SQL invoked through the global $wpdb object.

In deciding if such a major change is worth doing or not, also consider that my assertion of better efficiency is based more on theory than practical experience. Intuitively we’d expect one query to be more efficient than dozens, but I don’t really know how much more efficient. While fewer queries are involved, it’s still nearly the same amount of data being written. But with fewer meta key and post ID records so the total amount of data written is diminished to a degree.

You’d want to set up a test site with this alternative schema to ensure all the new code works as intended. You could then do an A/B comparison to see just how much of a gain is actually made. The Query Monitor plugin will report how long each query takes, but speed does not directly correlate to how much server resource is used. For resource utilization you’d need to use monitoring tools that might be available through your hosting account.

tks @bcworkz