All perguntas(dba)

Estou tentando entender/aprender a rastrear os detalhes de uma sessão bloqueada.

Então criei a seguinte configuração:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;

Agora eu me conecto ao banco de dados duas vezes de dois clientes diferentes.

As questões da primeira sessão:

begin transaction
update foo set some_data = 'update'
  where id = 1;

Eu explicitamente não me comprometo lá para manter os bloqueios.

Na segunda sessão eu emito a mesma declaração e claro que uma espera devido ao travamento. Agora estou tentando usar as diferentes consultas que circulam para ver que a sessão 2 está aguardando a footabela.

sp_who2mostra o seguinte (removi algumas colunas para mostrar apenas as informações importantes):

SPID | Situação | BlkBy | DBName | Comando | SPID | IDENTIFICAÇÃO DO PEDIDO
-----+--------------+-------+----------+---------- --------+------+----------
52 | dormindo | . | comidab | AGUARDANDO COMANDO | 52 | 0        
53 | dormindo | . | comidab | AGUARDANDO COMANDO | 53 | 0        
54 | SUSPENSO | 52 | comidab | ATUALIZAÇÃO | 54 | 0        
56 | EXECUTÁVEL | . | comidab | SELECIONE EM | 56 | 0        

Isso é esperado, a sessão 54 é bloqueada pelas alterações não confirmadas da sessão 52.

A consulta sys.dm_os_waiting_taskstambém mostra isso. A declaração:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;

retorna:

session_id | tipo_espera | endereço_recurso | descrição_recurso                                                            
-----------+-----------+--------------------+----- -------------------------------------------------- --------------------------
        54 | LCK_M_X | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associadoObjectId=72057594046054400

Novamente, isso é esperado.

Meu problema é que não consigo descobrir como encontrar o nome real do objeto que a sessão 54 está esperando.

Eu encontrei várias consultas que estão se juntando sys.dm_tran_lockse sys.dm_os_waiting_tasksassim:

SELECT ....
FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

Mas no meu cenário de teste acima, essa junção não retorna nada. Portanto, essa junção está errada ou dm_tran_locksnão contém as informações que estou procurando.

Então, o que estou procurando é uma consulta que retorne algo como:
" sessão 54 está aguardando um bloqueio na tabelafoo ".

Algumas informações de fundo:

O problema da vida real que estou tentando resolver é um pouco mais complicado, mas se resume à pergunta "em qual mesa a sessão 54 está esperando". O problema em questão envolve um procedimento armazenado grande que atualiza várias tabelas e um select de uma view que acessa algumas dessas tabelas. A selectinstrução é bloqueada mesmo que tenhamos o isolamento de instantâneo e o instantâneo confirmado de leitura habilitado. Descobrir por que o select está bloqueado (o que eu pensei que não seria possível se o isolamento de instantâneo estivesse habilitado) será o próximo passo.

Como primeiro passo, gostaria de saber o que essa sessão está esperando.

Eu tenho um grande banco de dados (16 milhões de linhas) contendo hashes perceptivos de imagens.

Eu gostaria de poder procurar linhas por hamming distância em um período de tempo razoável.

Atualmente, tanto quanto eu entendo corretamente o problema, acho que a melhor opção aqui seria uma implementação SP-GiST personalizada que implementa um BK-Tree , mas isso parece muito trabalhoso e ainda estou confuso na prática detalhes da implementação adequada de um índice personalizado. Calcular a distância hamming é tratável o suficiente, e eu conheço C, no entanto.

Basicamente, qual é a abordagem apropriada aqui? Eu preciso ser capaz de consultar correspondências dentro de uma certa distância de edição de um hash. Pelo que entendi, a distância Levenshtein com strings de comprimento igual é uma distância hamming funcional, então há pelo menos algum suporte existente para o que eu quero, embora não haja uma maneira clara de criar um índice a partir dele (lembre-se, o valor que estou consultando mudanças. Não posso pré-calcular a distância a partir de um valor fixo, pois isso só seria útil para aquele valor).

Os hashes são atualmente armazenados como uma string de 64 caracteres contendo a codificação ASCII binária do hash (por exemplo, "10010101..."), mas posso convertê-los para int64 com bastante facilidade. O problema real é que preciso ser capaz de consultar relativamente rápido.

Parece que seria possível conseguir algo na linha do que eu quero com o pg_trgm, mas estou um pouco confuso sobre como funciona o mecanismo de correspondência de trigramas (em particular, o que a métrica de similaridade que ele retorna realmente representa? Parece tipo distância de edição).

O desempenho de inserção não é crítico (é muito caro computacionalmente calcular os hashes para cada linha), então eu me preocupo principalmente com a pesquisa.

Para armazenar um UUID de 128 bits, existem várias opções de armazenamento:

1. uma coluna de byte[16]
2. duas colunas bigint/long(64 bits)
3. uma coluna CHAR(36) - 32 dígitos hexadecimais + 4 traços.
4. uma coluna específica do banco de dados UUID, se db suportar

Do ponto de vista da indexação, quais são os mais eficientes? Se o banco de dados não suportar um tipo uuid dedicado, qual de 1, 2, 3 são os melhores candidatos?

Eu jogo um jogo de basquete que permite gerar suas estatísticas como um arquivo de banco de dados, para que se possa calcular as estatísticas que não são implementadas no jogo. Até agora não tive problemas para calcular as estatísticas que queria, mas agora me deparei com um problema: contar o número de duplos duplos e/ou triplos duplos que um jogador fez ao longo da temporada a partir de suas estatísticas de jogo.

A definição de duplo duplo e triplo duplo é a seguinte:

Duplo duplo:

Um double-double é definido como um desempenho no qual um jogador acumula um número total de dois dígitos em duas das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

Triplo duplo:

Um triplo-duplo é definido como um desempenho no qual um jogador acumula um total de dois dígitos em três das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

Quádruplo-duplo (adicionado para esclarecimento)

Um quádruplo-duplo é definido como um desempenho no qual um jogador acumula um total de dois dígitos em quatro das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

A tabela "PlayerGameStats" armazena estatísticas para cada jogo que um jogador joga e tem a seguinte aparência:

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 1, 1,  1, 'Nuggets',    'Cavaliers',  6,  8,  2, 2,  0 ),
  ( 2, 1,  2, 'Nuggets',     'Clippers', 15,  7,  0, 1,  3 ),
  ( 3, 1,  6, 'Nuggets', 'Trailblazers', 11, 11,  1, 2,  1 ),
  ( 4, 1, 10, 'Nuggets',    'Mavericks',  8, 10,  2, 2, 12 ),
  ( 5, 1, 11, 'Nuggets',       'Knicks', 23, 12,  1, 0,  0 ),
  ( 6, 1, 12, 'Nuggets',         'Jazz',  8,  8, 11, 1,  0 ),
  ( 7, 1, 13, 'Nuggets',         'Suns',  7, 11,  2, 2,  1 ),
  ( 8, 1, 14, 'Nuggets',        'Kings', 10, 15,  0, 3,  1 ),
  ( 9, 1, 15, 'Nuggets',        'Kings',  9,  7,  5, 0,  4 ),
  (10, 1, 17, 'Nuggets',      'Thunder', 13, 10, 10, 1,  0 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

A saída que eu quero alcançar é assim:

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

A única solução que encontrei até agora é tão horrível que me dá vontade de vomitar... ;o)... Fica assim:

SELECT 
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10) 
                THEN 1 
                ELSE 0 
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

... e agora você provavelmente também está vomitando (ou rindo muito) depois de ler isso. Eu nem mesmo escrevi tudo o que seria necessário para obter todas as combinações duplo-duplo e omiti a declaração de caso para os triplos-duplos porque é ainda mais ridículo.

Existe uma maneira melhor de fazer isso? Seja com a estrutura de tabela que tenho ou com uma nova estrutura de tabela (eu poderia escrever um script para converter a tabela).

Posso usar MySQL 5.5 ou PostgreSQL 9.2.

Aqui está um link para SqlFiddle com dados de exemplo e minha terrível solução que postei acima: http://sqlfiddle.com/#!2/af6101/3

Observe que não estou realmente interessado em quádruplos-duplos (veja acima), pois eles não ocorrem no jogo que eu jogo até onde eu sei, mas seria uma vantagem se a consulta fosse facilmente expansível sem muito reescrever a conta para quádruplos-duplos.

Cada linha em uma tabela tem uma coluna ctid de sistema do tipo tidque representa a localização física da linha:

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid | Eu iria
:---- | -:
(0,1) | 1
(0,2) | 2

dbfiddle aqui

Qual é a melhor maneira de obter apenas o número da página ctidno tipo mais apropriado (por exemplo integer, bigintou numeric(1000,0))?

A única maneira que consigo pensar é muito feia.

Estou usando o Mysql 5.6.12 no ambiente do servidor Wamp. Agora quero registrar todas as consultas no arquivo ".log", as consultas que estão sendo executadas pelo PHP ou pelo PHPMyAdmin, quero registrá-las...

Enquanto trabalhava com o C# Entity Framework, notei uma falha na minha instância do SQL Server.

Eu fui capaz de rastreá-lo para esta declaração:

SELECT * FROM dbo.[TestTable]
where mpnr in (1099059904,
1038139906,
1048119902,
1045119902,
1002109903,
1117109910,
1111149902,
1063149902,
1117159902,
1116109904,
1105079905,
1012079906,
1129129904,
1103059905,
1065059905,
1091059906,
1110149904,
1129149903,
1083029905,
1080139904,
1076109903,
1010019902,
1058019902,
1060019903,
1053019902,
1030089902,
1018149902,
1077149902,
1010109901,
1011109901,
1000119902,
1023049903,
1107119909,
1108119909,
1106119909)

A tabela fica assim:

CREATE TABLE dbo.[TestTable]([MPNR] [numeric](9, 0) NOT NULL)

A falha ocorre toda vez que inicio a consulta. Se eu reduzir o número de valores dentro da INcláusula, funciona. (Ele não retorna nenhuma linha, é claro.)

Estou ciente de que os valores na INcláusula são números de 10 dígitos e a coluna tem apenas 9 dígitos, mas isso não deve levar a uma falha de toda a instância do SQL Server.

A versão do meu SQL Server é 2008 R2 em um Windows Server 2003 de 32 bits.

Isto é um erro conhecido? Existe um patch para o SQL Server?

Os bancos de dados geralmente são muito personalizáveis ​​com vários tipos de dados e comprimentos personalizados.

Surpreende-me, pois tento procurar a sintaxe para usar unsigned inttipos que não estão disponíveis no PostgreSQL e no MS SQL Server. MySQL e Oracle parecem.

Isso parece uma omissão gritante da parte deles - a próxima melhor opção de desempenho é um long/bigint (inteiro de 8 bytes), mas pode ser completamente desnecessário! Alguém sabe por que eles escolheriam não incluir o suporte nativo sem assinatura?

A inclusão DISTINCTno exemplo a seguir tem algum impacto no tempo de execução da consulta?
É sábio usá-lo como uma dica às vezes?

SELECT *
FROM   A
WHERE  A.SomeColumn IN (SELECT DISTINCT B.SomeColumn FROM B) 

Estou fazendo a interface de um banco de dados MySQL com PHP Data Objects (PDO) e executando uma extensa consulta SQL. Normalmente, leva cerca de 1500 ms; Ainda preciso otimizá-lo. Quando executo o script PHP duas vezes com um pequeno intervalo entre elas, a consulta leva apenas cerca de 90 ms. A consulta é a mesma em ambos os casos. Quando executo o script, com a mesma consulta, depois de algum tempo novamente, demora 1500 ms novamente.

Por que é que? O cache do banco de dados é feito automaticamente? Há algum tempo em que o banco de dados salva o cache e o exclui automaticamente?

Presumo que os resultados não possam ser armazenados em cache pelo PHP, porque isso acontece em dois threads diferentes. Eu não acho que o PHP armazenaria os resultados em cache, porque não pode saber se o banco de dados foi alterado.

Eu tenho um script rodando a cada minuto para inserir novas linhas no banco de dados. Isso também pode ser o motivo pelo qual demora 1500 ms novamente após algum tempo; o cache teria sido excluído, porque as tabelas relevantes não são mais as mesmas.