Hello,
I've been trying to figure out why our drupal site is going slow . . . this has ranged from disabling modules (update status and statistics) to looking into the slow queries log.
When I look into the log, the overwhelming majority of the entries are from term_data or term_node
E.g:
SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.vid = 169 ORDER BY v.weight, t.weight, t.name
and
SELECT t.* FROM term_data t INNER JOIN term_hierarchy h ON h.tid = t.tid WHERE t.vid = 2 AND h.parent = 22 ORDER BY weight, name
Are there suggestions as to how to speed these up . . . or where they are even coming from? I thought they were coming from the taxonomy breadcrumb at the top of the pages, so I removed those, but that doesn't seem to be the case.
Comments
Try EXPLAIN it could give you
Try EXPLAIN it could give you some clue,
EXPLAIN SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.vid = 169 ORDER BY v.weight, t.weight, t.nameI recently tried the mysql configuration tweak suggested in handbook page http://drupal.org/node/85768 I'm seeing noticeable difference in performance visually, if you are using dedicated server it is worth giving it a try.
explain
Ok, I tired EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE r ref PRIMARY,vid vid 4 const 1 Using temporary; Using filesort
1 SIMPLE t eq_ref PRIMARY,taxonomy_tree,vid_name PRIMARY 4 cultiva1_sbi.r.tid 1
1 SIMPLE v eq_ref PRIMARY PRIMARY 4 cultiva1_sbi.t.vid 1
I noticed the Using Temporary and file sort on the first row . . . maybe that is the problem?
On another note, I found out yesterday that we had some endless loops that bots were getting stuck in and returning 100's of 404s, and this usually occurred around the time of the biggest slow down. This makes sense given that a row is inserted into watchdog with every 404.
Also, after looking at the mysql, I noticed that a lot of these term_data entries all occur within seconds of each other, so they might be related to the endless loop somehow. But I'm not sure.
Avoid SELECT * ?
It has always been my impression that it is best to avoid "SELECT * FROM ..." statements. You will get better performance by selecting only the fields you require for that query.
If a module has extended the basic schema for these tables and added new fields (maybe one of them is a BLOB), all of a sudden the query which used to be short and sweet is bloated and pulling data you will not be using.
Maybe things have changed, but I always avoid "SELECT * ..."