All perguntas(dba)

Esta questão é a mais bem avaliada neste site sobre heaps. Ela pergunta sobre o caso de uso de heaps. No entanto, acredito que heaps muito grandes (por exemplo, milhares de gigabytes) são um caso especial que merece sua própria pergunta.

À medida que o tamanho do seu índice clusterizado cresce, também aumenta a penalidade do índice clusterizado , ou seja, o número de leituras lógicas necessárias para obter as chaves restantes dos seus índices não clusterizados. O mesmo não é verdade para um heap. Acredito que os heaps são injustamente descartados porque muitas pessoas aprenderam todo o design de seu banco de dados em data warehouses (onde varreduras de chave primária e, portanto, índices clusterizados, são geralmente uma boa ideia) ou nunca trabalharam em um banco de dados grande o suficiente para sentir o custo de ter uma tabela realmente grande.

Isso me faz perguntar: heaps muito grandes e bem indexados têm casos de uso práticos que não são encontrados em tabelas clusterizadas muito grandes e bem indexadas?

Com medo de tornar esta questão muito ampla, deixe as seguintes restrições se aplicarem. Suponha:

• Um heap de 1 TB
• Uma caixa da edição padrão
• Um ambiente OLTP ( não um data warehouse).

Cumulativamente, isso significa que você está limitado a 128 GB de RAM de pool de buffer e não fará varreduras grandes regularmente na chave primária. Ler todo o heap do disco será miserável, então quaisquer respostas sobre usar a tabela como uma tabela de preparação ou somente backup não devem se aplicar.

Tenho uma tabela de fatos que contém valores de licença médica para funcionários (Tabela 1) e preciso inserir cada valor em uma das faixas (Tabela 2)

O problema é que a tabela de fatos contém alguns valores negativos que representam bandas não numéricas. Esses valores são -6666,-7777,-8888,-9999 e não posso usar um operador between para eles porque eles também serão pegos na primeira banda na Tabela 2.

É possível usar uma instrução case para alterar o operador para esses valores? Algo assim:

SELECT * FROM  SickLeave lv
INNER JOIN SickLeaveBands lb
ON 
CASE WHEN  lb.SickLeave in (-6666,-7777,-8888, -9999)
THEN lb.SickLeave = MinHours
ELSE  lb.SickLeave BETWEEN MinHours AND MaxHours
END

Consegui contornar isso unindo usando uma consulta de união para o lado direito da junção, mas existe uma solução mais simples que também evite o uso de SQL dinâmico?

Recentemente, mudamos nosso banco de dados de produção log_statementde 'all' para 'mod', porque o arquivo de log resultante era muito grande para nosso armazenamento disponível. Infelizmente, ainda precisamos registrar todos os SELECTfeitos pelos usuários em uma tabela N específica para fins de auditoria.

Existe alguma solução para isso? Eu tentei usar pgaudite pg_stat_statementssem sucesso.

Definimos pgaudit.logpara ler, mas ele registra todas SELECTas consultas em vez de apenas selecionar a tabela N.

Contexto

Estou usando o postgres como um banco de dados gráfico multilocatário.

Todos os dados são armazenados em uma única tabela e eu confio em índices parciais para tornar as consultas eficientes.

Veja como é um esquema mínimo:

CREATE TABLE triples (
    app_id text NOT NULL,
    entity_id text NOT NULL,
    attr_id text NOT NULL,
    value jsonb NOT NULL,
    eav boolean NOT NULL DEFAULT false,
    ave boolean NOT NULL DEFAULT false,
    vae boolean NOT NULL DEFAULT false,
    created_at bigint NOT NULL DEFAULT 0,
    checked_data_type text
);

CREATE OR REPLACE FUNCTION triples_extract_number_value(value jsonb)
RETURNS double precision AS $$
BEGIN
  IF jsonb_typeof(value) = 'number' THEN
    RETURN value::double precision;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE INDEX vae_idx ON triples (app_id, value, attr_id, entity_id) WHERE vae;

CREATE INDEX ave_number_idx ON triples (app_id, attr_id, triples_extract_number_value(value), entity_id) WHERE ave AND checked_data_type = 'number';

Em um aplicativo com conversations, groups, e messages, os dados podem ser armazenados como:

{app_id: 'app_1', entity_id: 'convo_1', attr_id: 'title': "My Conversation"} 
{app_id: 'app_1', entity_id: 'group_1', attr_id: 'title': "My Group"} 
{app_id: 'app_1', entity_id: 'convo_1', attr_id: 'group': "'group_1'", vae: true} // this 'links' convos to groups
{app_id: 'app_1', entity_id: 'msg_1', attr_id: 'time': "123", ave: true, checked_data_type: 'number'} // this indexes our 'time' field  
{app_id: 'app_1', entity_id: 'msg_1', attr_id: 'convo': "'convo_1'", vae: true} // this  links `msg_1` to `convo_1`

Meta

Quero responder à seguinte questão:

"Dê-me todos convosos que pertencem ao group'grupo 1' e que têm um messagewho's timemaior que 5."

Para fazer isso, escrevi a seguinte consulta, que faz 3 autojunções:

SELECT 
    DISTINCT(match_0_0.entity_id)
FROM 
    triples AS match_0_0
JOIN 
    triples AS match_0_1
    ON match_0_1.app_id = match_0_0.app_id
    AND match_0_1.vae = true
    AND match_0_1.attr_id = 'convo'
    AND match_0_1.value = to_jsonb(match_0_0.entity_id)
JOIN 
    triples AS match_0_2
    ON match_0_2.app_id = match_0_1.app_id
    AND match_0_2.ave = true
    AND match_0_2.attr_id = 'time'
    AND triples_extract_number_value(match_0_2.value) >= 5
    AND match_0_2.checked_data_type = 'number'
    AND match_0_2.entity_id = match_0_1.entity_id
WHERE 
    match_0_0.app_id = 'chat_app'
    AND match_0_0.vae = true
    AND match_0_0.attr_id = 'groups'
    AND match_0_0.value = '"group_1"';

Problema

O problema é que essa consulta leva cerca de 14 segundos para ser satisfeita. Executando com EXPLAIN (ANALYZE, BUFFERS), mostra:

Loop aninhado

Observando EXPLAIN (ANALYZE, BUFFERS), notei que a junção nested_loop tem muitos acertos de buffer.

Se eu tentar desabilitar junções de loop aninhadas, a consulta será resolvida em 40 ms :

SET enable_nestloop TO off;

SELECT 
    DISTINCT(match_0_0.entity_id)
FROM 
    triples AS match_0_0
JOIN 
    triples AS match_0_1
    ON match_0_1.app_id = match_0_0.app_id
    AND match_0_1.vae = true
    AND match_0_1.attr_id = 'convo'
    AND match_0_1.value = to_jsonb(match_0_0.entity_id)
JOIN 
    triples AS match_0_2
    ON match_0_2.app_id = match_0_1.app_id
    AND match_0_2.ave = true
    AND match_0_2.attr_id = 'time'
    AND triples_extract_number_value(match_0_2.value) >= 5
    AND match_0_2.checked_data_type = 'number'
    AND match_0_2.entity_id = match_0_1.entity_id
WHERE 
    match_0_0.app_id = 'chat_app'
    AND match_0_0.vae = true
    AND match_0_0.attr_id = 'groups'
    AND match_0_0.value = '"group_1"';

Pergunta

Existe alguma maneira de dar uma dica ao postgres para que ele escolha uma estratégia melhor?

Reprodução

Configurei uma reprodução no DB Fiddle, que mostra a consulta lenta:

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15620

Vi algumas perguntas relacionadas, mas nenhuma deu uma resposta satisfatória para isso.

select
    ((5999.999999999999)::numeric) as num,
    ((5999.999999999999)::float8) as f64,
    ((5999.999999999999)::float8)::numeric as f64_to_num,
    ((5999.999999999999)::numeric)::float8 as num_to_f64;

      num          |      f64          | f64_to_num |      num_to_f64       
-------------------+-------------------+------------+-------------------
 5999.999999999999 | 5999.999999999999 |    6000    | 5999.999999999999
(1 row)

Isso é surpreendentemente inconsistente, porque um número que é representado no mesmo lugar em numeric e float8, eu não esperaria que ele fosse arredondado para cima ao converter.

O que está errado?

Recebemos este aviso em nossa loja online

report.WARNING: O tamanho da memória alocada para a tabela temporária é maior que 20% do innodb_buffer_pool_size. Atualize o innodb_buffer_pool_size ou diminua o valor do tamanho do lote (o que diminui o uso da memória para a tabela temporária). Tamanho atual do lote: 589; Tamanho da memória alocada: 1742262000 bytes; Tamanho do buffer pool do InnoDB: 8589934592 bytes.

Então eu executei o mysqltuner para analisar as configurações do banco de dados e ele recomendou definir o innodb_buffer_pool_size para >13.7GB. Ele está definido para 8GB atualmente.

Esperamos um tráfego muito alto de clientes em janeiro, então perguntei ao meu supervisor se eu poderia aumentar o valor para cerca de 14 GB (temos 12 GB de RAM livre), mas, segundo ele, é apenas um aviso e devemos ignorá-lo até mudarmos para outro hoster em março.

De acordo com minha pesquisa, essa configuração incorreta pode levar à perda de desempenho e até mesmo causar falhas nas transações e, por fim, causar bugs. Isso é realmente apenas um aviso que se pode ignorar?

Qual é o pior caso que pode acontecer quando ignorado?

Informações adicionais:

Estamos usando o rabbitMQ para importar dados de catálogo de um sistema PIM (Product Information Management) para o magento 2. O aviso está sempre aparecendo, quando os consumidores da fila estão em execução. Isso desacelera a loja e a média de carga sobe para cerca de 30 (10 cpus) durante esse tempo, enquanto está em 6 em média.

Temos algumas colunas na tabela que possivelmente poderiam obter alguns caracteres não ANSI e devemos armazená-los. Antes do SQL Server 2019, havia apenas uma opção - NVARCHARtipo de dados, no entanto, a partir do SQL Server 2019, é possível usar VARCHARo tipo de dados com as ordenações que terminam com %_UTF8. Entendo que, para compatibilidade total com o tamanho, precisarei dobrar o VARCHARtamanho das colunas, então, se fosse, VARCHAR(20)seria necessário VARCHAR(40)cobrir o caso se alguém inserisse um texto onde todos os 20 caracteres são não ANSI, mas há outras desvantagens dessa ordenação? Os dados nessa coluna serão ~99,9% apenas com caracteres ANSI.

Tive que tirar um db do AG e colocá-lo no modelo de recuperação SIMPLES para fazer algumas cópias de dados de alto volume. Colocando o db de volta no AG usando o assistente (depois de definir a recuperação de volta para FULL), ele disse que um backup completo é necessário (caixa de seleção desabilitada). Fiz um backup compactado completo para um compartilhamento UNC e, em seguida, um backup de log para o mesmo compartilhamento. A caixa de seleção foi habilitada na atualização, mas escolhi fazer os backups completos + log para um caminho de pasta novamente, aumentando a duração da tarefa (1 backup, 2 restaurações, incluindo uma lenta para um datacenter diferente). Eu poderia ter usado meu manual completo + log para preparar os secundários? Acho que é a última opção na tela de seeding.

Nota lateral: Eu vi em sp_whoisactive durante o backup completo que é um backup copy_only. Eu normalmente desabilito nosso serviço de backup Rubrik no primário ao fazer trabalho de AG como este, já que não quero dois processos competindo pela cadeia de backup. Não percebi que o assistente de AG usa copy_only.

Estou tentando alternar entre contextos de usuário, mas ORIGINAL_LOGIN()retorna alguma conta mágica do AD em vez da conta da máquina local com a qual me conectei. Isso torna impossível alternar de volta para o contexto do chamador original. Estou me conectando como um usuário local do Windows.

SELECT ORIGINAL_LOGIN(), SYSTEM_USER

retorna:

MicrosoftAccount\[email protected] | localMachine\localUser

O login com o qual me conectei é de fato localMachine\localUser. Nem tenho certeza de onde vem a conta, o Windows meio que inventou isso sozinho.MicrosoftAccount\[email protected]

Agora considere este procedimento armazenado:

CREATE OR ALTER PROCEDURE spTest WITH EXECUTE AS OWNER AS
BEGIN
    -- Do high-privilege things ...

    EXECUTE AS LOGIN = ORIGINAL_LOGIN(); -- Fails!
    -- Impersonate caller, check some permissions
    REVERT;

    -- Do more high-privilege things ...
END
GO

EXEC spTest;

A EXECUTE AS LOGINrepresentação invariavelmente falha com:

Could not obtain information about Windows NT group/user 'MicrosoftAccount\[email protected]', error code 0x54b.

Obviamente, since não é usado ou configurado em lugar nenhum. Isso o torna completamente inútil para nós, e não tenho ideia de como alternar temporariamente de volta para o contexto do chamador.MicrosoftAccount\[email protected]ORIGINAL_LOGIN()

Note que o código acima é apenas para demonstração. A base de código real é significativamente mais complexa com cadeias de chamadas de procedimento armazenado e gatilhos envolvidos.

Como posso ORIGINAL_LOGIN()retornar o login original?

Não estamos exatamente usando o Azure/Entra AD, ou pelo menos não de propósito. Mas o novo Windows realmente tenta arduamente amarrá-lo ao framework Azure/Entra AD, quer você goste ou não.

exec xp_logininfosomente expõe localMachine\localUser. Não há nada que o vincule à conta do Azure/Entra AD. A solução funciona bem quando não há Azure/Entra AD envolvido.

Se possível, gostaria de desabilitar completamente o recurso Azure/Entra AD para SQL Server. Nem sei por que o SQL Server o escolhe em vez de localMachine\localUser.

Para os dados de exemplo...

/*Quick and dirty generation of some rows of data*/
SELECT value as [orderid], 
       1 as [custid], 
       1 as [empid], 
       1 as [shipperid], 
       getdate() as [orderdate], 
       'abcdefgh' as [filler]
INTO dbo.Orders
FROM generate_series(1,10000000)

CREATE CLUSTERED INDEX [idx_cl_od] ON [dbo].[Orders]
(
    [orderdate] ASC
)

UPDATE STATISTICS dbo.Orders WITH FULLSCAN

E a seguinte consulta

SELECT [orderid], [custid], [empid], [shipperid], [orderdate], [filler]
FROM dbo.Orders
WHERE orderid <=7601715 AND 1=1 /*Prevent simple parameterisation*/

Então, na minha máquina de desenvolvimento (SQL Server 2022, DOP de 4), o custo de E/S da varredura de índice clusterizado é 46.8853independente do plano serial ou paralelo. E o custo de CPU da varredura está 11.0002no plano serial e 2.75004no paralelo. Então, eu esperava que o ponto de inflexão entre os planos fosse quando o operador de paralelismo excedesse 8.25016(um limite atingido quando as linhas estimadas que entram nele são em torno de 4,5 milhões). Na realidade, no ponto em que isso realmente ocorre, o custo para o operador de fluxos de coleta é 13.0501(em torno de 3 milhões de linhas a mais do que eu esperava).

Se o SQL Server não estiver usando o custo geral do plano como ponto de inflexão, qual é a lógica real?

( XML para planos estimados em pastetheplan )