1

My hosting provider (Pantheon) provides mysql slow query logs. Is there a quick/easy way to determined, based on an entry in the log, which module/path is causing the problem?

Since I built this site, for most of these I have a pretty good guess at which of my views is which query, but is there some way to determine it based on the data in the slow log alone?

For example, here is one such entry:

# Query 1: 0.00 QPS, 0.01x concurrency, ID 0x7F2CECDDEF25EDAB at byte 4948139
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.13
# Time range: 2015-04-07 21:58:12 to 2015-04-14 06:43:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         27     860
# Exec time     37   3578s      4s      9s      4s      6s   739ms      4s
# Lock time     27   195ms   161us   560us   226us   273us    36us   214us
# Rows sent      0   8.38k       1      10    9.98    9.83    0.38    9.83
# Rows examine  43   1.65G   1.54M   2.04M   1.96M   1.95M 141.63k   1.95M
# Query size    30   1.53M   1.81k   1.82k   1.82k   1.78k       0   1.78k
# String:
# Databases    pantheon
# Hosts        10.223.192.182
# Users        pantheon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'node'\G
#    SHOW CREATE TABLE `pantheon`.`node`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'users'\G
#    SHOW CREATE TABLE `pantheon`.`users`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'users_roles'\G
#    SHOW CREATE TABLE `pantheon`.`users_roles`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'field_data_field_acc_hide_profile'\G
#    SHOW CREATE TABLE `pantheon`.`field_data_field_acc_hide_profile`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'field_data_field_acc_search_weight'\G
#    SHOW CREATE TABLE `pantheon`.`field_data_field_acc_search_weight`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'taxonomy_index'\G
#    SHOW CREATE TABLE `pantheon`.`taxonomy_index`\G
#    SHOW TABLE STATUS FROM `pantheon` LIKE 'taxonomy_term_hierarchy'\G
#    SHOW CREATE TABLE `pantheon`.`taxonomy_term_hierarchy`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT users_node.uid AS users_node_uid, users_node.name AS users_node_name, users_node__field_data_field_acc_search_weight.field_acc_search_weight_value AS users_node__field_data_field_acc_search_weight_field_acc_sea, 'user' AS field_data_field_acc_profile_picture_user_entity_type, DATE_FORMAT((DATE_ADD('19700101', INTERVAL users_node.login SECOND) + INTERVAL 16200 SECOND), '%Y%m%d') AS users_node_login_day
FROM 
node node
INNER JOIN users users_node ON node.uid = users_node.uid
INNER JOIN users_roles users_node__users_roles ON users_node.uid = users_node__users_roles.uid
LEFT JOIN field_data_field_acc_hide_profile users_node__field_data_field_acc_hide_profile ON users_node.uid = users_node__field_data_field_acc_hide_profile.entity_id AND users_node__field_data_field_acc_hide_profile.field_acc_hide_profile_value = '1'
LEFT JOIN field_data_field_acc_search_weight users_node__field_data_field_acc_search_weight ON users_node.uid = users_node__field_data_field_acc_search_weight.entity_id AND (users_node__field_data_field_acc_search_weight.entity_type = 'user' AND users_node__field_data_field_acc_search_weight.deleted = '0')
WHERE (( (node.nid IN  (SELECT tn.nid AS nid
FROM 
taxonomy_index tn
LEFT OUTER JOIN taxonomy_term_hierarchy th ON th.tid = tn.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th1 ON th.parent = th1.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th2 ON th1.parent = th2.tid
LEFT OUTER JOIN taxonomy_term_hierarchy th3 ON th2.parent = th3.tid
WHERE ( (tn.tid = '378') OR (th1.tid = '378') OR (th2.tid = '378') OR (th3.tid = '378') ))) )AND(( (users_node.status <> '0') AND (users_node__users_roles.rid = '135888650') AND (users_node__field_data_field_acc_hide_profile.field_acc_hide_profile_value IS NULL ) )))
ORDER BY users_node__field_data_field_acc_search_weight_field_acc_sea DESC, users_node_login_day DESC
LIMIT 10 OFFSET 220\G

Browse other questions tagged or ask your own question.