Não estou muito familiarizado com suas necessidades, mas talvez armazenar cada ponto de dados no banco de dados seja um pouco exagerado. Parece quase como a abordagem de armazenar uma biblioteca de imagens armazenando cada pixel como um registro separado em um banco de dados relacional.

Como regra geral, armazenar dados binários em bancos de dados é errado na maioria das vezes. Geralmente há uma maneira melhor de resolver o problema. Embora não seja inerentemente errado armazenar dados binários em banco de dados relacional, muitas vezes as desvantagens superam os ganhos. Bancos de dados relacionais, como o nome alude, são mais adequados para armazenar dados relacionais. Dados binários não são relacionais. Ele adiciona tamanho (geralmente de forma significativa) aos bancos de dados, pode prejudicar o desempenho e pode levar a dúvidas sobre a manutenção de instâncias MySQL de bilhões de registros. A boa notícia é que existem bancos de dados especialmente adequados para armazenar dados binários. Um deles, embora nem sempre aparente, é o seu sistema de arquivos! Basta criar uma estrutura de nomeação de diretório e arquivo para seus arquivos binários,

Outra abordagem seria usar um sistema de armazenamento baseado em documentos para seus dados de pontos de dados (e talvez espectros) e usar o MySQL para as execuções (ou talvez colocar as execuções no mesmo banco de dados que os outros).

Certa vez, trabalhei com um banco de dados MySQL muito grande (Terabyte +). A maior tabela que tínhamos tinha literalmente mais de um bilhão de linhas. Isso estava usando o MySQL 5.0, então é possível que as coisas tenham melhorado.

Funcionou. O MySQL processou os dados corretamente na maioria das vezes. Embora fosse extremamente difícil de manejar. (Se você quer disponibilidade de nível seis sigma com um terabyte de dados, não use MySQL. Nós éramos uma startup que não tinha DBA e fundos limitados.)

Apenas fazer backup e armazenar os dados era um desafio. Levaria dias para restaurar a mesa se precisássemos.

Tínhamos várias tabelas na faixa de 10 a 100 milhões de linhas. Quaisquer associações significativas às tabelas consumiam muito tempo e levariam uma eternidade. Então, escrevemos procedimentos armazenados para 'andar' pelas tabelas e processar junções contra intervalos de 'id's. Dessa forma, processaríamos os dados de 10 a 100.000 linhas por vez (Junte-se aos id's 1-100.000 e depois 100.001-200.000, etc). Isso foi significativamente mais rápido do que entrar na mesa inteira.

Usar índices em tabelas muito grandes que não são baseadas na chave primária também é muito mais difícil. O Mysql 5.0 armazena índices em duas partes -- ele armazena índices (além do índice primário) como índices para os valores da chave primária. Assim, as pesquisas indexadas são feitas em duas partes: primeiro o MySQL vai para um índice e extrai dele os valores da chave primária que precisa encontrar, depois faz uma segunda pesquisa no índice da chave primária para descobrir onde estão esses valores.

O resultado disso é que, para tabelas muito grandes (1-200 milhões mais linhas), a indexação em relação às tabelas é mais restritiva. Você precisa de menos índices mais simples. E fazer comandos select simples que não estão diretamente em um índice pode nunca mais voltar. Onde as cláusulas devem atingir os índices ou esquecê-lo.

Mas tudo o que foi dito, as coisas realmente funcionaram. Conseguimos usar o MySQL com essas tabelas muito grandes e fazer cálculos e obter respostas corretas.

Tentar fazer análises em 200 bilhões de linhas de dados exigiria hardware de ponta e muita mão de obra e paciência. Apenas manter o backup dos dados em um formato que você possa restaurar seria um trabalho significativo.

Concordo com a resposta de srini.venigalla de que normalizar os dados como um louco pode não ser uma boa ideia aqui. Fazer junções em várias tabelas com tantos dados abrirá você para o risco de tipos de arquivos , o que pode significar que algumas de suas consultas nunca mais voltarão. A desnormalização com chaves simples e inteiras lhe daria uma chance melhor de sucesso.

Tudo o que tínhamos era InnoDB. Sobre MyISAM vs. InnoDB: O principal seria não misturar os dois. Você não pode realmente otimizar um servidor para ambos por causa da maneira como o MySQL armazena em cache as chaves e outros dados. Escolha um ou outro para todas as mesas de um servidor, se puder. O MyISAM pode ajudar com alguns problemas de velocidade, mas pode não ajudar com o trabalho geral do DBA que precisa ser feito - o que pode ser um matador.

normalizando os dados como um louco

Normalizar os dados como um louco pode não ser a estratégia certa neste caso. Mantenha suas opções abertas armazenando os dados tanto na forma Normalizada quanto na forma de visualizações materializadas altamente adequadas à sua aplicação. A chave nesse tipo de aplicativo NÃO é escrever consultas ad hoc. A modelagem de consulta é mais importante que a modelagem de dados. Comece com suas consultas de destino e trabalhe em direção ao modelo de dados ideal.

Is this reasonable?

Eu também criaria uma tabela plana adicional com todos os dados.

run_id | spectrum_id | data_id | <data table columns..> |

Usarei esta tabela como a fonte primária de todas as consultas. O motivo é evitar ter que fazer junções. Junções sem indexação tornarão seu sistema muito inutilizável, e ter índices em arquivos tão grandes será igualmente terrível.

A estratégia é consultar primeiro a tabela acima, despejar os resultados em uma tabela temporária e juntar a tabela temporária com as tabelas de pesquisa de Run e Spectrum e obter os dados desejados.

Você analisou suas necessidades de gravação versus necessidades de leitura? Será muito tentador abandonar o SQL e ir para mecanismos de armazenamento de dados não padrão. Na minha opinião, deve ser o último recurso.

Para acelerar as velocidades de gravação, você pode tentar o método Handler Socket. Percona, se bem me lembro, empacota Handler Socket em seu pacote de instalação. (sem relação com Percona!)

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

A resposta curta é um sim qualificado -- à medida que o número de linhas aumenta, o esquema preciso, os tipos de dados e as operações que você escolhe aumentam em importância.

O quanto você normaliza seus dados depende das operações que planeja realizar nos dados armazenados. Sua tabela de 'pontos de dados' em particular parece problemática - você está planejando comparar o enésimo ponto de qualquer espectro com o m de qualquer outro? Caso contrário, armazená-los separadamente pode ser um erro. Se seus pontos de dados não estiverem sozinhos, mas fizerem sentido apenas no contexto de seus espectros associados, você não precisará de uma CHAVE PRIMÁRIA - uma chave estrangeira para os espectros e uma coluna 'nth' (sua coluna 'índice'?) .

Defina as operações inter e intra-espectro que você deve realizar e, em seguida, descubra a maneira mais barata de realizá-las. Se a igualdade for tudo o que é necessário, eles podem ser desnormalizados -- possivelmente com alguns metadados estatísticos pré-calculados que auxiliam suas operações. Se você realmente precisar de acesso in-SQL a pontos de dados individuais, certifique-se de reduzir o tamanho de cada linha ao número mínimo de campos e ao menor tipo de dados possível.

O maior MySQL que já gerenciei pessoalmente foi de ~ 100 milhões de linhas. Nesse tamanho, você deseja manter suas linhas e, portanto, seus campos de tamanho fixo - isso permite que o MySQL calcule eficientemente a posição de qualquer linha na tabela multiplicando vezes o tamanho fixo de cada linha (pense em aritmética de ponteiro) - embora o os detalhes exatos dependem de qual mecanismo de armazenamento você planeja usar. Use o MyISAM se você puder se safar, o que falta em confiabilidade compensa em velocidade e, na sua situação, deve ser suficiente. Substitua campos de tamanho variável como VARCHAR por CHAR(n) e use RTRIM() em suas consultas de leitura.

Uma vez que as linhas da tabela tenham largura fixa, você pode reduzir o número de bytes avaliando cuidadosamente os tipos de dados inteiros do MySQL (alguns dos quais não são padrão). Cada economia de 1 byte que você pode obter convertendo um INT de 4 bytes em um MEDIUMINT de 3 bytes economiza cerca de 1 MB por milhão de linhas - o que significa menos E/S de disco e armazenamento em cache mais eficaz. Use os menores tipos de dados possíveis com os quais você possa se safar . Avalie cuidadosamente os tipos de ponto flutuante e veja se você pode substituir DOUBLEs de 8 bytes por FLOATs de 4 bytes ou mesmo NUMERICs de ponto fixo de <8 bytes . Execute testes para garantir que o que você escolher não o morda mais tarde.

Dependendo das propriedades esperadas de seu conjunto de dados e das operações necessárias, pode haver economias adicionais em codificações mais incomuns de seus valores (padrões/repetições esperados que podem ser codificados como um índice em um conjunto de valores, dados brutos que podem contribuir apenas significativamente para metadados e serem descartados, etc) -- embora otimizações exóticas, não intuitivas e destrutivas só valham a pena quando todas as outras opções foram tentadas.

Mais importante, não importa o que você acabe fazendo, não assuma que você escolheu o esquema perfeito e então comece cegamente a despejar dezenas de milhões de registros. Bons designs levam tempo para evoluir. Crie um conjunto grande, mas gerenciável (digamos, 1-5%) de dados de teste e verifique a correção e o desempenho do seu esquema. Veja como as diferentes operações são executadas (http://dev.mysql.com/doc/refman/5.0/en/using-explain.html) e certifique-se de equilibrar seu esquema para favorecer as operações mais frequentes.

Eu disse curto? Opa. De qualquer forma, boa sorte!

It would seem that the only reason to shred the data point data out of the XML (as opposed to the metadata like the time and type of run) and into a database form is when you are analyzing the spectra across arrays - i.e. perhaps finding all runs with a certain signature. Only you know your problem domain right now, but this could be akin to storing music sampled at 96kHz with 1 sample per row. I'm not sure size is the issue more than how the data is used. Querying across the data would be equivalent to asking the relative amplitude 2 minutes into the song across all songs by The Beatles. If you know the kind of analyses which might be performed, it's quite possible that performing these on the signals and storing those in the metadata about the run might make more sense.

I'm also not sure if your source data is sparse. It's completely possible that a spectrum in the database should only include non-zero entries while the original XML does include zero-entries, and so your total number of rows could be much less than in the source data.

So, like many questions, before asking about MySQL handling your model, stepping back and looking at the model and how it is going to be used is probably more appropriate than worrying about performance just yet.

After reviewing your question updates, I think a model where the binary data is stored as a BLOB or just a pointer to the file is sufficient and work on modifying your model to store data about the significant peaks which have been identified when the data is first read.

I run a web analytics service with about 50 database servers, each one containing many tables over 100 million rows, and several that tend to be over a billion rows, sometimes up to two billion (on each server).

The performance here is fine. It is very normalized data. However - my main concern with reading this is that you'll be well over the 4.2 billion row mark for these tables (maybe not "runs" but probably the other two), which means you'll need to use BIGINT instead of INT for the primary/foreign keys.

MySQL performance with BIGINT fields in an indexed column is ridiculously horrible compared to INT. I made the mistake of doing this once with a table I thought might grow over this size, and once it hit a few hundred million rows the performance was simply abysmal. I don't have raw numbers but when I say bad, I mean Windows ME bad.

This column was the primary key. We converted it back to be just an INT and presto magico, the performance was good again.

All of our servers at the time were on Debian 5 and with MySQL 5.0. We have since upgraded to Debian 6 and Percona MySQL 5.5, so things may have improved since then. But based on my experience here, no, I don't think it will work very well.

Whether or not it works, you're always going to run into the same problem with a single monolithic storage medium: disks are slow. At 100 MB/s (pretty good for spinning media) it takes 3 hours just to read a 1TB table; that's assuming no analysis or seeking or other delays slow you down.

This is why very nearly every "big data" installation uses some sort of distributed data store. You can spend 8 times as much money building one super amazing computer to run your DB, but if you have a lot of data that can be scanned in parallel, you're almost always better off distributing the load across the 8 cheaper computers.

Projects like hadoop were build specifically for purposes like this. You build a cluster of a whole bunch of inexpensive computers, distribute the data across all of them, and query them in parallel. It's just one of a half a dozen solutions all built around this same idea, but it's a very popular one.

Hm... Eu vejo apenas duas razões pelas quais você escolheria esse tipo de estrutura de dados:

• você realmente precisa fazer qualquer consulta de ponto de dados versus qualquer consulta de ponto de dados
• você pretende executar toda a sua lógica em SQL

Agora, sugiro dar uma boa olhada em seus requisitos e verificar se pelo menos uma das suposições acima é verdadeira. Se nenhum dos dois for verdade, você está apenas tornando as coisas mais lentas. Para esse tipo de conjunto de dados, sugiro primeiro descobrir como os dados devem ser acessados, que tipo de precisão você precisará etc.

PS: Lembre-se de que você precisará de pelo menos 36 + 5 bytes por ponto de dados, portanto, com pontos de dados de 200B, isso deve fornecer pelo menos 8,2 TB de espaço necessário.

PPS: Você não precisa da idcoluna na datapointstabela, PRIMARY KEY (spectrum_id, index)provavelmente é suficiente (apenas tome cuidado que indexpode ser uma palavra reservada)

EDIT:

DO NOT DO THIS IN MYSQL WITH DATA STORED ON A SINGLE DISK. Just reading that amount of data from a single medium will take hours. You need to SCALE OUT, NOT UP.

And you need to denormalize your data if you want to do effective data analysis. You are not designing a online system here. You want to crunch numbers, design accordingly.

Original answer below line.

The answer will vary depending on your queries, MySQL may not be the best tool for this job. You may want to look at solution you can scale "out" and not "up". If you are willing to put in some effort maybe you should look on a Map Reduce solution such as Hadoop.

If you want to do more ad-hoc queries Google's BigQuery solution may be a good fit for you. Relevant presentation from Google I/O 2012: Crunching Big Data with BigQuery

So, the solution will depend on if this is a one-shot thing and if you want to reasonably support ad hoc queries.

No one has mentioned, thus my suggestion. Take a look at massively sharded MySQL solutions. For example, see this highly regarded tumblr presentation.

The concept is:

• Instead of one extra large database
• Use many small ones holding parts of the original data

Thus you can scale horizontally, instead of trying to improve vertical performance. Google's BigTable and GFS are also using cheap horizontally scalable nodes to store and query petabytes of data.

However, there will be troubles if you need to run queries over different shards.