All perguntas(dba)

Introdução

Recentemente, durante uma atualização de um aplicativo, recebi a seguinte mensagem de erro:

Msg 5074 Level 16 State 1 Line 1
The statistics 'BreakingStuff' is dependent on column 'TaskText'.
Msg 4922 Level 16 State 9 Line 1
ALTER TABLE ALTER COLUMN TaskText failed because one or more objects access this column.

^{Exemplo de mensagem de erro baseada em análise}

Esta foi uma estatística criada manualmente em uma coluna de uma tabela. Na verdade, havia várias estatísticas criadas manualmente referenciando a coluna em questão.

Gambiarra

Depois de descartar as estatísticas da tabela, executar a atualização e recriar as estatísticas, tudo estava funcionando bem.

Análise

Comecei com uma simulação trivial em db<>fiddle para ver se conseguia reproduzir o problema. Depois de alguns ajustes (trocadilhos), descobri que a mensagem de erro só é acionada se eu reduzir o tamanho da coluna.

por exemplo varchar(50)-->varchar (40)

Aqui está o db<>fiddle .

Os únicos avisos que encontrei no Microsoft Learn foram:

1. Modificar o tipo de dados de uma coluna que já contém dados pode resultar na perda permanente de dados quando os dados existentes são convertidos para o novo tipo. Além disso, o código e os aplicativos que dependem da coluna modificada podem falhar. Isso inclui consultas, visualizações, procedimentos armazenados, funções definidas pelo usuário e aplicativos cliente. Observe que essas falhas ocorrerão em cascata. Por exemplo, um procedimento armazenado que chama uma função definida pelo usuário que depende da coluna modificada pode falhar. Considere cuidadosamente quaisquer alterações que você deseja fazer em uma coluna antes de fazê-la.

   ^{Referência: Modificar colunas (Microsoft Learn | SQL)}

Eu estava ciente de que reduzir o tamanho da coluna possivelmente resultaria em perda de dados, mas não foi o caso, pois os dados eram todos menores que varchar(40).

Questões

1. Por que reduzir o tamanho da coluna resulta em erro devido às estatísticas criadas manualmente?
2. Por que aumentar o tamanho de uma coluna não resulta em erro nas estatísticas criadas manualmente?

Tenho um banco de dados sqlite que cresceu para 30 GB e ainda estou enviando dados para ele todos os dias. Tenho alguns serviços que gravam nele usando libsqlite3. Em breve, ele ficará grande demais para ser mantido localmente. Qual é a melhor maneira de migrar para uma configuração distribuída? Uma maneira que posso pensar é sshfs, mas isso não funcionará se minha máquina estiver offline. Uma solução que armazena em cache as partes mais recentemente usadas do meu banco de dados localmente e mantém o restante em outro lugar seria perfeita. Na maioria das vezes, usarei dados enviados recentemente. Eu poderia começar a arquivar dados de anos atrás, mas acessá-los exigiria trabalho manual.

Estou lidando com um problema que não consigo resolver. Recebi esse procedimento armazenado que apresenta problemas de velocidade. Demora cerca de 35 segundos. Quando executado adhoc , ele é concluído instantaneamente.

Isso me fez pensar que tinha algo a ver com detecção de parâmetros. Eu tentei reescrever isso para usar variáveis ​​locais, otimizar para desconhecido\variável\etc, com recompilação.

A tabela em que a verificação está acontecendo é enorme, mas mesmo com as estatísticas atualizadas com fullscan, as estimativas ainda estão MUITO erradas. Talvez a consulta pudesse ser reescrita, mas eu gostaria de descobrir isso.

todos os dados do ambiente de teste com nomes alterados de arquivos de texto .sql para consulta sp e ad hoc - deixe-me saber se isso não funcionar

plano ad hoc - https://www.brentozar.com/pastetheplan/?id=Hkvg5Ge40

Parâmetros para ad hoc -

Plano SP - https://www.brentozar.com/pastetheplan/?id=S1w49GxER

Parâmetros para SP -

Os parâmetros vêm como:

(sp name)
    @ TransactionDescriptionId VARCHAR(32),
    @ CategoryCode VARCHAR(4000)
AS

DECLARE
    @ CategoryHash VARBINARY(16) = HashBytes('MD5', @CategoryCode);

Os mesmos valores são passados ​​tanto na versão SP quanto na versão ad hoc . Achei que poderia ser devido a algo com o hash sendo criado como uma variável local a partir do parâmetro passado, mas na versão ad hoc está fazendo o mesmo (para mim).

Recentemente atualizei minha instância do Amazon RDS de db.t2.micropara db.t3.small. Após a atualização, também ativei o performance_schemaparâmetro no grupo de parâmetros associado porque li em algum lugar que essa configuração pode ajudar a ativar o Performance Insights. Porém, a opção de configurar o Performance Insights não aparece quando vou para a aba “Modificar” da minha instância de banco de dados.

O banco de dados está rodando no MySQL 8.0.35 e aqui está um resumo do que fiz até agora:

• Atualizado de db.t2.micro para db.t3.small.
• Ativado performance_schema definindo-o como 1 no grupo de parâmetros.
• Verificou se performance_schema está ativo verificando a configuração atual.
• O monitoramento aprimorado também já está ativado.

Apesar dessas etapas, quando verifico as configurações ele mostra Performance Insights turned offmas ainda falta a opção de habilitar ou configurar o Performance Insights na aba "Modificar".

Para referência, encontrei um vídeo do YouTube ( https://www.youtube.com/watch?v=92UNEOcBW_g ) onde alguém usa uma instância db.t3.small semelhante (mas com PostgreSQL) e habilita e acessa o Performance Insights com sucesso. Isso me faz pensar se pode haver configurações ou etapas específicas que estão faltando na minha configuração.

Além disso, li informações conflitantes sobre a disponibilidade do Performance Insights. Algumas fontes sugerem que ele só está disponível a partir de db.t3.medium, enquanto outras indicam que deveria estar disponível em db.t3.small.

Obs: quando entro na aba de monitoramento do banco de dados, a própria AWS sugere habilitar o Performance Insights.

Quaisquer insights ou sugestões sobre como resolver esse problema serão muito apreciados.

Eu desejo ...

1. ... remova espaços
2. ... excluir apóstrofos
3. ... substitua 'é' e 'è' por 'e'

Eu uso a função regexp_replace().

No momento, posso deletar os espaços, mas mal. Na verdade, quando o atributo contém vários espaços, apenas um é eliminado.

Não consigo processar 1. , 2. e 3. ao mesmo tempo. Isso é possível?

Abaixo está um link para o meu código:

https://dbfiddle.uk/22ODtpNS

Gostaria de me conectar do meu SQL Server 2017 a um banco de dados Oracle por meio do servidor vinculado. Instalei o driver Oracle do Oracle-Client-for-Microsoft-Tools-64-bit.exe, mas o driver não aparece na lista de provedores em Servidor vinculado. Reiniciei o servidor, mas ainda sem ajuda. o que estou perdendo? Instalei apenas o driver Oracle do pacote de instalação do Oracle Client porque achei que não precisava das outras partes.

Eu estava olhando uma consulta de definição de tabela do WordPress e percebi algo estranho. Em primeiro lugar, a consulta era esta:

CREATE TABLE $wpdb->comments (
    comment_ID bigint(20) unsigned NOT NULL auto_increment,
    comment_post_ID bigint(20) unsigned NOT NULL default '0',
    comment_author tinytext NOT NULL,
    comment_author_email varchar(100) NOT NULL default '',
    comment_author_url varchar(200) NOT NULL default '',
    comment_author_IP varchar(100) NOT NULL default '',
    comment_date datetime NOT NULL default '0000-00-00 00:00:00',
    comment_date_gmt datetime NOT NULL default '0000-00-00 00:00:00',
    comment_content text NOT NULL,
    comment_karma int(11) NOT NULL default '0',
    comment_approved varchar(20) NOT NULL default '1',
    comment_agent varchar(255) NOT NULL default '',
    comment_type varchar(20) NOT NULL default 'comment',
    comment_parent bigint(20) unsigned NOT NULL default '0',
    user_id bigint(20) unsigned NOT NULL default '0',
    PRIMARY KEY  (comment_ID),
    KEY comment_post_ID (comment_post_ID),
    KEY comment_approved_date_gmt (comment_approved,comment_date_gmt),
    KEY comment_date_gmt (comment_date_gmt),
    KEY comment_parent (comment_parent),
    KEY comment_author_email (comment_author_email(10))
) $charset_collate;

Como você pode ver, comment_author_emailfoi definido como varchar(100), mas sua chave foi definida com comprimento de 10 caracteres. Por que é que? É para economizar espaço, já que a maioria dos e-mails tem entre 10 e 15 caracteres?

Nesse caso, isso significa que - para um e-mail com 15 caracteres - os primeiros 10 caracteres serão indexados e os 5 caracteres restantes não serão indexados? É assim que funciona? (Não sou especialista em banco de dados, como você adivinhou).

Por fim, adicionarei uma tabela própria para um mecanismo que estou construindo, que precisará armazenar endereços de e-mail e números de telefone. Para os endereços de e-mail usarei uma definição semelhante. Quanto ao telefone, estou pensando em defini-lo como varchar(25) só por segurança, mas para seu índice, use novamente uma chave com 10 caracteres de comprimento. O que você acha desse planejamento?

Temos algum código que passa por um gerador SQL para produzir cláusulas IN parametrizadas no formato WHERE id IN (?, ?, ?, ...) AND ....

Se a lista estiver vazia, o gerador não poderá emitir, IN ()pois é um SQL inválido. Em vez disso, trata-se WHERE id IN (SELECT NULL WHERE FALSE)de fornecer a mesma semântica.

Eu esperaria que o MYSQL pudesse reduzir isso para WHERE FALSE. Curiosamente, porém, ele não consegue fazer isso e produz uma varredura completa da tabela!

Consulta de exemplo (mytable é uma tabela de teste simples que criei com ID de 50 mil linhas como PK)

select *
from mytable
where id in (select null where false);

Plano:

Curiosamente, se eu substituir a subconsulta por uma que atinja uma tabela real, obtenho um plano muito melhor:

Consulta:

select *
from mytable
where id in (select null from information_schema.tables where false);

Plano:

Minha pergunta é: há alguma razão pela qual faz sentido para o MySQL se comportar dessa maneira (por exemplo, alguma semântica específica da primeira consulta?) Este é um bug conhecido?

ATUALIZAÇÃO : Eu registrei isso no MySQL e eles reconheceram a estranheza e aceitaram como uma solicitação de recurso .

Sou um jovem desenvolvedor web de Marrocos, para resumir a história, estou trabalhando em um projeto especial de mídia social com o qual aprendi codificação, mas ele se desenvolveu em um projeto maior com o tempo. cheguei a um ponto em que quero criar ferramentas analíticas adequadas para minhas postagens

então esta é a postagem com o slide de classificação de estrelas que estou armazenando em um banco de dados sql com (user_id, post_id, estrelas), mas queria saber se preciso contar () toda vez que um usuário busca a postagem? ou existe um método mais eficiente para exibir post analytics porque li muito mas não encontrei solução para o meu caso e não tenho experiência corporativa para resolver soluções dessa escala. toda vez que solicito a postagem tenho que contar a soma das estrelas e o número de eleitores e depois faço (estrelas/eleitores) para obter a média e não sei se é uma boa ideia contar sempre que a postagem é solicitada, mas não tenho certeza de como fazer isso de outra maneira.

• tenho outro lugar onde preciso contar visualizações e também não sei como calculá-las de forma mais eficiente. estou aberto a todas as sugestões

Estou executando a seguinte consulta no Postgres 15 com a extensão Timescale em uma tabela de alertas para obter o alerta mais recente para um nome de usuário.

EXPLAIN ANALYZE
SELECT *
FROM alerts_alerts
WHERE username IN ('<username_here>')
ORDER BY timestamp DESC
LIMIT 1

Para a maioria dos nomes de usuário, a consulta é executada rapidamente, em menos de 150 ms. No entanto, para alguns nomes de usuário, leva mais tempo. Quase todas as bases de dados têm aproximadamente o mesmo número de alertas, cerca de 450, e a maioria delas tem dados bastante recentes, todos dos últimos 6 meses.

Aqui está o Explain Analyzenome de usuário problemático:

"Limit  (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.346..36129.370 rows=1 loops=1)"
"  ->  Custom Scan (ChunkAppend) on alerts_alerts  (cost=0.29..2262.68 rows=1 width=86) (actual time=36129.344..36129.368 rows=1 loops=1)"
"        Order: alerts_alerts.""timestamp"" DESC"
"        ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_234_chunk  (cost=0.29..2262.68 rows=1 width=89) (actual time=5.795..5.796 rows=0 loops=1)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"              Rows Removed by Filter: 30506"
"        ->  Index Scan using _hyper_1_233_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_233_chunk  (cost=0.29..4337.82 rows=1 width=91) (actual time=11.112..11.112 rows=0 loops=1)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"              Rows Removed by Filter: 59534"
            [   ...     Cut redundant log lines here    ...    ]
"        ->  Index Scan using _hyper_1_156_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_156_chunk  (cost=0.42..11418.54 rows=2591 width=80) (never executed)"
"              Filter: ((username)::text = 'username_long_query'::text)"
"        ->  Index Scan using _hyper_1_155_chunk_alerts_alerts_timestamp_idx_1 on _hyper_1_155_chunk  (cost=0.29..7353.95 rows=749 width=84) (never executed)"
"              Filter: ((username)::text = 'username_long_query'::text)"
            [   ...     Cut redundant log lines here    ...    ]
"Planning Time: 13.154 ms"
"Execution Time: 36129.923 ms"

Agora, isto é Explain Analyzepara os nomes de usuário que são executados rapidamente:

"Limit  (cost=471.73..471.73 rows=1 width=458) (actual time=1.672..1.691 rows=1 loops=1)"
"  ->  Sort  (cost=471.73..472.76 rows=414 width=458) (actual time=1.671..1.689 rows=1 loops=1)"
"        Sort Key: _hyper_1_234_chunk.""timestamp"" DESC"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        ->  Append  (cost=0.29..469.66 rows=414 width=457) (actual time=1.585..1.654 rows=210 loops=1)"
"              ->  Index Scan using _hyper_1_234_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_234_chunk  (cost=0.29..2.49 rows=1 width=372) (actual time=0.006..0.007 rows=0 loops=1)"
"                    Index Cond: ((username)::text = 'username_value'::text)"
"              ->  Index Scan using _hyper_1_233_chunk_alerts_alerts_fleet_a3933a38_1 on _hyper_1_233_chunk  (cost=0.29..2.37 rows=1 width=385) (actual time=0.006..0.006 rows=0 loops=1)"
"                    Index Cond: ((username)::text = 'username_value'::text)"
            [   ...     Cut redundant log lines here    ...    ]
"              ->  Seq Scan on _hyper_1_83_chunk  (cost=0.00..1.12 rows=1 width=504) (actual time=0.013..0.013 rows=0 loops=1)"
"                    Filter: ((username)::text = 'username_value'::text)"
"                    Rows Removed by Filter: 10"
"              ->  Seq Scan on _hyper_1_81_chunk  (cost=0.00..1.12 rows=1 width=504) (actual time=0.009..0.009 rows=0 loops=1)"
"                    Filter: ((username)::text = 'username_value'::text)"
"                    Rows Removed by Filter: 10"
"Planning Time: 899.811 ms"
"Execution Time: 2.613 ms"

Pesquisas preliminares sugerem fazer manutenção na tabela do banco de dados. Após executar o comando de vácuo, as consultas foram executadas novamente, mas os resultados não foram alterados.

Ressalta-se também que existem outros nomes de usuário que utilizam o planejamento “problemático”, mas o tempo de execução ainda é rápido.

Não sei como resolver essa discrepância no tempo de execução da consulta. Poderia ser útil adicionar outro índice, mas como sou novo no PostgreSQL, atualmente não tenho certeza sobre a melhor abordagem para isso.