如何修复Rails中pg_attribute表的缓慢隐式查询

问题描述

限时删除！！

在我们的生产环境中，我们注意到我们的Rails应用程序中经常出现尖峰（〜每1小时）。深入挖掘，这是由于以下查询在单个HTTP请求中累计运行时间> 1.5s（称为100x）。

  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 
  

我们没有代码调用该表格明确，但似乎被Rails调用来找出每个模型的属性。 到您的项目并检查，什么是触发此查询。

在工作中，我使用此设置：

 ＃Gemfile 
 group：test，：development do 
 gemactive-record-query-trace
结束
 
＃config / initializers / ar_tracer.rb 
如果ENV.has_key？（AR_TRACER）&&界定？ ActiveRecordQueryTrace 
 ActiveRecordQueryTrace.enabled = true 
 ActiveRecordQueryTrace.lines = 20＃如果没有足够的空间，您可能需要增加
结束
  

然后简单地启动你的rails服务器，像这样： AR_TRACER = 1 bundle exec rails s 。

In our production environment, we noticed frequent spikes (~every 1 hour) in our Rails application. Digging deeper, it's due to the following query which cumulatively runs in >1.5 s (called 100x) in a single HTTP request.

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

We don't have code calling that table explicitly but seems it's called by Rails to figure out the attributes for each model. "Unexpected SQL queries to Postgres database on Rails/Heroku" is related.

But shouldn't it be called non-repetitively by Rails?

How do we speed this up?

解决方案

I have not experienced this issue in any Rails application I've worked so far. I think your solution is to add active-record-query-trace to your project and check, what is triggering this query.

At work I use this setup:

# Gemfile
group :test, :development do
  gem "active-record-query-trace"
end

# config/initializers/ar_tracer.rb
if ENV.has_key?("AR_TRACER") && defined? ActiveRecordQueryTrace
  ActiveRecordQueryTrace.enabled = true
  ActiveRecordQueryTrace.lines = 20 # you may want to increase if not enough
end

Then simply start your rails server like this: AR_TRACER=1 bundle exec rails s.

这篇关于如何修复Rails中pg_attribute表的缓慢隐式查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..