Eu tenho duas tabelas em um banco de dados PostgreSQL 9.3: Table link_replytem uma chave estrangeira chamada which_groupapontando para table link_group.
Eu quero excluir todas as linhas de link_grouponde não link_replyexiste nenhuma linha relacionada. Parece básico o suficiente, mas eu tenho lutado com isso.
Será algo simples assim (não está funcionando)?
DELETE FROM link_group WHERE link_reply = NULL;
No momento, estamos enfrentando alguns problemas de desempenho, pois nosso banco de dados está ficando muito grande. Há dados armazenados dos últimos 10 anos e não vejo motivo para que os dados com mais de 2 anos tenham que ser armazenados nas mesmas tabelas que os novos dados.
Agora, como não tenho experiência muito profunda em administrar bancos de dados, estou procurando as melhores maneiras de arquivar dados antigos.
Existem cerca de 310.000.000 registros no banco de dados no total.
O banco de dados precisa de 250 GB no disco rígido.
Já pensei em duas possibilidades:
Crie um banco de dados semelhante ao do servidor de produção e insira todos os dados antigos no novo banco de dados.
Crie um novo esquema fe [hist] com as mesmas tabelas do banco de dados de produção. Insira todos os dados antigos nessas novas tabelas no novo esquema.
desde já, obrigado
Pergunta adicional:
A tabela de arquivos recém-criada também precisaria de chaves primárias/estrangeiras?
Ou eles deveriam ter apenas as colunas, mas sem chaves/restrições?
Estou executando a consulta, que produz o plano:
Estatística IO:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 128, logical reads 5952, physical reads 576, read-ahead reads 6080, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 9, logical reads 90450, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Então, algumas perguntas
1. Por que o Statistics IO mostra leituras mais altas do que o Profiler? .
Quanto ao KB314648 , tudo bem se o Profiler relatar números mais altos do que o Statistics IO. Mas o Profiler mostra 92283as leituras na consulta descrita, na mesma execução. Isso significa que o Profiler não conta as leituras do arquivo de trabalho/tabela de trabalho?
2. Qual é a diferença entre "Worktable" e "Workfile"
Eu descobri que:
Existe alguma diferença física entre eles?
3. Por que existe "Worktable" neste caso particular?
Por que eu tenho uma mesa de trabalho, se houver 0 leituras lógicas? Está incluído nas estatísticas IO apenas porque pode ser necessário (no caso de estimativas incorretas)?
A descrição encontrada no technet parece vaga.
4. O que significa "leituras físicas" no arquivo de trabalho?
Isso significa que não havia memória suficiente concedida para a consulta, então os dados tiveram que ser gravados no disco enquanto a consulta é executada (aviso amarelo no Hash Match)? Posso presumir que sempre que vejo a tabela de trabalho/arquivo de trabalho no Statistics IO com leituras físicas, não há memória suficiente concedida para a consulta e alguns resultados intermediários da consulta tiveram que ser gravados no disco tempdb? E sempre que vejo apenas leituras lógicas, a RAM é usada?
5. Um "arquivo de trabalho" significa exatamente uma tabela que é usada para um propósito?
Se houver mais de um arquivo/mesa de trabalho, não posso saber para qual operação ele é usado.
Precisamos nos afastar desses protocolos, para usar outros mais novos e mais fortes. Em servidores Windows, você pode facilmente desabilitar esses protocolos antigos e, em vez disso, oferecer apenas TLS1.1 ou superior. No entanto, conforme observado em outro lugar , o Microsoft SQL Server 2008 R2 e o SQL Server 2012 (padrão, pelo menos) não serão iniciados se esses protocolos inferiores estiverem desabilitados. No entanto, há um número crescente de versões do MS SQL Server. Existem as edições SQL Server Standard, Business Intelligence, Enterprise, Express, Web e Compact. E, claro, há o SQL Server 2008, 2012, 2014 e (em pré-lançamento) 2016.
Qual dessas edições oferece suporte ou oferecerá suporte ao uso apenas de protocolos TLS1.1 ou superior?
Quais diretrizes devem ser consideradas para manter índices de texto completo?
Devo RECONSTRUIR ou REORGANIZAR o catálogo de texto completo (ver BOL )? O que é uma cadência de manutenção razoável? Que heurística (semelhante aos limites de fragmentação de 10% e 30%) poderia ser usada para determinar quando a manutenção é necessária?
(Tudo abaixo é simplesmente informação extra elaborando a questão e mostrando o que eu pensei até agora.)
Informações extras: minha pesquisa inicial
Existem muitos recursos sobre a manutenção do índice b-tree (por exemplo, esta questão , os scripts de Ola Hallengren e várias postagens de blog sobre o assunto em outros sites). No entanto, descobri que nenhum desses recursos fornece recomendações ou scripts para manter índices de texto completo.
Há documentação da Microsoft que menciona que desfragmentar o índice b-tree da tabela base e, em seguida, executar REORGANIZE no catálogo de texto completo pode melhorar o desempenho, mas não aborda nenhuma recomendação mais específica.
Também encontrei esta pergunta , mas ela é focada principalmente no rastreamento de alterações (como as atualizações de dados na tabela subjacente são propagadas no índice de texto completo) e não no tipo de manutenção agendada regularmente que pode maximizar a eficiência do índice.
Informações extras: testes básicos de desempenho
Este SQL Fiddle contém código que pode ser usado para criar um índice de texto completo com controle de AUTOalterações e examinar o tamanho e o desempenho da consulta do índice à medida que os dados na tabela são modificados. Quando executo a lógica do script em uma cópia dos meus dados de produção (em oposição aos dados fabricados artificialmente no violino), aqui está um resumo dos resultados que estou vendo após cada etapa de modificação de dados:
Mesmo que as declarações de atualização neste script tenham sido bastante planejadas, esses dados parecem mostrar que há muito a ganhar com a manutenção regular.
Informações extras: Ideias iniciais
Estou pensando em criar uma tarefa noturna ou semanal. Parece que esta tarefa pode executar uma RECONSTRUÇÃO ou REORGANIZAÇÃO.
Como os índices de texto completo podem ser muito grandes (dezenas ou centenas de milhões de linhas), gostaria de poder detectar quando os índices no catálogo estão fragmentados o suficiente para justificar uma RECONSTRUÇÃO/REORGANIZAÇÃO. Estou um pouco incerto sobre o que a heurística pode fazer sentido para isso.
Eu estou querendo saber como consultar a definição de uma visão materializada no Postgres. Para referência, o que eu esperava fazer é muito semelhante ao que você pode fazer para uma visualização regular:
SELECT * FROM information_schema.views WHERE table_name = 'some_view';
que lhe dá as seguintes colunas:
table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into
Isso é possível para visualizações materializadas?
Da minha pesquisa até agora, parece que as visões materializadas são deliberadamente excluídas do information_schema, porque
O information_schema só pode mostrar objetos que existem no padrão SQL.
( http://www.postgresql.org/message-id/[email protected] )
Como eles parecem estar totalmente excluídos do information_schema, não tenho certeza de como fazer isso, mas o que eu gostaria de fazer é duplo:
view_definitioncoluna em information_schema.views).
Se eu abrir uma conexão com o Postgres e emitir uma consulta de longa duração e, em seguida, interrompê-la (por exemplo, encerrar o processo do cliente que abriu a conexão), a consulta de longa duração continuará a ser executada ou será interrompida automaticamente? Isso é configurável?
(estou usando o Postgresql 9.2.9)
Estou tentando alcançar o seguinte:
California | Los Angeles, San Francisco, Sacramento
Florida | Jacksonville, Miami
Infelizmente, estou recebendo ", Los Angeles, San Francisco, Sacramento, Jacksonville, Miami"
Eu posso alcançar meus resultados desejados usando a função STUFF, mas queria saber se existe uma maneira mais limpa de fazer isso usando COALESCE?
STATE | CITY
California | San Francisco
California | Los Angeles
California | Sacramento
Florida | Miami
Florida | Jacksonville
DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + city
FROM tbl where city = 'California';
SELECT @col;
Obrigado
Parece que a velocidade de execução do T-SQL depende da latência da conexão de rede com o servidor. Presumi que, se o SQL Server não tiver nada para relatar ao cliente, ele será executado até que seja concluído, mas o teste mostra outra história.
create procedure UselessLoop
@I int
as
declare @D datetime = getdate()
while @I > 0 set @I -= 1
print datediff(millisecond, @D, getdate())
exec UselessLoop 100000
Server Milliseconds
local 53
nearby 63
faraway 660
exec UselessLoop 1000000
Server Milliseconds
local 546
nearby 640
faraway 6183
Os testes são executados no mesmo servidor de diferentes computadores usando o SSMS. O local é executado a partir do servidor, o local é executado na mesma rede local e o distante é executado a partir de outro escritório a 500 km de distância conectado com fibra de 1 gigabit.
Obviamente, há alguma comunicação acontecendo entre o SQL Server e o cliente que depende diretamente do número de instruções executadas.
Usei o Wireshark para ver o que é transportado e não posso dizer que entendo muito, mas era um tcp.stream trocando um total de 26 MB em 22740 pacotes.
Que tal uma função inútil?
create function dbo.UDFUselessLoop(@I int)
returns int
as
begin
declare @D datetime = getdate()
while @I > 0 set @I -= 1
return datediff(millisecond, @D, getdate())
end
print dbo.UDFUselessLoop(1000000)
Ele é executado em 406 milissegundos, independentemente de onde é executado. Parece que não há comunicação com o cliente no loop.
Estou executando o PostgresSQL 9.2 e tenho uma relação de 12 colunas com cerca de 6.700.000 linhas. Ele contém nós em um espaço 3D, cada um referenciando um usuário (que o criou). Para consultar qual usuário criou quantos nós, faço o seguinte (adicionado explain analyzepara obter mais informações):
EXPLAIN ANALYZE SELECT user_id, count(user_id) FROM treenode WHERE project_id=1 GROUP BY user_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=253668.70..253669.07 rows=37 width=8) (actual time=1747.620..1747.623 rows=38 loops=1)
-> Seq Scan on treenode (cost=0.00..220278.79 rows=6677983 width=8) (actual time=0.019..886.803 rows=6677983 loops=1)
Filter: (project_id = 1)
Total runtime: 1747.653 ms
Como você pode ver, isso leva cerca de 1,7 segundos. Isso não é tão ruim considerando a quantidade de dados, mas eu me pergunto se isso pode ser melhorado. Tentei adicionar um índice BTree na coluna do usuário, mas isso não ajudou em nada.
Você tem sugestões alternativas?
Por uma questão de completude, esta é a definição completa da tabela com todos os seus índices (sem restrições de chave estrangeira, referências e gatilhos):
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------
id | bigint | not null default nextval('concept_id_seq'::regclass)
user_id | bigint | not null
creation_time | timestamp with time zone | not null default now()
edition_time | timestamp with time zone | not null default now()
project_id | bigint | not null
location | double3d | not null
reviewer_id | integer | not null default (-1)
review_time | timestamp with time zone |
editor_id | integer |
parent_id | bigint |
radius | double precision | not null default 0
confidence | integer | not null default 5
skeleton_id | bigint |
Indexes:
"treenode_pkey" PRIMARY KEY, btree (id)
"treenode_id_key" UNIQUE CONSTRAINT, btree (id)
"skeleton_id_treenode_index" btree (skeleton_id)
"treenode_editor_index" btree (editor_id)
"treenode_location_x_index" btree (((location).x))
"treenode_location_y_index" btree (((location).y))
"treenode_location_z_index" btree (((location).z))
"treenode_parent_id" btree (parent_id)
"treenode_user_index" btree (user_id)
Edit: Este é o resultado, quando uso a consulta (e índice) proposta por @ypercube (a consulta leva cerca de 5,3 segundos sem EXPLAIN ANALYZE):
EXPLAIN ANALYZE SELECT u.id, ( SELECT COUNT(*) FROM treenode AS t WHERE t.project_id=1 AND t.user_id = u.id ) AS number_of_nodes FROM auth_user As u;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on auth_user u (cost=0.00..6987937.85 rows=46 width=4) (actual time=29.934..5556.147 rows=46 loops=1)
SubPlan 1
-> Aggregate (cost=151911.65..151911.66 rows=1 width=0) (actual time=120.780..120.780 rows=1 loops=46)
-> Bitmap Heap Scan on treenode t (cost=4634.41..151460.44 rows=180486 width=0) (actual time=13.785..114.021 rows=145174 loops=46)
Recheck Cond: ((project_id = 1) AND (user_id = u.id))
Rows Removed by Index Recheck: 461076
-> Bitmap Index Scan on treenode_user_index (cost=0.00..4589.29 rows=180486 width=0) (actual time=13.082..13.082 rows=145174 loops=46)
Index Cond: ((project_id = 1) AND (user_id = u.id))
Total runtime: 5556.190 ms
(9 rows)
Time: 5556.804 ms
Edit 2: Este é o resultado, quando eu uso um indexon project_id, user_id(mas sem otimização de esquema, ainda) como sugerido por @erwin-brandstetter (a consulta é executada com 1,5 segundos na mesma velocidade que minha consulta original):
EXPLAIN ANALYZE SELECT user_id, count(user_id) as ct FROM treenode WHERE project_id=1 GROUP BY user_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=253670.88..253671.24 rows=37 width=8) (actual time=1807.334..1807.339 rows=38 loops=1)
-> Seq Scan on treenode (cost=0.00..220280.62 rows=6678050 width=8) (actual time=0.183..893.491 rows=6678050 loops=1)
Filter: (project_id = 1)
Total runtime: 1807.368 ms
(4 rows)