Início / dba / Perguntas / 186937
Accepted
Hassan Baig
Asked: 2017-09-27 08:18:46 +0800 CST

Desempenho lento de consulta de manutenção/exclusão simples na tabela postgres 9.3.10

  • 772

Eu tenho um aplicativo do tipo reddit chamado 'Links', onde 1) os usuários postam hiperlinks interessantes e 2) outros usuários respondem publicamente a essas postagens. O primeiro é definido como links_linktabela no meu banco de dados postgresql 9.3.10, o último é links_publicreply.

Percebi que a exclusão é bastante rápida na maioria das tabelas no banco de dados deste aplicativo da web.

No entanto, links_publicreplyé problemático. Por exemplo, acabei de excluir 238 linhas dessa tabela e esperei cerca de 20 minutos para concluir. Isso não é uma exceção; tem sido mais como a regra há mais de um ano.

Agora, confessamente, é uma tabela grande (~ 75 milhões de linhas). O hardware em que está operando tem 16 núcleos e 120 GB de memória. Tenho monitorado o desempenho do servidor - não há gargalo, longe disso.

Veja os resultados da análise explicativa aqui: https://explain.depesz.com/s/ATwE Parece que o tempo é consumido por uma varredura sequencial.

Além disso, aqui está a saída de \d links_publicreply:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Existe alguma maneira que eu poderia ter realizado uma consulta como esta de forma diferente? Preciso realizar manutenção nesta mesa - mas os tempos são exorbitantes. Por favor, informe sobre todas as opções (se houver) que tenho aqui.

Caso seja importante, adicione também a saída para \d links_linkaqui:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
postgresql postgresql-9.3

1 respostas

  1. Best Answer

    A varredura sequencial está levando apenas 23 segundos. A chave é olhar o texto completo do plano, que mostra o tempo que falta:

    Trigger for constraint publicreplyposter_link_fkey: time=766392.766 calls=238

    (explain.depesz.com não faz um trabalho tão bom marcando os planos de consultas não selecionadas)

    Então, parece que você está perdendo um arquivo create index on links_link (latest_reply_id).

    Isso significa que cada linha da qual você exclui links_publicreplyprecisa fazer uma verificação completa da tabela links_linkpara garantir que não esteja fazendo referência a essa linha.

    • 1

relate perguntas