我已经在database.yml
中配置了我的statement_timeout
,但是我的应用程序中有一些需要更长查询执行时间的耗时查询。怎样才能在每个查询层面上推荐的实现这个目的呢?
我需要临时将statement_timeout
设置为较大的值,执行查询并将其重置为默认值吗?还是根本不需要重置?
我已经在database.yml
中配置了我的statement_timeout
,但是我的应用程序中有一些需要更长查询执行时间的耗时查询。怎样才能在每个查询层面上推荐的实现这个目的呢?
我需要临时将statement_timeout
设置为较大的值,执行查询并将其重置为默认值吗?还是根本不需要重置?
我认为你只能通过更改整个连接的 statement_timeout
,然后将其恢复原样来实现这一点:
def execute_expensive_query
ActiveRecord::Base.connection.execute 'SET statement_timeout = 600000' # 10 minutes
# DB query with long execution time
ensure
ActiveRecord::Base.connection.execute 'SET statement_timeout = 5000' # 5 seconds
end
在数据库层面上,你可以按照这个指南的说明,仅为当前事务设置statement_timeout
:
BEGIN;
SET LOCAL statement_timeout = 250;
...
COMMIT;
DatabaseTimeout
的模块,同时还确保将statement_timeout
设置重置回其原始值。# Ruby's `Timeout` doesn't prevent queries from running for a long time.
#
# To prove this, run the following in a console (yes, twice):
# Timeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# Timeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# => The 2nd call should run for a long time.
#
# DatabaseTimeout's purpose is to enforce that each query doesn't run for more than the given timeout:
# DatabaseTimeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# DatabaseTimeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# => Both queries are interrupted after 1 second
module DatabaseTimeout
# Usage: DatabaseTimeout.timeout(10) { run_some_query }
def self.timeout(nb_seconds)
original_timeout = ActiveRecord::Base.connection.execute('SHOW statement_timeout').first['statement_timeout']
ActiveRecord::Base.connection.execute("SET statement_timeout = '#{nb_seconds.to_i}s'")
yield
ensure
if original_timeout
ActiveRecord::Base.connection.execute("SET statement_timeout = '#{original_timeout}'")
end
end
end
module DatabaseTimeout
module_function
# Usage: DatabaseTimeout.timeout(10) { run_some_query }
def timeout(nb_seconds)
ActiveRecord::Base.transaction do
ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '#{nb_seconds.to_i}s'")
yield
end
end
end
DatabaseTimeout.timeout
时,SHOW语句和2个SET语句是事务的一部分,我认为这不是问题。 - Matthieu LibeerDatabaseTimeout.timeout
都封装在数据库事务中,否则会有污染并发查询(在同一AR连接上)的风险,并更改超时时间,是吗? - berkes