All perguntas(dba)

Estou depurando um problema de alta carga de CPU com o servidor flexível Azure PostgreSQL. Na janela de tempo em que o uso da CPU aumenta, estou vendo muitas consultas de utilitários. Eles não demoram muito para serem processados, mas existem centenas de milhares deles.

As consultas são todas DISCARD ALLconsultas. Para que servem essas consultas e é normal ver tantas delas?

Tenho 3 tabelas de dados (todas contêm mais colunas, mas estas são as que preciso de cada uma):

t1: tabela1

t2: tabela2

t3: tabela3

Em t1, há apenas uma linha para cada workID, que é a transação atual para esse workID. À medida que os dados são adicionados, todas as 3 tabelas são atualizadas, com t1 mostrando apenas a transação atual. Em t2 e t3, há várias linhas porque mostra transações históricas para cada workID.

As colunas h1Amt e h2Amt mostram o mesmo custo, mas formatadas de forma diferente. Além disso, h1Dt, h2Dt e h3Dt refletem a mesma data, apenas formatados de forma diferente.

Preciso encontrar um valor atual específico e quando esse valor foi alterado pela última vez. No meu caso, estou procurando um valor de 0,00 ou 0000 e quando foi alterado pela última vez para esse valor. Além disso, o status (t1) deve ser “A” e o tipo (t2 ou t3) deve ser 1, 2 ou 3.

Aqui está a saída que estou procurando:

Isso é o que eu descobri até agora, mas está retornando valores que atualmente não são 0000 (0,00) ou não mostrando workIDs que tenham um valor atual de 0000 (0,00).

select t2.workID2
     ,min(t2.h2Dt)
     ,min(t2.h2Amt)
from table2 t2
inner join table1 t1 on t2.workID = t1.workID
where not exists (
     select 1
     from table2
     where workID2 = t2.workID2
          and h2Amt <> t2.h2Amt
          and h2Dt > t2.h2Dt
     )
     and t2.h2Amt = '0000'
     and t1.status = 'A'
     and t2.type1 in ('1','2','3')
group by t2.workID2

Não usei a tabela3, principalmente porque a tabela2 contém o h2Amt necessário. Não acho que seria inteligente usar h1Amt da tabela1, pois mostra apenas o que é atual. No mínimo preciso usar a tabela1 por causa da coluna de status. Se eu usasse a tabela3, a tabela1 seria um requisito, pois é a única tabela com uma coluna de status, mas acho que teria o problema de encontrar o valor histórico, já que ele não está registrado na tabela1 ou na tabela3. Além disso, tive problemas para obter linhas duplicadas em outras consultas que tentei, incluindo o uso de todas as três tabelas.

A consulta acima é a consulta mais próxima que vi depois de pesquisar bastante por situações semelhantes.

Espero ter fornecido detalhes suficientes para encontrar uma solução. Desde já, obrigado!

O planejador de consultas do Postgres é capaz de reorganizar os termos de uma WHEREcláusula de acordo com as regras de comutatividade e associatividade?

Por exemplo, WHEREtodas as cláusulas a seguir resultam em um plano de consulta com a mesma complexidade de tempo aproximada?:

• WHERE a AND b AND c
• WHERE (a AND b) AND c
• WHERE a AND (b AND c)
• WHERE (c AND b) AND a

Eu tenho uma tabela Postgres com um grande número de colunas indexadas (cerca de 100 colunas indexadas no total e, sim, preciso de todas elas e, sim, todas precisam ser indexadas separadamente). Qualquer atualização de linha faz com que todos os índices sejam atualizados, o que dá muito trabalho para o mecanismo de banco de dados.

Quero entender as implicações de simultaneidade da discussão na página de documentação do Postgres intitulada Considerações sobre bloqueio de índice e também o fato de que o Postgres é de thread único (multiprocesso) , em termos de como o design atual afeta o desempenho do leitor e do gravador para um grande número de consultas simultâneas, visto que tenho muitos índices de colunas.

Minha interpretação dessas coisas é a seguinte (corrija as que estiverem erradas):

• Os gravadores que atualizam linhas individuais não bloqueiam os leitores, a menos que o leitor esteja executando uma consulta que produza um conjunto de resultados que inclua a linha que está sendo atualizada.
• Os escritores só bloqueiam uns aos outros se estiverem tentando atualizar a mesma linha ao mesmo tempo.
• Atualizações simultâneas de índices baseados em btree de vários gravadores são mescladas de acordo com um conjunto de regras que geralmente faz a coisa certa (portanto, atualizar os mesmos índices ao mesmo tempo não causa o bloqueio dos gravadores, a menos que estejam atualizando a mesma linha).

Minhas perguntas são:

• Como pode haver vários leitores ou escritores simultâneos, se o Postgres for de thread único? Se você tiver vários processos em execução, eles simplesmente dependem da consistência entre processos dos caches de disco (ou precisam liberar manualmente o conteúdo no disco) para coordenar atualizações simultâneas?
• E se algo puder ser bloqueado enquanto um grande número de índices estiver sendo atualizado devido a uma atualização de linha? Se alguma coisa puder ser bloqueada durante uma atualização, é possível ativar o equilíbrio entre consistência e disponibilidade para que, por exemplo, uma atualização de linha não seja atômica (ou seja, para que os índices sejam atualizados um de cada vez, mas a atualização de todos os índices não precisa acontecer atomicamente)? Estou bem com a falta de consistência em nome de uma maior simultaneidade.

Dada uma tabela como esta,

Quero criar uma tabela via SQL sem fazer funções de janela

id | time| attribute1| attribute1atFirstRecordedTimeperID

1  | 0 | a  | a

1 | 2 | b | a

2| 0 | c | c

2| 4 | d | c

tentei

SELECT 
    t1.id, 
    t1.time, 
    t1.attribute1,
    attribute1fixedattime0 = t2.attribute1
FROM s t1
INNER JOIN (
    SELECT attribute1 AS attribute1fixedattime0
    FROM s
    WHERE time = 0
) t2 ON t1.id = t2.id

O alias da coluna está correto?

Isso gera um erro

Se eu corrigir esse código, quero fazer o código SQL funcionar para o caso em que id=2 não tem seu primeiro atributo1 registrado no tempo t=0, mas para t>0. Então faço um min na condição de join ou dentro do JOIN?

Quero criar uma tabela via SQL sem fazer funções de janela para virar

em

Para ir de

identificação | tempo| atributo1

1  | 0 | a 

1 | 2 | b 

2| 42 | c 

2| 69 | d 

para a mesa

id | time| attribute1| attribute1atFirstRecordedTimeperID

1  | 0 | a  | a

1 | 2 | b | a

2| 42 | c | c

2| 69 | d | c

se você usar uma função de janela row_number() para marcar o local em que a primeira vez está dentro de cada id, então como você se junta ou não usaria uma junção, mas algo mais?

SELECT 
    id,
    time,
    attribute1,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) AS rn
FROM your_table

SELECT
    id,
    time,
    attribute1,
    FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attribute1atFirstRecordedTimeperID
FROM (
    -- Previous query here
) AS subquery

Percebi que importar dados truncados apenas gera um aviso. Achei que seria simples capturar esse aviso e sinalizar um erro por meio de um manipulador, mas não consigo fazer isso. Alguma sugestão?

CREATE OR REPLACE PROCEDURE FILE_IMPORT_HANDLER(filename VARCHAR(255), tablename 
VARCHAR(255))
LANGUAGE SQL
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '22001'
        SIGNAL SQLSTATE '75002'
            SET MESSAGE_TEXT = '...';

    CALL ADMIN_CMD('import from ' || filename || ' of del insert into ' || tablename);
END @

Eu gostaria de falhar muito se 22001 for encontrado. De certa forma, aumente a gravidade do truncamento de dados durante a importação

Resumo: tenho um cluster de 2 nós e se um dos nós estiver inativo, não consigo fazer login. Eu recebo o erro.

Connection error: ('Unable to connect to any servers', {'192.168.1.104:9042': AuthenticationFailed('Failed to authenticate to 192.168.1.104:9042: Error from server: code=0100 [Bad credentials] message="Unable to perform authentication: Cannot achieve consistency level LOCAL_QUORUM"')})

• Estou usando um ROLE que criei (não um SUEPRUSER, então o problema de SUPERUSER sempre usar QUORUM não deve se aplicar aqui. Pelo que li deveria usar LOCAL_ONE mas não é!
• Eu configurei a replicação do keyspace system_auth para 2 propositalmente, pois isso é recomendado para não ter um único ponto de falha.

Aqui está uma captura de tela para mostrar que não estou usando o cassandra SUPERUSER e ainda estou recebendo o erro:

Gostaria que o login continuasse funcionando mesmo se um nó estivesse inativo.

Eu tenho um CTE recursivo dentro de uma função com valor de tabela embutida. O ITVF está retornando uma lista de IDs contendo uma longa sequência de ancestrais de uma pessoa, geralmente retorna cerca de 12 a 18 vezes antes de chegar ao fim. É bastante rápido, mas há um erro nas estimativas que se acumula quando usado por muitas pessoas, tornando-se extremamente lento.

O CTE fica assim

WITH ancestors AS (
  SELECT
    IndID,
    AncestorID
  FROM
    dbo.persons

  UNION ALL

  SELECT 
    IndID,
    AncestorID
  FROM
    ancestors a
    INNER JOIN dbo.persons p ON p.IndID = a.AncestorID
)
SELECT IndID, AncestorID FROM ancestors

Eu tenho uma dúzia de milhões de linhas, então é uma tabela bem grande. Quando solicito um IndID, o plano de execução diz que estimou 7 linhas, mas obteve 1.300 linhas reais. Para uma única solicitação, é aceitável (é executado em menos de um segundo), mas se eu me juntar a ela em outra solicitação para que ela seja chamada, digamos 100 vezes, a velocidade cai para um rastreamento, pois a estimativa está ficando cada vez pior.

Só para ficar claro, o erro de estimativa está presente mesmo fora do IVTF. Especifiquei apenas para deixar claro que não posso usar apenas uma tabela temporária. Ele precisa ficar em um IVTF para que eu possa integrá-lo em solicitações maiores e mais complexas e permanecer paralelizado. O que posso fazer para estimar melhor as linhas?

Atualização: cole o plano

Atualização 2: Menos simplificado

Estou meio preso entre dois problemas. Ou eu uso um MSTVF e todas as minhas consultas não podem ser paralelizadas, ou uso um ITVF e espero que os deuses do SQL sejam generosos e não subestimem terrivelmente a contagem de linhas, então tudo agora é trocado no disco rígido em vez de na RAM. Espero que seja apenas porque sou burro e que seja uma solução estúpida e fácil em algum lugar.

Atualização 3 Para responder, com o melhor de meu conhecimento, às perguntas feitas.

uno) Atualizado para a atualização cumulativa mais recente. Não mudou nada como esperado, mas é bom estar atualizado como você disse :)

dos) Estamos na edição Standard, mas tenho uma Column Store e não me lembro por que fiz isso. Está em IndID, FirstNameID, LastNameID. Vou tentar abandoná-lo, somos apenas 2 usuários no banco de dados hoje, podemos gerenciar os tempos de inatividade se ele travar alguma outra coisa.

Depois de remover o ColumnStore, ele economizou cerca de 30 segundos! Ainda lento, mas está melhor. Terei que verificar minhas anotações para descobrir por que fiz aquele ColumnStore.

dos: parte 2) A sensação de "caixa com pouca potência" que você tem é exatamente o que me pegou até agora. Achei que nossa máquina não tinha potência suficiente, mas depois de conversar com a TI aqui, eles disseram que não estávamos usando mais de 25% dos recursos disponíveis, então o gargalo estava definitivamente no nível SQL. Então, pedi uma atualização do SQL 2017 para 2022 no mês passado e então, agora que vi que a maioria das minhas consultas pesadas estavam sempre rodando serializadas, comecei a otimizar até chegar a esta. Eu tentei OPTION(USE HINT('DISALLOW_BATCH_MODE'), MAXDOP 8);e não vejo nenhuma mudança na velocidade.

tres) Essa solicitação deve retornar cerca de 14 milhões de linhas, então não se preocupe desse lado. Mas o fato de apenas 8 linhas terem sido estimadas na reserva de recursos não é uma razão pela qual ela é muito mais lenta do que deveria?

mais contexto) Eu estava usando um MSTVF antes de todo o meu trabalho este mês, quando mudei para um IVTF ele é mais rápido, mas a curva de tempo gasto versus linhas solicitadas é exponencial em vez de linear, se é que você me entende. Estou aberto para repensar como tudo isso é feito.

Trabalho para um grupo de pesquisa e parte do meu trabalho é extrair conjuntos de dados para pesquisadores. Sou praticamente o único usuário pesado do banco de dados, meus colegas estão mais envolvidos na parte do trabalho "inserir e limpar os dados". Portanto, posso fazer praticamente o que quiser com os índices, funções, etc., desde que a estrutura da tabela em si não seja muito alterada.

Atualização 4 - O quê? Não entendi, estava tentando fazer um belo gráfico para mostrar a curva exponencial "tempo gasto versus linhas solicitadas", então mudei minha consulta para obter bons números quadrados.

select 
    count(*)
FROM
    (SELECT TOP 10000 * FROM individus.Individus WHERE AnneeNaissance > 1901 AND AnneeDeces < 1911) i CROSS APPLY
    individus.GetAscendanceSimple(i.IndID) a

E isso funcionou em 10 segundos... Até tentei o TOP 10.000.000 e ainda rápido, então só tenho que colocar um número arbitrário grande para que todos os meus casos sejam cobertos e ele rode tão rápido quanto eu esperava (O TOP é importante). Antes de colocar isso como solução, devo estar errado, não? Essa é uma solução realmente estúpida se for tudo o que precisamos fazer para corrigir o planejamento.

Sem subconsulta vs Com subconsulta

Configurei duas VMs de servidor SQL no Azure com grupos de disponibilidade e criptografia de dados transparente com a chave residente no keyvault. Achei que o servidor SQL só precisaria entrar em contato com o keyvault para obter a chave na inicialização/reinicialização do serviço, mas parece chegar com uma solicitação de "desembrulhar" a cada 15 minutos ou mais, o que me parece estranho. Com que frequência o conector do servidor SQL para keyvault realmente precisa entrar em contato com o keyvault e por que isso acontece? É algo que pode fazer com que isso aconteça?

Agradeço qualquer ajuda dada. 🙂

Estou tentando consertar um trabalho excluindo-o e recriando-o. Mas não posso porque parece não existir quando tento largar, e já existe quando tento criar

Begin
        dbms_scheduler.drop_job(job_name => 'add_error_rate_sig');
        
End;
[2024-01-24 13:12:33] [99999][27475]
[2024-01-24 13:12:33]   ORA-27475: unknown job "SREAPP"."ADD_ERROR_RATE_SIG"
[2024-01-24 13:12:33]   ORA-06512: at "SYS.DBMS_ISCHED", line 278
[2024-01-24 13:12:33]   ORA-06512: at "SYS.DBMS_SCHEDULER", line 751
[2024-01-24 13:12:33]   ORA-06512: at line 2
[2024-01-24 13:12:33] Position: 0
SREAPP> BEGIN
            DBMS_SCHEDULER.CREATE_JOB(
                    job_name => 'add_error_rate_sig',
                    job_type => 'PLSQL_BLOCK',
                    job_action => 'BEGIN SREAPP.add_error_rate_sig(null); END;',
                    number_of_arguments => 0,
                    start_date => TO_TIMESTAMP_TZ('2024-01-24 00:00:00.000000000 AMERICA/LOS_ANGELES',
                                                  'YYYY-MM-DD HH24:MI:SS.FF TZR'),
                    repeat_interval => 'FREQ=WEEKLY;BYHOUR=01;BYMINUTE=00',
                    end_date => NULL,
                    enabled => TRUE,
                    auto_drop => FALSE,
                    comments => 'push to error rate summary'
                );
        
        END;
[2024-01-24 13:12:44] [99999][27477]
[2024-01-24 13:12:44]   ORA-27477: "SREAPP"."ADD_ERROR_RATE_SIG" already exists
[2024-01-24 13:12:44]   ORA-06512: at "SYS.DBMS_ISCHED", line 175
[2024-01-24 13:12:44]   ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
[2024-01-24 13:12:44]   ORA-06512: at line 2
[2024-01-24 13:12:44] Position: 0