All perguntas(dba)

Esta resposta SO altamente votada recomenda colocar imagens em tabelas separadas, mesmo que haja apenas uma relação 1:1 com outra tabela:

Se você decidir colocar suas imagens em uma tabela do SQL Server, eu recomendo usar uma tabela separada para armazenar essas imagens - não armazene a foto do funcionário na tabela do funcionário - mantenha-as em uma tabela separada. Dessa forma, a tabela Employee pode permanecer enxuta, média e muito eficiente, supondo que você nem sempre precise selecionar a foto do funcionário também como parte de suas consultas.

Por quê? Fiquei com a impressão de que o SQL Server armazena apenas um ponteiro para alguma estrutura de dados BLOB dedicada na tabela, então por que se preocupar em criar manualmente outra camada de indireção? Realmente melhora o desempenho significativamente? Se sim, por quê?

Gostaria de obter estatísticas sobre o número máximo de conexões durante um período de tempo.

Conheço a pg_stat_activityvisão, tipo select count(*) from pg_stat_activity, mas acho que esse método não é muito inteligente.

Existem outras visualizações ou tabelas que podem fornecer as informações de que preciso?

Minha pergunta é baseada nisso: https://stackoverflow.com/q/35575990/5089204

Para dar uma resposta, fiz o seguinte cenário de teste.

cenário de teste

Primeiro eu crio uma tabela de teste e a preencho com 100.000 linhas. Um número aleatório (0 a 1000) deve levar a aproximadamente 100 linhas para cada número aleatório. Esse número é colocado em uma coluna varchar e como um valor em seu XML.

Aí eu faço uma chamada como o OP aí precisa com .exist() e com .nodes() com uma pequena vantagem para o segundo, mas ambos demoram de 5 a 6 segundos. Na verdade, faço as chamadas duas vezes: uma segunda vez na ordem trocada e com parâmetros de pesquisa ligeiramente alterados e com "//item" em vez do caminho completo para evitar falsos positivos por meio de resultados ou planos em cache.

Então eu crio um índice XML e faço as mesmas chamadas

Agora - o que realmente me surpreendeu! - o .nodescaminho completo é muito mais lento do que antes (9 segundos), mas .exist()é reduzido para meio segundo, com caminho completo até cerca de 0,10 segundos. (enquanto .nodes()com caminho curto é melhor, mas ainda muito atrás .exist())

Perguntas:

Meus próprios testes resumem: os índices XML podem explodir extremamente um banco de dados. Eles podem acelerar as coisas extremamente (s. edit 2), mas também podem retardar suas consultas. Gostaria de entender como funcionam... Quando se deve criar um índice XML? Por que .nodes()com um índice pode ser pior do que sem? Como alguém poderia evitar o impacto negativo?

CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY, SomeData VARCHAR(100),XmlColumn XML);
GO

DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));

INSERT INTO #testTbl VALUES('Data_' + @RndNumber,
'<error application="application" host="host" type="exception" message="message" >
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="My test ' +  @RndNumber + '" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>');

GO 100000

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO

CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO

DROP TABLE #testTbl;

EDIÇÃO 1 - Resultados

Este é um resultado com o SQL Server 2012 instalado localmente em um laptop médio Neste teste não consegui reproduzir o impacto extremamente negativo no NodesFullPath_with_index, embora seja mais lento do que sem o índice ...

NodesFullPath_no_index    6.067
ExistFullPath_no_index    6.223
ExistShortPath_no_index   8.373
NodesShortPath_no_index   6.733

NodesFullPath_with_index  7.247
ExistFullPath_with_index  0.217
ExistShortPath_with_index 0.500
NodesShortPath_with_index 2.410

EDIT 2 Teste com XML maior

De acordo com a sugestão de TT, usei o XML acima, mas copiei os item-nodes para chegar a cerca de 450 itens. Deixei o hit-node bem alto no XML (porque acho que .exist()isso pararia no primeiro hit, enquanto .nodes()continuaria)

A criação do índice XML aumentou o arquivo mdf para ~ 21 GB, ~ 18 GB parecem pertencer ao índice (!!!)

NodesFullPath_no_index    3min44
ExistFullPath_no_index    3min39
ExistShortPath_no_index   3min49
NodesShortPath_no_index   4min00

NodesFullPath_with_index  8min20
ExistFullPath_with_index  8,5 seconds !!!
ExistShortPath_with_index 1min21
NodesShortPath_with_index 13min41 !!!

Eu tenho uma seleção simples distinta em alguns dados de séries temporais:

SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';

E leva 112 segundos. Aqui está o plano de consulta:

http://explain.depesz.com/s/NTyA

Meu aplicativo tem que realizar muitas operações distintas e contagens como esta. Existe uma maneira mais rápida de obter esse tipo de dados?

Estou tentando executar o seguinte comando:

sshpass  -p "pass" ssh  [email protected] "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db

Eu recebo:

failed: FATAL:  database "new_db" does not exist

Cenário : Você recebe um backup de banco de dados e é instruído a restaurá-lo em um servidor (que já está hospedando outros bancos de dados), mas não recebe nenhuma informação útil sobre o que o backup contém ou se a fonte deve ser confiável.

Pergunta 1 : Quais são as possíveis implicações de restaurar um backup que pode ser malicioso?

Pergunta 2 : O que você pode fazer para proteger seu servidor/os dados em outros bancos de dados do impacto da restauração de um backup potencialmente malicioso? RESTORE VERIFYONLYparece ser um bom primeiro passo. A resposta final provavelmente é 'restaurar o banco de dados em uma VM de sandbox sem acesso ao mundo externo', mas vamos supor que essa opção esteja fora de questão. O que mais deve ser feito nesta situação?

Eu tenho um banco de dados onde carrego arquivos em uma tabela de teste, dessa tabela de teste tenho 1-2 junções para resolver algumas chaves estrangeiras e depois insiro essas linhas na tabela final (que tem uma partição por mês). Eu tenho cerca de 3,4 bilhões de linhas para três meses de dados.

Qual é a maneira mais rápida de obter essas linhas do teste para a mesa final? Tarefa de fluxo de dados do SSIS (que usa uma exibição como origem e tem carregamento rápido ativo) ou um comando Insert INTO SELECT .... ? Eu tentei a tarefa de fluxo de dados e posso obter cerca de 1 bilhão de linhas em cerca de 5 horas (8 núcleos / 192 GB de RAM no servidor), o que parece muito lento para mim.

Como desinstalar o SQL Server Management Studio 2012?

O "Desinstalar ou alterar programas" do ControlPanel (no Windows 7 Prof) fornece uma linha de entradas:

enquanto os resultados da pesquisa na Internet sempre informam : "clique em Microsoft SQL Server 2005 e clique em Alterar"

ATUALIZAÇÃO (comentando a resposta de Shawn Melton ):

Ao escolher " "Microsoft SQL Server 2012 (64 bits)" --> Desinstalar/Alterar, observo:

e depois de escolher "Remover", "Configurar regras de suporte" sem qualquer SSMS ou opção de continuação:

Estamos executando um site (Moodle) que os usuários atualmente acham lento. Acho que rastreei o problema do MySQL criando tabelas temporárias no disco. Eu observo a variável created_tmp_disk_tablesna administração do servidor Mysql Workbench e o número aumenta com aproximadamente 50 tabelas/s. Após um dia de uso, created_tmp_disk_tablesé >100k. Além disso, a memória não parece ser liberada. O uso continua aumentando até que o sistema se torne praticamente inutilizável e tenhamos que reiniciar o MySQL. Eu preciso reiniciá-lo quase todos os dias e ele começa usando cerca de 30-35% da memória disponível e terminando o dia com 80%.

Eu não tenho blobs no banco de dados e também não tenho controle sobre as consultas, então não posso tentar otimizá-las. Também usei o Percona Configuration Wizard para gerar um arquivo de configuração, mas o my.ini também não resolveu meu problema.

Perguntas

1. O que devo mudar para impedir o MySQL de criar tabelas temporárias no disco? Existem configurações que eu preciso alterar? Devo jogar mais memória nele?

2. Como posso impedir o MySQL de consumir minha memória?

Editar

Ativei slow_querieso log e descobri que a consulta SELECT GET_LOCK() foi registrada como lenta. Uma pesquisa rápida revelou que eu havia permitido conexões persistentes na configuração do PHP ( mysqli.allow_persistent = ON). Eu desliguei isso. Isso reduziu a taxa na qual o MySQL consome memória. Ele ainda está criando tabelas temporárias.

Eu também verifiquei se o key_buffer sizeé grande o suficiente. Eu olhei para a variável key_writes. Isso deve ser zero. Se não, aumente o key_buffer_size.Eu tenho zero key_readse zero key_writes, então suponho que o key_buffer_sizeé grande o suficiente.

Aumentei o tmp_table_sizee max-heap-table-sizepara 1024M pois um aumento em created_tmp_disk_tables pode indicar que as tabelas não cabem na memória. Isso não resolveu.

Ref: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

Editar 2

Se você vir muitos sort_merge_passespor segundo na saída SHOW GLOBAL STATUS, considere aumentar o sort_buffer_sizevalor. Eu tinha 2 sort_merge_passesem uma hora, então considero sort_buffer_sizeque é grande o suficiente.

Ref: Manual do MySQL emsort_buffer_size

Editar 3

Modifiquei os buffers de classificação e junção, conforme sugerido por @RolandoMySQLDBA. O resultado é exibido na tabela abaixo, mas acho que created_tmp_tables_on_diskainda é alto. Reiniciei o servidor mysql depois de alterar o valor e verifiquei created_tmp_tables_on_diskdepois de um dia (8h) e calculei a média. Alguma outra sugestão? Parece-me que há algo que não cabe dentro de algum tipo de recipiente, mas não consigo descobrir o que é.

+---------------------+-------------+-------------+--------------------+
| Tmp_table_size,     | Sort_buffer | Join_buffer | No of created      |
| max_heap_table_size |             |             | tmp_tables on disk |
+---------------------+-------------+-------------+--------------------+
| 125M                | 256K        | 256K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 512K        | 512K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 1M          | 1M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 4M          | 4M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+   
                                                                  

Esta é a minha configuração:

+-----------------------+-----------------------+
|DATABASE SERVER        |WEB SERVER             |
+-----------------------+-----------------------+
|Windows Server 2008 R2 |Windows Server 2008 R2 |
+-----------------------+-----------------------+
|MySQL 5.1.48           |IIS 7.5                |
+-----------------------+-----------------------+
|4 Core CPU             |4 Core CPU             |
+-----------------------+-----------------------+
|4GB RAM                |8GB RAM                |
+-----------------------+-----------------------+

Informação adicional

+--------------------+---------+
|PARAM               |VALUE    |
+--------------------+---------+
|Num of tables in Db |361      |
+--------------------+---------+
|Size of database    |2.5G     |
+--------------------+---------+
|Database engine     |InnoDB   |
+--------------------+---------+
|Read/write ratio    |3.5      |
|(Innodb_data_read/  |         |
|innodb_data_written)|         |
+--------------------+---------+
|Avg table size      |15k rows |
+--------------------+---------+
|Max table size      |744k rows|
+--------------------+---------+

Esta configuração foi dada a mim, então eu tenho controle limitado sobre ela. O servidor da web está usando muito pouca CPU e RAM, então excluí essa máquina como um gargalo. A maioria das configurações do MySQL se origina de uma ferramenta de geração automática de configuração.

Eu monitorei o sistema usando PerfMon durante alguns dias representativos. A partir disso, concluo que não é o sistema operacional que está trocando para o disco.

My.ini

[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/DBs/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=125
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
table-definition-cache= 1024
max-heap-table-size= 32M
thread_cache_size=38

MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=1024K
read_rnd_buffer_size=256K
sort_buffer_size=1024K
join_buffer_size=1024K


INNODB Specific options
innodb_data_home_dir="D:/DBs/"
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=8

Suponha que eu tenha que exportar dados de um servidor para outro (por meio de servidores vinculados). Qual declaração será mais eficiente?

Executando no servidor de origem:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Ou executando no servidor de destino:

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Qual deles será mais rápido e consumirá menos recursos no total (servidor de origem e de destino)? Ambos os servidores são SQL Server 2005.