对Rails / Heroku上Postgres数据库的意外SQL查询 [英] Unexpected SQL queries to Postgres database on Rails/Heroku

问题描述

我正在使用NewRelic向我的一个Rails应用程序提出一个非常长的请求，并发现大量SQL查询完全是外来的，而且这些查询花费了相当长的时间。我已经在谷歌周围了，但我已经空手而归了，更不用说我是否可以阻止它们发生。

$ b $ pre $ SELECT COUNT（*）FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind in ？，？）AND c.relname =？ AND n.nspname = ANY（current_schemas（false））


...和...

  SELECT a.attname，format_type（a.atttypid，a.atttypmod），pg_get_expr（d.adbin，d.adrelid），a.attnotnull，a .atttypid，a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid =？:: regclass AND a.attnum> ？ AND NOT a.attisdropped ORDER BY a.attnum 
  

...每次发生7次，分别为145ms和135ms（分别）总数。

  SELECT DISTINCT（attr.attname）FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey [？] WHERE cons.contype =？ AND dep.refobjid =？:: regclass 
  

...执行了两次，耗时104毫秒， ...

  SHOW search_path 
  

...在一次调用中命令45ms。

我的直觉说这些与Postgres Rails适配器有关，但我不明白是什么触发了它们或者他们在做什么，或者（更重要的是）他们为什么在一个典型的请求中被解雇。

---

我刚刚检出日志更彻底，看起来像这个请求运行的Dyno已经在几秒钟之前转换到up，所以很可能这个请求是第一个。

解决方案

表pg_class，pg_attribute，pg_depend等都在postgres中描述表，列和依赖关系。在Rails中，模型类由表定义，所以Rails读取表和列以找出每个模型的属性。

在开发模式下，每次访问模型时都会查找这些值，所以如果您最近发生了一些变化，Rails就会知道这一点。在生产模式下，Rails会缓存这些信息，这样你就不会再频繁看到这些信息了，所以这真的不是问题。

I was diving into a really long request to one of my Rails applications using NewRelic and found a number of SQL queries that appear entirely foreign that are taking up a significant length of time. I've Google'd around but I've come up empty handed as to what they are, let alone whether I can prevent them from occurring.

SELECT COUNT(*) FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind in (?, ?) AND c.relname = ? AND n.nspname = ANY (current_schemas(false))

…and…

SELECT a.attname, format_type(a.atttypid, a.atttypmod), pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = ?::regclass AND a.attnum > ? AND NOT a.attisdropped ORDER BY a.attnum

…each occurred 7 times, taking 145ms and 135ms (respectively) total.

SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[?] WHERE cons.contype = ? AND dep.refobjid = ?::regclass

…was performed 2 times at a cost of 104ms, and…

SHOW search_path

…commanded 45ms in a single call.

My gut says these are related to the Postgres Rails adapter, but I don't understand what triggers them or what they're doing, or (more importantly) why they fired during a typical request.

---

I just checked out the logs more thoroughly and it looks like the Dyno this request ran on had been transitioned to "up" just a few seconds earlier, so it's likely this request was the first.

解决方案

The tables pg_class, pg_attribute, pg_depend etc all describe table, columns and dependencies in postgres. In Rails, model classes are defined by the tables, so Rails reads the tables and columns to figure out the attributes for each model.

In development mode it looks up these values everytime the model is accessed, so if you've mad e a recent change, Rails knows about it. In production mode, Rails caches this so you would see these much less frequently, and so it really isn't a concern.

这篇关于对Rails / Heroku上Postgres数据库的意外SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！