All perguntas(dba)

Tenho visto muitos blogs de ajuste de desempenho do PostgreSQL e empresas como a EDB sugerindo colocar a configuração max_worker_processes do PostgreSQL para o número de CPUs na máquina.

Estamos usando uma VM PostgreSQL 13 dedicada na nuvem com 16 vCPUs . Mas a configuração padrão do Postgres max_worker_processes=8 . Baseia-se no valor padrão O que eu estava pensando O Postgres irá apenas até 8 vCPUs no máximo e o uso máximo esperado da CPU está em torno de 50% até alguns dias atrás, tivemos algum pico de conexão que na verdade estava ligado à sobrecarga de E/S, mas a CPU atingiu 95 -99% durante o pico.

Então, como max_worker_processes está relacionado à CPU? Isso terá algum impacto se eu deixar o valor padrão de 8? Ou existem outras maneiras de encontrar um valor ideal?

Tenho script para criptografar colunas na tabela. >200 mil linhas. Em 100k está funcionando.

UPDATE table
SET col1 = TO_BASE64(AES_ENCRYPT(col1, @keyBase, @static_iv)), 
col2 = TO_BASE64(AES_ENCRYPT(col2 , @keyBase, @static_iv))

Editar: reproduzi isso em um exemplo mais simples (> 200 mil registros):

UPDATE table
SET col1 = '1'

Estou tentando criar uma UDF que crie um recurso de cláusula VIEWdinamicamente usando o USINGrecurso. O problema é que EXECUTEnão aceitará o parâmetro passado no CREATE VIEWcomando.

CREATE OR replace FUNCTION test(name varchar) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('create or replace view %I as select $1 as id limit 1;',$1) using $1;
    RETURN;
END
$$;

Teste:

select test('tabel_view');

Saída atual:

Erro SQL [42P02]: ERRO: não há parâmetro $1
Onde: teste de função PL/pgSQL (caractere variável) linha 3 em EXECUTE

O mesmo funciona para CREATE TABLE:

CREATE OR REPLACE FUNCTION test(name varchar) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('create table %I as select $1 as id limit 1;',$1) using $1;
    RETURN;
END
$$;

Estou tentando dessa maneira porque a concatenação de strings é propensa à injeção de SQL. Além disso, a UDF em que estou trabalhando terá um array como entrada.

Alguma ideia de como resolver esse problema?

Estou fazendo este curso essencial do EDB PostgreSQL e o instrutor explicou sobre a arquitetura PostgreSQL referindo-se ao diagrama que, sempre que um cliente faz uma solicitação de atualização e supõe que os dados estão presentes no buffer compartilhado (isso significa que não há necessidade de buscá-los no armazenamento de arquivos), então ' farei uma entrada nos buffers WAL e, ao confirmar , o gravador WAL gravará a transação nos logs de transações e a tornará permanente, mas não nos sistemas de arquivos (até onde entendi, essa é a tarefa do checkpointer , abaixo). tão bom.

imagem cortesia traning.enterprisedb.com

Agora vem o checkpointer, é um processo que é executado após cada determinado intervalo de tempo "geralmente 5 minutos é o tempo ideal" e grava qualquer coisa no buffer compartilhado no armazenamento de arquivos.

Minha pergunta é: suponha que o checkpointer tenha sido executado e depois disso eu iniciei uma transação atômica e transferi 100 dólares para meu amigo, como é que meu amigo pode vê-lo imediatamente, o Postgres está fazendo consulta aos logs de transações? Ou como isso está acontecendo?

Mas depois de pensar um pouco, percebo que quando é feita a solicitação para atualizar os dados e para atualizá-los, o Postgres os traz para a memória principal e uma maneira viável de fazer isso é acompanhar os dados sujos no buffer compartilhado e atualizar os dados no próprio buffer compartilhado e nos logs de transações que podemos ter 0/1com cada entrada de transação DML para identificar se os dados estão presentes no buffer compartilhado ou não. Isso também pode ser útil ao fazer análises.

Alguém pode me ajudar a entender? Desde já, obrigado!

Para agilizar a CREATE INDEXoperação aumentei work_meme configurei max_parallel_maintenance_workerspara 8 e funcionou conforme o esperado quando estava criando um B-treeíndice na tabela. Vi 7 trabalhadores paralelos além do principal. Mas quando comecei a criar um Hashíndice na mesma tabela vi que havia apenas 1 núcleo de CPU usado pelo único trabalhador.

Existem algumas opções especiais que devo alterar para obter o mesmo resultado de quando estava criando um índice de árvore b?

Eu TABLOCKeditei uma tabela para poder executar inserções em massa. Como posso lê-lo enquanto ainda está bloqueado?

Em circunstâncias normais, eu consideraria isso obviamente impossível. Felizmente, há um fator-chave que torna as circunstâncias anormais. Nesse caso, não me importo nem um pouco com ACID . A única coisa que me importa é não cancelar a consulta que editou TABLOCKa tabela.

Eu tentei WITH (NOLOCK), mas os sinais sugerem que não funciona. Estou pedindo o impossível?

Só para esclarecer, suponha que eu esteja correndo

INSERT [dbo].[Table1] WITH (TABLOCK)
SELECT * FROM [A_Lot_Of_Rows]

Meu objetivo é consultar Table1enquanto isso é executado.

Estou fazendo inserções em massa e, enquanto elas estão rodando, decidi que quero verificar alguns detalhes. Eu não me importo de qual linha eu recebo esses detalhes.

Cada linha A_Lot_Of_Rowscontém uma data. A_Lot_Of_Rowsé truncado pelo meu procedimento armazenado e é preenchido novamente em lotes baseados em data antes de ser truncado novamente. Ao ler uma linha arbitrária da tabela, posso saber em que lote estou.

Estou fazendo login com usuário root no MySQL e o servidor foi protegido, então este parâmetro está definido default-authentication-plugin=caching_sha2_passworde este é atualmente o único usuário que tenho.

recebendo o seguinte erro: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Eu tenho uma tabela enorme, digamos 500.000 linhas. Quero copiá-lo (esquema e dados) de um servidor para outro. Este não é um upsert ou qualquer tipo de atualização; É uma cópia e colagem direta e única. Quais são as abordagens idiomáticas para isso?

Eu tentei:

• Restaurar um backup de um servidor para outro. Isso é impraticável porque o SQL Server notoriamente não pode restaurar tabelas de um backup; Ele só pode restaurar bancos de dados. E meu banco de dados é enorme!
• Usando SSMS para criar scripts dos dados da tabela como uma sequência de instruções INSERT. Isto é impraticável, pois as inserções devem ser feitas linha por linha agonizante. Eu suspeito que isso também faz coisas horríveis no log de transações, mas ninguém me atacou por isso ainda (estou executando esse script agora, vai levar horas).

Na documentação do postgresql na opção GRANT está escrito:

Não há necessidade de conceder privilégios ao proprietário de um objeto (geralmente o usuário que o criou), pois o proprietário possui todos os privilégios por padrão. (O proprietário pode, no entanto, optar por revogar alguns dos seus próprios privilégios por segurança.)

Agora tenho uma tabela de banco de dados com um usuário proprietário, mas recebo ERROR: permission denied for table alertsempre que tento selecionar nesta tabela:

database=# SET ROLE user;
SET
database=> select * from pg_tables where tablename = 'alert';
 schemaname | tablename |     tableowner     | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+--------------------+------------+------------+----------+-------------+-------------
 public     | alert     | user |            | t          | f        | f           | f
(1 строка)
database=> select * from alert limit 1;
ERROR:  permission denied for table alert

Por que é isso?

Não há privilégios de tabela adicionais:

database=> \z                                             Access privileges
 Schema |                Name                |   Type   | Access privileges | Column privileges | Policies 
--------+------------------------------------+----------+-------------------+-------------------+----------
 public | alert                              | table    |                   |                   | 

Além disso, o esquema e o proprietário do banco de dados foram alterados para usuário.

Para solucionar esse problema, preciso GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;e tudo funciona bem.

Então, reiterando a pergunta: como é possível que o proprietário não consiga selecionar na mesa? Sim, eu executei REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user;, mas espero que isso redefina os privilégios para os valores padrão e, provavelmente, esta minha suposição está errada. Mas então, como posso descobrir que minhas permissões não são mais padrão? Se eu olhar para \za saída, tudo parecerá como era antes GRANT/ REVOKE. Além disso, olhando para outros bancos de dados, onde não tenho problemas com o select, não vejo nenhuma diferença na saída do \z. Depois de pesquisar muito no Google, descobri que \la saída (e suponho \z) não é exibida para valores padrão . Mas então como posso encontrar esses padrões? \ddpa saída está vazia e estou perdido aqui :(

Agradecemos antecipadamente por qualquer ajuda.

Primeiro, gostaria de pedir desculpas se meu conhecimento na área de DBA é muito limitado, já que eu e vários outros membros da equipe acabamos de receber este projeto porque temos recursos significativamente limitados.

A tarefa dada foi atualizar várias instâncias do SQL Server de 2014 para 2017 e algumas para 2022, uma vez que o EOS está iminente no próximo ano. O escopo também combinará vários bancos de dados em uma instância. Elaboramos um plano sobre como executá-lo, mas precisamos de alguma validação de que esta etapa está correta e de alguns conselhos e sugestões para a implantação.

Pré-atualização

1. Backups Primeiro faríamos backup de todos os bancos de dados que iremos atualizar para o formato .bak. Também salvaremos instantâneos caso o .bak falhe.
2. Testando os referidos backups Após a conclusão do backup, criaremos uma VM para restaurar os backups para garantir que o .bak ou os snapshots possam ser usados ​​​​em caso de problema durante a atualização (esta etapa é obrigatória, pois o cliente está cauteloso porque instantâneos anteriores falharam)
3. Geraremos um relatório do Data Migration Assistant (DMA) para descobrir quais SP e funções não são compatíveis após a atualização.
4. Tudo isso será feito primeiro em um estágio de desenvolvimento; se tudo tiver sucesso, prosseguiremos na produção.

Atualizar

1. Corrigiremos os referidos SP e funções do relatório DMA que terão problemas após a atualização
2. Crie um período de inatividade que também coordenaremos com a equipe de operações para interromper todos os trabalhos de e para os bancos de dados (como Talend e Microstrategy e assim por diante).

Pós-atualização

1. Certifique-se de que nenhum SP e Funções estejam obsoletos
2. Verifique os dados e a acessibilidade
3. Verifique todos os aplicativos que estão usando o banco de dados.
4. Documente todas as alterações feitas em SP e Funções

Possível método alternativo a ser usado

1. No produto, crie outra instância e atualize-a. Se for bem-sucedido, todos os aplicativos serão redirecionados para a nova instância e desabilitarão a instância antiga.

Limitações

1. Os dados no desenvolvimento são significativamente menores, não há recurso para replicar
2. Criar uma VM/servidor com as mesmas especificações do prod para testar ou tentar fazer isso pode ser difícil devido à restrição de recursos

Então este é o plano de jogo atual, há alguma sugestão ou conselho? Há algo que perdemos? Há algo que devemos observar? Existe uma maneira melhor de fazer isso?

Temos uma experiência muito limitada em lidar com este tipo de tarefa, por isso estamos realmente preocupados com a possibilidade de as coisas correrem mal. É claro que testaremos isso de antemão, mesmo antes de atualizar o desenvolvedor, para garantir que tudo esteja tão tranquilo quanto possível.

Muito obrigado.