All perguntas(dba)

Todo mundo sabe que, em tabelas que usam o InnoDB como motor, consultas do tipo SELECT COUNT(*) FROM mytablesão muito inexatas e muito lentas, principalmente quando a tabela fica maior e há constantes inserções/exclusões de linhas enquanto a consulta é executada.

Pelo que entendi, o InnoDB não armazena a contagem de linhas em uma variável interna, que é o motivo desse problema.

Minha pergunta é: Por que isso acontece? Seria tão difícil armazenar tais informações? É uma informação importante saber em muitas situações. A única dificuldade que vejo se tal contagem interna seria implementada é quando as transações estão envolvidas: se a transação não for confirmada, você conta as linhas inseridas por ela ou não?

PS: Não sou especialista em DBs, sou apenas alguém que tem o MySQL como um simples hobby. Portanto, se acabei de perguntar algo estúpido, não seja excessivamente crítico: D .

Alguém está usando HierarchyId em produção real com tabelas de tamanho razoável, mais do que alguns milhares de linhas? É confiável/desempenho? Até agora, não encontrei ninguém não afiliado ao fornecedor que o recomendasse, e Paul Nielsen desaconselha isso aqui .

Qual é a sua experiência com o uso do HierarchyId em sistemas de produção reais?

Quais critérios você usou ao escolher HierarchyId em vez de suas alternativas?

Estou usando SQLite e preciso armazenar preços. O tipo de dados do SQLite REALdiz que usa floating-pointarmazenamento inaceitável para preços. Existe um tipo de dados além de TEXT que eu possa usar para armazenar os preços numericamente para que eles classifiquem corretamente?

Eu tenho uma tabela Postgres com ~ 2,1 milhões de linhas. Eu executei a atualização abaixo nele:

WITH stops AS (
    SELECT id,
           rank() OVER (ORDER BY offense_timestamp,
                     defendant_dl,
                     offense_street_number,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1
)

UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;

Essa consulta levou 39 horas para ser executada. Estou executando isso em um processador de laptop i7 Q720 de 4 núcleos (físicos), muita RAM, nada mais funcionando na grande maioria do tempo. Sem restrições de espaço no disco rígido. A tabela havia sido recentemente aspirada, analisada e reindexada.

Durante todo o tempo em que a consulta estava em execução, pelo menos após a WITHconclusão inicial, o uso da CPU geralmente era baixo e o HDD estava em uso 100%. O HDD estava sendo usado com tanta força que qualquer outro aplicativo rodava consideravelmente mais devagar do que o normal.

A configuração de energia do laptop estava em Alto desempenho (Windows 7 x64).

Segue a EXPLICAÇÃO:

Update on master  (cost=822243.22..1021456.89 rows=2060910 width=312)
  CTE stops
    ->  WindowAgg  (cost=529826.95..581349.70 rows=2060910 width=33)
          ->  Sort  (cost=529826.95..534979.23 rows=2060910 width=33)
                Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
                ->  Seq Scan on master  (cost=0.00..144630.06 rows=2060910 width=33)
                      Filter: (citing_jurisdiction = 1)
  ->  Hash Join  (cost=240893.51..440107.19 rows=2060910 width=312)
        Hash Cond: (stops.id = consistent.master.id)
        ->  CTE Scan on stops  (cost=0.00..41218.20 rows=2060910 width=48)
        ->  Hash  (cost=139413.45..139413.45 rows=2086645 width=268)
              ->  Seq Scan on master  (cost=0.00..139413.45 rows=2086645 width=268)

citing_jurisdiction=1exclui apenas algumas dezenas de milhares de linhas. Mesmo com essa WHEREcláusula, ainda estou operando em mais de 2 milhões de linhas.

O disco rígido é todo criptografado com TrueCrypt 7.1a. Isso atrasa um pouco as coisas, mas não o suficiente para fazer com que uma consulta demore tantas horas.

A WITHparte leva apenas cerca de 3 minutos para ser executada.

O arrest_idcampo não tinha índice para chave estrangeira. Existem 8 índices e 2 chaves estrangeiras nesta tabela. Todos os outros campos da consulta são indexados.

O arrest_idcampo não tinha restrições, exceto NOT NULL.

A tabela tem um total de 32 colunas.

arrest_idé do tipo caractere variando(20) . Percebo que rank()produz um valor numérico, mas tenho que usar o caractere variando (20) porque tenho outras linhas em citing_jurisdiction<>1que usam dados não numéricos para esse campo.

O arrest_idcampo estava em branco para todas as linhas com citing_jurisdiction=1.

Este é um laptop pessoal de ponta (a partir de 1 ano atrás). Eu sou o único usuário. Nenhuma outra consulta ou operação estava em execução. Bloquear parece improvável.

Não há gatilhos em nenhum lugar desta tabela ou em qualquer outro lugar do banco de dados.

Outras operações neste banco de dados nunca levam muito tempo. Com a indexação adequada, SELECTas consultas geralmente são bastante rápidas.

É melhor definir chaves estrangeiras no banco de dados ou na parte do código de um aplicativo?

Do msdn :

Ao contrário de uma tabela derivada, uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.

Estou usando bastante CTEs, mas nunca pensei profundamente sobre os benefícios de usá-los.

Se eu fizer referência a um CTE várias vezes na mesma consulta:

• Existe algum benefício de desempenho?
• Se eu estiver fazendo uma autojunção, o SQL Server verificará as tabelas de destino duas vezes?

Temos um banco de dados para um produto com muita gravação. Acabamos de comprar uma nova máquina servidora com um SSD para ajudar. Para nossa surpresa, as inserções não eram mais rápidas do que em nossa máquina antiga com armazenamento muito mais lento. Durante o benchmarking notamos que a taxa de IO exibida pelo processo do SQL Server era muito baixa.

Por exemplo, executei o script encontrado nesta página , exceto que adicionei um BEGIN TRAN e COMMIT ao redor do loop. Na melhor das hipóteses, pude ver o uso do disco atingir 7Mb/s, enquanto a CPU mal chegava a 5%. O servidor tem 64 Gb instalados e está usando 10. O tempo total de execução foi de 2 minutos e 15 segundos para a primeira chamada para cerca de 1 minuto para as chamadas subsequentes. O banco de dados está em recuperação simples e estava ocioso durante o teste. Larguei a mesa entre cada chamada.

Por que um script tão simples é tão lento? O hardware quase não está sendo usado. As ferramentas de benchmarking de disco dedicadas e o SQLIO indicam que o SSD funciona corretamente com velocidades acima de 500 Mb/s para leitura e gravação. Entendo que as gravações aleatórias são mais lentas do que as gravações sequenciais, mas espero que uma inserção simples como esta, em uma tabela sem indexação em cluster, seja muito mais rápida.

Em última análise, nosso cenário é muito mais complexo, mas sinto que preciso primeiro entender um caso simples. Resumindo, nosso aplicativo exclui dados antigos, então usa SqlBulkCopy para copiar novos dados para tabelas de teste, realiza alguma filtragem e, finalmente, usa MERGE e/ou INSERT INTO, dependendo dos casos, para copiar os dados para as tabelas finais.

--> EDIT 1: Segui o procedimento linkado por Martin Smith e obtive o seguinte resultado:

[Wait Type]  [Wait Count] [Total Wait (ms)] [T. Resource Wait (ms)] [T. Signal Wait (ms)]
NETWORK_IO          5008              46735                 46587        148
LOGBUFFER           901               5994                  5977         17
PAGELATCH_UP        40                866                   865          1
SOS_SCHEDULER_YIELD 53279             219                   121          98
WRITELOG            5                 145                   145          0
PAGEIOLATCH_UP      4                 58                    58           0
LATCH_SH            5                 0                     0            0

Acho estranho NETWORK_IO levar a maior parte do tempo, considerando que não há resultado para exibir e nenhum dado para transferir para outro lugar que não seja para os arquivos SQL. O tipo NETWORK_IO inclui todos os IO?

--> EDIT 2: criei um disco de 20Gb RAM e montei um banco de dados a partir dele. O melhor tempo que tive no SSD é 48s, com o disco RAM caiu para 37 segundos. NETWORK_IO ainda é a maior espera. A velocidade máxima de gravação no disco RAM foi de cerca de 250 Mb/s, embora seja capaz de fazer vários gigabytes por segundo. Ele ainda não estava usando muita CPU, então o que está atrapalhando o SQL?

Eu tenho um grande banco de dados MySql (150 GB) e só agora notei que o innodb_file_per_tableestá definido para offo que faz com que todo o banco de dados seja hospedado em um único arquivo ( ibdata1). Eu quero ativar innodb_file_per_tablee dividir retroativamente o banco de dados em vários arquivos, qual é a melhor maneira de fazer isso?

Existe uma consulta que fará isso?

Encontrei algumas consultas que podem fazer isso para uma tabela, mas não consegui modificá-la para ver:

tablename | column | type

Costumo usar o SSMS para testar meus procedimentos armazenados lentos em busca de índices ausentes. Sempre que vejo um "Índice ausente (impacto xxx)", minha reação instintiva é apenas criar o novo índice. Isso resulta em uma consulta mais rápida sempre, tanto quanto eu posso dizer.

Algum motivo para eu não continuar fazendo isso?