Mark Amery's questions

Muitos ALTER TABLEcomandos do PostgreSQL, como adicionar uma nova coluna com um valor padrão , têm otimizações inteligentes nas versões mais recentes do PostgreSQL que permitem que eles sejam executados basicamente instantaneamente, mesmo em tabelas grandes, uma vez que o Postgres adquiriu brevemente um bloqueio na tabela .

Infelizmente, essa advertência final é importante. Um comando como este da postagem do blog vinculada

ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;

ainda precisa esperar por um bloqueio exclusivo na userstabela antes de poder ser executado, mesmo que seja executado instantaneamente assim que o bloqueio for adquirido. Pior, enquanto espera por esse bloqueio, ele bloqueia todas as gravações e leituras que envolvem a tabela.

Alguns passos simples para reproduzir isso (testado no Postgres 13.3):

1. Em um psqlshell, crie uma tabela, inicie uma transação, faça uma leitura da tabela e não confirme:

   CREATE TABLE users (id SERIAL, name TEXT);
   INSERT INTO users (name) VALUES ('bob'), ('fred');
   START TRANSACTION;
   SELECT * FROM users WHERE id = 1;
   

2. Deixe o primeiro shell aberto, abra um segundo e tente alterar a tabela:

   ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;
   

   Observe que esta consulta trava, esperando que a transação no primeiro shell seja confirmada.

3. Abra um terceiro terminal e tente executar

   SELECT * FROM users WHERE id = 2;
   

   Observe que isso também trava; agora está bloqueado aguardando ALTER TABLEa conclusão do comando, que por sua vez é bloqueado aguardando a conclusão da primeira transação.

Parece que a maioria ou todos os ALTER TABLEcomandos se comportam assim. Mesmo que a operação em si seja muito rápida ou possa ser executada sem manter um bloqueio para toda a operação, ALTER TABLEainda precisa adquirir brevemente um bloqueio exclusivo na tabela antes de iniciar seu trabalho e, enquanto espera por esse bloqueio, todas as outras instruções que tocar na mesa - até lê! - estão bloqueados.

Desnecessário dizer que esse comportamento é bastante problemático se você deseja fazer alterações em uma tabela que ocasionalmente está envolvida em transações de longa duração. Se a ALTER TABLEinstrução for bloqueada por uma transação de longa duração que esteja mantendo qualquer tipo de bloqueio envolvendo a tabela no momento em que a ALTER TABLEinstrução for executada, todas as interações com essa tabela serão bloqueadas até o final de qualquer transação aleatória de longa duração. , e qualquer coisa que dependa dessa tabela provavelmente passa por um tempo de inatividade.

Existe uma solução canônica para este problema?

Uma solução grosseira que tentei é usar um script wrapper que tenta repetidamente executar a ALTER TABLEinstrução por meio de uma conexão com lock_timeoutum valor pequeno (por exemplo, 5 segundos). Se ALTER TABLEfalhar devido ao tempo limite de bloqueio, a transação é abortada e o script detecta o erro, aguarda um ou dois minutos e tenta todo o processo novamente. Isso evita o tempo de inatividade total, mas ainda tem implicações de desempenho, pois cada tentativa fracassada de executar a ALTER TABLEinstrução ainda bloqueia as consultas por alguns segundos.

O que eu realmente gostaria de fazer é, de alguma forma, dizer ao Postgres que quero que a ALTER TABLEinstrução aguarde um momento em que possa adquirir o bloqueio na tabela sem bloquear outras consultas nesse meio tempo. (Não me importo se isso significa esperar horas até finalmente chegar a um momento em que nenhuma outra consulta esteja tocando a mesa; se evitar bloquear outras consultas, é uma troca absolutamente aceitável.) Existe alguma maneira de fazer isso - talvez algum encantamento que posso incluir na ALTER TABLEdeclaração ou algum parâmetro de configuração que posso definir para alterar esse comportamento?

A maneira normal de converter um valor enum em uma string no PostgreSQL é convertê-lo (com ::text) ou simplesmente confiar na conversão implícita ao, digamos, concatená-lo para outra string:

testdb=# CREATE TYPE color AS ENUM ('red', 'green', 'blue');
CREATE TYPE
testdb=# CREATE TABLE object (id serial, name text, color color);
CREATE TABLE
testdb=# INSERT INTO object (name, color) VALUES ('table', 'red'), ('chair', 'green');
INSERT 0 2
testdb=# SELECT name || color FROM object;
  ?column?  
------------
 tablered
 chairgreen
(2 rows)

No entanto, há um problema: essas conversões parecem não ser consideradas imutáveis ​​pelo Postgres. Como consequência, você obtém um erro se tentar usá-los em um local onde uma expressão imutável é esperada, como na definição de uma GENERATEDcoluna :

testdb=# ALTER TABLE object ADD COLUMN namecolor text GENERATED ALWAYS AS (name || color) STORED;
ERROR:  generation expression is not immutable
testdb=# ALTER TABLE object ADD COLUMN colorastext text GENERATED ALWAYS AS (color::text) STORED;
ERROR:  generation expression is not immutable

Isso parece pouco intuitivo e inconveniente, pois gostaria de poder concatenar valores de enumeração em strings em uma coluna computada.

Assim, tenho duas perguntas:

1. Por que o Postgres considera converter um enum em uma string para não ser imutável? Esse comportamento é um bug?
2. Existe uma solução alternativa que me permita convencer o Postgres de que essa conversão é imutável (e há algum risco que eu deva ter cuidado)?

No MySQL, existe alguma maneira de fazer uma ALTER TABLEou outra instrução DDL ser executada mais lentamente, para mitigar seu impacto no desempenho de outras consultas no mesmo banco de dados? Descobri que uma instrução DDL de execução longa pode prejudicar o desempenho de outras consultas no banco de dados e gostaria de poder fazer ALTERtabelas enormes sem sofrer esse impacto.

Para ser claro, esta questão não é sobre bloqueios - o DDL online resolve a maioria dos nossos problemas de bloqueio e nos permite executar comandos DDL em grandes tabelas sem bloquear totalmente as gravações na tabela. Trata-se apenas de mitigar o impacto no desempenho que a carga pesada de CPU e E/S imposta pela execução de uma instrução DDL em uma tabela grande pode ter no servidor MySQL. Também não estou interessado em estratégias que envolvam configurar um escravo replicante em outro computador e executar a instrução DDL lá; Eu sei que isso é possível, mas preferiria a abordagem mais leve de apenas limitar o DDL, se possível.

O MySQL tem a palavra- LOW_PRIORITYchave para instruções DML para mitigar seu impacto no desempenho, e há alguma discussão no Stack Overflow sobre o problema de princípio semelhante de mitigar o impacto no desempenho demysqldump , mas não consigo encontrar nada semelhante para ALTER TABLEinstruções.

Estou tentando usar o recurso DDL online do MySQL para adicionar uma NOT NULLrestrição a uma tabela InnoDB sem bloquear as gravações na tabela. De acordo com o manual, isso deve ser possível - a tabela vinculada que resume os recursos DDL on-line do MySQL 5.6 tem um 'SIM' na coluna Allows concurrent DML da linha Make column NOT NULL .

No entanto, quando eu realmente tento isso, ele falha em absolutamente todas as circunstâncias que eu tentei. Mesmo que a coluna à qual estou tentando adicionar a NOT NULLrestrição não contenha nenhum NULLvalor, recebo o seguinte erro estranho:

ERRO 1846 (0A000): LOCK=NONE não é suportado. Motivo: não é possível converter silenciosamente valores NULL, conforme exigido neste SQL_MODE. Tente LOCK=SHARED.

Aqui está um caso de teste ultra-simples no shell do MySQL:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try LOCK=SHARED.

Como (se for o caso) posso contornar isso e adicionar uma NOT NULLrestrição enquanto permito DML simultâneo?

Caso seja relevante: minha versão exata do MySQL é 5.6.16 e meu SQL_MODE é NO_ENGINE_SUBSTITUTION.

Temos algumas tabelas enormes em nosso banco de dados MySQL 5.6 que têm latin-1 como conjunto de caracteres e queremos convertê-las todas para UTF-8 (junto com a conversão do conjunto de caracteres global do banco de dados). As ALTER TABLEdeclarações necessárias levam, entre elas, cerca de uma hora para serem executadas. Gostaríamos de minimizar nosso tempo de inatividade.

Nosso plano é configurar a replicação, fazer as alterações de codificação no banco de dados escravo, permitir que os bancos de dados sejam sincronizados e, em seguida, desativar brevemente nosso servidor da web e apontá-lo para o banco de dados escravo.

Isso funcionará ou há risco de causar corrupção de dados no escravo?

Minha preocupação sobre isso decorre de haver duas abordagens possíveis óbvias de como os valores de string (que estão sendo INSERTed ou UPDATEed) podem ser armazenados no binlog do MySQL e enviados para o escravo. Qualquer...

1. Os valores são armazenados como sequências de caracteres unicode (ou seja, o log binário sabe em qual codificação eles estão codificados e envia essas informações ao escravo para que ele possa converter os valores para corresponder ao conjunto de caracteres da coluna de destino no escravo).

   ou...

2. Os valores codificados são armazenados apenas como sequências de bytes, sem registro de qual era o conjunto de caracteres.

A documentação não esclarece qual dos modelos acima o MySQL usa.

Se o nº 1 for verdadeiro, nosso plano funcionará bem.

No entanto, se o nº 2 for verdadeiro, tentar replicar consultas do mestre para o escravo dará errado de alguma forma se uma das colunas envolvidas tiver um conjunto de caracteres diferente. Por exemplo, uma vez que convertemos nossas tabelas de latin-1 para UTF-8 no slave, o slave pode tentar armazenar strings codificadas em latin-1 transmitidas pelo master em uma coluna codificada em UTF-8, resultando em o texto armazenado não está sendo codificado de forma válida em UTF-8 ou representa os caracteres errados.

Os documentos do MySQL sobre replicação e conjuntos de caracteres lançam pouca luz sobre o problema; eles discutem possíveis problemas resultantes de diferenças no conjunto de caracteres globais de cada servidor, mas não abordam problemas potenciais causados ​​por diferenças em conjuntos de caracteres de tabela ou coluna.

Nosso plano é sólido ou corremos o risco de acabar com dados corrompidos no escravo?

Como parte de nosso processo de implantação automatizada para um aplicativo da Web em execução em uma pilha LAMP, descartamos todos os nossos gatilhos e procedimentos armazenados e os recriamos a partir do controle de origem. Acontece que havia um perigo oculto nessa abordagem que não havíamos pensado.

Alguns dias atrás, conseguimos acabar com o banco de dados para (a versão de teste de) nosso aplicativo da web travado em um estado horrivelmente travado após a seguinte sequência de eventos:

1. Eu me conecto ao banco de dados remoto de nosso escritório (através do MySQLdb do Python, por acaso) e executo algumas consultas SELECT na tabela Foo.
2. Deixo a conexão aberta, porque estou com preguiça.
3. Meu chefe faz algumas alterações em seu laptop, envia para o repositório remoto no servidor da web e vai almoçar sem olhar para a saída
4. O gancho de implantação no servidor da Web tenta atualizar os gatilhos e os procedimentos armazenados no banco de dados, mas não consegue nem DROP o primeiro gatilho porque o gatilho envolve a tabela Foo, da qual minha conexão atualmente adormecida já havia feito alguns SELECTs.
5. Agora ninguém pode SELECT da tabela Foo, porque a conexão que está tentando DROP o gatilho já removeu um bloqueio na tabela Foo que impede qualquer outra conexão de acessar a tabela Foo de qualquer maneira - mesmo que ainda esteja esperando pelo conexão adormecida seja fechada antes que possa realmente fazer qualquer coisa.
6. Processos de negócios cruciais que dependem da tabela Foo param, soam alarmes e nosso aplicativo da web para de atender os clientes. Meu chefe fica furioso e declara que cabeças vão rolar se a causa do problema não for encontrada e corrigida para que isso nunca mais aconteça. (Brincadeirinha, era apenas nosso servidor de teste e meu chefe é muito amigável.)

O interessante é que esse cenário não foi causado por nenhum tipo de impasse; foi causado por uma conexão adormecida segurando implicitamente algum tipo de bloqueio que impedia a DROP TRIGGERexecução da instrução, apenas por ter feito um SELECTna mesma tabela anteriormente. Nenhum dos recursos anti-deadlock do MySQL poderia matar automaticamente um processo e salvar a situação, porque no final das contas tudo poderia continuar assim que meu processo original - o ocioso que só fazia SELECTs - fosse morto. O fato de os bloqueios do MySQL se comportarem dessa maneira por padrão parece perverso para mim, mas esse não é o ponto. Estou tentando descobrir uma maneira de garantir que o cenário de desastre descrito acima não volte a ocorrer (especialmente em nosso servidor ativo). Como você sugere que eu faça isso?

Conversamos sobre o problema no escritório e vimos algumas soluções hipotéticas:

• Altere algumas configurações em algum lugar para que os processos adormecidos expirem após 10 segundos por padrão, para que um processo adormecido nunca fique bloqueado. Melhor ainda, faça com que eles liberem todos os bloqueios após 10 segundos para que eu ainda possa ir almoçar e deixar meu shell MySQL aberto, ou minha janela Python aberta com uma conexão MySQLdb ativa, depois voltar e usá-lo, sem medo de quebrar nada .

  • Isso pode ser realmente irritante ao tentar executar consultas manualmente, especialmente aquelas que exigem agrupamento em uma transação.

• Faça alguma mágica nas consultas que tentam substituir os gatilhos e procedimentos armazenados para que a aquisição de bloqueios necessários para os DROPs e CREATEs relevantes seja transformada em uma operação atômica - algo como, se a consulta não puder adquirir todos os bloqueios de que precisa imediatamente em sequência, então ele os libera e tenta novamente periodicamente até funcionar.

  • Isso pode fazer com que nosso processo de implantação nunca seja concluído, no entanto, se o banco de dados estiver muito ocupado para conseguir obter todos os bloqueios de uma só vez.

• Reduza drasticamente a frequência de consultas de modificação de esquema que fazemos (parece que apenas essas podem ser bloqueadas de iniciar por uma conexão que é feita apenas SELECTs), por exemplo, fazendo com que nosso script de implantação verifique se um procedimento armazenado ou gatilho no controle de origem mudou da versão no banco de dados antes de DROPping e recriar aquela no banco de dados.

  • Isso apenas atenua o problema, na verdade não o elimina.

Não temos certeza se alguma das duas primeiras soluções que consideramos são possíveis no MySQL, ou se estamos perdendo uma solução melhor (somos desenvolvedores, não DBAs, e isso está fora de nossa zona de conforto). O que você recomendaria?

No trabalho, hospedamos todos os nossos servidores web no Amazon EC2 e geralmente usamos bancos de dados MySQL instalados na mesma caixa que nosso servidor web Apache e nos comunicamos com eles em arquivos localhost. Agora enfrentamos a necessidade de migrar nosso banco de dados para um servidor próprio para um de nossos sistemas. Eu posso escolher entre duas soluções: usar o Amazon RDS ou apenas iniciar uma nova caixa do Amazon EC2 e instalar o MySQL nela.

O RDS, sendo um serviço de banco de dados dedicado fornecido pela mesma empresa que o EC2, parece ser a opção obviamente melhor . No entanto, quando vejo os preços das duas opções (consulte http://aws.amazon.com/ec2/pricing e http://aws.amazon.com/rds/pricing ), parece que um servidor RDS custa quase duas vezes mais que um servidor EC2 para uma caixa com as mesmas especificações.

Dado que sou capaz de lidar com backups sozinho e que o EC2 oferece a mesma capacidade de escalar a instância conforme exigido pelo RDS, não vejo nenhum motivo para usar o RDS em vez do EC2. Parece que provavelmente estou perdendo algo grande, porque se eu estivesse certo, ninguém usaria o RDS. O que exatamente estou perdendo e quais são as vantagens do RDS em relação à instalação de seu próprio banco de dados em uma instância do EC2?

Atualmente, estou desenvolvendo um site baseado em LAMP. Não sei quais alterações de configuração foram feitas no MySQL desde que ele foi instalado em nosso servidor. Do jeito que usamos o MySQL, não há razão para nosso banco de dados aceitar conexões remotas; ele só precisa ser acessado localmente via PHP ou por meio do shell de linha de comando em uma sessão ssh.

Por motivos de segurança, quero ter certeza de que não há como conectar-se remotamente ao nosso banco de dados. Quais configurações eu preciso verificar para ter certeza de que este é o caso? Existe uma única opção em algum lugar que eu possa definir para impedir todas as conexões remotas?