MySQL: organização de tabelas para conjuntos muito grandes com alta frequência de atualização

Question

Estou enfrentando um dilema na escolha do meu aplicativo de esquema do MySQL. Então, antes de começar, aqui está uma imagem extremamente simplificada do meu banco de dados:

Esquema aqui: link

Em uma frase: para cada cliente, o aplicativo coleta dados de texto e tags anexadas a cada dado coletado.

Como aproximação do uso de cada tabela, aqui está o que eu espero:

• cliente: ~ 5000, não deve crescer rapidamente
• dados: 5 milhões por cliente, podem dobrar ou triplicar para grandes clientes.
tag
• : ~ 1000, tamanho bastante fixo
• data_tag: centenas de milhões por cliente facilmente. Cada dado pode ser marcado muito.

O processo de coleta é permanente, o que significa que a cada 15 minutos novos dados chegam e são marcados, o que requer uma atualização de índice muito constante.

Muitas das minhas consultas são uma CONTAGEM DE SELEÇÃO DE DADOS entre DATAS específicas e marcadas com um TAG específico em um CLIENTE específico (muito raramente envolverá vários clientes).

Aqui está a situação, você pode imaginar com esse tipo de volume de dados que estou enfrentando um desafio em termos de organização e indexação de dados. Mais uma vez, é uma versão muito minimalista e simplificada da minha estrutura. Minha pergunta é: é melhor:

1. para ficar com esse modelo e gerenciar a otimização de índices malucos? (que envolve potencialmente ter bilhões de linhas na tabela data_tag)
2. muda o esquema e usa uma tabela de dados e uma tabela data_tag por cliente? (o que envolve ter 5000 tabelas no meu banco de dados)

Estou executando tudo isso em um servidor dedicado do MySQL 5.0 (quad-core, 8Go of ram) replicado. Eu só uso o InnoDB, eu também tenho outro servidor que executa o Sphinx. Então, sabendo de tudo isso, mal posso esperar para ouvir sua opinião sobre isso.

Obrigado.

editar

Graças às suas respostas, percebo como esses números são loucos. Portanto, aqui está um uso mais realista e atualizado das tabelas (com base no servidor real que é apenas uma caixa de espaço rackspace).

• cliente: 2000 (fixo)
• dados: 1 milhão por cliente (fixo, arquivamento de dados antigos. E muito injusto: alguns clientes têm poucos milhares, os maiores 5 milhões)
tag
• : 1000 (fixo)
• data_tag: ~ 3 ou 5 milhões por cliente (depende de dados, tão injusto também).

Obrigado.

    

Answer 1

Meus 2 centavos baseados na minha experiência com o MySQL por muitos anos é que sua última opção soa mais lógica e realista.

Ir com um dado e um data_tag por cliente tem uma capacidade de gerenciamento geral mais simples do que seu esquema atual. Codificar sua segunda opção também será mais simples.

Você pode pedir muito mais especialistas do MySQL; sua segunda opção é a melhor.

Eu posso entrar em detalhes se você quiser, essa é uma resposta simples para uma pergunta simplificada para um grande problema. vai nos dois sentidos

    

Answer 2

Sem saber muito sobre seu aplicativo além do que você colocou aqui, é um pouco difícil dizer. Seu modelo de dados é bastante simplista e isso é para seu benefício, já que você espera, literalmente, bilhões de linhas. Eu evitaria criar mais de 5k tabelas como você provavelmente vai correr em problemas de descritor de arquivo e limitações de cache no caminho, se você tentar isso.

Concedido, você pode provavelmente ulimit / configure-os, ainda não é uma configuração ideal.

Você também está criando índices em dados não chave? Essas colunas de nome, por exemplo? Isso pode atrasar seu desempenho de gravação, de modo que seus trabalhos em lote de 15 minutos sejam armazenados em backup.

Honestamente, se esta fosse a minha candidatura, eu olharia para duas possíveis soluções:

1. Vá com o que você tem agora e divida os clientes entre vários servidores MySQL se o desempenho se tornar um problema. A menos que você tenha esses dados e esses clientes alinhados, ainda não é um problema. Não gaste muito tempo projetando para "e se". Continue com o esquema simplista e introduza seu primeiro conjunto de usuários no primeiro servidor. Quando você começa a obter capacidade, introduza um segundo servidor e isole esses novos usuários para esse banco de dados. Sharding, por assim dizer. Faça backup com monitoramento de recursos e boas técnicas de administração para saber quando essa linha de "capacidade" está se aproximando.

2. Algo como o Cassandra ou o MongoDB funcionaria? Não sei o suficiente sobre suas dúvidas para sugerir ou descartar. O MongoDB pode ser uma opção. Vale a pena conferir.

Então, em suma, deixe o MySQL fazer o que ele faz bem, apenas execute mais deles. Ou, se possível, olhe para algo como Mongo.

    

Answer 3

Hm, da minha experiência - você tem certeza que o MySQL é o melhor banco de dados para isso? Tentei ver o Oracle ou o SQL Server (embora o cluster oracle possa ter uma vantagem aqui)?

Se você acha que o custo de licenciamento vai te matar, deixe-me apenas dizer que você ainda não tem uma ideia do hardware necessário para executá-lo. Assim que você obtiver as primeiras ofertas para o SAN, você precisará disso - você provavelmente rirá do preço do software correspondente.

Apenas uma ideia.

• Cliente - digamos 10.000, conforme você indica que vai crescer rapidamente.
• Dados - vamos supor 7 milhões para um cliente médio. Isso já é 70 bilhões de linhas para a tabela de dados. Sim, desculpe, os 4 zeros realmente adicionam isso.
• Se você receber 10 tags por dados (você não indica nada), falamos de perto de 700 bilhões de linhas para o campo data_tag.

Fica mais louco.

• Se o DataTag não tiver nenhum índice e nenhuma sobrecarga (que ele possui), data: tag é 10 bytes por entrada - 2 para o tag_id (65536 é suficiente), infelizmente 8 para o data_id - você não pode endereçar 700 bilhões de entradas 4 bytes. Este é um total de cerca de 7800 gigabytes de dados brutos (700.000.000.000 * 12/1024/1024/1024). Indexação POSSIBILMENTE duplica isso.

Para processar isso com eficiência, essa é uma SAN DE ALTA RESISTÊNCIA. Nós não falamos de "10 discos" aqui, falamos de um SAN de alto nível com possivelmente 400 discos ascendentes para lidar com todos esses dados - não esqueça até agora que não temos realmente nenhum índice.

I'm running all of this on a MySQL 5.0 dedicated server (quad-core, 8Go of ram) replicated.

Boa tentativa. Isso é bom para exatamente o que? Desculpe perguntar, mas 8GB RAM não vai realmente ajudar (não impressionado aqui), vá para uma máquina de 256GB ... O que provavelmente requer AMD e um daqueles realmente caro Opteron 8000. Mas você vai precisar da RAM.

De qualquer forma, isso seria (duvido que você representasse adequadamente os fatos) uma das maiores instalações de banco de dados do mundo.

Você DEFINITIVAMENTE deseja algo que possa lidar com isso - o clustering do Oracle ou o clustering do SQL Server pode funcionar acelerando isso se você realmente tiver que fazer isso. Isso é muito acima do que bancos de dados livres podem até pensar em manipular. Realmente.

E você precisa de procedimentos de backup adequados (que o MySQL não possui). Você também pode AMAR SQL Serve 2008 Data Page Compressão que pode reduzir o tamanho dos dados em cerca de 50% em disco. Não apenas pelos custos salvos em disco, mas porque significa menos IO - o que se traduz diretamente em mais desempenho aqui (já que você não pode armazenar em cache a tabela na memória).

Por mais que eu odeie dizer isso, você também pode querer considerar o uso do IBM DB2 em um Mainframe agradável - e eu não quero dizer rodando uma VM Linux nele. O VMS é imensamente superior para lidar com bancos de dados de superescala devido à arquitetura de hardware. Não pergunte sobre o preço;)