All perguntas(dba)

Eu estava enfrentando esse problema em uma instância de produção e tentei uma recriação local em pequena escala e consegui.

Eu tenho uma tabela de teste simples

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Estou usando a versão Mysql 8.0.26com um innodbmecanismo de armazenamento

Preenchi esta tabela com cerca de 4 milhões de linhas aleatórias. Selecionar 5000 linhas desta tabela leva cerca de 50 ms. Em seguida, apaguei todas as linhas usandoDELETE FROM test;

Após excluir todas as linhas, se eu executar SELECT * FROM test LIMIT 5000;essa consulta, ela agora leva mais de 1 minuto para ser executada. Quando a consulta select finalmente termina de ser executada, ela retorna 0 linhas (como esperado).

Antes de prosseguir, quero esclarecer que entendo que executar um optimize tableresolverá o problema, mas estou interessado em entender o que realmente está acontecendo nos bastidores. Aqui estão alguns pensamentos que tive

1. Depois de cerca de uma hora, a consulta se torna instantânea novamente. Isso me leva a acreditar que algum tipo de coleta de lixo ou limpeza está acontecendo por baixo dos panos. Não consigo encontrar nada sobre isso nos documentos.
2. A consulta fica significativamente mais lenta após a exclusão. Como não estou usando nenhuma cláusula não indexada na minha consulta select, ela foi resolvida em cerca de 50 ms antes da exclusão, então, de alguma forma, é 1500x mais lenta após a exclusão.
3. Eu pesquisei o conceito de "registros fantasmas", no entanto, todos os resultados que consigo encontrar estão falando apenas sobre o SQL Server em vez do Mysql, o que me leva a acreditar que o conceito de "registros fantasmas" pode não ser uma coisa no Mysql. Não consigo encontrar nenhuma menção nos documentos do Mysql sobre isso.

Eu tentei o log de consulta lenta e esperava ver um monte de dados escaneados, mesmo que tivessem sido excluídos (registros fantasmas), mas além da duração, não consigo ver nada estranho ou inesperado lá

# Time: 2025-02-11T10:29:24.510606Z
# User@Host: root[root] @  [172.17.0.1]  Id: 20978
# Query_time: 69.680895  Lock_time: 0.000135 Rows_sent: 0  Rows_examined: 0 Thread_id: 20978 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 130 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2025-02-11T10:28:14.829711Z End: 2025-02-11T10:29:24.510606Z
SET timestamp=1739269694;
select * from test
LIMIT 0, 5000;

Alguém pode explicar em detalhes o que exatamente está acontecendo aqui? Idealmente, as duas coisas que eu adoraria saber, se possível, são:

1. O que está realmente acontecendo?
2. É possível ver/diagnosticar esse problema olhando algumas estatísticas internas do Mysql? Algo como o status/information_schema/performance_schema do mecanismo innodb, por exemplo. Basicamente, algo que eu poderia olhar e dizer que uma tabela está tendo esse problema sem saber que uma exclusão acabou de ser executada

Aqui está a explicação do select -SELECT * FROM test LIMIT 5000

Antes de excluir (rápido 50 ms)

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '6958223', '100.00', 'Using index'

Após a exclusão (lento 69s) - Corri analyze table testpara atualizar as estatísticas antes de executar novamente a seleção e a explicação

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '1', '100.00', 'Using index'

Tenho uma tabela com ids e nomes de jogadores. Para cada rodada, a tabela será embaralhada, dividida ao meio e colocada lado a lado. O tratamento de quantidades desiguais de linhas em cada grupo não é o escopo desta questão.

A única coisa que preciso alcançar é (metade superior + metade inferior) OU (tabela a + tabela b) lado a lado. Em detalhes, tabela a como está + ambas as colunas de b sem nenhuma referência uma à outra - linha por linha.

preparei um violino simples:

https://dbfiddle.uk/vkahsaFj

Português: https://dbfiddle.uk/ASM405Co

Algumas referências:

https://stackoverflow.com/questions/7983369/how-do-i-combine-a-mysql-query-and-subquery-when-theres-multiple-rows

O resultado deve ser este:

Tentei todos os tipos de junções, tabelas temporárias, agrupamentos e assim por diante, sem sucesso.

A documentação sobre os benefícios do particionamento lista o seguinte como o primeiro benefício de desempenho do particionamento de tabelas

o otimizador de consultas pode processar consultas de junção equivalente entre duas ou mais tabelas particionadas mais rapidamente quando as colunas de particionamento são as mesmas que as colunas nas quais as tabelas são unidas.

A mesma página fala um pouco mais sobre esse tópico mais adiante , mas não chega a nada além de dizer que unir tabelas particionadas que não são particionadas exatamente da mesma forma não obtém as mesmas otimizações que unir tabelas particionadas equivalentemente. Isso é, claro, irrelevante para o que a seção anterior alegou; qualquer comparação de desempenho entre duas formas de particionar tabelas não tem nada a ver com os benefícios do particionamento quando comparado a não particionar.

Isso me faz perguntar: unir tabelas particionadas equivalentemente é mais rápido do que unir duas tabelas com a mesma chave de índice clusterizado líder? Eu ficaria feliz com uma resposta experimental ou uma que usasse a teoria relevante para chegar a uma conclusão.

Eu li sobre os internos e li muitos white papers e blogs, mas acho que não vi isso abordado em lugar nenhum. Meu entendimento dos internos me leva a concluir que a abordagem particionada deve ser mais lenta do que a abordagem não particionada. Afinal, tudo o que o particionamento realmente faz é colocar outra chave de índice na frente da sua lista de chaves. Meus próprios experimentos descobriram o mesmo.

Notei que para uma tabela (ou visualização ou visualização materializada) no PostgreSQL, há uma linha em pg_depend vinculando a linha pg_class da tabela com a linha pg_namespace do seu esquema.

No entanto, estou pensando se há algum caso em que uma linha em pg_depend pode vincular a linha pg_class a uma linha pg_namespace de um esquema no qual ela não está.

Isso está no contexto de Como posso listar as tabelas em um esquema sem uma varredura sequencial de pg_class?, onde estou tentando encontrar uma consulta eficiente para encontrar as tabelas em um esquema e me pergunto se tenho que lidar com esse caso.

O PostgreSQL tem uma OR REPLACEvariante " " (como em " CREATE OR REPLACE ____") para tabelas, funções, visualizações, regras, etc. No entanto, ele não tem uma para tipos.

Eu poderia ir registrar um bug, mas imagino que essa foi uma decisão deliberada dos mantenedores. Minha pergunta é: por quê?

Em outras palavras, o que há de diferente nos tipos que os faz não precisar de um OR REPLACE(mesmo que tabelas, funções, etc. precisem)?

PS Acabei de descobrir que ele tem um DROP TYPE IF EXISTS... o que só me confunde mais. Realmente parece que CREATE TYPEfoi escolhido para não ter um IF EXISTS, e eu simplesmente não vejo o porquê.

No PostgreSQL, eu gostaria de listar as tabelas em um esquema. Eu tenho essa consulta que faz isso para o esquema público, por exemplo:

SELECT
  relname
FROM
  pg_class
WHERE
  relnamespace = 'public'::regnamespace
  AND relkind = 'r';

No entanto, no meu sistema isso leva ~400-500ms (e às vezes pode levar muito mais tempo). Suspeito que isso seja porque pg_class tem mais de 5 milhões de linhas e tem quase 5gb de tamanho.

Olhando para a explicação e análise da consulta, vejo isto:

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..356932.33 rows=14 width=64) (actual time=2.190..418.126 rows=120 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pg_class  (cost=0.00..355930.92 rows=6 width=64) (actual time=5.318..375.222 rows=40 loops=3)
         Filter: ((relnamespace = '2200'::oid) AND (relkind = 'r'::"char"))
         Rows Removed by Filter: 1792453
 Planning Time: 0.070 ms
 Execution Time: 418.162 ms

e vejo que tem uma varredura sequencial em pg_class. Olhando para os índices em pg_class, isso não é surpreendente - não há índice na coluna relnamespace (pelo menos, nenhum índice que tenha relnamespace como sua primeira coluna).

Então, como posso evitar a varredura sequencial em pg_class ao tentar encontrar as tabelas em um esquema? Isso é possível?

No Neo4j, quando utilizo a seguinte consulta:

MATCH (p:Person)-[:ACTED_IN]->(m) WHERE 'Neo' IN r.roles RETURN p

então ele retorna apenas um nó Pessoa.

Mas quando eu mudo a consulta para:

MATCH (p:Person)-[:ACTED_IN]->(m) WHERE 'Neo' IN r.roles RETURN p.name

então ele retorna 3 linhas.

Isso é estranho para mim, pois espero que apenas uma linha seja retornada?

Para testes de compatibilidade, precisamos de uma lista completa de todos os tipos de dados disponíveis no MySQL v8.0, para garantir que um software de destino fora do ecossistema MySQL possa manipular corretamente todos os tipos de dados da fonte MySQL.

O ChatGPT me disse para tentar a seguinte consulta:

SELECT DISTINCT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY DATA_TYPE;

Mas ele retorna apenas os tipos usados ​​na instância do MySQL à qual estamos nos conectando. Por exemplo, nossa consulta retornou 24 tipos, enquanto o ChatGPT me disse depois que o MySQL suporta 36 tipos no total.

Também consultamos o seguinte documento oficial online, mas ele não forneceu uma lista geral e também nos preocupamos que o documento possa não fornecer informações suficientes sobre a implementação.

• https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Se quisermos descobrir isso programaticamente, dê-nos um exemplo.

Ou, se pudermos nos referir a um repositório de código aberto. Por favor, nos dê algumas dicas, pois não estamos familiarizados com os repositórios do mundo de código aberto.

Agradecemos muito quaisquer dicas e sugestões.

Temos um grupo de disponibilidade que consiste em 42 bancos de dados com uma réplica síncrona e uma réplica assíncrona. A réplica síncrona é capaz de acompanhar a primária (por definição), mas a maioria dos bancos de dados na réplica assíncrona efetivamente congela seu progresso no início do dia útil e não faz progresso até perto do fim do dia útil. Abaixo está um gráfico mostrando o atraso do AG por banco de dados, calculado last_commit_timepor sys.dm_hadr_database_replica_states

Para a maioria dos bancos de dados, o last_commit_timefica por volta das 9:00 AM até quase o fim do dia útil. A citação abaixo é de Troubleshoot: Potential data loss with asynchronous-commit availability-group replicas

As seções a seguir descrevem as causas comuns para alto potencial de perda de dados de uma réplica secundária de confirmação assíncrona, supondo que você não tenha um problema de desempenho sistêmico na sua instância de servidor que não esteja relacionado a grupos de disponibilidade.

1. Alta latência de rede ou baixa taxa de transferência de rede causa acúmulo de log na réplica primária

2. O gargalo de E/S do disco retarda o endurecimento do log na réplica secundária

Cerca de metade dos bancos de dados usam os arquivos de dados do SQL Server no recurso Microsoft Azure. O desempenho de E/S é muito ruim para esses bancos de dados a ponto de às vezes eu ver avisos de E/S de 15 segundos no log de erros. A parte que me confunde é que a maioria dos bancos de dados com o pior atraso não está no armazenamento do Azure. O único padrão que consigo ver é que os bancos de dados com o melhor desempenho de E/S parecem ter o maior atraso.

Considerando vários bancos de dados em um grupo de disponibilidade, é possível que um banco de dados com baixo desempenho de E/S cause atraso em um banco de dados com bom desempenho de E/S?

Alguns detalhes técnicos adicionais sobre minha situação, se necessário:

• A versão do SQL Server é Microsoft SQL Server 2019 (RTM-CU30).

• O data center que hospeda a réplica assíncrona está a 1800 milhas de distância do primário. Ambos os data centers estão nos EUA.

• O ping da réplica assíncrona para o primário é de 40-50 ms. Já me disseram muitas vezes que não estamos atingindo algum tipo de limitação geral de throughput de rede. Um teste netttcp mostra capacidade de largura de banda de 25-30 Mbit/seg.

• De acordo com o hadr_database_flow_control_actionevento estendido, os bancos de dados que não fazem progresso durante o dia útil gastam efetivamente 100% do tempo esperando no controle de fluxo. Os bancos de dados com menos lag não gastam tempo esperando no controle de fluxo.

• De acordo com o hadr_transport_flow_control_actionevento estendido, não há nenhuma espera no controle de fluxo no nível de transporte. O Sends to Transport/seccontador perfmon parece validar isso.

• O sinalizador de rastreamento 12310 foi habilitado de qualquer maneira e não teve nenhum efeito perceptível.

• A latência de gravação para os bancos de dados assíncronos com armazenamento não no Azure é entre 1-10 ms. A latência de gravação para os bancos de dados com armazenamento no Azure chega a 400 ms.

• Usando o Bytes Sent to Replica/seccontador perfmon, a réplica assíncrona consegue manter o ritmo até por volta das 8:30 da manhã. Depois, ela fica para trás e só recupera o atraso depois de algumas grandes explosões de progresso após horas.

• O Log Bytes Flushed/seccontador perfmon (dados ausentes por parte do dia) parece corresponder ao que vemos com bytes enviados. A atividade do usuário final começa a cair por volta das 16h30, que é quando a réplica assíncrona faz mais progresso em recuperação.

• Gerei um relatório de latência AG em 02/06/2025 e os gargalos são envio e controle de fluxo no primário. Não sei como acompanhar esses resultados:

• Estou ciente de que colocar bancos de dados usando os arquivos de dados do SQL Server no recurso Microsoft Azure em um AG assíncrono pode ser uma configuração incomum. Nós efetivamente enviamos os dados 1800 milhas apenas para enviá-los para a nuvem. A documentação da Microsoft sobre o recurso sugere que isso pode não ser necessário:

Benefícios de alta disponibilidade e recuperação de desastres: Usar o recurso SQL Server Data Files no Microsoft Azure pode simplificar as soluções de alta disponibilidade e recuperação de desastres. Por exemplo, se uma máquina virtual no Microsoft Azure ou uma instância do SQL Server travar, você pode recriar seus bancos de dados em uma nova instância do SQL Server apenas restabelecendo links para os blobs.

No momento, estou enfrentando um problema com consultas parametrizadas no SQL Server e não entendo onde ele está enraizado.

Eu resumi tudo em um exemplo simples:

Vamos supor uma tabela que contém dados sobre alguma entidade filha, bem como o parent_ide um índice correspondente no parent_id. Os dados são acessados ​​com base nisso, parent_idmas por meio de uma visualização que, adicionalmente aos dados da tabela, contém uma coluna que calcula um row_numbersobre todas as entradas particionadas pelo parent_id.

Configuração reproduzível

Crie a tabela, o índice e a visualização da seguinte forma:

CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO

CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id);
GO

CREATE VIEW dbo.test_view
AS
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
  FROM dbo.test
GO

Agora coloque alguns dados na tabela:

DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
    SET @i = @i + 1
    INSERT INTO dbo.test (text, parent_id)
    VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END

A questão

Ao acessar os dados por meio de uma consulta parametrizada a partir da exibição, o SQL Server fará uma varredura completa na tabela.

DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id

Ao acessar os dados diretamente (sem usar um parâmetro), obteremos o índice de busca esperado.

SELECT *
FROM dbo.test_view
WHERE parent_id = 123456

O que eu tentei

Pesquisando em diferentes fóruns, não entendi realmente o que está acontecendo aqui. Encontrei problemas semelhantes em que o parâmetro tinha o tipo de dado errado e, portanto, o desempenho era ruim, mas isso não é um problema no meu caso. Também li sobre problemas com sniffing de parâmetros, mas também não acho que isso seja um problema aqui, pois não acesso dados por meio de procedimentos ou funções armazenados.

Além disso, quando estou acessando os dados diretamente da tabela com uma consulta parametrizada, o problema não ocorrerá. Uma busca de índice é feita mesmo com os parâmetros.

O mesmo acontece quando adiciono o OPTION (RECOMPILE)à consulta acessando a exibição com uma consulta parametrizada, o SQL Server acabará fazendo uma busca de índice.

Pergunta

Alguém pode explicar qual é o problema aqui? Como isso é um problema para a view, mas não para a tabela em si? Eu realmente preciso me livrar da view calculando isso row_numberde forma diferente durante inserções/exclusões?

Configurar

• SQL Server 2022 v16.0.4165 em execução em um contêiner docker
• Imagem do Docker: mcr.microsoft.com/mssql/server:2022-latest

A tabela real tem uma chave primária, é claro. Mas também tem muito mais colunas do que apenas a textcoluna. Incluir todas essas colunas no índice seria uma possibilidade. O problema, no entanto, não está ocorrendo ao selecionar da tabela em si, então não parece ser um problema do índice para mim.

Eu não sabia que estava executando o banco de dados em um modo de compatibilidade. No ambiente produtivo, estou até recebendo CardinalityEstimationModelVersion="140". Não acho que o tenha configurado em algum lugar de propósito.

Planos de execução

• seleção direta com busca de índice
• seleção parametrizada com varredura completa da tabela
• Verificação completa da tabela comQUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150