All perguntas(dba)

Estamos executando um banco de dados AWS Aurora PostgreSQL versão 13. Outro dia, eu estava tentando configurar uma tarefa de backup para ser executada a partir da réplica de leitura e acabei encontrando o erro "O usuário manteve um bloqueio de relacionamento por muito tempo". Eu trabalhei em torno disso usando a resposta nesta pergunta .

Minha pergunta é teórica e me perdoe se parece estúpida, mas estou faltando alguma coisa aqui. Se o Postgres tiver MVCC em vez de bloquear, por que o pg_dump se preocuparia com o fato de um usuário "bloquear" outra relação? Não deveria estar apenas lendo a última versão da linha?

Eu tinha a seguinte tabela:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence') PARIMARY KEY,
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id)
);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

e quero particioná-lo da transaction_dateseguinte maneira:

CREATE TABLE transactions
(
    id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('transactions_sequence'),
    transaction_id VARCHAR(255) DEFAULT NULL NULL,
    transaction_amount NUMERIC(10,0) DEFAULT NULL NULL,
    customer_name VARCHAR(256) DEFAULT NULL NULL,
    transaction_date TIMESTAMP DEFAULT NULL NULL,
    CONSTRAINT transactions_pkey PRIMARY KEY (id, transaction_date),
    CONSTRAINT uq_transactions_transaction_id UNIQUE (transaction_id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE INDEX transactions_transaction_id_idx ON transactions (transaction_id, id);

com este particionamento transaction_idnão é verdadeiramente único, mas único por partição, pois a chave exclusiva deve ter a chave de partição, ou seja, transaction_date.

O aplicativo que usa esta tabela não reconhece o particionamento e usa o estado SQL 23505 para executar a atualização em vez de uma inserção.

Estou inserindo aproximadamente 300 mil linhas por dia e o tamanho atual da tabela é de aproximadamente 30 milhões de linhas.

O mesmo ocorre com o seguinte gatilho:

CREATE OR REPLACE FUNCTION transactions_validate() RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF EXISTS (SELECT id FROM transactions WHERE transaction_id = NEW.transaction_id AND id <> NEW.id) THEN
        RAISE EXCEPTION USING ERRCODE = '23505';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER transactions_validate_trigger
    BEFORE INSERT OR UPDATE
    ON transactions
    FOR EACH ROW
EXECUTE PROCEDURE transactions_validate();

Uma má ideia? Quais seriam as implicações de desempenho?

Existe uma prática recomendada sobre onde instalar os binários do SQL Server para uma nova implantação do Windows Server. Já vi algumas pessoas sugerirem que é melhor separar isso da unidade do sistema operacional e colocá-lo em sua própria unidade. Já vi outras pessoas separá-lo do sistema operacional e colocá-lo na mesma unidade do arquivo de banco de dados e agora, ao ler o Architecting Microsoft SQL Server no VMWare vSphere, o documento diz:

Os binários do SQL Server devem ser instalados no SO VMDK. O SQL Server, mesmo que outra unidade seja selecionada para instalação binária, ainda instalará coisas na unidade do sistema operacional, portanto, não há sentido real em instalar em outro lugar

Tenho a necessidade de configurar banco de dados, logs de transações, tempdb e backups, cada um em sua própria unidade e os motivos para essas recomendações, no entanto, não tenho 100% de certeza sobre a recomendação de práticas recomendadas sobre onde instalar os binários do SQL Server e por quê, especialmente porque vi várias recomendações de fontes confiáveis.

Para nossa aplicação, este será um servidor virtualizado em VMware.

Temos um banco de dados SQL Server ERP de terceiros hospedado localmente com o qual trabalho há algum tempo.

Para um de nossos relatórios de KPI, precisamos de dados históricos. Esses dados não são gerados pelo banco de dados do ERP em lugar nenhum.

No momento, estamos armazenando as sequências de datas livremente em um campo de 'notas' de texto simples em cada item afetado, mas isso é problemático por vários motivos, alguns dos quais tenho certeza que você pode imaginar imediatamente.

Gostaria de armazenar esses dados corretamente, em uma tabela. Uma opção é criar uma tabela em nosso banco de dados Access, mas isso está se transformando em uma bagunça complicada que só está tornando, e vai tornar, as coisas mais difíceis.

Então minha pergunta é a seguinte: posso adicionar diretamente uma tabela a esse banco de dados ERP com segurança, mantendo novas adições com um gatilho, ou haverá algum tipo de rotina de coleta/manutenção de lixo geralmente encontrada em bancos de dados que tornaria isso inseguro? Tenho muito pouco administrador de banco de dados. experiência, então não tenho ideia sobre qualquer pegadinha ou qualquer outra coisa que possa ser um problema se eu fizer isso.

Muito obrigado

Estou tendo um caso muito peculiar em que recentemente tivemos que atualizar nosso banco de dados do MySQL 5.7 para 8.0 e tive que reescrever algumas consultas.

Se minha consulta for complicada o suficiente, geralmente preparo a escrita no DBeaver e, eventualmente, coloco-a em nosso aplicativo baseado em Laravel (PHP), que obviamente usa PDO para se conectar ao MySQL.

Percebi que essa consulta frequentemente demorava de 4 a 5 segundos para ser executada, enquanto leva cerca de 30 ms no DBeaver. (Simplifiquei o original e mantive apenas a parte que o torna lento.)

A consulta lenta é esta:

select
    COUNT(location) as count,
    ST_Latitude(location) as lat,
    ST_Longitude(location) as lng
from
    `LocationCache`
where
  MBRContains(ST_GeomFromText('Polygon((
                    -116.41480990949 40.923245158437,
                    -116.41480990949 38.44220035439,
                    -120.68677260991 38.44220035439,
                    -120.68677260991 40.923245158437,
                    -116.41480990949 40.923245158437
                    ))', 4326, 'axis-order=long-lat'),
              location)
group by
    `location`;

Descobri também que, se eu me conectar diretamente ao meu host de banco de dados via SSH e executar o mysqlcliente de linha de comando, a consulta será igualmente lenta.

Se eu remover COUNT() e o grupo por, e até mesmo as chamadas de função ST_Latitude/ST_Longitude, ainda será lento - então parece ser com a própria condição geoespacial where.

A sintaxe da tabela CREATE (cortada de colunas/índices irrelevantes) é semelhante a esta:

CREATE TABLE `LocationCache` (
  `media_id` bigint unsigned NOT NULL,
  `location` point NOT NULL /*!80003 SRID 4326 */ COMMENT 'Location point',
  UNIQUE KEY `media_id_unique` (`media_id`),
  SPATIAL KEY `location_spatialindex` (`location`),
  CONSTRAINT `1` FOREIGN KEY (`media_id`) REFERENCES `Media` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

Para deixar claro, estou acessando o mesmo servidor MySQL a partir de PHP, linha de comando e DBeaver, que está sendo executado em um contêiner Docker local (imagem mysql/mysql-server:8.0.32). Tentei usar mysqlo programa de linha de comando do meu computador host, fazer SSH no contêiner do docker e executar seu mysqlcomando local, mas ambos são lentos.

Mostrei isso para algumas pessoas que nunca tinham ouvido falar de uma consulta lenta apenas usando PHP e rápida em minha GUI, e eles recomendaram instalar o MySQL Workbench e tentar lá. Na verdade: no MySQL Workbench também foi rápido!

Usar EXPLAINna consulta no DBeaver vs CLI fornece a mesma explicação. (Ambos especificam a location_spatialindexchave como possible_keymas nenhum deles a usa na keycoluna por algum motivo?)

Meu único palpite é que há algo diferente no driver MySQL que o Workbench/DBeaver usa em comparação com o PDO/CLI, mas estou realmente perdido aqui.

Qualquer ajuda será apreciada! Desde já, obrigado!

EDIT: A tabela tem aproximadamente 700.000 linhas. E eu nunca tinha ouvido falar EXPLAIN ANALYZE SELECT(Obrigado @RickJames), mas o resultado é este:

-> Table scan on <temporary>  (actual time=5032.824..5032.824 rows=6 loops=1)
    -> Aggregate using temporary table  (actual time=5032.823..5032.823 rows=6 loops=1)
        -> Filter: mbrcontains(<cache>(st_geomfromtext('Polygon((\n                    -116.41480990949 40.923245158437,\n                    -116.41480990949 38.44220035439,\n                    -120.68677260991 38.44220035439,\n                    -120.68677260991 40.923245158437,\n                    -116.41480990949 40.923245158437\n                    ))',4326,'axis-order=long-lat')),LocationCache.location)  (cost=73576.76 rows=135833) (actual time=301.622..5024.938 rows=1343 loops=1)
            -> Table scan on LocationCache  (cost=73576.76 rows=663100) (actual time=0.022..511.299 rows=703852 loops=1)

Atualmente, estou executando o PostgreSQL 13, mas também observei esse comportamento no PostgreSQL 11. Estou me deparando com um problema em que um erro de violação UniqueConstraint é gerado, mas esperaria bloquear pg_advisory_xact_lockaté que a transação fosse confirmada (e visível para outras transações aguardando na fechadura) ou revertido.

Estou calculando um valor exclusivo sort_idpara linhas em uma tabela com base em uma chave estrangeira na tabela de um aplicativo da web. UniqueConstraint(sort_id, foreign_key_value). Estamos utilizando pg_advisory_xact_lockem Python via SQL Alchemy e psycopg2. Originalmente, isso foi calculado com base em um modelo SQL Alchemy (veja o sort_id mais recente na propriedade do modelo). Também tentei uma consulta direta como ( SELECT max(sort_id) from table where foreign_key_value=:value;) e ainda encontrei erros de restrição.

À medida que a simultaneidade desta chamada/funcionalidade da API aumenta, a taxa de erros de restrição aumenta. Executando thread único, nunca encontramos o erro de restrição. Isso me leva a acreditar que o bloqueio pode ser liberado antes que os dados fiquem visíveis para outras transações.

Quando é pg_advisory_xact_locklançado? Os dados ficarão visíveis para outras transações antes que o bloqueio seja realizado por um processo diferente?

WITHA diferença entre cláusulas simples e detalhadas :

No tutorial do YouTube Tutorial 20 do SQL Server: Compressão de tabela, índice e linha , o exemplo da vez 5:43contém uma WITHcláusula simples das opções de índice, apenas WITH (DATA_COMPRESSION = ROW).

USE myDatabase;
CREATE TABLE dbo.Ch7RowCompression
(
ID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
BirthDate datetime
)
WITH (DATA_COMPRESSION = ROW);

Entretanto, com o GUI Management Studio, o Storage > Manage Compression...assistente gera comandos com uma WITHcláusula mais detalhada, fornecendo explicitamente valores nas opções de índice. Por exemplo, para compactar um índice, ele obtém o comando de exemplo abaixo:

ALTER INDEX [SALES_ORDER_CREATED_AT] ON [schema].[sales_order] 
REBUILD PARTITION = ALL WITH (
  PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
, DATA_COMPRESSION = PAGE
)

Outra observação sobre o comportamento do GUI Management Studio:

O GUI Management Studio gera comandos errados para desfazer uma compactação de índice.

Já habilitamos Script Data Compression Optionsisso Trueno contexto do Management Studio > Toolbar > Tools > Options... > SQL Server Object Explorer > Scripting > Object scripting options. O sistema em teste possui Management Studio 2016 na versão 13.0.16106.4 e SQL Server 2016. Informe se precisar de mais detalhes.

Suspeitamos que o comportamento do sistema está errado ao desfazer a compactação em um índice: Por exemplo, ao definir a compactação de "Página" para "Nenhum", o assistente obtém o comando abaixo, faltando DATA_COMPRESSION = NONE. E confirmamos no teste que o comando não desfaz a compactação.

ALTER INDEX [SALES_ORDER_CREATED_AT] ON [schema].[sales_order] 
REBUILD PARTITION = ALL WITH (
  PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
)

O assistente funciona bem para desfazer a compactação em uma tabela , e o comando gerado inclui DATA_COMPRESSION = NONEconforme o esperado.

Nossas perguntas:

• Como obtemos um comando do SQL Server para fazer e desfazer a compactação? Pelo assistente da GUI, pela caligrafia ou por maneiras melhores?

• Qual é a diferença entre a WITHcláusula simples e a cláusula detalhada com opções de índice explícitas? E, quando a WITHcláusula não contém opção, qual será o valor padrão?

• Apenas verificando se todos os outros estão vendo o mesmo sintoma no GUI Management Studio tentando desfazer uma compactação de índice? Não tenho certeza se é um bug ou se perdemos alguma coisa.

Agradecemos quaisquer dicas e sugestões.

Como determino o nível de isolamento padrão para nossa instância 11.5 do DB2 em execução no Linux?

Vejo que há um "registro especial" nesta documentação da IBM , no entanto, não sou um especialista em DB2, portanto não tenho certeza de onde procurar isso ou como "ver" valores para registros "especiais".

Eu também olhei para this , mas também não estou tendo sorte em entender como obter o isolamento atual disso.

Tentando usar um gatilho para a) preencher uma nova coluna top_categorycom valores à esquerda de dois pontos, encontrados em category, então b) Se não houver dois pontos, coloque todo o valor de inserção em top_category. Não consigo estruturar esse segundo gatilho para criação.

https://dbfiddle.uk/jwLle13e

CREATE TABLE qn_txs (
  id SERIAL PRIMARY KEY,
  category VARCHAR(250),
  top_category VARCHAR(250)
);

-- Stage 1 trigger works
--  CREATE TRIGGER `t_add_top_category1` BEFORE INSERT ON `qn_txs` 
--    FOR EACH ROW 
--  SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1);

DELIMITER //
CREATE TRIGGER `t_add_top_category2` BEFORE INSERT ON `qn_txs` 
     FOR EACH ROW BEGIN
         SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1)
         -- make sure top_category is not null if there is a value in category
            IF (NEW.`top_category` = '')
             THEN
              SET NEW.`top_category` = NEW.`category`;
           END IF;
END//

DELIMITER;

INSERT INTO qn_txs (category)
VALUES ('I should also be a top cat'), ('bananas:green'), ('baseball:table'), (''), ('Very long string: with spaces');

SELECT * FROM qn_txs;

Aqui está uma tabela da saída desejada após a conclusão da inserção.

Após uma migração do MySQL 5.7 para o MySQL 8.0.34, temos um comportamento muito estranho com uma consulta quando semijoinestá onno otimizador_switch.

1. A consulta problemática:

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
    SELECT s4_.id
    FROM emails_history i5_
    INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
    WHERE i5_.rule_id IN (1517676 , 1517677)
)

Neste caso este é o plano de execução:

O problema é que o tipo i5_JOIN é ALL, nenhum índice é usado no JOIN.

2. Com apenas um valor na cláusula IN:

Se a INcláusula na consulta incluir apenas um valor:

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
    SELECT s4_.id
    FROM emails_history i5_
    INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
    WHERE i5_.rule_id IN (1517676) -- only one value
)

O plano de execução passa a ser:

Neste caso, o JOIN usa um índice mas o plano de execução ainda é diferente do plano de execução que temos no MySQL 5.7

3. Usando i5_.shopper_id em vez de s4_.id (que deveria ser idêntico)

SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
    SELECT i5_.shopper_id -- using i5_.shopper_id instead of s4_.id
    FROM emails_history i5_
    INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
    WHERE i5_.rule_id IN (1517676 , 1517677)

Neste caso, o plano de execução é o mesmo que tínhamos no MySQL 5.7

4. Com semijoin=off

SET optimizer_switch = 'semijoin=off';
SELECT COUNT(s0_.id)
FROM stores_shoppers s0_
WHERE s0_.id NOT IN (
    SELECT s4_.id
    FROM emails_history i5_
    INNER JOIN stores_shoppers s4_ ON i5_.shopper_id = s4_.id
    WHERE i5_.rule_id IN (1517676 , 1517677)
)

O mesmo plano de execução de antes.

Questões:

Alguém pode me explicar o que está acontecendo?
Isso é um bug no otimizador?
Por que a junção não usa índice na primeira consulta?