All perguntas(dba)

Estou tentando adicionar a capacidade de um usuário ser autorizado com uma determinada função em um contrato/site/experimento.

Mais especificamente, quero que um usuário tenha a função A em um contrato, mas ao mesmo tempo a função B em um site específico desse mesmo contrato e possivelmente uma função C em um experimento desse site.

Minha primeira ideia foi uma tabela de junção entre Usere Rolecom uma terceira coluna armazenando o id de um contrato, site ou experimento.

Esta solução tem muitos problemas na minha opinião:

• Preciso de uma linha para o contrato, cada site autorizado e cada experimento autorizado. Pode crescer muito muito rápido.
• Nenhuma integridade. Um contrato inexistente pode ser referenciado
• Se eu quiser saber todos os sites e experimentos de um contrato onde um usuário está autorizado, primeiro tenho que consultar todos os sites desse contrato, depois todos os experimentos desses sites e então encontrar todas as linhas que fazem referência a esses IDs. Parece um pouco hackeado.

Sinto que essa solução pode funcionar, mas eu estava pensando se havia outra maneira? Talvez algum padrão que eu não conheça?

PS: Estou usando MySQL v8

Usei o guia de particionamento e indexação de Rick James para criar isso alguns anos atrás, mas agora, depois de ler o tutorial novamente, não tenho tanta certeza se entendi corretamente e se a tabela abaixo é a ideal. ( referência do guia )

Acho que um dos índices normais é redundante e não tenho certeza se devo deixar de fora a coluna de chave de particionamento (data/hora) ou alterar um dos índices para ter a coluna de chave de particionamento (data/hora) por último. Tê-lo como parte do índice ajudará no desempenho ao selecionar um intervalo de data/hora específico dentro de uma partição?

Também seria melhor ter a chave primária (deviceService, datetime) e ter um índice no incremento automático (id)?

A tabela baseada em séries temporais abaixo tem cerca de 80+ milhões de linhas por partição mensal. Ela é quase sempre consultada para um intervalo de data/hora específico e para um subconjunto de deviceServiceId's.

O particionamento é puramente para arquivamento e manutenção de tabelas e não para desempenho.

CREATE TABLE `serviceResultLinkState` 
(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deviceServiceId` int(10) unsigned NOT NULL,
  `dateTime` datetime NOT NULL DEFAULT current_timestamp(),
  `priority` tinyint(1) unsigned NOT NULL,
  `alias` varchar(20) NOT NULL,
  `active` tinyint(3) unsigned DEFAULT NULL,
  `stable` tinyint(3) unsigned DEFAULT NULL,
  `attempts` int(5) unsigned DEFAULT NULL,
  `retries` int(5) unsigned DEFAULT NULL,
  `resultState` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`dateTime`),
  KEY `dt_ds_idx` (`deviceServiceId`),
  KEY `datetime_idx` (`dateTime`,`deviceServiceId`)
) ENGINE=InnoDB AUTO_INCREMENT=24814201874 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE  COLUMNS(`dateTime`)
(PARTITION `p_202405` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202406` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202407` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202408` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202409` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202410` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202411` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_max` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

alteramos recentemente o servidor de arquivos que é o servidor do repositório (o local onde os backups são armazenados).

Notei que o hardware, especialmente o IO, é muito melhor e o backup demora menos para terminar, dependendo de muitos fatores. No entanto, as restaurações ainda demoram o mesmo tempo de antes.

esta não é uma pergunta complexa, tenho apenas uma pergunta aqui:

o que é physical_block_size?

quando você executa a seguinte consulta no msdbbanco de dados:

SELECT * 
  FROM msdb.dbo.backupmediafamily BMF WITH(NOLOCK)
 order by 1 desc

este é o resultado da consulta acima e observe como o physical_block_sizevalor mudou de 4096 para 512 exatamente quando decidi usar nosso novo servidor mencionado acima para o destino dos backups:

Quero dizer, dada a entrada

NAME location
ANNA USA
Alica USA
Lisa CA
Arch CA
Ketty Germany
...

Eu quero resultado

USA   CA    Germany ...
Anna  Lisa  Ketty ...
Alica Arch  

É possível fazer isso com Mysql sem extensões e vars?

Ao executar sob READ COMMITTED(o tipo pessimista, não RCSI), acabei de ver um UPDATEdeadlock de bloqueio IX de 's com um SELECTbloqueio compartilhado de 's no PAGEnível -. Eles SELECTexecutaram com um plano de execução paralelo. Nenhum deles usou explicitamente uma transação. Entendo que UPDATEs implicitamente criam transações. Isso sugere que SELECTs paralelos também criam transações implícitas? Isso não é verdade para s não paralelos SELECT?

Estou achando isso um tanto confuso, porque nenhum dos exemplos de deadlocks nos livros didáticos mostra duas consultas sem transação gerando deadlocks.

Estou procurando ajuda para projeto de particionamento de tabelas para implementar no UAT e depois no PROD.

O processo atual é do SP inserindo dados da tabela de preparação para a tabela principal e atualizando a tabela com lógica de negócios e excluindo dados com mais de 90 dias. A tabela principal é uma tabela de 120 GB com 100 milhões de dados, a inserção, atualização e exclusão leva mais tempo, cerca de 1 hora para concluir o processo. Aqui eu postei --> Sugestão de lentidão do DML SP para carregamento de dados ETL

Este processo está sendo executado diariamente 4 vezes. Estou tentando testar e automatizar este processo com particionamento de tabela. O problema é que os dados são carregados diariamente 4 vezes e às vezes também executamos lotes extras com base na necessidade. Não consegui usar o particionamento diário ou mensal, pois não consigo alternar com os dados existentes. Acredito que preciso usar o particionamento por carga com base na data máxima atual. Alguém pode ajudar?

Não temos nenhum índice diferente da chave primária como índice clusterizado. Aqui está o teste e a amostra que testei.

Criação de tabela:

--select top 10 * from [C1810429].[STYTOTAL_RAW]

use master
go
alter database DB_Partition set single_user with rollback immediate
drop database DB_Partition
create database DB_Partition
go
use master
go

go
use DB_Partition
go
create schema [C1810429]

go
--*/
use DB_Partition
go

---1
--drop table [C1810429].[STYTOTAL_RAW]
CREATE TABLE [C1810429].[STYTOTAL_RAW](
    [STYTOTALID] [int] IDENTITY(1,1) NOT NULL,
    [STYLE] [decimal](5, 0) NOT NULL,
    [InsertedDateTime] [datetime2](7) NOT NULL,
    [UpdatedDateTime] [datetime2](7) NULL,
    [IsCurrent] [bit] NULL,
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL,
    --/*
 CONSTRAINT [PK_C1810429_STYTOTAL_RAW] PRIMARY KEY CLUSTERED 
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)  
--*/
)

--*/


--drop table [C1810429].[DLSTYTOTAL]
CREATE TABLE [C1810429].[DLSTYTOTAL](
    [STYLE] [decimal](5, 0) NULL,   
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL
)

Inserir amostra:

use DB_Partition

SET NOCOUNT ON
DECLARE @DateTime DATETIME2(7) = '2024-05-01 00:00:00.000'

WHILE @DateTime <= '2024-10-18 23:59:59.999'
BEGIN

 INSERT INTO [C1810429].[STYTOTAL_RAW] ([STYLE],[InsertedDateTime])

 SELECT  ABS(CHECKSUM(NEWID())%8)+1,@DateTime

 SET @DateTime = DATEADD(Minute, 1, @DateTime)
 

END
GO

Próxima Inserção / Amostra de lote:

/*
use DB_Partition
-- populate further test data
SET NOCOUNT ON
DECLARE @DateTime DATETIME2(7) = '20240701'
    
WHILE @DateTime <= '20241016'
BEGIN
    
     insert into [C1810429].[DLSTYTOTAL] ([STYLE])
    
     SELECT  ABS(CHECKSUM(NEWID())%8)+1
    
     SET @DateTime = DATEADD(Minute, 1, @DateTime)
     
END
GO
*/
INSERT INTO  [C1810429].[STYTOTAL_RAW] ([STYLE],[DELDATTIM],[DELFLAG],[InsertedDateTime],[IsCurrent])     

SELECT STG.[STYLE],STG.[DELDATTIM],STG.[DELFLAG],DATEADD(MICROSECOND, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), SYSDATETIME()) as [InsertedDateTime],1   as [IsCurrent]  
FROM [C1810429].[DLSTYTOTAL] STG Left Join [C1810429].[STYTOTAL_RAW] TGT  
on STG.STYLE = TGT.STYLE AND STG.DELDATTIM = TGT.DELDATTIM
Where TGT.DELDATTIM is null

Atualizar e Excluir:

--update
UPDATE [C1810429].[STYTOTAL_RAW]
SET ISCURRENT = 0,UpdatedDateTime = SYSDATETIME()                                      
WHERE IsCurrent = 1 

--delete
delete FROM [C1810429].[STYTOTAL_RAW] 
WHERE InsertedDateTime < DATEADD(DAY, -90, SYSDATETIME()) and IsCurrent = 0 

Obrigado pela ajuda.

Testei esta partição mensal:

use DB_Partition
go
--drop PARTITION FUNCTION PF_myDateRange

CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES 
(
'20240601',
'20240701',
'20240801',
'20240901',
'20241001',
'20241101'
)
GO
-- not sure I need to time as well in the function
CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES 
(
'2024-06-01 23:59:59.997',
'2024-07-01 23:59:59.997',
'2024-08-01 23:59:59.997',
'2024-09-01 23:59:59.997',
'2024-10-01 23:59:59.997',
'2024-11-01 23:59:59.997'
)
GO
CREATE PARTITION SCHEME PS_myPartitionScheme AS PARTITION PF_myDateRange ALL TO ([PRIMARY]);

-- drop and create index to make existing table into partition data alignment

CREATE UNIQUE CLUSTERED INDEX [PK_C1810429_STYTOTAL_RAW] ON [C1810429].[STYTOTAL_RAW]
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
) 
WITH (DROP_EXISTING=ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON PS_myPartitionScheme([InsertedDateTime]);

Editado: Tentei automatizar usando tempo máximo e +1 segundo etc., mas os dados, ao alternar, vão para limites de partição diferentes quando carrego 2 e 3 etc.

---1
--drop table [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
CREATE TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging(
    [STYTOTALID] [int] IDENTITY(1,1) NOT NULL,
    [STYLE] [decimal](5, 0) NOT NULL,
    [InsertedDateTime] [datetime2](7) NOT NULL,
    [UpdatedDateTime] [datetime2](7) NULL,
    [IsCurrent] [bit] NULL,
    [DELDATTIM] [datetime2](7) NULL,
    [DELFLAG] [char](1) NULL,
    --/*
 CONSTRAINT [PK_C1810429_STYTOTAL_RAW_Intermediate_Staging] PRIMARY KEY CLUSTERED 
(
    [STYTOTALID] ASC,
    [STYLE] ASC,
    [InsertedDateTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)  

) on [Primary]--on PS_myPartitionScheme([InsertedDateTime]);

----

use DB_Partition
--truncate table C1810429.STYTOTAL_RAW_Intermediate_Staging
select * from C1810429.STYTOTAL_RAW_Intermediate_Staging
--00,06,12,18,23:59.59

-- load 1
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 00:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 01:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 11:59:59.999')

-- load 2
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 12:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 13:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 17:59:59.999')

-- load 3
select max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW]
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 18:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 19:00:00.000')
insert into C1810429.STYTOTAL_RAW_Intermediate_Staging (STYLE,InsertedDateTime) values (1,'2024-10-19 23:59:59.999')

--create new partitions for new data load use the primary FG
--get the staging table max date and add one minute and create new boundary
select max([InsertedDateTime]) from [C1810429].STYTOTAL_RAW_Intermediate_Staging

Declare @New_PartitionBoundaryDate_Plus datetime2 (7)
select @New_PartitionBoundaryDate_Plus = max (DATEADD(minute,+1,[InsertedDateTime])) from [C1810429].STYTOTAL_RAW_Intermediate_Staging
select @New_PartitionBoundaryDate_Plus as [Plus]

--load 1  max([InsertedDateTime])
7   25920   2024-10-19 00:00:00.000
8   0   NULL

--load 2  max([InsertedDateTime])+ 1 minute

ALTER PARTITION SCHEME PS_myPartitionScheme     NEXT USED [Primary];
ALTER PARTITION FUNCTION PF_myDateRange ()  SPLIT RANGE('2024-10-19 11:59:59.9970000');
---------
--select *  from [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
select min([InsertedDateTime]) ,max([InsertedDateTime]) from [C1810429].[STYTOTAL_RAW_Intermediate_Staging]
 
-- pass the min and max value of staging table
--load 1 >=  min([InsertedDateTime]) <=max([InsertedDateTime])
--load 2 >=  min([InsertedDateTime]) <=max([InsertedDateTime])

8   3   2024-10-19 12:00:59.997 -- Here the data is failling under
9   0   2024-10-19 18:00:59.997
10  0   NULL

ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
WITH CHECK ADD CONSTRAINT ck_Min_InsertedDateTime
CHECK ([InsertedDateTime] IS NOT NULL AND [InsertedDateTime] >= '2024-10-19 00:00:00.0000000' )

ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
WITH CHECK ADD CONSTRAINT ck_Max_InsertedDateTime
CHECK ([InsertedDateTime] IS NOT NULL AND [InsertedDateTime] <= '2024-10-19 11:59:59.9970000' )

-- get the data for bounday and enter the partition no or date

-- Create partition for each load by getting max datetime of [InsertedDateTime] in table  [C1810429].[STYTOTAL_RAW]
--2024-10-19 12:00:00.001
Declare @New_PartitionBoundaryDate_SWITCH datetime2 (7)
select @New_PartitionBoundaryDate_SWITCH = max (DATEADD(minute,0,[InsertedDateTime]))  from [C1810429].STYTOTAL_RAW_Intermediate_Staging
select @New_PartitionBoundaryDate_SWITCH as [SWITCH] --Get the swith parttion

--load 1 is not working since the last data is in that boundary value
ALTER TABLE   [C1810429].STYTOTAL_RAW_Intermediate_Staging SWITCH TO [C1810429].[STYTOTAL_RAW] PARTITION $PARTITION.PF_myDateRange('2024-10-19 11:59:59.997');

--load 1 taken last not null boundary partition no
--load 1 taken last not null boundary partition no
ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging SWITCH TO [C1810429].[STYTOTAL_RAW] PARTITION 8 -- 2024-10-19 12:00:00.001

declare @ck_Min_InsertedDateTime_drop varchar (max)
set @ck_Min_InsertedDateTime_drop = 'ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
drop CONSTRAINT ck_Min_InsertedDateTime;'
--select @ck_Min_InsertedDateTime_drop
Exec (@ck_Min_InsertedDateTime_drop)

declare @ck_Max_InsertedDateTime_drop varchar (max)
set @ck_Max_InsertedDateTime_drop = 'ALTER TABLE [C1810429].STYTOTAL_RAW_Intermediate_Staging
drop CONSTRAINT ck_Max_InsertedDateTime;'
--select @@ck_Max_InsertedDateTime_drop
Exec (@ck_Max_InsertedDateTime_drop)

No Db2 v11.5 no Linux, se eu executar um comando para verificar se a instância do banco de dados está ativa e em execução:

ps -e -o command | grep db2sysc

a saída é:

db2sysc 0

O que significa o número 0? Só estou perguntando porque, talvez isso possa ser útil para usar em scripts bash.

O cenário: Eu tenho um processo ETL. Esse processo envolve ingerir dados de fontes distintas e fazer meu banco de dados sincronizar com o mais recente.

Digamos que eu tenha uma tabela chamada [catalog]. Ela contém o catálogo de produtos da minha empresa. A fonte da verdade para essa tabela me envia um CSV a cada quatro horas. Eu ingiro esse CSV e certifico-me de que minha tabela [catalog] esteja sincronizada com ele. Eu uso uma atualização como esta:

UPDATE mc 
SET
      [field1] = tc.[field1]
    , [field2] = tc.[field2]
FROM [my].[catalog] as mc  
INNER JOIN [their].[catalog] as tc
    ON mc.id = tc.id
WHERE mc.[field1] <> tc.[field1]
OR mc.[field2] <> tc.[field2]

Este comando comparará cada correspondência e filtrará apenas aqueles que verão mudanças significativas e atualizará apenas essas linhas. Não quero tocar em nenhuma linha desnecessariamente--a tarefa perene dos desenvolvedores de ETL.

Nenhuma atualização trivial será executada (por atualização trivial, quero dizer uma atualização para uma linha que não altera nenhum dos campos naquela linha). As linhas sem alterações permanecem como estavam, sem serem molestadas.

Unmolested é uma boa palavra para usar aqui porque atualizar uma linha sem impactar valores tem um custo desnecessário. Na RAM e no disco, uma atualização de uma linha exclui fisicamente a linha com os valores antigos e insere uma nova linha com os novos valores. Todas essas atualizações precisam ser registradas! Então, no processo de não fazer nada, fragmentamos e dividimos as páginas de índice e dados que temos para a tabela na RAM e no disco. E estamos colocando tráfego desnecessário no log.

Se não estamos mudando nada, então uma atualização trivial ainda está causando rotatividade na RAM e no disco. Dar a uma tabela uma maneira automática de ignorar atualizações triviais seria uma grande vitória para muitos cenários.

Agora, digamos que algum desenvolvedor desajeitado faça uma atualização por meio de algum cliente ou em uma alteração de procedimento que faça algo semelhante a uma tabela grande, mas não inclua nenhuma medida para ignorar linhas que não precisam 'realmente' da atualização. Há algo que eu possa fazer na tabela para fazê-la agir como se soubesse passar atualizações triviais?

temos um banco de dados no SQL Server 2019 com compatibilidade definida como 110 (SQL Server 2012). Temos um procedimento armazenado que está sendo recompilado com frequência, e notei que estamos usando muitas tabelas temporárias. Criamos uma tabela temporária, inserimos dados e, em seguida, adicionamos um índice nela para consultas posteriores. Suspeito que isso pode estar causando as recompilações. As tabelas temporárias acionam recompilações e, em caso afirmativo, em que condições? Obrigado!

Tenho uma tabela com dados que coletamos de várias fontes. Ela tem o nome do item, a fonte e o valor da fonte.

A tabela se parece com a seguinte:

Usando a JSON_OBJECTAGG()função, posso agrupar as fontes com base no nome:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name

Isso dá corretamente:

Agora, quero executar essa consulta, mas com um filtro no preço/fonte, então executei a seguinte consulta:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(data, 2, '$.print')

Isso parece funcionar, mas há um pequeno problema:

Por que de repente há um extra }no final dos dados JSON? A HAVINGcláusula está filtrando corretamente as linhas, mas por que ela modifica os dados do resultado?

Demonstração online: https://sqlfiddle.com/mariadb/online-compiler?id=ccf1041c-3ada-4fc9-bd77-2b0fe1428392

Não tenho certeza de qual versão do MariaDB esse site usa, mas testar isso no meu computador local usando o MariaDB 10.11.9 dá os mesmos resultados.

EDIT : Parece que posso consertar isso usando:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(JSON_OBJECTAGG(source, value), 2, '$.print')

Eu só estava tentando evitar escrever isso JSON_OBJECTAGG()várias vezes. Além disso, embora isso corrija o problema, ainda estou curioso para saber por que isso era um problema em primeiro lugar.

EDIT 2 : Outra "correção" é substituir JSON_OBJECTAGG()por GROUP_CONCAT().

O seguinte retorna os dados corretos:

SELECT name,
    REPLACE(GROUP_CONCAT(JSON_OBJECT(source, value)), '},{', ',') AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(data, 2, '$.print')