Início / dba / Perguntas / 132851
Accepted
Gonzalo Vasquez
Asked: 2016-03-22 05:28:58 +0800 CST

Banco de dados "congelado" em ALTER TABLE

  • 772

Nosso ambiente de produção congelou* esta manhã por um tempo ao alterar uma tabela, adicionando uma coluna na verdade.

SQL ofensivo:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];

* O login em nosso sistema requer uma seleção dessa mesma tabela, para que ninguém possa fazer login durante a alteração da tabela. Na verdade, tivemos que encerrar o processo para permitir que o sistema retomasse as operações normais.

Estrutura da tabela:

CREATE TABLE cliente
(
  rut character varying(30) NOT NULL,
  nombre character varying(150) NOT NULL,
  razon_social character varying(150) NOT NULL,
  direccion character varying(200) NOT NULL,
  comuna character varying(100) NOT NULL,
  ciudad character varying(100) NOT NULL,
  codigo_pais character varying(3) NOT NULL,
  activo boolean DEFAULT true,
  id serial NOT NULL,
  stock boolean DEFAULT false,
  vigente boolean DEFAULT true,
  clase integer DEFAULT 1,
  plan integer DEFAULT 1,
  plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,
  facturable integer DEFAULT 1,
  toolkit integer DEFAULT 0,
  propietario integer DEFAULT 0,
  creacion timestamp without time zone DEFAULT now(),
  codelco boolean NOT NULL DEFAULT false,
  familia integer DEFAULT 0,
  enabled_machines boolean DEFAULT false,
  enabled_canbus boolean DEFAULT false,
  enabled_horometro boolean DEFAULT false,
  enabled_comap boolean DEFAULT false,
  enabled_frio boolean DEFAULT false,
  enabled_panico boolean DEFAULT false,
  enabled_puerta boolean DEFAULT false,
  enabled_rpm boolean DEFAULT false,
  enabled_supervisor integer DEFAULT 0,
  demo boolean,
  interno boolean,
  mqtt_enable boolean NOT NULL DEFAULT false,
  topicos character varying(20)[],
  CONSTRAINT pk_cliente PRIMARY KEY (rut),
  CONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia)
      REFERENCES cliente_familia (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pk_pais FOREIGN KEY (codigo_pais)
      REFERENCES pais (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_id_cliente UNIQUE (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cliente
  OWNER TO waypoint;
GRANT ALL ON TABLE cliente TO waypoint;
GRANT ALL ON TABLE cliente TO waypointtx;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat;
GRANT SELECT ON TABLE cliente TO waypointphp;
GRANT SELECT ON TABLE cliente TO waypointpphppublic;
GRANT ALL ON TABLE cliente TO waypointsoporte;
GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce;
GRANT SELECT ON TABLE cliente TO waypointadminuser;
GRANT SELECT ON TABLE cliente TO waypointagenda;
GRANT SELECT ON TABLE cliente TO waypointmachines;
GRANT SELECT ON TABLE cliente TO waypointreports;
GRANT SELECT ON TABLE cliente TO readonly;

CREATE INDEX index_cliente
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default");

CREATE INDEX index_cliente_activo
  ON cliente
  USING btree
  (activo);

CREATE INDEX index_cliente_id_activo
  ON cliente
  USING btree
  (id, activo);

CREATE INDEX index_cliente_rut_activo
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default", activo);


CREATE TRIGGER trigger_default_admin
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_admin();

CREATE TRIGGER trigger_default_grupo
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_clientegrupo();

Devo desabilitar CONSTRAINTS, TRIGGERS ou algo mais?

Talvez algum DB Tuning?

O que mais devo fornecer para uma análise mais aprofundada?

Versão: PostgreSQL 9.4.5 em x86_64-unknown-linux-gnu, compilado por gcc (Debian 4.9.2-10) 4.9.2, 64 bits

postgresql postgresql-9.4

2 respostas

  1. O comando que você deseja executar obtém um bloqueio ACCESS EXCLUSIVE na tabela, impedindo todos os outros acessos a essa tabela. Mas a duração desse bloqueio deve ser de apenas alguns milissegundos, pois adicionar uma coluna como a que você deseja adicionar não requer que a tabela seja reescrita, apenas requer que os metadados sejam atualizados.

    Onde o problema pode surgir, e aposto dólares em rosquinhas que é o problema que você está vendo, está nas prioridades de bloqueio. Alguém tem um bloqueio fraco, como bloqueio ACCESS SHARE, naquela tabela, e eles estão acampando nele indefinidamente (talvez uma conexão ociosa na transação que vazou? Alguém que abriu o psql, iniciou uma consulta em um modo de leitura repetível, e depois saiu de férias?).

    A COLUNA ADICIONAR tenta obter o ACESSO EXCLUSIVO de que precisa e se enfileira atrás do primeiro cadeado.

    Agora, todas as solicitações de bloqueio futuras se enfileirarão atrás da solicitação ACCESS EXCLUSIVE em espera.

    Conceitualmente, as solicitações de bloqueio recebidas que são compatíveis com o bloqueio já concedido podem pular o ACCESS EXCLUSIVE em espera e serem concedidas fora de hora, mas não é assim que o PostgreSQL faz.

    Você precisa encontrar o processo que está segurando o bloqueio fraco de longa duração.

    Você pode fazer isso consultando a tabela pg_locks.

    select * from pg_locks where 
    granted and relation = 'cliente'::regclass \x\g\x

    Se você fizer isso enquanto tudo estiver bloqueado, deverá obter apenas uma resposta (a menos que haja vários culpados de longa data). Se você fizer isso depois de já ter matado o ADD COLUMN, poderá ver muitos bloqueios concedidos, mas se repetir algumas vezes, deve haver um ou alguns que permanecerão por perto a cada vez.

    Você pode então pegar o PID que obteve de pg_lock e consultá-lo em pg_stat_activity para ver o que o infrator está fazendo:

    select * from pg_stat_activity where pid=28731 \x\g\x

    ...

    backend_start    | 2016-03-22 13:08:30.849405-07
xact_start       | 2016-03-22 13:08:36.797703-07
query_start      | 2016-03-22 13:08:36.799021-07
state_change     | 2016-03-22 13:08:36.824369-07
waiting          | f
state            | idle in transaction
backend_xid      |
backend_xmin     |
query            | select * from cliente limit 4;

    Então, ele executou uma consulta, dentro de uma transação, e depois ficou ocioso sem nunca fechar a transação. Agora são 13:13, então eles estão inativos há 5 minutos.

    • 58
  2. Best Answer

    As operações DDL geralmente bloqueiam o objeto sobre o qual estão agindo, portanto, não devem ser executadas fora das janelas de manutenção planejadas (quando os usuários esperam interrupção ou o sistema fica completamente offline por um período de tempo planejado) - não há nada que você possa fazer sobre isso facilmente 1 .

    Algumas operações mantêm apenas um bloqueio de gravação, portanto, seu aplicativo pode continuar atendendo a solicitações que leem apenas os objetos afetados.

    A documentação parece muito boa em listar quais bloqueios provavelmente serão mantidos por operações DDL.

    Esta entrada de blog tem um resumo que sugere que adicionar uma coluna pode ser uma operação online se a coluna for anulável e não tiver um valor padrão ou restrição exclusiva, embora isso implique que a instrução declarada deveria ter sido executada sem bloqueios (como IIRC postgres padroniza as colunas como NULLable, a menos que você declare explicitamente o contrário). Você executou alguma outra operação após adicionar a coluna? Talvez criando um índice sobre ele (o que levaria a um bloqueio de gravação na tabela por padrão)?

    1 Alguns arranjos de replicação/clustering/espelhamento permitem que você atualize um espelho (pausando as atualizações durante a alteração e reproduzindo-as novamente depois), passar a usar essa cópia como a ativa e assim por diante até que cada cópia seja atualizada. o tempo de inatividade é limitado ao tempo necessário para reproduzir as alterações feitas durante a operação DDL. No entanto, operações ao vivo como essa não são isentas de riscos; portanto, a menos que você absolutamente não possa, é recomendável que você organize uma janela de manutenção adequada para executar e verificar atualizações estruturais.

    • 12

relate perguntas