Ruby on Rails 请求/查询超时

Question

我有一个Rails应用程序，它已经工作了一段时间。现在，我我在生产中遇到问题，因为请求检索超时。这似乎很奇怪，因为数据库仅重 12MB。

我安装了pg_hero，并更改了数据库配置，但是，这并没有解决问题。

在宝石文件中，重要的宝石如下

gem 'rails',                      '5.0.1'
gem 'pg',                         '0.18.4'
gem 'ransack',                    '1.8.0'
gem 'autonumeric-rails',          '1.9.43'
gem 'axlsx',                      '2.0.1'
gem 'axlsx_rails',                '0.5.2'
gem 'pghero'
gem 'pg_query', '>= 0.9.0'

这是控制器中的代码

def download
  @q = current_consultant.admin? ? @q = Task.ransack(params[:q]) : @q = Task.where(:consultant_id => current_consultant.id).ransack(params[:q])
  @q.sorts = ['task_date desc'] if @q.sorts.empty?
  @tasks = @q.result(distinct: true)
  render xlsx: "tasks", template: "/tasks/download.xlsx.axlsx"
end

这是模型中的代码

def self.worked(id)
  query_sql ="
  SELECT
    consultants.id as consultant_id,
    clients.name as client,
    categories.name as category_name,
    tasks.task_date as start_time,
    SUM(tasks.hours * 60 + tasks.minutes) as name
    FROM consultants
    INNER JOIN tasks ON consultants.id = tasks.consultant_id
    INNER JOIN clients ON tasks.client_id = clients.id
    INNER JOIN categories ON tasks.category_id = categories.id
    WHERE consultants.id = #{id}
    GROUP BY clients.id, consultants.id, tasks.task_date, categories.id;
  "
  Consultant.find_by_sql(query_sql)
end

这是 AXLSX 文件

wb = xlsx_package.workbook
wb.add_worksheet(name: "Carga de Horas") do |sheet|
  title = sheet.styles.add_style(:bg_color => "55FF0000", :fg_color=>"FFFFFFFF", :border=>Axlsx::STYLE_THIN_BORDER, :alignment=>{:horizontal => :center})
  time_title = sheet.styles.add_style(:bg_color => "55FF0000", :fg_color=>"FFFFFFFF", :border=>Axlsx::STYLE_THIN_BORDER, :alignment=>{:horizontal => :center}, :num_fmt => 46)
  text = sheet.styles.add_style(:alignment=>{:horizontal => :center})
  date = wb.styles.add_style :num_fmt => 15
  time = wb.styles.add_style :num_fmt => 20
  currency = wb.styles.add_style :num_fmt => 5
  sheet.add_row [
    "Consultor",
    "Recurrente",
    "Cliente",
    "Fecha",
    "Duración",
    "Sector",
    "Tarea",
    "Comentarios"
  ], :style => title
  @tasks.each do |task|
      sheet.add_row [
        task.consultant.full_name,
        "=IF(#{task.recurrent},"SI","NO")",
        task.client.name,
        task.task_date,
        "=#{task.duration}/1440",
        task.category.name,
        task.subcategory.name,
        task.comments
      ], :style => [
        text,
        text,
        text,
        date,
        time,
        text,
        text,
        text
      ]
  end
  range_amount = "E2:E#{@tasks.size+1}"
  range_iva = "F2:F#{@tasks.size+1}"
  sheet.add_row ["Total", nil, nil, nil, "=SUM(#{range_amount})", nil, nil, nil], :style => [title, title, title, title, time_title, title, title, title]
end

行数来自

consultans -> 25
tasks -> 14420
clients -> 133
categories -> 4
subcategories -> 49

在数据库中执行的查询持续大约 70 秒

我不确定是否有办法解决这个问题

Answer 1

您是否使用 nginx 或 apache 作为转发代理？通常它们有一个默认的超时窗口 ~ 60 秒，这将匹配此行为。