All perguntas(dba)

Eu uso um google could sql (mysql) como meu banco de dados de produção. Para replicar esse banco de dados para teste no docker, eu uso a imagem mysql:8.

Notei que um dos meus scripts de migração é bem-sucedido no banco de dados da nuvem, mas falha durante os testes. O script a seguir causa um erro:

CREATE TABLE testTable
(
    name varchar(1000)
);
CREATE INDEX idx_device_designs_name ON testTable (name);

O erro:Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071)...

Entendo o motivo do erro, mas como nosso banco de dados de produção padrão não o produz, estou procurando a configuração que desabilita essa verificação.

EDITAR 1:

SELECT VERSION();-> 8.0.31-google

SHOW CREATE TABLE tableName

produção

CREATE TABLE `testTable` (
  `name` varchar(1000) COLLATE utf8mb3_unicode_ci NOT NULL,
KEY `idx_device_designs_name` (`name`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

contêiner docker

CREATE TABLE `testTable` (
  `name` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

a produção teve o índice aplicado no passado (antes de uma atualização do mysql, então 5.7).

SHOW TABLE STATUS LIKE 'testTable'(em produção)

testTable,InnoDB,10,Dynamic,2,8192,16384,0,32768,0,3,2024-11-14 08:52:26,,,utf8mb3_unicode_ci,,"",""

Tenho esse procedimento armazenado que quero a base max startDate no AgentId ou sem o AgentId. A maneira como estou fazendo isso é usando um if else e queria ver se havia uma maneira melhor de fazer isso?

  IF (@AgentId = 0)
      BEGIN
          SELECT top (1)max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0
          group by ag.AgentId,ag.Name
      end

    ELSE
    BEGIN
          SELECT max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0 and ag.AgentId = @AgentId
          group by ag.AgentId,ag.Name
    end

O Postgresql 17 adicionou um novo recurso para fazer backups incrementais. Quero usar isso para fazer backups incrementais diários, no entanto, estou lutando para encontrar um bom método para automatização, pois o build in pg_basebackup --incrementalsempre precisa de um caminho para o último backup incremental. Fornecer o caminho é fácil para a operação normal, no entanto, não quero implementar o tratamento de erros para todos os casos em que algo pode dar errado.

Também dei uma olhada no barman, que tem suporte para backups incrementais em nível de bloco, porém não preciso emparelhá-lo com o arquivamento WAL (que não consegui desativar) e parece que ainda preciso acionar manualmente o backup de qualquer maneira. O pgBackRest parece não ter suporte algum para esses backups.

Talvez haja uma alternativa melhor do que usar o backup incremental integrado, mas ela parece atender quase perfeitamente às minhas necessidades:

• Grande banco de dados (>1 TB) e espaço de backup limitado
• Não é possível desligar o banco de dados
• Não há grande problema se um dia de dados for perdido

Existe alguma ferramenta que esteja faltando e que possa resolver meu problema?

aspas duplas são usadas no sql server e seu comportamento pode ser definido pela opção SET QUOTED_IDENTIFIER ON \ OFF

aspas duplas também são usadas no powershell .

SET QUOTED_IDENTIFIER deve estar ON quando você invocar métodos de tipo de dados XML.

esta é uma visão parcial de um procedimento armazenado que desejo implantar em vários servidores, no entanto, por causa do identificador entre aspas, ou aspas duplas, este caractere - " - não está funcionando no PowerShell, no código visual que uso para implantar.

DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
            BEGIN
                PRINT ' - Base URL needs updating'
            END 
            ELSE
            BEGIN
                PRINT ' - Base URL does not need updating'
            END;

            IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT ' - OPS Service URL needs updating'
                SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
            END 
            ELSE
            BEGIN
                PRINT ' - OPS Service URL does not need updating'
            END;

esta é a mensagem de erro:

"' in expression or statement.
At C:\sp_PosRestore.ps1:636 char:114
+ ... ServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
+                                                 ~~~~~~~~~~~~~~~~~~~~~
The splatting operator '@' cannot be used to reference variables in an expression. '@stored_OpsServiceURL' can be used only as an argument to a command. To reference variables in an expression use      
'$stored_OpsServiceURL'.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

O código do Powershell:

$SQLQuery = 
"
DECLARE @WebXConfigurationID INT,
                @stored_URL VARCHAR(2000), @cur_URL VARCHAR(2000), 
                @stored_OpsServiceURL VARCHAR(2000), @cur_OPSServiceURL  VARCHAR(2000),
                @cur_Properties XML;


            PRINT 'WebX Configuration: ' + CAST(@WebXConfigurationID AS VARCHAR(20));

            IF (ISNULL(@stored_URL, '') <> ISNULL(@cur_URL, ''))
            BEGIN
                PRINT ' - Base URL needs updating'
            END 
            ELSE
            BEGIN
                PRINT ' - Base URL does not need updating'
            END;

            IF (ISNULL(@stored_OpsServiceURL, '') <> ISNULL(@cur_OPSServiceURL, '') AND @cur_OPSServiceURL IS NOT NULL)
            BEGIN
                PRINT ' - OPS Service URL needs updating'
                SET @cur_Properties.modify('replace value of (/properties[1]//OPSServiceURL[1]/text())[1] with sql:variable("@stored_OpsServiceURL")')
            END 
            ELSE
            BEGIN
                PRINT ' - OPS Service URL does not need updating'
            END;
"

No momento, estou enfrentando problemas de espaço em disco com um servidor MySQL 5.7.32 que tem >500 logs binários de 1,1 GB cada.

Passei vários dias lendo sobre isso e descobri que esse PURGE BINARY LOGSé o comando certo a ser usado, mas não consegui encontrar nenhuma informação sobre o possível impacto no desempenho ou se o servidor MySQL trava durante o processo.

Verifiquei as variáveis ​​do servidor e descobri que: binlog_gtid_simple_recoverye gtid_modeestão definidas como ON e, até onde posso perceber, isso afetaria o desempenho de forma positiva.

Alguém pode me explicar o impacto no desempenho ou os bloqueios que posso esperar ao limpar logs em pequenos lotes?

Quando escrevi alguns comentários antes do CREATE PROCEDUREcomando, esses comentários parecem ser preservados como parte da definição do procedimento.

Por exemplo, quando eu crio um procedimento como este

/*Comment header*/
CREATE OR ALTER PROCEDURE example_procedure
AS 
SELECT 1 AS Id

O /*Comment header*/é armazenado como parte da definição.

Violino

Esse é um comportamento documentado, posso sempre contar com isso? Ou é apenas uma peculiaridade não confiável do SQL Server?

Meu entendimento ingênuo é que a definição do procedimento começa com CREATEpalavra-chave, então hesito em confiar em comportamento que vai contra minha intuição.

Estamos construindo um grupo de disponibilidade multi-subnet, always-on. Inicialmente, solicitei os IPs abaixo.

Para cluster de failover

192.168.1.129

192.168.2.129

Para ouvinte

192.168.1.130

192.168.2.130

Minha equipe de rede forneceu os IPs abaixo.

Para cluster de failover

192.168.1.129

192.168.2.130

Para ouvinte

192.168.1.131

192.168.2.132

No meu entendimento, os IPs acima devem funcionar, pois pertencem a sub-redes diferentes, mas quero ter 100% de certeza de que os IPs não precisam ser idênticos, exceto pela sub-rede. Contanto que os IPs pertençam a sub-redes separadas, deve ficar tudo bem.

Estou procurando adicionar criptografia ao meu cluster Cassandra para melhor segurança de dados. Aqui estão as especificações da minha configuração e os problemas que encontrei:

Detalhes do cluster: 8 nós, cada um com TBs de dados.

Tentativas Feitas: Tentou adicionar volumes EBS criptografados e alterar o caminho dos dados, mas isso criou um atraso significativo no desempenho em todo o cluster. Adicionou novos nós configurados com criptografia (JKS para SSTable e criptografia internode), mas causou grandes interrupções e desestabilizou todo o cluster.

Desafios enfrentados: Grande atraso ao migrar para volumes EBS criptografados. Instabilidade do cluster ao introduzir novos nós com criptografia.

Pergunta: Qual é a abordagem recomendada para habilitar a criptografia (dados em repouso) em um cluster Cassandra com tempo de inatividade mínimo e sem afetar severamente o desempenho?

Quaisquer insights ou melhores práticas seriam muito apreciados!

Estou usando o PostgreSQL 17

Estou modelando um índice de pacote para o ecossistema Haskell, e um recurso útil é determinar dependências transitivas. Pacotes Haskell podem ser normalizados como:

Package 
(name + package-specific metadata)
 \-> Releases 
    (version + release-specific metadata like synopsis, attached data files)
   \-> Components 
      (library, executable, test suite, benchmark suite)
     \-> Dependencies 
        (Each component declares a dependency of a package name and version expression).

(Cada uma dessas seções é uma tabela e elas são vinculadas por relacionamentos um-para-muitos. Um pacote vincula-se a muitas versões, cada versão vincula-se a muitos componentes, cada componente vincula-se a muitas dependências)

Para o propósito do meu próprio esclarecimento, primeiro reduzi a complexidade do modelo para criar um CTE que faz o que espero. Especialmente, não uso bigints como PKs da tabela na minha base de código, mas UUIDs.

(O dbfiddle completo está disponível em https://dbfiddle.uk/hVOmMdYQ )

-- Data model where packages and versions are combined, 
-- and dependencies refer to packages

create table packages (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null,
  version int[] not null
);

create unique index on packages(name, version);

create table dependencies (
  dependency_id bigint generated always as identity PRIMARY KEY,
  dependent_id bigint references packages(package_id),
  depended_id bigint references packages(package_id) 
);

create unique index on dependencies(dependent_id, depended_id);

E aqui estão os dados:

insert into packages (name, version) values ('base', '{1,0,0,0}');
insert into packages (name, version) values ('vector', '{0,0,7,0}');
insert into packages (name, version) values ('random', '{0,1,5,8}');
insert into packages (name, version) values ('unix', '{1,2,1,0}');
insert into packages (name, version) values ('time', '{3,14,1,2}');

insert into dependencies (dependent_id, depended_id) values (2, 1);
insert into dependencies (dependent_id, depended_id) values (3, 1);
insert into dependencies (dependent_id, depended_id) values (3, 2);
insert into dependencies (dependent_id, depended_id) values (4, 1);
insert into dependencies (dependent_id, depended_id) values (5, 1);
insert into dependencies (dependent_id, depended_id) values (5, 3);
insert into dependencies (dependent_id, depended_id) values (5, 4);

Aqui está um resultado preliminar:

select dependent.package_id, dependent.name as dependent, depended.name as depended
  from dependencies as d1
  inner join packages as dependent on d1.dependent_id = dependent.package_id
  inner join packages as depended  on d1.depended_id = depended.package_id;

Até agora, tudo parece bom. Então, fiz este CTE recursivo para criar uma visão de dependências transitivas, com breadcrumbs:

with recursive transitive_dependencies ( dependent_id, dependent, depended_id, breadcrumbs) as 
( select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', dependent.name, depended.name) as breadcrumbs
  from dependencies as d1
    inner join packages as dependent on d1.dependent_id = dependent.package_id
    inner join packages as depended  on d1.depended_id = depended.package_id
    where dependent_id = 5

  union all
  
  select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', t2.breadcrumbs, depended.name) as breadcrumbs   

  from dependencies as d1
     inner join packages as dependent on d1.dependent_id = dependent.package_id
     inner join packages as depended  on d1.depended_id = depended.package_id
     inner join transitive_dependencies as t2 on t2.depended_id = dependent.package_id -- ← This is where we refer to the CTE
)

  cycle dependent_id set is_cycle using path
 
select t3.dependent_id
     , t3.dependent
     , t3.depended_id
     , t3.breadcrumbs
from transitive_dependencies as t3;

Vejam só, funciona!

Agora, estou pensando em dividir as coisas um pouco mais. Ou seja, pacote e release serão separados. Isso se deve ao fato de que há alguns metadados específicos para o ecossistema Haskell que têm como alvo a noção de "pacote" e alguns que são relevantes apenas para "releases", e eles não são intercambiáveis.

-- Data model where packages and releases are separated

create table packages2 (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null
);

create table releases2 (
  release_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  package_id bigint references packages2,
  version text not null
);

create unique index on releases2(package_id, version);

create table dependencies2 (
  dependency_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  release_id bigint references releases2 not null,
  package_id bigint references packages2 not null,
  requirement int[] not null
);

E aqui estão os dados

insert into packages2 (name) values ('base'); -- 1
insert into packages2 (name) values ('vector'); -- 2
insert into packages2 (name) values ('random'); -- 3
insert into packages2 (name) values ('unix'); -- 4
insert into packages2 (name) values ('time'); -- 5

insert into releases2 (package_id, version) values (1, '{1,0,0,0}');
insert into releases2 (package_id, version) values (2, '{0,0,7,0}');
insert into releases2 (package_id, version) values (3, '{0,1,5,8}');
insert into releases2 (package_id, version) values (4, '{1,2,1,0}');
insert into releases2 (package_id, version) values (5, '{3,14,1,2}');


insert into dependencies2 (release_id, package_id, requirement) values ( 2, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 2, '>= 0.0.7.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 4, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 3, '<= 0.1.5.8' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 4, '== 1.2.1.0' );

E tentei aplicar a lição do CTE acima a este esquema:

with recursive transitive_dependencies2 ( dependent_id, dependent, dependency_id, breadcrumbs) as
(select p2.package_id as dependent_id
       , p2.name as dependent
       , p3.package_id as dependency_id
       , concat_ws(' > ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
  where r1.release_id = 5

  union

  select p2.package_id as dependent_id
       , p2.name as dependent
       , p3.package_id as dependency_id
       , concat_ws(' > ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
    inner join transitive_dependencies2 as t2 on t2.dependency_id = p2.package_id ← This is where we refer to the CTE
)

    cycle dependent_id set is_cycle using path

select t3.dependent_id
     , t3.dependent
     , t3.dependency_id
     , t3.breadcrumbs
from transitive_dependencies2 as t3;

Infelizmente, isso não dá o resultado esperado:

Minha pergunta é a seguinte: Como posso construir minha intuição para dividir ainda mais um CTE que funcione, em tabelas mais granulares? Ainda sou muito novo em tudo isso, e este é meu primeiro caso de uso "real" de CTEs.

Ficarei feliz em esclarecer ou esclarecer coisas.

Por meio disso, também estou interessado nas melhores práticas quando se trata de modelagem de dados. Fui avisado no passado contra o armazenamento de arrays de chaves estrangeiras, por exemplo, e para me esforçar e atingir formas normais e dividir entidades que têm ciclos de vida diferentes.

Estou trabalhando com um arquivo de dados primário de 2,3 TB e atualmente tenho cerca de 1 TB de espaço não utilizado. Recentemente, realizei compactação em nível de linha na maior tabela do banco de dados, o que reduziu o tamanho da tabela de 0,9 TB para 0,4 TB. No entanto, após essa compactação, o tamanho do arquivo cresceu e, embora o espaço usado pela tabela tenha diminuído, o espaço geral não utilizado no arquivo não diminuiu de acordo.

Meu problema é como recuperar esse espaço não utilizado, considerando que este é um banco de dados baseado em relatórios sem modelagem, o que significa que não há chaves primárias ou índices agrupados.

Meu teste inicial foi executar dbcc shrinkfile(<file_name>, TRUNCATEONLY), mas nenhum espaço não alocado foi encontrado no final da página. A próxima coisa que eu tentaria seria voltar para a tabela massiva e criar um índice de cluster com a esperança de mover o espaço não alocado da operação de compactação para o final do arquivo, mas não há espaço não alocado naquela tabela. Além disso, há apenas cerca de 7 GB de espaço não alocado nas tabelas.

Isto é o que eu descobri depois de verificar o uso de espaço do arquivo: Tamanho do arquivo (GB): 2.287 Espaço usado (GB): 1.311 Espaço não alocado (GB): 976. Aqui estão os resultados da execução de exec sp_spaceused: Tamanho do banco de dados: 2.372.346,56 MB Espaço não alocado: 999.150,95 MB Reservado: 1.375.788.088 KB Dados: 1.162.793.432 KB Tamanho do índice: 204.355.584 KB Não utilizado: 8.639.072 KB.

Não sou administrador de banco de dados, então não tenho ideia do que poderia fazer. Alguém tem alguma sugestão?