All perguntas(dba)

Estou montando um sistema SaaS, onde planejamos dar a cada cliente seu próprio banco de dados. O sistema já está configurado para que possamos expandir facilmente para servidores adicionais se a carga se tornar muito grande; esperamos ter milhares, ou mesmo dezenas de milhares de clientes.

Perguntas

• Existe alguma limitação prática no número de micro-bancos de dados que você pode/deve ter em um SQL Server?
• Isso pode afetar o desempenho do servidor?
• É melhor ter 10.000 bancos de dados de 100 MB cada ou um banco de dados de 1 TB?

Informação adicional

Quando digo "microbancos de dados", não quero dizer "micro"; Quero dizer apenas que estamos visando milhares de clientes, de modo que cada banco de dados individual seria apenas um milésimo ou menos do armazenamento total de dados. Na realidade, cada banco de dados teria cerca de 100 MB, dependendo de quanto uso ele recebe.

A principal razão para usar 10.000 bancos de dados é a escalabilidade. O fato é que a V1 do sistema tem um banco de dados e tivemos alguns momentos desconfortáveis ​​quando o banco de dados estava sobrecarregado.

Estava sobrecarregando CPU, memória, E/S - todos os itens acima. Apesar de termos corrigido esses problemas, eles nos fizeram perceber que em algum momento, mesmo com a melhor indexação do mundo, se formos tão bem-sucedidos quanto esperamos, simplesmente não podemos colocar todos os nossos dados em um grande honkin ' base de dados. Portanto, para a V2, estamos fragmentando, para que possamos dividir a carga entre vários servidores de banco de dados.

Passei o último ano desenvolvendo essa solução fragmentada. É uma licença por servidor, mas de qualquer forma isso é resolvido, pois estamos usando VMs no Azure. A razão pela qual a questão surge agora é porque anteriormente estávamos oferecendo apenas para grandes instituições e montando cada uma por nós mesmos. Nossa próxima ordem de negócios é um modelo de autoatendimento em que qualquer pessoa com um navegador pode se inscrever e criar seu próprio banco de dados. Seus bancos de dados serão muito menores e muito mais numerosos do que as grandes instituições.

Tentamos os Pools Elásticos do Banco de Dados SQL do Azure . O desempenho foi muito decepcionante, então voltamos para as VMs normais.

Conforme descrito aqui nos documentos do MySQL :

A coluna filtrada indica uma porcentagem estimada de linhas da tabela que serão filtradas pela condição da tabela. Ou seja, linhas mostra o número estimado de linhas examinadas e linhas × filtradas / 100 mostra o número de linhas que serão unidas às tabelas anteriores. Antes do MySQL 5.7.3, esta coluna é exibida se você usar EXPLAIN EXTENDED. A partir do MySQL 5.7.3, a saída estendida é habilitada por padrão e a palavra-chave EXTENDED é desnecessária.

Eu ainda não entendo. Qual é o significado de "filtrado" aqui? Que informações podemos obter desta coluna?

Por exemplo, quando começo a consultar, algumas consultas mostrarão 100 e outras mostrarão 18 ou qualquer coisa menor que 100.

+-------------+-------+--------+---------+---------+------+----------+
| select_type | table | type   | key     | key_len | rows | filtered |
+-------------+-------+--------+---------+---------+------+----------+
| PRIMARY     | a     | range  | search  | 4       |  174 |   18.00  | <--
| PRIMARY     | b     | eq_ref | PRIMARY | 4       |    1 |   100.00 |
| PRIMARY     | c     | ALL    | PRIMARY | 4       |    1 |   100.00 |

Qual é o ponto principal que podemos concluir desse valor?

Está dizendo que a coluna filtrou apenas 18%? Ou está dizendo que quanto menor a pontuação, melhor o índice ou a consulta?

Estou usando o MySQL 5.7.

Tenho uma dúvida relacionada ao desempenho. Digamos que eu tenha um usuário com o primeiro nome Michael. Faça a seguinte consulta:

UPDATE users
SET first_name = 'Michael'
WHERE users.id = 123

A consulta realmente executará a atualização, mesmo que esteja sendo atualizada para o mesmo valor? Se sim, como faço para evitar que isso aconteça?

Por que essa consulta simples recebe tanta memória?

-- Demo table
CREATE TABLE dbo.Test
(
    TID integer IDENTITY NOT NULL,
    FilterMe integer NOT NULL,
    SortMe integer NOT NULL,
    Unused nvarchar(max) NULL,

    CONSTRAINT PK_dbo_Test_TID
    PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
    (FilterMe, SortMe)
SELECT TOP (100 * 1000)
    CHECKSUM(NEWID()) % 1000,
    CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO    
-- Query
SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe;

Para cerca de 50 linhas, o otimizador reserva quase 500 MB para a classificação:

A caixa de diálogo pgAdmin para adicionar novas conexões de banco de dados solicita um "DB de manutenção" .
Para poder me conectar, defino-o para o banco de dados que quero conectar (e também tenho o direito de conectar).

Então, por que é chamado de "DB de manutenção" em vez de "DB" ou "Banco de dados"?.

Estou procurando as melhores práticas para lidar com trabalhos agendados do SQL Server Agent em grupos de disponibilidade do SQL Server 2012. Talvez eu tenha perdido alguma coisa, mas no estado atual sinto que o SQL Server Agent não está realmente integrado a esse ótimo recurso do SQL2012.

Como posso tornar um trabalho de agente SQL agendado ciente de uma alternância de nó? Por exemplo, eu tenho um trabalho em execução no nó primário que carrega dados a cada hora. Agora, se o primário ficar inativo, como posso ativar o trabalho no secundário que agora se torna primário?

Se eu agendar o trabalho sempre no secundário, ele falha porque o secundário é somente leitura.

Eu tenho uma instância do PostgreSQL 9.2 em execução no RHEL 6.3, máquina de 8 núcleos com 16 GB de RAM. O servidor é dedicado a este banco de dados. Dado que o postgresql.conf padrão é bastante conservador em relação às configurações de memória, achei que seria uma boa idéia permitir que o Postgres usasse mais memória. Para minha surpresa, seguir o conselho em wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server diminuiu significativamente praticamente todas as consultas que eu executo, mas é obviamente mais perceptível nas consultas mais complexas.

Eu também tentei executar o pgtune que deu a seguinte recomendação com mais parâmetros ajustados, mas isso não mudou nada. Ele sugere shared_buffers de 1/4 do tamanho da RAM, o que parece estar de acordo com os conselhos de outros lugares (e no PG wiki em particular).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

Tentei reindexar todo o banco de dados depois de alterar as configurações (usando reindex database), mas isso também não ajudou. Eu brinquei com shared_buffers e work_mem. Mudá-los gradualmente dos valores padrão muito conservadores (128k / 1 MB) diminuiu gradualmente o desempenho.

Executei EXPLAIN (ANALYZE,BUFFERS)algumas consultas e o culpado parece ser que o Hash Join é significativamente mais lento. Não está claro para mim o porquê.

Para dar um exemplo específico, tenho a seguinte consulta. Ele é executado em ~2100ms na configuração padrão e ~3300ms na configuração com tamanhos de buffer aumentados:

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS)para a consulta acima:

• Buffers padrão: http://explain.depesz.com/s/xaHJ
• Buffers maiores: http://explain.depesz.com/s/Plk

A questão é por que estou observando um desempenho reduzido quando aumento os tamanhos de buffer? A máquina definitivamente não está ficando sem memória. Alocação se a memória compartilhada no SO for ( shmmaxe shmall) estiver configurada para valores muito grandes, isso não deve ser um problema. Também não estou recebendo nenhum erro no log do Postgres. Estou executando o autovacuum na configuração padrão, mas não espero que tenha algo a ver com isso. Todas as consultas foram executadas na mesma máquina com poucos segundos de intervalo, apenas com configuração alterada (e PG reiniciado).

Edit: Acabei de encontrar um fato particularmente interessante: quando executo o mesmo teste no meu iMac de meados de 2010 (OSX 10.7.5) também com Postgres 9.2.1 e 16 GB de RAM, não sinto a lentidão. Especificamente:

set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

Quando faço exatamente a mesma consulta (aquela acima) com exatamente os mesmos dados no servidor, recebo 2100 ms com work_mem=1MB e 3200 ms com 96 MB.

O Mac tem SSD, então é compreensivelmente mais rápido, mas exibe um comportamento que eu esperaria.

Veja também a discussão de acompanhamento sobre pgsql-performance .

Eu salvei anteriormente uma cópia do diretório /var/lib/mysql/ddms ("ddms" é o nome do esquema). Agora instalei um novo MySQL em um Ubuntu 10.04.3 LTS recém-instalado executando apt-get install mysql-server, acredito que a versão 5.1 foi instalada. Depois de copiar o diretório ddms em /var/lib/mysql, algumas de suas tabelas funcionam bem, estas são as tabelas com um conjunto associado de três arquivos: um arquivo .frm, um arquivo .MYD e um arquivo .MYI.

No entanto, existem duas tabelas com um conjunto diferente de arquivos: um arquivo .frm e um arquivo .ibd. Essas duas tabelas não apareceram na lista de tabelas no phpMyAdmin. Quando eu olho para o log de erros, ele diz:

[ERROR] Cannot find or open table ddms/dictionary_item from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Por favor, ajude a restaurar essas duas tabelas. Obrigado.

Eu tenho uma tabela com coluna de identidade diga:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

É bem conhecido, que este

select * into copy_from_with_id_1 from with_id;

resulta em copy_from_with_id_1 com identidade no id também.

A seguinte pergunta de estouro de pilha menciona a listagem de todas as colunas explicitamente.

Vamos tentar

select id, val into copy_from_with_id_2 from with_id;

Ops, mesmo neste caso id é uma coluna de identidade.

O que eu quero é uma mesa como

create table without_id (
 id int,
 val varchar(30)
);

Antes do Oracle 11.2, eu usava uma função de agregação personalizada para concatenar uma coluna em uma linha. 11.2 Adicionada a LISTAGGfunção, então estou tentando usá-la. Meu problema é que preciso eliminar duplicatas nos resultados e não consigo fazer isso.

Aqui está um exemplo.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual 
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

O que eu quero ver é isso:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Aqui está uma listaggversão que está próxima, mas não elimina duplicatas.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;

Eu tenho uma solução, mas é pior do que continuar usando a função de agregação personalizada.