All perguntas(dba)

Estou desenvolvendo uma aplicação em Ruby on Rails com o banco de dados PostgreSQL (9.4). Para o meu caso de uso, as colunas nas tabelas serão pesquisadas com muita frequência, pois o objetivo principal do aplicativo é procurar atributos muito específicos em um modelo.

Atualmente estou decidindo se devo usar um integertipo ou simplesmente usar um tipo de string típico (por exemplo character varying(255), que é o padrão em Rails ) para as colunas, pois não tenho certeza de qual será a diferença de desempenho no índice.

Essas colunas são enumerações . Eles têm um tamanho fixo para a quantidade de valores possíveis que podem ter. A maioria dos comprimentos de enumeração não excede 5, o que significa que o índice seria mais ou menos fixo durante toda a vida útil do aplicativo ; assim, os índices integer e string seriam idênticos no número de nós.

No entanto, a string que seria indexada pode ter cerca de 20 caracteres, o que na memória é aproximadamente 5x o número inteiro (se um inteiro tiver 4 bytes e as strings forem ASCII puro em 1 byte por caractere, isso será válido). Eu não sei como os mecanismos de banco de dados fazem pesquisas de índice, mas se ele precisar "varrer" a string até que ela corresponda exatamente , então, em essência, isso significa que a pesquisa de string seria 5x mais lenta que uma pesquisa de inteiro; o "scan" until match para a pesquisa de inteiros seria de 4 bytes em vez de 20. Isto é o que estou imaginando:

O valor de pesquisa é (inteiro) 4:

digitalização......................... ENCONTRADO | obtendo registros... |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

O valor de pesquisa é (string) "some_val" (8 bytes):

digitalização.................................................. .................................... ENCONTRADO | obtendo registros... |BYTE_1|BYTE_2|BYTE_3|BYTE_4|BYTE_5|BYTE_6|BYTE_7|BYTE_8|...|

Espero que isso faça sentido. Basicamente, como o inteiro ocupa menos espaço, ele pode ser "combinado" mais rapidamente do que sua contraparte de string. Talvez este seja um palpite completamente errado, mas eu não sou especialista, então é por isso que estou perguntando a vocês! Suponho que esta resposta que acabei de encontrar parece apoiar minha hipótese, mas quero ter certeza.

O número de valores possíveis na coluna não mudaria usando nenhum deles, então o índice em si não mudaria (a menos que eu adicionasse um novo valor ao enum). Nesse caso, haveria uma diferença de desempenho em usar integerou varchar(255), ou usar um tipo inteiro faz mais sentido?

A razão pela qual estou perguntando é que o enumtipo do Rails mapeia inteiros para chaves de string, mas eles não devem ser colunas voltadas para o usuário. Essencialmente, você não pode verificar se o valor enum é válido, porque um valor inválido causará um erro ArgumentErrorantes que qualquer validação possa ser executada. Usar um stringtipo permitiria validações, mas se houver um custo de desempenho, prefiro apenas contornar o problema de validação.

Estou executando o sqlcmd de um arquivo em lotes e queria saber como fazê-lo retornar um ERRORLEVEL diferente de 0 quando algo der errado com o backup.

Eu sei que os tipos de dados NUMERIC e DECIMAL no SQL Server funcionam da mesma forma: a sintaxe para criá-los é a mesma, os intervalos de valores que você pode armazenar neles são os mesmos etc.

No entanto, a documentação do MSDN descreve a relação entre os dois assim:

numeric é funcionalmente equivalente a decimal.

Normalmente, quando vejo o qualificador " funcionalmente equivalente", significa que as duas coisas não são exatamente iguais, mas que são dois tipos diferentes que são indistinguíveis do lado de fora .

Essa implicação é verdadeira? Existem diferenças entre NUMERIC e DECIMAL que por acaso se comportam da mesma forma para um observador externo? Ou eles são realmente equivalentes, por exemplo, NUMERIC é apenas um sinônimo herdado para DECIMAL?

Eu tenho duas tabelas de banco de dados. Um contém centenas de milhões de registros. Vamos chamar aquele history. O outro é calculado diariamente e quero copiar todos os seus registros para historyum.

O que eu fiz foi rodar:

INSERT INTO history SELECT * FROM daily

E funcionou por um tempo, mas começou a ficar cada vez mais lento à medida que o número de discos continuava crescendo. Agora eu tenho cerca de 2 milhões de registros que precisam ser copiados de dailypara historyem uma única operação e demora muito para ser concluído.

Existe outra maneira mais eficiente de copiar dados de uma tabela para outra?

Estou gerando algumas consultas automagicamente com o código que escrevi para SELECT de um banco de dados Pg remoto e insiro em um banco de dados local do SQL Server. Porém, um deles está gerando este erro:

[Microsoft][ODBC SQL Server Driver][SQL Server]String ou dados binários seriam truncados. (SQL-22001) [estado era 22001 agora 01000]

[Microsoft][ODBC SQL Server Driver][SQL Server]A instrução foi encerrada. (SQL-01000) em .\insert.pl linha 106.

Como descubro qual coluna está gerando esse erro e não tem o comprimento da entrada? Existe uma maneira de fazer isso sem forçar a adivinhar todos os varchar?

Supondo que você precise garantir que seu aplicativo que depende do SQL Server 2012 como seu back-end de banco de dados esteja disponível 24 horas por dia, mesmo que uma máquina servidora falhe.

Como desenvolvedor e não DBA, estou com dificuldades para entender quando usar qual cenário para meu failover/alta disponibilidade:

• Dois (ou mais) servidores em um cluster de failover do Windows, SQL Server como uma instância clusterizada
• Duas (ou mais) instâncias do SQL Server que são mantidas atualizadas com replicação transacional
• Dois (ou mais) SQL Servers em um SQL Server Availability Group, configurados em um modo de confirmação síncrona

Qual desses cenários funciona para que tipo de carga de trabalho e que tipo de falha/interrupção pode ser tratada por esses cenários? Eles são mesmo comparáveis/trocáveis?

Alguém poderia me fornecer uma visão melhor sobre o recurso de modo de compatibilidade? Está se comportando diferente do que eu esperava.

Até onde eu entendo os modos de compatibilidade, trata-se da disponibilidade e suporte de certas estruturas de linguagem entre as várias versões do SQL Server.

Ele não afeta o funcionamento interno da versão do mecanismo de banco de dados. Ele tentaria impedir o uso de recursos e construções que ainda não estavam disponíveis em versões anteriores.

Acabei de criar um novo banco de dados com nível de compatibilidade 80 no SQL Server 2008 R2. Criou uma tabela com uma única coluna int e a preencheu com algumas linhas.

Em seguida, executou uma instrução select com uma row_number()função.

Meu pensamento era que, como a função row_number foi introduzida apenas em 2005, isso geraria um erro no modo compat 80.

Mas para minha surpresa isso funcionou bem. Então, certamente, as regras de compatibilidade são avaliadas apenas quando você 'salva algo'. Então eu criei um proc armazenado para minha instrução row_number.

A criação do proc armazenado correu bem e posso executá-lo perfeitamente e obter resultados.

Alguém poderia me ajudar a entender melhor o funcionamento do modo de compatibilidade? Meu entendimento é obviamente falho.

Estou usando uma máquina baseada em código aberto (RHEL 6.2) executando o software SIEM. Quando executo o topcomando, vejo postgrese postmasterambos com 96% de uso da CPU. Existe uma maneira de identificar ou ver o que está causando o acúmulo desses serviços?

Nosso sistema grava muitos dados (tipo de sistema Big Data). O desempenho de gravação é bom o suficiente para nossas necessidades, mas o desempenho de leitura é realmente muito lento.

A estrutura de chave primária (restrição) é semelhante para todas as nossas tabelas:

timestamp(Timestamp) ; index(smallint) ; key(integer).

Uma tabela pode ter milhões de linhas, até bilhões de linhas, e uma solicitação de leitura geralmente é para um período específico (timestamp/índice) e tag. É comum ter uma consulta que retorna em torno de 200 mil linhas. Atualmente, podemos ler cerca de 15 mil linhas por segundo, mas precisamos ser 10 vezes mais rápidos. Isso é possível e se sim, como?

Nota: O PostgreSQL é empacotado com nosso software, então o hardware é diferente de um cliente para outro.

É uma VM usada para testes. O host da VM é o Windows Server 2008 R2 x64 com 24,0 GB de RAM.

Especificação do servidor (VMWare de máquina virtual)

Server 2008 R2 x64
2.00 GB of memory
Intel Xeon W3520 @ 2.67GHz (2 cores)

postgresql.confotimizações

shared_buffers = 512MB (default: 32MB)
effective_cache_size = 1024MB (default: 128MB)
checkpoint_segment = 32 (default: 3)
checkpoint_completion_target = 0.9 (default: 0.5)
default_statistics_target = 1000 (default: 100)
work_mem = 100MB (default: 1MB)
maintainance_work_mem = 256MB (default: 16MB)

Definição de tabela

CREATE TABLE "AnalogTransition"
(
  "KeyTag" integer NOT NULL,
  "Timestamp" timestamp with time zone NOT NULL,
  "TimestampQuality" smallint,
  "TimestampIndex" smallint NOT NULL,
  "Value" numeric,
  "Quality" boolean,
  "QualityFlags" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  CONSTRAINT "PK_AnalogTransition" PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag" ),
  CONSTRAINT "FK_AnalogTransition_Tag" FOREIGN KEY ("KeyTag")
      REFERENCES "Tag" ("Key") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE,
  autovacuum_enabled=true
);

Consulta

A consulta leva cerca de 30 segundos para ser executada no pgAdmin3, mas gostaríamos de ter o mesmo resultado em menos de 5 segundos, se possível.

SELECT 
    "AnalogTransition"."KeyTag", 
    "AnalogTransition"."Timestamp" AT TIME ZONE 'UTC', 
    "AnalogTransition"."TimestampQuality", 
    "AnalogTransition"."TimestampIndex", 
    "AnalogTransition"."Value", 
    "AnalogTransition"."Quality", 
    "AnalogTransition"."QualityFlags", 
    "AnalogTransition"."UpdateTimestamp"
FROM "AnalogTransition"
WHERE "AnalogTransition"."Timestamp" >= '2013-05-16 00:00:00.000' AND "AnalogTransition"."Timestamp" <= '2013-05-17 00:00:00.00' AND ("AnalogTransition"."KeyTag" = 56 OR "AnalogTransition"."KeyTag" = 57 OR "AnalogTransition"."KeyTag" = 58 OR "AnalogTransition"."KeyTag" = 59 OR "AnalogTransition"."KeyTag" = 60)
ORDER BY "AnalogTransition"."Timestamp" DESC, "AnalogTransition"."TimestampIndex" DESC
LIMIT 500000;

Explique 1

"Limit  (cost=0.00..125668.31 rows=500000 width=33) (actual time=2.193..3241.319 rows=500000 loops=1)"
"  Buffers: shared hit=190147"
"  ->  Index Scan Backward using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..389244.53 rows=1548698 width=33) (actual time=2.187..1893.283 rows=500000 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-16 01:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-16 15:00:00-04'::timestamp with time zone))"
"        Filter: (("KeyTag" = 56) OR ("KeyTag" = 57) OR ("KeyTag" = 58) OR ("KeyTag" = 59) OR ("KeyTag" = 60))"
"        Buffers: shared hit=190147"
"Total runtime: 3863.028 ms"

Explique 2

No meu último teste, levou 7 minutos para selecionar meus dados! Veja abaixo:

"Limit  (cost=0.00..313554.08 rows=250001 width=35) (actual time=0.040..410721.033 rows=250001 loops=1)"
"  ->  Index Scan using "PK_AnalogTransition" on "AnalogTransition"  (cost=0.00..971400.46 rows=774511 width=35) (actual time=0.037..410088.960 rows=250001 loops=1)"
"        Index Cond: (("Timestamp" >= '2013-05-22 20:00:00-04'::timestamp with time zone) AND ("Timestamp" <= '2013-05-24 20:00:00-04'::timestamp with time zone) AND ("KeyTag" = 16))"
"Total runtime: 411044.175 ms"

Eu gostaria de usar um valor padrão para uma coluna que deve ser usada se nenhuma linha for retornada. Isso é possível no PostgreSQL? Como eu posso fazer isso? Ou existe alguma outra maneira de eu resolver isso?

Por exemplo, algo assim:

SELECT MAX(post_id) AS max_id DEFAULT 0 FROM my_table WHERE org_id = 3

E se não houver nenhuma linha org_id = 3na tabela, quero retornar 0.