Início / dba / 问题

All perguntas(dba)

Martin Hope
Rick Neubig
Asked: 2024-08-05 09:41:13 +0800 CST
  • 5

Tenho um banco de dados de nomes científicos de aves e quero obter o ID da espécie que está relacionada a uma subespécie. Provavelmente preciso usar uma consulta de junção interna, mas estou tendo problemas para construí-la. Posso extrair o nome científico da espécie (sciname) da subespécie sciname com uma função SubString(sciname, 1, localizar(' ', sciname, localizar(' ', sciname) + 1)) e tentar consultar com isso para encontrar o eID que eu quero, mas me dá um resultado nulo - provavelmente porque está olhando apenas um único registro por vez.

ID eletrônico ecat sciname
39 espécies Crypturellus noctivagus
40 espécies Crypturellus atrocapillus
17341 subespécies Crypturellus atrocapillus atrocapillus
17342 subespécies Crypturellus atrocapillus garleppi
41 espécies Crypturellus boucardi
17343 subespécies Crypturellus boucardi boucardi
17344 subespécies Crypturellus boucardi costaricensis
42 espécies Crypturellus kerriae

A saída que eu gostaria é o eID de cada subespécie e o eID de suas espécies relacionadas.

eID1 eID2
17341 40
17342 40
17343 41
17344 41

Eu tentei isso, mas não funcionou.

select e1.eid, e2.eid from species as e1 
    left join species as e2
    on e1.sciname=e2.sciname 
    where e1.ecat="subspecies" 
       and e2.sciname=(select SubString(e1.sciname, 1, locate(' ', e1.sciname, locate(' ', e1.sciname) + 1)));
mysql
Martin Hope
Randomize
Asked: 2024-08-02 21:39:26 +0800 CST
  • 4

Dada esta tabela chamada prices:

meu encontro meu_valor meu_separador
10/02/2012 100 1
11/02/2012 120 1
12/02/2012 120 1
13/02/2012 110 1
14/02/2012 100 0
15/02/2012 115 0
16/02/2012 130 1
17/02/2012 190 1
18/02/2012 180 1
19/02/2012 150 0
20/02/2012 130 1

Preciso somar os valores na my_valuecoluna com base na condição que my_separatorestá 1dentro desse grupo (excluindo assim os valores where my_separator = 0). Isso deve retornar os seguintes valores:

  • para o período 2012-02-10-2012-02-13=> 100 + 120 + 120 + 110 = 450
  • para o período 2012-02-16-2012-02-18=> 130 + 190 + 180 = 500
  • para a data 2012-02-20=> 130

Tentei usar funções de janela, mas não consegui encontrar nada válido. Qualquer ajuda?

postgresql
Martin Hope
bqback
Asked: 2024-08-02 17:07:47 +0800 CST
  • 5

Tenho uma tabela post_likescom uma coluna sendo um ID de postagem ( uint) e outra coluna sendo uma matriz de IDs ( uint[]) de usuários que curtiram a postagem.

Se eu estiver selecionando informações de várias tabelas sobre vários usuários, como faço para unir essa post_likestabela de forma que, para cada usuário, cada resultado tenha uma matriz de IDs de postagens que o usuário gostou?

Sei que um armazenamento de um para muitos ou de muitos para muitos tornaria isso trivial, mas é com isso que tenho que trabalhar.

postgresql
Martin Hope
Jayron Soares
Asked: 2024-08-02 08:10:40 +0800 CST
  • 6

Para identificar índices redundantes e duplicados, ignorando chaves primárias e estrangeiras e verificando sua cardinalidade. Qual é a prática recomendada para descartar índices em bancos de dados MySQL do AWS RDS no ambiente AWS RDS?

Meu ambiente (contexto): AWS RDS Aurora MySQL 8.0 O objetivo é reduzir custos de armazenamento e melhorar as transações INSERT/UPDATE/DELETE.

mysql
Martin Hope
Andrew B
Asked: 2024-08-01 22:20:02 +0800 CST
  • 6

Após a redução do arquivo de dados, há cerca de 180 Gb de espaço livre no disco rígido (antes da operação, a caixa de diálogo de redução do arquivo também mostra que é possível liberar cerca de 180 Gb de espaço). Após 1-2 dias, o arquivo de dados volta ao tamanho original (antes da operação de redução) e é possível reduzi-lo novamente (a caixa de diálogo Reduzir arquivo mostra novamente que é possível liberar cerca de 180 Gb de espaço).

Forcei estritamente a manter algum espaço livre no disco rígido, portanto, evitar a operação de redução não é uma opção.

Procurando conselhos sobre o que poderia causar esse problema e possível solução.

sql-server-2008-r2
Martin Hope
Soheil Rahsaz
Asked: 2024-08-01 21:12:07 +0800 CST
  • 5

Resumo

Eu criei um cenário com 3 nós onde 1 nó está fora de sincronia com outros nós e quando me conecto a esse nó, vejo que ele está recuperando dados que não deveria porque está lendo os dados de outro nó (I acredito que o nó ao qual estou conectado usando um driver é o nó coordenador).

Não sei o que estou perdendo e por que Cassandraexatamente estou fazendo isso?

Passos:

  1. Crie um cluster de 3 nós com configurações padrão e sem autenticação, usando docker e cassandra versão 4.1.5 e usandoCASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch
docker run --name cass1 -d -p 19001:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass1" -e CASSANDRA_BROADCAST_ADDRESS="cass1" cassandra:4.1.5
docker run --name cass2 -d -p 19002:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass2" -e CASSANDRA_BROADCAST_ADDRESS="cass2" cassandra:4.1.5
docker run --name cass3 -d -p 19003:9042 --network=casscluster -e CASSANDRA_CLUSTER_NAME=chat -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_SEEDS="cass1,cass2" -e CASSANDRA_LISTEN_ADDRESS="cass3" -e CASSANDRA_BROADCAST_ADDRESS="cass3" cassandra:4.1.5
  1. Em cada nó, desabilitou a transferência sugerida de yml e nós reiniciados (verificou cada nó para nodetool statushandoffter certeza de hinted_hand_offque está desabilitado)
nodetool statushandoff
Hinted handoff is not running
  1. Crie um keyspace com fator de replicação de 3.
create keyspace testak WITH REPLICATION = {
    'class' : 'NetworkTopologyStrategy',
    'dc1' : 3
    };
  1. Criar tabela e também desabilitar speculative_retry(para que ela não leia de outros nós caso a consulta demore muito)
create table testak.mooz
(
    userid int,
    chatid int,
    name   text,
    primary key (userid, chatid)
);
alter table testak.mooz with speculative_retry = 'none';
  1. Pare o nó 3
  2. Inserir no nó 1
insert into testak.mooz (userid, chatid) values (1, 1);
insert into testak.mooz (userid, chatid) values (2, 1);
insert into testak.mooz (userid, chatid) values (3, 1);
insert into testak.mooz (userid, chatid) values (4, 1);
insert into testak.mooz (userid, chatid) values (5, 1);
insert into testak.mooz (userid, chatid) values (6, 1);
insert into testak.mooz (userid, chatid) values (7, 1);
insert into testak.mooz (userid, chatid) values (8, 1);
insert into testak.mooz (userid, chatid) values (9, 1);
insert into testak.mooz (userid, chatid) values (10, 1);
  1. Iniciar nó 3
  2. Use cqlsh externo para conectar-se ao nó 3 docker run --name cqlsh --rm -it --network=casscluster nuvo/docker-cqlsh cqlsh cass3 9042 --cqlversion=3.4.6
  3. Definir consistência um CONSISTENCY ONE
  4. Rastreamento ativado TRACING ON
  5. Selecione 1 linha select * from testak.mooz where userid = 1 and chatid = 1;

Como o fator de replicação é 3 e eu tenho 3 nós, espero que todos os nós assumam que possuem todos os dados e não precisam consultar os dados de outro nó, mas ao emitir a consulta várias vezes, vejo que às vezes as solicitações vai para outros nós e realmente recupera dados que não deveria porque o nó 3 tem inconsistência e não possui os dados reais do registro.

insira a descrição da imagem aqui Aqui está uma captura de tela mostrando que está recuperando dados de outro nó.

Nota: Na maioria das vezes (~90%) ele não lê dados de outro nó.

Atualizar

Mais tarde eu configurei o nível de log usando nodetool setlogginglevel org.apache.cassandra ALLe olhei debug.log, e aqui está o log quando cassandra decide ler dados de outro nó:

TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,109 CoordinatorWarnings.java:49 - CoordinatorTrackWarnings.init()
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,110 Dispatcher.java:164 - Received: QUERY select * from testak.mooz where userid = 1 and chatid = 1; [pageSize = 100] at consistency ONE, v=4/v4
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,110 QueryProcessor.java:251 - Process SelectStatement[aggregationSpecFactory=<null>,bindVariables=[],isReversed=false,limit=<null>,..(truncated long log line)
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 ReadCallback.java:90 - Blockfor is 1; setting up requests to org.apache.cassandra.locator.ReplicaPlan$SharedForTokenRead@74da23a8
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 MessagingService.java:401 - cass3/172.20.0.4:7000 sending READ_REQ to 2781@/172.20.0.3:7000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,111 AbstractReadExecutor.java:226 - Decided not to speculate as 9223372036854775807 > 5000000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 CoordinatorWarnings.java:80 - CoordinatorTrackWarnings.done() with state {}
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 CoordinatorWarnings.java:61 - CoordinatorTrackWarnings.reset()
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:01,122 Dispatcher.java:214 - Responding: ROWS [userid(testak, mooz), org.apache.cassandra.db.marshal.Int32Type][chatid(testak, mooz), org.apache.cassandra.db.marshal.Int32Type][name(testak, mooz), org.apache.cassandra.db.marshal.UTF8Type]
 | 1 | 1 | null
---, v=4/v4

Mas quando os dados são lidos do próprio nó, em vez da linha with sending READ_REQ, vejo esta linha:

TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:49,248 EndpointMessagingVersions.java:67 - Assuming current protocol version for cass3/172.20.0.4:7000
TRACE [Native-Transport-Requests-1] 2024-07-18 08:47:49,248 AbstractReadExecutor.java:158 - reading data locally

Atualização2

Executando nodetool getendpoints testak mooz 1em cass3:

172.20.0.3
172.20.0.4
172.20.0.2

nodetool status testak:

Datacenter: dc1
===============
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address     Load        Tokens  Owns (effective)  Host ID                               Rack
UN  172.20.0.3  227.48 KiB  16      100.0%            2a4a84a4-1894-488a-8661-680cf818384b  rack1
UN  172.20.0.4  232.56 KiB  16      100.0%            9ed9d3f6-13e0-424f-bf52-1bd359d8a26e  rack1
UN  172.20.0.2  281.54 KiB  16      100.0%            68de8980-ab63-4745-85b1-6b98636ea9af  rack1

nodetool describecluster:

Cluster Information:
        Name: chat
        Snitch: org.apache.cassandra.locator.GossipingPropertyFileSnitch
        DynamicEndPointSnitch: enabled
        Partitioner: org.apache.cassandra.dht.Murmur3Partitioner
        Schema versions:
                629e0d12-c942-3eeb-bce3-5616a291ae90: [172.20.0.4, 172.20.0.2, 172.20.0.3]

Stats for all nodes:
        Live: 3
        Joining: 0
        Moving: 0
        Leaving: 0
        Unreachable: 0

Data Centers:
        dc1 #Nodes: 3 #Down: 0

Database versions:
        4.1.5: [172.20.0.4:7000, 172.20.0.2:7000, 172.20.0.3:7000]

Keyspaces:
        system_auth -> Replication class: SimpleStrategy {replication_factor=1}
        testak -> Replication class: NetworkTopologyStrategy {dc1=3}
        system_distributed -> Replication class: SimpleStrategy {replication_factor=3}
        system_traces -> Replication class: SimpleStrategy {replication_factor=2}
        system_schema -> Replication class: LocalStrategy {}
        system -> Replication class: LocalStrategy {}
cassandra
Martin Hope
Unmesh Kadam
Asked: 2024-08-01 13:35:04 +0800 CST
  • 6

Estou otimizando o desempenho da consulta SQL reordenando um índice composto no PostgreSQL. Preciso entender as possíveis repercussões, incluindo uso de espaço, sobrecarga de economia de dados e quaisquer outros impactos dessa modificação.

Para resolver o problema de desempenho, implementei uma abordagem que envolve a reordenação do índice composto.

Detalhes: Banco de dados: Nome da tabela PostgreSQL : instrução DDL readingTable :

    CREATE TABLE readingTable (
    ts timestamp NOT NULL,
    plant_id int8 NOT NULL,
    instance_type varchar(30) NOT NULL,
    instance_id int8 NOT NULL,
    readings jsonb NULL,
    CONSTRAINT idx_readingTable_pk PRIMARY KEY (ts, plant_id, instance_type, instance_id));
CREATE INDEX readingTable_ts_idx ON readingTable USING btree (ts DESC);

Dados da tabela deat:

ts ID_planta tipo_de_instância ID_da_instância leituras
31/05/2024 23:59:00.000 4 ALARME 1765 [{"c": "ALARM_TAG_1", "d": "NUMBER", "s": "S15", "u": "kW/kWp", "v": null}, {"c": "ALARM_TAG_2 ", "d": "BOOLEAN", "s": "S00", "u": "AU", "v": "false"}, {"c": "ALARM_TAG_3", "d": "NÚMERO ", "s": "S00", "u": "kW/kWp", "v": "12.25"}]

Consulta:

SELECT *
FROM readingTable, LATERAL jsonb_array_elements(readings) AS readings_data
WHERE plant_id = 2
  AND instance_type = 'ALARM'
  AND instance_id IN (1765)
  AND ts BETWEEN '2024-05-01 00:00:00' AND '2024-05-30 23:59:00.000'
  AND readings_data ->> 'c' IN ('ALARM_TAG_1')
ORDER BY ts, instance_id;

Se eu executar a consulta acima para idx_readingTable_pk(ts, plant_id, instance_type, instance_id) então levará algum tempo para executar a consulta, então atualizei o idx_readingTable_pk de (ts, plant_id, instance_type, instance_id) para (plant_id, instance_type, instance_id, ts) .

No índice atualizado, reordenei a sequência da chave composta porque quando uma condição inclui verificações de igualdade (=) e verificações de intervalo/desigualdade (>, >=, <, <=, IN), as colunas envolvidas nas verificações de igualdade devem aparecem primeiro no índice, seguidos pelas colunas de desigualdade.

Consulta para atualizar idx_readingTable_pk:

-- Drop the old primary key constraint
ALTER TABLE readingTable
DROP CONSTRAINT idx_readingTable_pk;

-- Add the new primary key constraint with the updated column order
ALTER TABLE readingTable
ADD CONSTRAINT idx_readingTable_pk PRIMARY KEY (plant_id, instance_type, instance_id,ts);

Esta modificação levou a uma melhoria no desempenho, conforme mostram as estatísticas abaixo. Na imagem abaixo, você pode ver que testei a consulta com os índices antigo e novo em vários períodos e pode observar a melhoria no desempenho.

estatísticas de desempenho

Questões:

  1. Quando o primeiro executou a consulta para atualizar a ordem da chave composta, ele atualiza o índice existente, cria um novo ou ambos?
  2. Há alguma complexidade de espaço envolvida, como maiores requisitos de espaço para armazenar o índice atualizado em comparação com o antigo?
  3. A reordenação do índice introduz alguma sobrecarga ao salvar dados?
  4. Há alguma outra repercussão potencial da modificação do índice composto que eu deva estar ciente?
postgresql
Martin Hope
Megakoresh
Asked: 2024-08-01 01:53:31 +0800 CST
  • 6

Eu tenho algumas tabelas unidas com dados industriais:

create table v2.tag (
  tag_id integer generated always as identity,
  tag text not null,

  primary key (tag_id),
  unique (tag)
);

create table v2.state (
  tag_id integer not null,
  "timestamp" timestamp without time zone not null,
  value float not null,

  primary key (tag_id, timestamp),
  foreign key (tag_id) references v2.tag (tag_id)
) partition by range (timestamp);

A statetabela contém dados de série temporal de cerca de 50 milhões de linhas dos últimos 6 meses e preciso executar um benchmark com várias consultas. A tabela é particionada mensalmente.

A consulta que tentei simplesmente obtém o número de pontos de dados por dia e tags, que qualquer TSDB real pode fazer sem suar a camisa em um conjunto de dados tão pequeno:

SELECT 
    count(*) as points,date_trunc('day', timestamp) as timestamp,tag.tag 
FROM 
    v2.state 
JOIN 
    v2.tag USING (tag_id) 
GROUP BY 
    timestamp, tag 
ORDER BY 
    timestamp ASC;

O problema é que, por algum motivo, essa consulta faz com que o banco de dados ocupe quase 3 GB de RAM e retorne um monte de duplicatas. Assim:

 2024-02-01 00:00:00 | /Heave         |      1
 2024-02-01 00:00:00 | /Pitch         |      1
 2024-02-01 00:00:00 | /Roll          |      1
 2024-02-01 00:00:00 | /Velocity      |      1
 2024-02-01 00:00:00 | /Heave         |      1
 ...

E assim por diante. Tudo no mesmo dia, não consegui rolar para o próximo, apenas repetia essas linhas no resultado em vez de contá-las por tag como eu esperava. Portanto, em vez de contar o número de pontos de dados por dia/tag, parece apenas produzir uma duplicata para cada linha real dos aproximadamente 50 milhões de linhas no banco de dados.

Então algo não está funcionando na agregação. Eu esperaria que essa consulta retornasse cerca de 12 mil linhas ( 65*30*6), mas, em vez disso, ela retorna milhões de linhas, fazendo com que o notebook Jupyter no qual estou tentando carregá-lo seja eliminado pelo OOM.

Tentei rodar isso com EXPLAIN ANALYZE, mas como sou novato no Postgres, isso realmente não explica nada:

 Sort  (cost=700769.72..700798.22 rows=11400 width=78) (actual time=80503.260..83825.211 rows=47499969 loops=1)
   Sort Key: (date_trunc('day'::text, state."timestamp"))
   Sort Method: external merge  Disk: 4703296kB
   ->  Finalize GroupAggregate  (cost=697027.86..700001.55 rows=11400 width=78) (actual time=35609.801..64328.719 rows=47
499969 loops=1)
         Group Key: state."timestamp", tag.tag
         ->  Gather Merge  (cost=697027.86..699688.05 rows=22800 width=70) (actual time=35609.453..55143.276 rows=4749996
9 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Sort  (cost=696027.84..696056.34 rows=11400 width=70) (actual time=34526.070..42018.956 rows=15833323 
loops=3)
                     Sort Key: state."timestamp", tag.tag
                     Sort Method: external merge  Disk: 1414088kB
                     Worker 0:  Sort Method: external merge  Disk: 1446832kB
                     Worker 1:  Sort Method: external merge  Disk: 1470664kB
                     ->  Partial HashAggregate  (cost=695145.67..695259.67 rows=11400 width=70) (actual time=8690.289..20
138.661 rows=15833323 loops=3)
                           Group Key: state."timestamp", tag.tag
                           Batches: 1029  Memory Usage: 8241kB  Disk Usage: 1694608kB
                           Worker 0:  Batches: 901  Memory Usage: 8241kB  Disk Usage: 1727928kB
                           Worker 1:  Batches: 773  Memory Usage: 8241kB  Disk Usage: 1748528kB
                           ->  Hash Join  (cost=2.28..652834.40 rows=5641502 width=62) (actual time=138.598..4142.702 row
s=15833323 loops=3)
                                 Hash Cond: (state.tag_id = tag.tag_id)
                                 ->  Parallel Append  (cost=0.00..599769.83 rows=19794743 width=12) (actual time=138.383.
.2665.699 rows=15833323 loops=3)
                                       ->  Parallel Seq Scan on state_y2024m04 state_4  (cost=0.00..221214.31 rows=874583
1 width=12) (actual time=39.308..827.302 rows=6996457 loops=3)
                                       ->  Parallel Seq Scan on state_y2024m02 state_2  (cost=0.00..172317.34 rows=680943
4 width=12) (actual time=58.866..1102.604 rows=8171318 loops=2)
                                       ->  Parallel Seq Scan on state_y2024m03 state_3  (cost=0.00..78305.04 rows=3095204
 width=12) (actual time=0.766..694.493 rows=7428501 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m05 state_5  (cost=0.00..28879.42 rows=1141442
 width=12) (actual time=180.418..416.467 rows=2739461 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m01 state_1  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m06 state_6  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m07 state_7  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.000..0.001 rows=0 loops=1)
                                       ->  Parallel Seq Scan on state_y2024m08 state_8  (cost=0.00..20.00 rows=1000 width
=12) (actual time=0.002..0.002 rows=0 loops=1)
                                 ->  Hash  (cost=1.57..1.57 rows=57 width=58) (actual time=0.149..0.268 rows=65 loops=3)
                                       Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                       ->  Seq Scan on tag  (cost=0.00..1.57 rows=57 width=58) (actual time=0.031..0.036 
rows=65 loops=3)
 Planning Time: 2.447 ms
 JIT:
   Functions: 96
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 14.487 ms, Inlining 105.515 ms, Optimization 203.723 ms, Emission 143.355 ms, Total 467.081 ms
 Execution Time: 86164.911 ms

Então, o que há de errado com minha consulta? Por que não está agregando?

postgresql
Prev
Próximo