All perguntas(dba)

Estou executando o PostgreSQL 11 shared_buffersconfigurado para 3 GB no meu Mac. Eu tenho uma tabela jobcom 5 milhões de linhas. A estrutura da tabela é

                           Table "public.job"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 id         | uuid                     |           | not null |
 name       | text                     |           |          |
 created_on | timestamp with time zone |           |          |
 updated_on | timestamp with time zone |           |          |
Indexes:
    "job_pkey" PRIMARY KEY, btree (id)
    "job_created_on_idx" btree (created_on)
    "job_name_idx" btree (name)
    "job_updated_on_idx" btree (updated_on)
    "job_updated_on_name_compound_asc_idx" btree (updated_on, upper(name))
    "job_updated_on_name_compound_desc_idx" btree (updated_on DESC, upper(name))

Observe que criei um índice composto nas colunas updated_one name.

Quando executo query select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC limit 25, o PostgreSQL usa o índice composto job_updated_on_name_compound_asc_idxe leva mais de 4 segundos.

Plano de execução

Limit  (cost=0.43..102.29 rows=25 width=61) (actual time=4549.668..4550.235 rows=25 loops=1)
   Buffers: shared hit=4859940
   ->  Index Scan using job_updated_on_name_compound_asc_idx on job  (cost=0.43..416764.16 rows=102293 width=61) (actual time=4549.667..4550.230 rows=25 loops=1)
         Filter: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
         Rows Removed by Filter: 4828894
         Buffers: shared hit=4859940
 Planning Time: 0.218 ms
 Execution Time: 4550.260 ms

Há um índice na created_oncoluna, mas não é usado. Posso forçar o PostgreSQL a usar o índice da created_oncoluna anexando idà cláusula order by . A consulta é select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC, id limit 25;. Desta vez, o PostgreSQL usa o índice da created_oncoluna e retorna o resultado muito rápido.

Plano de execução

Limit  (cost=52190.61..52193.52 rows=25 width=77) (actual time=125.192..138.055 rows=25 loops=1)
   Buffers: shared hit=42788
   ->  Gather Merge  (cost=52190.61..62136.44 rows=85244 width=77) (actual time=125.191..138.049 rows=25 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=42788
         ->  Sort  (cost=51190.58..51297.14 rows=42622 width=77) (actual time=119.359..119.362 rows=20 loops=3)
               Sort Key: updated_on, (upper(name)), id
               Sort Method: top-N heapsort  Memory: 30kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 31kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 31kB
               Buffers: shared hit=42788
               ->  Parallel Bitmap Heap Scan on job  (cost=2512.94..49987.82 rows=42622 width=77) (actual time=19.915..109.984 rows=36562 loops=3)
                     Recheck Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
                     Heap Blocks: exact=24557
                     Buffers: shared hit=42738
                     ->  Bitmap Index Scan on job_created_on_idx  (cost=0.00..2487.36 rows=102293 width=0) (actual time=16.909..16.909 rows=109685 loops=1)
                           Index Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
                           Buffers: shared hit=395
 Planning Time: 0.168 ms
 Execution Time: 138.115 ms

A diferença no tempo de execução torna-se maior se o banco de dados estiver ocupado atualizando uma grande coluna de linhas.

O índice composto foi criado para melhorar o desempenho da classificação e é muito útil em alguns casos. Como meu sistema gera o SQL dinamicamente com base na seleção do usuário, a condição e a classificação da consulta podem variar. Neste caso específico, adicionar idà cláusula order by para evitar o uso de um índice composto pode melhorar o desempenho, mas talvez em alguns outros casos, usar o índice composto seja melhor, então não posso simplesmente remover o índice composto.

Também verifiquei a tabela pg_stats e aqui está o resultado:

  attname   | inherited | n_distinct | most_common_vals
------------+-----------+------------+------------------
 id         | f         |         -1 |
 name       | f         |         -1 |
 created_on | f         |  -0.908167 |
 updated_on | f         |         -1 |

Eu tenho duas perguntas:

1. Para a consulta acima, obviamente é melhor usar o índice created_on. Por que o PostgreSQL escolhe o índice composto da cláusula order by ? Existe algo que eu possa configurar no PostgreSQL para permitir que ele use o índice correto?
2. Parece que o PostgreSQL não usará índices de colunas na condição de consulta e ordenará por . Está Filtersob o índice composto, embora a coluna usada esteja Filterindexada. É possível que o PostgreSQL use o índice composto para ordenar por e o índice para a coluna de condição de consulta juntos em uma única consulta?

Temos uma grande variedade de tabelas particionadas por mês. Estatísticas incrementais ativadas. Após a coleta programada de estatísticas, a estimativa de cardinalidade torna-se estranha, como

select count(*) from my_table where date >= trunc(sysdate) - 30 and date < trunc(sysdate)

fornece 1,3 milhão de linhas, mas a estimativa é de 20 mil. Somente depois de reunir manualmente as estatísticas, a estimativa se torna precisa. Exemplos de código:

-- Scheduled
dbms_stats.gather_table_stats
(
    ownname=> 'ownname', 
    tabname=> 'tabname' , 
    estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,  
    cascade=> DBMS_STATS.AUTO_CASCADE, 
    degree=> 4,  
    no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
    granularity=> 'AUTO', 
    method_opt=> 'FOR ALL COLUMNS SIZE AUTO'
);

-- Manual
DBMS_STATS.GATHER_TABLE_STATS 
(
    ownname => '"ownname"',
    tabname => '"tabname"',
    partname => '"partname"',
    method_opt => 'FOR COLUMNS DATE SIZE 254',
    estimate_percent => 1
);

Outras tabelas particionadas estão ok.

As diferenças entre esta tabela e outras são (como sabemos):

1. Havia inserções erradas nesta tabela. A maioria das datas está entre 2014 e 2023, mas há algumas linhas com 1970 e 2024 (não podemos alterá-las). Também há uma partição vazia com 2045. Tentamos recriar isso, mas não obtivemos o mesmo comportamento.
2. Mexemos nos histogramas, removemos alguns criados automaticamente e criamos manualmente alguns úteis baseados em funções. Mas em USER_TAB_COL_STATISTICS e USER_TAB_HISTOGRAMS os histogramas para a coluna DATE estavam presentes.

O que pode causar tal comportamento? Como podemos arranjá-lo?

Tenho 2 modelos, model_A e model_B

Há uma relação um para n entre eles

Um registro no model_A possui vários registros relacionados no model_B

Gostaria de introduzir uma restrição: apenas UM registro relacionado em model_B pode ter um de seus campos definido como True

Como posso expressar isso?

Estou fazendo uma atualização em lote onde processo os registros sequencialmente (Usuário 1, Usuário 2 etc.). Cada registro possui diversas consultas de atualização associadas a ele. Preciso pular se houver um problema de dados para um registro individual (usuário neste caso). Idealmente, gostaria de processá-los em paralelo, mas ainda não atingi esse nível (muitos desafios).

É possível fazer isso de forma que:

1. Existe uma única transação.
2. Se houver uma falha, as instruções associadas a esse registro serão revertidas, sem afetar outras.
3. Comprometer-se.

Por exemplo, tenho 4 usuários em um arquivo CSV. Se 3 forem bons e 1 for ruim, 3 deverão ser confirmados (ou abortados) atomicamente; 1 deve ser ignorado com erros.

Observado:

do everything for user 1;
do everything for user 2;
--> if there is failure, it rolls back the *entire* transaction
do everything for user 3;

Na verdade, qualquer nível de erro >= 16 está revertendo toda a transação.

Esperado:

do everything for user 1;
do everything for user 2;
--> if there is failure, roll back this *block* only
do everything for user 3;
do everything for user 4;

É um try-catchrequisito normal em qualquer linguagem de programação; no entanto, não foi possível ver um equivalente do SQL Server (envolvendo transações). Eu li sobre pontos de verificação, mas não tenho certeza se essa é uma opção a ser considerada.

Li recentemente sobre uma configuração chamada Percona Server max_binlog_filesque restringe o número total de arquivos binlog. Isso é exatamente o que eu preciso. Atualmente, o meu binlog_expire_logs_secondsestá definido para 3 dias, o que funciona muito bem 99% do tempo. No entanto, atualmente estou envolvido em um projeto que executa uma grande quantidade de consultas e os binlogs consumiram mais de 70 GB em apenas algumas horas. Nesse caso, eu realmente preciso limitar o número de arquivos.

Existe alguma configuração nativa do MySQL para conseguir isso? Qual é a melhor maneira de restringir o espaço total em disco consumido pelos binlogs?

Tenho trabalhado com SQL Server e seus tipos de instância - padrão e nomeada. Meu entendimento sempre foi que para conectar-se a uma instância nomeada, é necessário especificar o nome da instância. No entanto, descobri recentemente que, se eu definir a porta TCP como 1433, posso me conectar à instância nomeada sem precisar especificar o nome da instância e sem precisar definir aliases.

Estou curioso para saber se esse é o comportamento esperado. Não consegui encontrar nenhuma documentação que explique ou confirme esse comportamento. Alguém pode fornecer algumas dicas sobre isso? É normal poder conectar-se a uma instância nomeada sem fornecer o nome da instância quando a porta TCP está definida como 1433?

Agradeço qualquer esclarecimento sobre este assunto.

Desde já, obrigado!

Tenho que criar o DACPAC a partir do banco de dados de produção. Gostaria de fazer isso por meio do Visual Studio 2022 (SQL Server Object Explorer e "Extract - Data-tier application"). É possível bloquear algum processo/consulta ou utilizar mais CPU/memória para fazer isso?

Estou enfrentando algum problema com minha assinatura (replicação transacional) recentemente, pois sempre que tentei adicionar uma nova assinatura à minha publicação existente de qualquer maneira, uso Inicializar quando imediatamente ou na primeira sincronização, sempre encontrei problemas resultando em assinatura malsucedida.

Quando seleciono inicializar quando Imediatamente, depois de apertar o botão Concluir, encontrei este erro:

Mensagem: Tempo limite de execução expirou. O período de tempo limite decorrido antes da conclusão da operação ou o servidor não está respondendo. Texto do comando: sp_MSreplupdateschema Parâmetros: @object_name = [dbo].[myTableName]

Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() em Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() em Microsoft.SqlServer.Replication.AgentCore.Run() (Fonte: MSSQLServer, número do erro: -2) Obtenha ajuda: http:/ /help/-2 Servidor MyServerName, Nível 11, Estado 0, Procedimento, Linha 0 Tempo limite de execução expirado. O período de tempo limite decorrido antes da conclusão da operação ou o servidor não está respondendo. (Fonte: MSSQLServer, Número do erro: -2) Obtenha ajuda: http://help/-2 Fonte: Site de destino: Mensagem: A operação de espera expirou Pilha: (Fonte:, Número do erro: 0) Obtenha ajuda: http: //ajuda/0 //help/-2 Servidor MyServerName, Nível 11, Estado 0, Procedimento, Linha 0 Tempo limite de execução expirado. O período de tempo limite decorrido antes da conclusão da operação ou o servidor não está respondendo. (Fonte: MSSQLServer, Número do erro: -2) Obtenha ajuda: http://help/-2 Fonte: Site de destino: Mensagem: A operação de espera expirou Pilha: (Fonte:, Número do erro: 0) Obtenha ajuda: http: //ajuda/0 //help/-2 Servidor MyServerName, Nível 11, Estado 0, Procedimento, Linha 0 Tempo limite de execução expirado. O período de tempo limite decorrido antes da conclusão da operação ou o servidor não está respondendo. (Fonte: MSSQLServer, Número do erro: -2) Obtenha ajuda: http://help/-2 Fonte: Site de destino: Mensagem: A operação de espera expirou Pilha: (Fonte:, Número do erro: 0) Obtenha ajuda: http: //ajuda/0

No entanto, se eu selecionar Inicializar quando na primeira sincronização, isso me dará o status de O instantâneo inicial do artigo myTableName ainda não está disponível.

Além disso, se eu reinicializar a assinatura, nada acontece

Observação:

Estou usando o SQL Server Standard 2019 Edition, Microsoft SQL Server Management Studio 2019

Aqui também está a especificação do meu servidor: HP Proliant Gen10 +

e minha utilização atual do sistema via gerenciador de tarefas

Existem algumas fontes na internet que insistem que idle in transactionas conexões podem impedir que o vácuo limpe tuplas mortas. Abaixo estão alguns exemplos:

Guia do usuário para Aurora :

Uma transação no estado inativo em transação pode conter bloqueios que bloqueiam outras consultas. Ele também pode impedir que VACUUM (incluindo autovacuum) limpe linhas mortas, levando ao inchaço do índice ou da tabela ou ao wraparound do ID da transação .

Blog da Cybertec

Na verdade, uma transação longa não é um problema – o problema começa se for necessária uma transação longa e muitas pequenas alterações. Lembre-se: a transação longa pode fazer com que o VACUUM não limpe suas linhas mortas .

Na verdade, existem muitos deles, mas do meu ponto de vista isso parece absolutamente ridículo: na maioria dos casos, o nível de isolamento da transação é lido como confirmado, o que, por sua vez, significa que não há necessidade de manter tuplas mortas para tais transações, além disso, encontrei alternativas opinião sobre esse assunto :

Na verdade, não se trata de transações de longa duração, mas de instantâneos de longa duração . Certamente uma instrução select ou insert de longa duração fará isso. Para níveis de isolamento superiores aos de leitura confirmada, toda a transação reterá o instantâneo até que seja inativo ; portanto, se alguém abrir uma transação de leitura repetível e depois sair de férias sem confirmá-la, isso seria um problema. As transações preparadas suspensas também funcionarão (se você não sabe o que é uma transação preparada, provavelmente não as está usando).

ou o comentário de Pavel Luzanov na postagem do blog Cybertec :

Acredito que esse exemplo de transação longa seja verdadeiro apenas para o nível de isolamento de leitura repetível (ou serializável). Mas por padrão BEGIN usou Read Commited. Portanto, após o término de SELECT na primeira sessão, VACUUM removerá as linhas mortas em uma tabela após os comandos UPDATE, DELETE subsequentes na sessão 2.

o que foi confirmado por @ Bill Karwin em sua resposta (obrigado!)

A questão é: existem cenários “válidos” “não ficcionais” em que idle in transactionas conexões devem ser consideradas prejudiciais? (Não estou perguntando sobre transações com nível de isolamento superior a leitura confirmada, vazamentos de transação ou conexão, longos bloqueios de transação, etc.).

Por algum motivo comercial, estou executando a consulta SELECT * FROM table WHERE updated_at > $1 AND user_id = $2;.

Considere duas transações, A e B, que estão atualizando linhas na tabela: UPDATE table SET value = $1, updated_at = NOW() WHERE id = $2;. A transação A começa primeiro, portanto, a updated_atlinha de A é anterior à linha de B. updated_atNo entanto, por algum motivo, A fica mais lento e B confirma primeiro.

Se a primeira consulta for executada antes de A ser confirmado, o software exibirá a linha de B, mas não a linha de A, o que as empresas dizem não ser aceitável.

Isso poderia ser resolvido usando pg_xact_commit_timestamp(), mas retorna quando a transação foi concluída, não confirmada (provavelmente é adequado para o meu caso de uso).

Outra solução poderia ser executar as transações SELECT FROM table WHERE user_id = $1 FOR UPDATE;antes de atualizar a linha, mas essas consultas fazem parte de transações maiores e acho que isso destruiria o desempenho de gravação.

Uma solução simples parece fazer com que a transação B espere que A seja confirmada. Existe uma maneira de conseguir isso?