Как снизить нагрузку на базу данных Wordpress?

Question

[Ordabasy]

Здравствуйте!
Проекту на Wordpress уже почти 7 лет, счёт записей идёт на десятки тысяч. В последнее время стали возникать проблемы с нагрузкой на базу данных. На дня Мастерхост прислал письмо с уведомлением и примерами длинных запросов (они ниже). Оптимизацию провёл, кэширование работает (DB Reloaded Fix). Что делать, как снизить нагрузку, чтобы не доводить до критической?

spoiler

SET timestamp=1539935306;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'yzrssenabled_meta_value' ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND (

wp_postmeta.post_id IS NULL

OR

( mt1.meta_key = 'yzrssenabled_meta_value' AND mt1.meta_value != 'yes' )

) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 50;

SET timestamp=1539935282;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (11)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 12, 12;

SET timestamp=1539935281;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (94)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 12;

SET timestamp=1539935280;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (612)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 564, 12;

SET timestamp=1539935280;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (612)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 24, 12;

SET timestamp=1539935280;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (16)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 24, 12;

SET timestamp=1539935278;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (5467)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 504, 12;

SET timestamp=1539935271;

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND (

wp_term_relationships.term_taxonomy_id IN (2806)

) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 12;

SET timestamp=1539935255;

UPDATE wp__wsd_plugin_alerts

SET

`alertType` = 0,

`alertSeverity` = 0,

`alertActionName` = 'fix_wp_index_content',

`alertTitle` = '<strong>\"/wp-content\"</strong> directory is secure from directory listing.',

`alertDescription` = '<p>A directory listing provides an attacker with the complete index of all the resources located inside of the directory.\r\n The specific risks and consequences vary depending on which files are listed and accessible.\r\n Therefore, it is important to protect your directories by having an empty index.php or index.htm file inside them.</p>',

`alertSolution` = '',

`alertDate` = CURRENT_TIMESTAMP()

WHERE alertId = 23;

SET timestamp=1539935255;

UPDATE wp__wsd_plugin_alerts

SET

`alertType` = 0,

`alertSeverity` = 0,

`alertActionName` = 'fix_wp_login_errors',

`alertTitle` = 'WordPress login errors are not displayed.',

`alertDescription` = '<p>Currently, these errors are hidden to all users.</p>',

`alertSolution` = '',

`alertDate` = CURRENT_TIMESTAMP()

WHERE alertId = 9;

Answer 1

[spaceatmoon]

Из простого что я делал, это сделать импровизированный кэш запросов в классе где вызывается функция.

Пример кода

Немного поджирает памяти, но запросов на порядок будет меньше.

class Post {

$cache = [];
function getListPost($type = 'public') {
   if (isset($this->cache[$type]) 
       return  $this->cache[$type];
   /**
  Далее простой код с получением результата и присвоением в кэш.
*/
}
}

Другой способ найти изъян в архитектуре и переписать. Что-то подсказывает, что запросы в цикле крутятся.