First time here but always reading and using your tips. I run a decent size site over at http://www.2old2play.com/
Page loads are kinda all over the place. I've got memcached, Varnish and APC all rolling and it's sped things up tremendously. However I still have issues with a delay to first byte and insanely long submission for new posts (up to 44 sec).
I've started using newrelic which is AMAZING, but I still don't know how to go about fixing the issue. This is what I've got so far....
node - SELECT
SELECT DISTINCT node.nid AS nid, node.type AS node_type, node.title AS node_title, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, og_ancestry.nid AS og_ancestry_nid, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp FROM node node INNER JOIN users users ON node.uid = users.uid INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= ? AND ((na.gid = ? AND na.realm = '?') OR (na.gid = ? AND na.realm = '?') OR (na.gid = ? AND na.realm = '?'))) AND ( node.status = ? )ORDER BY node_type DESC LIMIT ?, ?
12 seconds for just this one SQL is pretty dismal. Average load times are about 10sec and if someone is submitting content it can hang to almost 40+. It's a dedicated server with only a few tiny WP sites, but for the most part 2o2p is using all the resources.
I've also got:
UPDATE users SET access = ? WHERE uid = ?
running at almost 9 sec.