Performance bug - slow DB query

Since 2017-09-26 I have terrible problems with performance on my website.

Loading single post or any other page takes even 20s. I've been debugging it it found out, that the problems are DB queries.

For example this one is executing almost with every page load and takes about 36s:

SELECT t.*, tt.*, tr.object_id
FROM   az2_terms AS t 
INNER JOIN az2_term_taxonomy AS tt
ON     t.term_id = tt.term_id
INNER JOIN az2_term_relationships AS tr
ON     tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE  tt.taxonomy IN ('znacka', 'model')
AND    tr.object_id IN (27130, 27290, 27822, 27899, 30862, 33536, 33721, 34901, 37448, 37482, 37496, 37522, 37572, 37580, 37742, 37750, 38160, 38165)
ORDER BY t.name ASC

When it's executing for the second time, it's lamost instant. Since the 2017-09-26 I haven(t made any changes, only switched to HTTPS, could that be the problem?

Topic database query Wordpress performance

Category Web

1
answered at

In the comment to the first answer you ask "What are 7692832 rows?" That's 7,692,832 rows examined for your query. Probably way more than you expected.

It could be 43 records found in one side on a join against the 180,000 records on the other side, or something like that. With joins it can build up fast.

If you have queries looking for data that's not indexed, you'll be doing table scans to find the data. Very slow. Make sure you have the right fields indexed for your queries. Try this for assistance.

1
answered at

If you are using a shared server with good traffic, there is no meaning of doing research. You simply need upgrade to VPS/cloud server.

If it is VPS/cloud server, such sudden odd slow can needs checking security matters. In general Fail2Ban is enough yet you need to perform a security audit to find any vulnerable plugin, you must test various things with VPS/cloud server like High Availability. Take a full backup with all custom snippets/modifications. Then load site with no plugin, no custom snippets/modifications, default theme.

I am not database expert but I suggest to use Percona MySQL with InnoDB engine (MariaDB is no way closest to inferior), keep WordPress comments and stuffs like pingback, trackback off, use W3 Total Cache, disable database caching of W3 Total Cache, enable MySQL caching etc. I have seen for WordPress, theme alone can do the best or worst. The default theme and StudioPress Genesis (paid) both appears to me near to be optimal.

You should enable MySQL caching, which is important (Percona's article on MySQL caching, my article on MySQL caching) and test various settings. Tweaking database from my.cnf is practical after a limit for colocation servers, hardly good dedicated servers. If hardware is under performer, nothing going to work. I would suggest to read this answer of DB StackExchange for both hardware and software part.

1
answered at

It is relatively rare for WP to have query performance issues on a small to moderately sized site:

  1. The queries it typically runs are reasonable (not perfect, but reasonable).
  2. A lot of things queried are aggressively cached within page load, and persistently with object cache available.

While you certainly can throw enough code at it for queries to boggle down, 20s seems extreme. Unless we are talking hundreds of thousands of items.

If the change is sudden as well, I would strongly suspect the issue is on DB server side and tables might need optimize/repair.

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.