0

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?

  • Best to take this up with your web host and see if they have any ideas. You may also want to look into optimizing your database, possibly with a plugin. Specific performance issues are considered off-topic. – WebElaine Oct 3 '17 at 15:57
  • I already have conatcted them and the answer was, that my website is running many and many DB request like the one above. The server is not able to prcess them and that causes the performance issues – Tomáš Bláha Oct 3 '17 at 16:29
0

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.

  • Well, there are about 20.000 posts and about 180.000 meta rows Do you have any reccomandations for DB optimization/reparation? I've tried some plugins for WP, but they only asked me to remove trashed posts and revisions - I woulnd't say that that is the problem. – Tomáš Bláha Oct 3 '17 at 16:30
  • I would guess this is realm of MySQL tools, not WP tools. If you are not too comfortable working with database side of things it might be good idea to request your hosting's support to look into it. – Rarst Oct 3 '17 at 16:32
  • Thank you for your reccomendation. I'm trying to run the automatic repair tool from Phpmyadmin and we will see... If it won't help I guess i will contact the ISP support to help. They have already told me, that website i rapidly using CPU since that day I've received the log and it says: Query_time: 5.833446 Lock_time: 0.000109 Rows_sent: 4 Rows_examined: 7692832 What are 7692832 rows? – Tomáš Bláha Oct 3 '17 at 16:34
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.