All perguntas(dba)

Eu tenho uma tabela definida como

CREATE TABLE details_search (
    id int4 NOT NULL PRIMARY KEY,
    "search" tsvector NULL
);
CREATE INDEX details_search_idx ON details_search USING gin (search);

Executei isso para ter uma ideia do tamanho:

SELECT pg_size_pretty(pg_relation_size('details_search')) relation_size,
       pg_size_pretty(pg_total_relation_size('details_search')) total_relation_size,
       pg_size_pretty(pg_table_size('details_search')) table_size,
       pg_size_pretty(pg_indexes_size('details_search')) indexes_size;

e estes são os resultados

relation_size|total_relation_size|table_size|indexes_size|
-------------+-------------------+----------+------------+
800 MB       |64 GB              |57 GB     |6830 MB     |

Estou interessado em realizar apenas pesquisas de frases, e elas são usadas de forma agregada. Quando realizo pesquisas de frases com termos incomuns, tudo funciona bem. Agora quando uso uma frase que tem um termo comum, o desempenho sofre muito.

Esta consulta levou 192 segundos:

SELECT COUNT(id)
FROM   details_search
WHERE  search @@ phraseto_tsquery('simple', 'data management') 

Aqui está o plano de consulta (e aqui o plano de consulta em uma interface agradável):

  Output: count(id)
  Buffers: shared hit=25942383 read=6354221 written=4588
  I/O Timings: shared/local read=512605.708 write=122.864
  ->  Gather  (cost=178176.43..178176.64 rows=2 width=8) (actual time=192857.512..192861.652 rows=3 loops=1)
        Output: (PARTIAL count(id))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=25942383 read=6354221 written=4588
        I/O Timings: shared/local read=512605.708 write=122.864
        ->  Partial Aggregate  (cost=177176.43..177176.44 rows=1 width=8) (actual time=192852.434..192852.435 rows=1 loops=3)
              Output: PARTIAL count(id)
              Buffers: shared hit=25942383 read=6354221 written=4588
              I/O Timings: shared/local read=512605.708 write=122.864
              Worker 0:  actual time=192851.530..192851.531 rows=1 loops=1
                Buffers: shared hit=8650807 read=2115877 written=1469
                I/O Timings: shared/local read=170775.853 write=38.985
              Worker 1:  actual time=192848.579..192848.581 rows=1 loops=1
                Buffers: shared hit=8623424 read=2115864 written=1551
                I/O Timings: shared/local read=170720.335 write=41.527
              ->  Parallel Bitmap Heap Scan on details_search  (cost=33664.19..173376.94 rows=1519795 width=4) (actual time=1231.216..192758.374 rows=121050 loops=3)
                    Output: id, search
                    Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                    Rows Removed by Index Recheck: 2268868
                    Heap Blocks: exact=12114 lossy=22061
                    Buffers: shared hit=25942383 read=6354221 written=4588
                    I/O Timings: shared/local read=512605.708 write=122.864
                    Worker 0:  actual time=1230.572..192759.521 rows=121482 loops=1
                      Buffers: shared hit=8650807 read=2115877 written=1469
                      I/O Timings: shared/local read=170775.853 write=38.985
                    Worker 1:  actual time=1227.317..192754.854 rows=120483 loops=1
                      Buffers: shared hit=8623424 read=2115864 written=1551
                      I/O Timings: shared/local read=170720.335 write=41.527
                    ->  Bitmap Index Scan on job_posts_details_search_idx  (cost=0.00..32752.32 rows=3647509 width=0) (actual time=1226.674..1226.675 rows=3956386 loops=1)
                          Index Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                          Buffers: shared hit=832 read=2242
                          I/O Timings: shared/local read=424.365
Settings: effective_cache_size = '13153520kB', search_path = 'public, public, "$user"'
Query Identifier: 1461135140272243366
Planning:
  Buffers: shared hit=194
Planning Time: 7.346 ms
Execution Time: 192861.763 ms

A maior parte do tempo foi para a leitura na varredura paralela de heap de bitmap. Também foi uma leitura bastante lenta, de 97 MB/s considerando que possui SSDs (e possui um SSD exclusivamente para cache de dados ). Isso não melhora se eu pg_prewarmcarregar a tabela antes da consulta.

Vejo que sim Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery), então acho que está extraindo todos os dados do trabalho do disco para verificar a condição na searchcoluna real, como se apenas verificar o índice não fosse suficiente para validar se há uma correspondência de frase. Isso explicaria por que esse problema ocorre apenas em termos comuns.

O que eu poderia fazer para otimizar essas pesquisas de frases? Ficarei feliz em considerar limitações de possíveis coisas a serem pesquisadas (como "fazer apenas consultas de até 3 palavras") ou alterações nas configurações do servidor (para acelerar essas leituras incômodas), se isso puder trazer consistência ao tempo de consulta.

Estou usando o MariaDB 10.11.6 e tenho uma tabela grande (~ 1,4 bilhão de linhas) com dados de log.

Estou tentando adicionar um índice a uma VIRTUALcoluna criada com:

ALTER TABLE log_table ADD COLUMN Days int(11) UNSIGNED AS (TO_DAYS(LogTime)) VIRTUAL INVISIBLE;

Com base na documentação do MariaDB para colunas geradas , isso deveria ser possível, mas recebo o seguinte erro:

ERROR 1904 (HY000): Key/Index cannot be defined on a virtual generated column

A tabela está usando o mecanismo de armazenamento "Aria" e as colunas geradas devem ser suportadas:

InnoDB, Aria, MyISAM e CONNECT suportam colunas geradas

— Colunas Geradas - Suporte ao Mecanismo de Armazenamento

A adição de um índice na coluna gerada deve ser suportada:

A definição de índices em colunas geradas VIRTUAL e PERSISTENT é suportada.

— Colunas Geradas – Suporte ao Índice

Nota: A tabela em questão também é particionada em uma datetimecoluna, usando código semelhante VALUES LESS THAN TO_DAYS('2024-02-15')ao guia de Rick James para manutenção de partições .

Você poderia me ajudar a descobrir por que não consigo criar um índice na coluna virtual gerada?

Atenciosamente
-Bjarne

Eu tenho banco de dados, e neste banco de dados cerca de 10 usuários alteram linhas, como posso obter essas alterações e o usuário que alterou, sem gatilhos. Tentei ler o WAL e não há informações sobre quem mudou.

Recentemente tivemos alto uso de CPU/memória e E/S em nosso MongoDB. Ao verificar os logs, tudo que encontrei foram alguns insertdurante esse período. Ao inspecionar os logs, notei que a maioria dos logs de inserção está bytesReadna seção de armazenamento. Portanto, suspeito que isso cause E/S e o armazenamento em cache dos dados cause muita memória.

Após o pico de inserção, a E/S e a CPU caíram, mas a memória permaneceu a mesma, o que foi resolvido após a reinicialização.

A leitura deste disco é normal com a operação de inserção? Estamos usando o Mongo v4.0 com WiredTigermecanismo de armazenamento na VM CentOS7.

2024-02-14T23:39:44.533+0800 I COMMAND  [conn939845] insert db.user_log ninserted:1 keysInserted:11 numYields:0 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { w: 1 } }, Collection: { acquireCount: { w: 1 } } } storage:{ data: { bytesRead: 34390, timeReadingMicros: 140837 } } 141ms
2024-02-14T23:40:16.785+0800 I COMMAND  [conn939845] insert db.user_log ninserted:1 keysInserted:11 numYields:0 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { w: 1 } }, Collection: { acquireCount: { w: 1 } } } storage:{ data: { bytesRead: 24150, timeReadingMicros: 506594 } } 507ms

Quando o ajuste automático está habilitado, usando SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN), então com base na regressão de consulta, suponha que o SQL Server auto decida forçar um plano, então quando ele revisita sua decisão e reavalia se existe ou não um plano melhor que pode executar a consulta mais rápido/com menos recursos?

Estou trabalhando em um projeto com um conjunto de dados bastante grande. Exigimos agregados arbitrários neste conjunto de dados, que são gerados no momento da solicitação de um usuário. Aqui está uma descrição básica de nossa configuração atual no PostgresQL v11 (sim, sabemos que é EOL, a atualização está prevista para o próximo trimestre)

A estrutura básica da tabela é assim:

create table if not exists sales
(
    category_a  smallint,    -- sequential integer values from 0 - 10000
    category_b  varchar(3),  -- 3-digit ids (all numeric, padded with zeros)
    product     varchar(14), -- essentially random 14 character identifiers
    location_id varchar(5),  -- location id, 5-digit number (left padded with zeros)
    units       int,         -- value of interest
    sales       float,       -- second value of interest
    primary key (category_a, category_b, product, location_id)
) partition by range (category_a);

Atualmente particionamos por Aporque eles mudam após cerca de 200 valores e são eliminados do conjunto de dados. Apartições são subparticionadas por B. Cada uma dessas A_Bpartições contém cerca de 50 a 70 milhões de linhas.

Os valores para Bnão são sequenciais e possuem lacunas.

Existem muitos valores diferentes de produto, cerca de um milhão.

location_id, existem cerca de 50 a 100 locais por categoria B, cada um com a maioria dos produtos.

Um exemplo de consulta é semelhante a este:

select category_a, category_b, product, sum(units), sum(sales)
from sales
where category_a between 1 and 100
  and sales.category_b in ('001', '010', '018', '019', '024')
  and product in ('00000000000147', '00000000000900', '00000000000140', '00000000009999')
group by category_a, category_b, product;

A explicação para esta consulta indica que realizamos uma verificação sequencial completa para cada partição no conjunto de dados. Isso parece estranho, pois temos o índice único com os três valores à esquerda sendo os três nas cláusulas where e group. Não entendo por que isso não usa o índice.

Aqui está uma consulta que carregará dados de exemplo na tabela:

insert into sales
    (category_a, category_b, product, location_id, units, sales)
select cat_a,
       lpad(cat_b::varchar, 3, '0'),
       lpad(product::varchar, 14, '0'),
       lpad(location_id::varchar, 5, '0'),
       (random() * 10000)::int,
       (random() * 100000)::int
from generate_series(1, 50) cat_a
         cross join generate_series(1, 25) cat_b
         cross join generate_series(1, 10) location_id
         cross join generate_series(1, 5000) product;

A explicação para essa consulta é muito longa, mas posso fornecê-la se acharmos que vai ajudar.

Essas consultas podem ser extremamente lentas (minutos, às vezes mais de 10). Terei todo o prazer em fornecer detalhes adicionais, mas esta é a informação essencial (a meu ver).

Existem alterações que podemos/devemos fazer em nossa tabela ou consulta que aumentariam o desempenho desta consulta?

Tenho duas tabelas que estou tentando combinar em uma. Eles não têm uma chave correspondente além do ID. Preciso procurar valores distintos na tabela 1 (Zip) e adicionar todos os valores listados na tabela 2 (atividade). Todas as atividades são únicas (cerca de 20+), mas as resumi abaixo. Não consigo encontrar nenhuma solução online.

tabela 1

     ID    City       State  Zip 
     1     Houston    TX     77009
     2     Miami      FL     33101
     3     Cleveland  OH     44101
     4     Boston     MA     02108
     5     Rochester  MN     14602

mesa 2

    ID     Activity
    1      Park
    2      Playground
    3      Dog Park
    4      Walking trail
    5      Treasure

Espere saída para nova tabela

    ID    City       State    Zip    Activity
     1     Houston    TX     77009   Park
     2     Houston    TX     77009   Playground
     3     Houston    TX     77009   Dog Park
     4     Houston    TX     77009   Walking Trail
     5     Houston    TX     77009   Treasure
     6     Miami      FL     33101   Park
     7     Miami      FL     33101   Playground
     8     Miami      FL     33101   Dog Park
     9     Miami      FL     33101   Walking Trail
     10    Miami      FL     33101   Treasure
     11    Cleveland  OH     44101   Park
     12    Cleveland  OH     44101   Playground
     13    Cleveland  OH     44101   Dog Park
     14    Cleveland  OH     44101   Walking Trail
     15    Cleveland  OH     44101   Treasure

O melhor que encontrei está listado abaixo, mas não consigo descobrir como fazer um loop. Agradeço qualquer ajuda.

SELECT * FROM places AS A
LEFT JOIN geocache_activities AS b 
ON a.id=b.id

Recentemente, configurei um novo grupo de disponibilidade do SQL Server usando scripts T-SQL. A configuração não foi convencional, pois iniciei o grupo de disponibilidade sem incluir nenhum banco de dados inicialmente.

CREATE AVAILABILITY GROUP ag1
FOR
REPLICA ON 'node1'
    WITH (
        ENDPOINT_URL = N'TCP://node1.dev.com:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
        SEEDING_MODE = MANUAL
    ),
    N'node2'
    WITH (
        ENDPOINT_URL = N'TCP://node2.dev.com:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL),
        SEEDING_MODE = MANUAL
    );

Depois de configurar a réplica primária, adicionei uma réplica secundária e configurei um ouvinte para o grupo. Todos os comandos T-SQL foram executados com sucesso e sem erros.

No entanto, ao inspecionar o painel do Grupo de Disponibilidade do SQL Server, percebi que alguns avisos são exibidos. Aqui está um instantâneo da aparência do painel:

Dado que o Grupo de Disponibilidade está atualmente vazio (nenhum banco de dados foi adicionado ainda), estou me perguntando se esses avisos são típicos nesse cenário ou se indicam um possível problema de configuração que devo resolver.

Alguém já passou por uma situação semelhante com grupos de disponibilidade vazios ou alguém sabe se esses avisos são simplesmente espaços reservados padrão até que os bancos de dados sejam adicionados?

Qualquer informação ou orientação sobre este assunto seria muito apreciada.

Agradeço antecipadamente!

Eu tenho um requisito de mover registros de tabela original_tablepara tabela new_tablenos últimos N dias.

Abaixo está uma pequena amostra do meu script básico que faz isso.

while True:

    result = INSERT INTO new_table (SELECT * FROM original_table WHERE (crdb_internal_expiration > (current_timestamp() + INTERVAL 'X days')) ORDER BY (unique_id) DESC LIMIT batch_limit OFFSET starting_point);

    if result.rowcount < batch_size:
        break
    offset += batch_size

O problema é que temos que mover os registros entre as tabelas em tempo hábil e, no momento, mover os registros com um LIMIT de 10_000 leva cerca de 30 segundos de cada vez. Temos cerca de 2 milhões de registros nos últimos 2 dias, digamos, e queremos limitar o tempo de inatividade.

A razão pela qual estamos fazendo isso é porque precisamos usar famílias de colunas em nossa tabela, mas o cockroachdb não nos permite alterar uma tabela existente para adicionar novos famílias de colunas se ela ainda não tiver nenhuma.

Existe alguma maneira de otimizar esta consulta para que ela seja executada mais rapidamente, por favor? Talvez não usar um OFFSETe rastrear o registro id fosse mais rápido?

AND id > last_processed_id
ORDER BY id DESC
LIMIT 100;

a versão do cockroachdb está em 23

Considero o Query Store a melhor ideia que o SQL Server da Microsoft teve nos últimos dez anos. Tenho medo de sentir muita falta disso quando mudar para o Postgres. Quais equivalentes o Postgres tem para o Query Store do SQL Server?