Suponha o seguinte:

1. Eu tenho um banco de dados em um servidor de produção. Eu não corro regularmente DBCC CHECKDBnele.
2. Eu regularmente faço backups completos, dif e de log de transações em meu banco de dados.
3. Como é uma boa prática, eu restauro regularmente o banco de dados em um servidor separado e executo DBCC CHECKDBnele. Assim .
4. Um dia, DBCC CHECKDBrelata que as coisas estão muito erradas.
5. Existe apenas um verdadeiro servidor de produção. Não estou fazendo nada inteligente para me dar alta disponibilidade.

Que medidas corretivas devem ser tomadas numa situação (felizmente hipotética) como esta? Entendo que deveríamos testar nossos backups, como descrevi acima, mas não ouvi praticamente nada sobre o que fazer quando um teste falha.

Entendo que o Stack Exchange não gosta de hipóteses, mas tenho certeza de que existem muitas práticas recomendadas e padrões conhecidos do setor que se aplicam a essa situação. Muitas fontes enfatizam a importância de testar seus backups para que esta seja uma pergunta sem resposta ou baseada em opinião.

Estou hospedando um banco de dados Postgresql no Heroku com um plano padrão 0.

Meu banco de dados tem uma tabela chamada transactionsque contém aproximadamente 18 milhões de linhas:

SELECT COUNT(*) FROM transactions;
  count   
----------
 17927768
(1 row)

Nos últimos meses tenho notado que o banco de dados está ficando cada vez mais lento. Agora estou no ponto em que recebo tempos limite de meus aplicativos porque as consultas (mesmo simples) demoram mais de 30 segundos.

Ao tentar descobrir o que está acontecendo, percebi algo estranho:

No servidor hospedado, uma consulta simples como:

EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate  (cost=405691.73..405691.74 rows=1 width=8) (actual time=34941.061..34961.256 
rows=1 loops=1)
   ->  Gather  (cost=405691.63..405691.73 rows=1 width=8) (actual time=34940.913..34961.247 rows=2
 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Partial Aggregate  (cost=404691.63..404691.63 rows=1 width=8) (actual time=34924.080.
.34924.081 rows=1 loops=2)
               ->  Parallel Seq Scan on transactions  (cost=0.00..400083.56 rows=9216145 width=0) 
(actual time=77.981..34179.970 rows=7801236 loops=2)
                     Filter: (partner_id = 1)
                     Rows Removed by Filter: 1164606
 Planning Time: 0.755 ms
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.912 ms, Inlining 0.000 ms, Optimization 30.606 ms, Emission 119.538 ms, To
tal 152.057 ms
 Execution Time: 35190.328 ms
(14 rows)

leva até 35 segundos .

Mas quando faço download do dump de produção para minha máquina (um thinkpad mais antigo), a consulta leva menos de um segundo :

EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
 Finalize Aggregate  (cost=251757.89..251757.90 rows=1 width=8) 
(actual time=669.234..674.362 rows=1 loops=1)
   ->  Gather  (cost=251757.67..251757.88 rows=2 width=8) (actua
l time=669.008..674.348 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=250757.67..250757.68 rows=
1 width=8) (actual time=638.447..638.448 rows=1 loops=3)
               ->  Parallel Index Only Scan using index_transact
ions_on_partner_id on transactions  (cost=0.44..234528.06 rows=6
491844 width=0) (actual time=0.061..405.148 rows=5199597 loops=3
)
                     Index Cond: (partner_id = 1)
                     Heap Fetches: 0
 Planning Time: 0.231 ms
 JIT:
   Functions: 11
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.109 ms, Inlining 0.000 ms, Optimization 0.826 ms, Emission 11.406 ms, Total 15.342 ms
 Execution Time: 676.047 ms
(14 rows)

Também pode-se ver que o Postgresql hospedado usa uma varredura sequencial paralela, enquanto a instância local usa uma varredura de índice paralela.

Como isso é possível? O que preciso fazer para chegar perto desse desempenho no servidor hospedado?

Edição 1: Mais informações sobre 'inchaço'

Tentei investigar o possível inchaço e recebi isto para a tabela de transações:

type   | schemaname |  object_name  | bloat |   waste    
-------+------------+--------------+-------+------------
 table | public     | transactions |   1.3 | 571 MB

E isto:

 schema |             table              | last_vacuum | last_autovacuum  |    rowcount    | dead_rowcount  | autovacuum_threshold | expect_autovacuum 
--------+--------------------------------+-------------+------------------+----------------+----------------+----------------------+-------------------

 public | transactions                   |             |                  |     17,949,072 |            600 |      3,589,864       | 

Essas consultas são geradas pelas ferramentas integradas do Herokus para analisar o inchaço, conforme descrito aqui .

Um valor dead rowcountde 600 em comparação com os 17 milhões de linhas parece negligenciável - mas por que o desperdício é tão alto (570 MB)? Essa poderia ser a origem do problema? Parece que o vácuo nunca foi realizado.

Lembro-me da universidade que:

Por padrão, inserir, excluir e atualizar em 2 ou mais fontes de dados não pode ser participante de uma transação

Por favor, considere este código:

begin tran Insert2

begin try
    Insert into Northwind.dbo.Categories ([CategoryName], [Description]) 
    values ('New Category', 'Some Desc')
    
    Insert into [TestDB].[dbo].[tblRate]([Year], [Month], [Rate])
    values(1111, 1, null) <-- Failed because of null value

    commit tran Insert2
end try
begin catch
    rollback tran Insert2;
    throw;
End catch

Não consigo criar um script que execute um Insert no Northwindbanco de dados e outro Insert não execute, TestDBmas a reversão não pode excluir a linha inserida no Northwindbanco de dados.

Questão 1) As transações são gerenciadas sob um Instanceou gerenciadas sob um Database? Quero dizer, vários bancos de dados podem existir em uma determinada instância, o participante pode participar de uma transação?

Questão 2) Se TestDBexistir em outra instância do SQL Server (por exemplo MyServer2), é possível incluí-lo em uma transação com a instância atual (por exemplo MyServer1).

Você poderia me mostrar um código de exemplo?

Obrigado

Descrição

Atualmente estou enfrentando desafios de desempenho em um cenário de banco de dados PostgreSQL envolvendo uma tabela pai e 30 tabelas filho. Notavelmente, nenhuma dessas tabelas é particionada e algumas das tabelas secundárias têm tamanhos individuais substanciais, com um tamanho cumulativo de todas as tabelas atingindo 5 TB. Apesar de ter índices nas tabelas pai e filho, a execução de consultas, como a mostrada abaixo, leva um longo período, geralmente várias horas.

Pergunta

Estou buscando orientação sobre como otimizar o desempenho neste contexto. Existem configurações específicas, além dos índices, que poderiam melhorar significativamente a velocidade de consulta para uma estrutura de banco de dados tão grande e complexa?

Além disso, estou curioso para saber se o PostgreSQL pode ter limitações inerentes ao manuseio eficiente de bancos de dados desse tamanho e peso. Em caso afirmativo, existem estratégias alternativas que poderiam ser consideradas para um melhor desempenho?

Tem havido especulações sobre o PostgreSQL enfrentar desafios com E/S de disco, especialmente quando comparado a outros bancos de dados como Oracle ou NoSQL. Esta especulação é precisa?

Informações

Versão

Executando no Google Cloud SQL

PostgreSQL 13.12 em x86_64-pc-linux-gnu, compilado pelo Debian clang versão 12.0.1, 64 bits

Mesa

CREATE TABLE mytable (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW()
);

CREATE TABLE mytable_child1 (
    id_pos int8 NOT NULL PRIMARY KEY,
    date_insert DATE DEFAULT NOW(),
    other_field varchar(10) NOT NULL
) INHERITS (mytable);

Índices

Em cada tabela (pai e filho) tenho esses índices

CREATE INDEX IF NOT EXISTS mytable_date_insert_idx ON mytable USING btree (date_insert);

CREATE INDEX IF NOT EXISTS mytable_child1_date_insert_idx ON mytable_child1 USING btree (date_insert);

Consulta

SELECT * FROM mytable WHERE date_insert >= CURRENT_DATE - INTERVAL 1 MONTH;

Tenho a seguinte tabela no Postgres:

CREATE TABLE avg (
    user_id INT,
    numer DOUBLE PRECISION,
    denom DOUBLE PRECISION,
    frac DOUBLE PRECISION
);

Quero realizar o seguinte e estou tendo dificuldade em colocar isso em uma consulta. Basicamente quero poder atualizar algumas médias atomicamente, atualizando separadamente o numerador e o denominador, e depois calcular e atualizar a média fracna mesma operação, com base nos novos valores:

1. Dados os parâmetros ae b, quero encontrar a linha para um determinado user_id, então quero fazer numer += ae denom += bcalcular frac = numer / denoma partir dos novos valores de numere e denom, em seguida, atualizar os valores de numer, denome fracnesta linha.
2. Porém, se não houver nenhuma linha para o solicitado user_id, ou se houver uma linha, mas os valores de numere denomforem nulos, então quero assumir que o valor dessas somas é zero, ou seja, uma nova linha deve ser inserida em a tabela, ou uma operação de atualização deve ser executada se houver uma linha, mas os valores forem nulos, com o resultado sendo definido numer = a, denom = be frac = a / bpara o user_id.
3. Quero garantir que tudo isso aconteça atomicamente.

Pode haver vários numeradores e denominadores em uma determinada tabela, daí a necessidade de ser capaz de inserir (se a linha não existir) ou atualizar (se a linha existir, mas os valores iniciais de um par numerador/denominador específico forem nulo).

Eu uso PostgreSQL. Existe uma consulta para obter uma lista de todas as tabelas que não possuem a restrição exclusiva na coluna de sequência? Se ajudar a formar uma consulta, uso "referência" para minha coluna de sequência.

Este é um exemplo de tabela que possui this.das

\d+ a_practical_approach_when_there_is_a_lot_to_do
                                                                   Table "public.a_practical_approach_when_there_is_a_lot_to_do"
        Column        |           Type           | Collation | Nullable |                                      Default                                      | Storage  | Stats target | Description
----------------------+--------------------------+-----------+----------+-----------------------------------------------------------------------------------+----------+--------------+-------------
 reference            | bigint                   |           | not null | nextval('a_practical_approach_when_there_is_a_lot_to_do_reference_seq'::regclass) | plain    |              |
 display_order        | bigint                   |           |          |                                                                                   | plain    |              |
 principal            | text                     |           |          |                                                                                   | extended |              |
 explanation          | text                     |           |          |                                                                                   | extended |              |
 add_date             | timestamp with time zone |           |          |                                                                                   | plain    |              | UTC
 membership_reference | bigint                   |           |          |                                                                                   | plain    |              |
Indexes:
    "a_practical_approach_when_there_is_a_lot_to_do_reference_unique" UNIQUE CONSTRAINT, btree (reference)
Access method: heap

Eu tenho uma tabela com milhões de linhas e quero encontrar todas as linhas que contenham qualquer um de uma lista fornecida de alguns milhares de valores em uma coluna específica. Basicamente, quero executar uma IN(...set...)consulta, que é reescrita internamente em uma = ANY(...array...)construção, com um tamanho de matriz de milhares, em uma coluna indexada com milhões de linhas.

Minhas perguntas são:

1. Existe um limite para o tamanho de um conjunto ou array neste tipo de consulta?
2. Como esse tipo de consulta é dimensionado? Presumo que a matriz não esteja indexada, então, presumivelmente, cada valor da matriz atinge o índice, fornecendo escala O(n log N)para nvalores da matriz e Nlinhas da tabela.
3. Qual seria o impacto no rendimento da consulta ao enviar esses tipos de consultas grandes, em meio a um fluxo de consultas mais simples? Em outras palavras, seria bom dividir isso em algumas dezenas de consultas separadas, com, digamos, 100 valores de array em cada uma, para permitir que o trabalho desta consulta fosse intercalado com outras consultas?

Preciso criar uma única instrução MySQL para acelerar o processo de atualização de eventos repetidos semanalmente em uma tabela de banco de dados que possuo.

Neste momento, o usuário está adicionando registros à tabela. Alguns registros são para eventos de data única e alguns registros são eventos que se repetem a cada 7 dias dentro de uma data de início e de término definidas. Ambos os tipos de registros são inseridos na mesma tabela.

Cada evento tem a data de início e uma data de término da data do evento inserida em dois campos: 'date_start' e 'date_end'. Para um evento de data única, o usuário insere a mesma data em 'date_start' e 'date_end'. Para um evento repetido, eles inserem a data da ocorrência do primeiro evento em 'data_início' e a data da última data da ocorrência semanal em 'data_fim'. Os eventos repetidos são marcados como 'Weekly_Event' conforme mostrado abaixo nos dados de exemplo.

Preciso pegar esta tabela e fazer várias cópias de todos os registros marcados como 'Weekly_Event'. Uma cópia do registro original para cada semana entre a data de início e de término.

No exemplo abaixo, um registro tem a data de início definida como 3 de maio e a data de término como 7 de junho. Preciso de uma instrução SQL que crie 6 novos registros a partir desse registro: 3 de maio, 10 de maio, 17 de maio, 24 de maio, 31 de maio, 7 de junho. Há também outro evento 'Weekly_Event' e um evento 'Single_Day' como exemplo.

MySQL version: 8.0.36
Table Type: InnoDB  
Collation: latin1_swedish_ci


CREATE TABLE `original_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `original_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 1, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 1, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day');

Após a operação, os registros ficariam principalmente parecidos com o exemplo abaixo. Quero que o date_start e o date_end gerados dos Weekly_Events sejam iguais porque cada um dos eventos recém-gerados acontece dentro do único dia atribuído.

CREATE TABLE `updated_shows_24` (
  `id` int NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL DEFAULT '1',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL,
  `name` varchar(200) NOT NULL,
  `details` mediumtext NOT NULL,
  `url` varchar(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'https://',
  `slug` varchar(128) NOT NULL,
  `category` varchar(300) NOT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `updated_shows_24` (`id`, `status`, `date_start`, `date_end`, `name`, `details`, `url`, `slug`, `category`) VALUES
(1, 0, '2024-05-03', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event', 'Weekly_Event'),
(2, 0, '2024-05-09', '2024-08-18', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event', 'Weekly_Event'),
(3, 1, '2024-05-02', '2024-05-02', 'A regular Single Day Event', 'Details about this event ', 'https://link', 'a-single-day-event', 'Single_Day'),
(101, 1, '2024-05-03', '2024-05-03', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-03', 'Weekly_Event'),
(102, 1, '2024-05-10', '2024-05-10', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-10', 'Weekly_Event'),
(103, 1, '2024-05-17', '2024-05-17', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-17', 'Weekly_Event'),
(104, 1, '2024-05-24', '2024-05-24', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-24', 'Weekly_Event'),
(105, 1, '2024-05-31', '2024-05-31', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-05-31', 'Weekly_Event'),
(106, 1, '2024-06-07', '2024-06-07', 'Weekly Event Name', 'Details about this event', 'https://link', 'weekly-event-2024-06-07', 'Weekly_Event'),
(107, 1, '2024-05-09', '2024-05-09', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-09', 'Weekly_Event'),
(108, 1, '2024-05-16', '2024-05-16', 'Another Weekly Event', 'Details about this other weekly event that starts May 9 and goes every week until Aug 18', 'https://link', 'another-weekly-event-2024-05-16', 'Weekly_Event');

O 'id' para os novos eventos deve ser único e, idealmente, gerado automaticamente com base no próximo 'id' atual em 'original_shows_24'. No meu exemplo, estou assumindo que 'id' está em uso em 'original_shows_24' até 100 por outros registros.

Observe o 'status' dos eventos usados ​​para gerar os eventos repetidos. É definido como '0' nos registros que foram processados ​​('date_start' diferente de 'date_end' e 'category' = 'Weekly_Event')

Outro elemento do resultado final que precisa ser considerado: o slug gerado. O campo slug é usado para gerar o link para a subpágina exclusiva daquele evento, portanto ele também precisa ser atualizado nesta operação. Observe o formato nos dados de amostra, basicamente preciso acrescentar um traço ('-') e 'date_start' no final do slug para cada um dos novos registros gerados. Todas as entradas em 'slug' devem ser únicas.

Ele também precisará ser capaz de gerar a data correta conforme o mês muda. Por exemplo, o último evento nos dados de amostra é 7 de junho. Isso ocorre 7 dias após 31 de maio. Se o MySQL cuidar disso automaticamente nas operações de data, então isso é ótimo.

Criei apenas 'updated_shows_24' para mostrar a saída. Se a saída puder ser simplesmente colocada de volta em 'original_shows_24' (com o status dos registros apropriado alterado de '1' para '0'), isso é melhor. Além disso, 'updated_shows_24' mostra apenas os dois primeiros registros do outro evento repetido apenas para ilustrar a saída desejada. Como a data final desse evento repetido é 18/08/2024, haveria muito mais registros gerados pela solução do que apenas as duas UI incluídas.

Obrigado por qualquer ajuda.

Crie o banco de dados mais simples possível:

CREATE DATABASE DatabaseCheck

Adicione este banco de dados a um Grupo de Disponibilidade, onde o secundário não é legível

Na execução primária:

DBCC CHECKDB ('DatabaseCheck') WITH EXTENDED_LOGICAL_CHECKS

É bem-sucedido:

CHECKDB encontrou 0 erros de alocação e 0 erros de consistência no banco de dados 'DatabaseCheck'. Execução do DBCC concluída. Se o DBCC imprimir mensagens de erro, entre em contato com o administrador do sistema.

Execute o mesmo comando DBCC no secundário. Ele falha mesmo que nenhum erro tenha sido encontrado:

CHECKDB encontrou 0 erros de alocação e 0 erros de consistência no banco de dados 'DatabaseCheck'. Msg 0, Nível 11, Estado 0, Linha 1 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

• Remover COM EXTENDED_LOGICAL_CHECKSfaz com que o erro desapareça
• Tornar a réplica secundária legível faz com que o erro desapareça
• Fazer um failover e verificar o novo primário faz com que o erro desapareça. Verificar o novo secundário mostra o erro novamente.

Alguma ideia do que se trata? A verificação da documentação DBCC CHECKDB não diz nada sobre EXTENDED_LOGICAL_CHECKS não ser compatível com réplica secundária não legível. Parece um bug no próprio comando para mim.

Uma coisa que notei na saída do comando é que ele verifica estatísticas como aqui:

Integridade verificada das estatísticas 'sys.sysrscols.clst'. Integridade verificada das estatísticas 'sys.sysrowsets.clust'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000002_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000003_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000006_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000004_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000005_00000005'. Integridade verificada das estatísticas 'sys.sysrowsets._WA_Sys_00000008_00000005'.

Eles estão ausentes quando executados no secundário

Reproduzido em:

Microsoft SQL Server 2019 (RTM-CU23) (KB5030333) - 15.0.4335.1 (X64) 21 de setembro de 2023 17:28:44 Copyright (C) 2019 Microsoft Corporation Developer Edition (64 bits) no Windows Server 2019 Standard 10.0 (Build 17763 :)

Editar:

A única mensagem no log de erros é esta:

DBCC CHECKDB (DatabaseCheck) executado por Domain\user encontrou 0 erros e reparou 0 erros. Tempo decorrido: 0 horas 0 minutos 0 segundos. O instantâneo do banco de dados interno tem ponto de divisão LSN = 00000034:000001b1:0001 e primeiro LSN = 00000034:000001af:0002.

Não há dumps recentes em sys.dm_server_memory_dumps

Ao tentar executar esta consulta, minha suposição foi que o PostgreSQL executará a atualização duas vezes seguidas com id = 0 tendo contador inicial = 0 e terminaria com contador = 11, mas na verdade é executado apenas uma vez e resulta em contador = 6 .

Alguém pode ajudar a descobrir o porquê? Passei um bom tempo tentando descobrir isso e não consigo entender por que isso acontece.

with duplicatedids as 
  ((select 0 as idx, 6 as v) union 
   (select 1 as idx, 5 as v))
update  verify_seq_update 
set counter = counter + duplicatedids.v 
from duplicatedids 
where verify_seq_update.id <= duplicatedids.idx ;