By jcisio on

Hello,

Just migrate a site to Drupal. About 15k nodes, 20k users. I use the workflow module, that creates a node_access with 80k+ rows. A query takes 40s to complete, and when I close my site, run those queries myself, each takes 3-4s :( Any idea?

SELECT COUNT(*) FROM (SELECT DISTINCT node.nid AS nid
 FROM node node 
 LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid
  INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 3 AND na.realm = 'workflow_access') OR (na.gid = 4 AND na.realm = 'workflow_access') OR (na.gid = 5 AND na.realm = 'workflow_access') OR (na.gid = 64 AND na.realm = 'workflow_access_owner'))) AND (  (node.status <> 0) AND (node_counter.totalcount > 0) AND (node_counter.timestamp >= 1265987728-259200) AND (node.vid IN (
  SELECT tn.vid FROM term_node tn
    LEFT JOIN term_hierarchy th ON th.tid = tn.tid
    LEFT JOIN term_hierarchy th1 ON th.parent = th1.tid
  WHERE tn.tid = 80
    OR th1.tid = 80
  )) AND (node.nid != 15243)
   )) count_alias;
⋅ Categories: Drupal 6.x

Comments

jcisio’s picture

jcisio commented

Just another query

# Query_time: 12  Lock_time: 0  Rows_sent: 6  Rows_examined: 170656
SELECT DISTINCT node.nid AS nid,
   node.created AS node_created,
   node.title AS node_title
 FROM node node 
 LEFT JOIN term_node term_data_term_node ON node.vid = term_data_term_node.vid
 LEFT JOIN term_data term_data_term_node__term_data ON term_data_term_node.tid = term_data_term_node__term_data.tid
  INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'workflow_access') OR (na.gid = 0 AND na.realm = 'workflow_access_owner'))) AND (  (term_data_term_node__term_data.name = 'cms') AND (node.nid != 3035)
   )ORDER BY node_created DESC
 LIMIT 0, 6;
jcisio’s picture

jcisio commented

OK, don't bother. Just did some core hack and Views hack, node_access's gone with there're other problems. I imagine that I need to work more.

With Views and Panels, the number of MySQL queries for each pageview is:

  • Homepage (a dozen of mini-panel): 538
  • Single node view (with 3 views related/recent/popular posts put in panels): 378
  • Taxonomy page (taxonomy/term/tid: 10 nodes and a single views: popular posts): 459

While the number of queries isn't important: most of them finish in less than 1 ms, others in a few ms, those numbers make me really surprise. Memory usage is only about 4-10 MB however.

locomo’s picture

locomo commented

Could you share a little bit about what you did.

I'm also using workflow and am having lots of performance issues.

jcisio’s picture

jcisio commented

I don't hack any, just don't use any node access module, so that the node_access table has only one row. Try to live without workflow access module :)