Desejo manipular um grande conjunto de dados (> 1 bilhão de linhas) no SQL Server 2008 R2. Ouvi dizer que, se você configurar "fragmentação" ou "particionamento horizontal", será mais rápido lidar com grandes conjuntos de dados, pois divide as tabelas em vários arquivos.
Você já usou sharding ou particionamento horizontal antes? Em caso afirmativo, você poderia sugerir algum padrão que, em sua experiência, alcance melhores resultados em comparação com uma única mesa massiva?
Primeiro, observe que 1 bilhão de linhas podem ser tratadas de forma eficaz com uma arquitetura particionada em hardware de servidor comum. Arquiteturas exóticas de nada compartilhado não serão necessárias para esse volume de dados, no entanto, você provavelmente obterá benefícios significativos do particionamento de tabelas.
O sharding é algo diferente do particionamento horizontal e implica uma arquitetura de 'nada compartilhado', que não é compatível com a maioria das versões do SQL Server 1
O SQL Server pode oferecer suporte ao particionamento horizontal e uma arquitetura de disco compartilhado será adequada para cerca de 1 bilhão de linhas.
No SQL Server, você cria uma função de partição seleciona uma partição com base em valores ou intervalos de valores em uma coluna em uma tabela, por exemplo
Em seguida, crie um ou mais grupos de arquivos para alocar as partições. Para um grande conjunto de dados, esses grupos de arquivos podem ser configurados em diferentes volumes físicos. Observe que o armazenamento de conexão direta será muito mais rápido do que uma SAN para isso em quase todos os casos. No exemplo abaixo, teríamos criado 6 grupos de arquivos chamados PartVol1-PartVol6.
Um ou mais esquemas de partição podem ser criados para alocar partições de tabela para grupos de arquivos com base no valor da função de partição, por exemplo
Este esquema é projetado para particionar em um período contábil. As datas também são frequentemente usadas para isso, embora qualquer chave possa ser usada.
Você pode criar uma tabela no esquema de partição como se fosse um grupo de arquivos, por exemplo
Observe que a tabela é criada no esquema de partição em vez de um grupo de arquivos especificado e a cláusula especifica a coluna a ser usada como a chave de partição. Com base na chave de partição, as linhas da tabela serão alocadas para um dos grupos de arquivos no esquema de partição.
Nota: Uma regra prática para projetar um esquema de particionamento é que cada partição deve ter uma contagem de linhas na casa dos 10 milhões, digamos entre 10 e 50 milhões, dependendo da largura das linhas. O volume do disco em que a partição se encontra deve ser rápido o suficiente para fazer uma varredura de pelo menos uma única partição em alguns segundos.
Sistemas de particionamento, sharding e nada compartilhado
Um pouco de terminologia parece adequado aqui para desambiguar parte da discussão sobre esse tópico.
Um sistema de 'nada compartilhado' é um sistema paralelo em que os nós não têm armazenamento SAN compartilhado, mas usam armazenamento local para o nó. O exemplo clássico desse tipo de arquitetura é o Teradata. Os sistemas nada compartilhados se adaptam bem a conjuntos de dados muito grandes, pois não possuem gargalos de E/S centrais. A taxa de transferência de E/S aumenta com o número de nós no sistema.
Um sistema de 'disco compartilhado' é aquele em que um ou mais servidores de banco de dados compartilham um único subsistema de armazenamento em disco. O banco de dados pode ser um único servidor com armazenamento local ou conectado a uma SAN, ou um cluster de servidores conectados a uma SAN compartilhada. Os sistemas desse tipo são limitados pelo rendimento disponível do subsistema de armazenamento.
'Sharding' é um termo usado para descrever a divisão de um banco de dados entre vários servidores físicos em uma arquitetura sem compartilhamento. Várias plataformas terão maior ou menor suporte para bancos de dados fragmentados. Nos círculos do Teradata, o termo não é usado porque o Teradata apresenta uma única imagem de sistema transparente para os clientes, mesmo que a arquitetura física seja do tipo nada compartilhado.
Versões mais antigas do SQL Server têm suporte limitado para sharding por meio de exibições particionadas distribuídas. A Microsoft agora faz uma versão do SQL Server 2008 R2 que oferece suporte a uma arquitetura nada compartilhada com uma única imagem do sistema, mas esta versão está disponível apenas para OEMs e só pode ser adquirida em um pacote de hardware.
Para 1 bilhão de linhas
Para 1 bilhão de linhas (a menos que as linhas individuais sejam extremamente largas), um nada compartilhado ou uma arquitetura fragmentada está confortavelmente no reino do exagero. Esse tipo de volume pode ser manipulado em um único servidor de especificação razoável se ele tiver um subsistema de disco adequadamente rápido.
O disco de conexão direta local é de longe o mais econômico em termos de preço por desempenho. Um único controlador RAID SAS pode ocupar vários arrays e vários controladores podem ser instalados em um servidor. Dependendo da configuração, um array SAS moderno de 24-25 slots pode realizar milhares de IOPS ou 1 GB+/s em desempenho de streaming; um servidor com vários barramentos PCI-e e vários controladores teoricamente pode lidar com mais.
O tipo de desempenho necessário para trabalhar com um banco de dados de 1 bilhão de linhas pode ser alcançado de forma bastante fácil e barata com hardware de servidor comum e armazenamento de conexão direta desse tipo. Uma SAN também pode ser usada, mas você pode precisar de vários controladores SAN para obter desempenho equivalente, e o hardware provavelmente será muito mais caro.
Como recomendação geral, use o armazenamento de conexão direta para aplicativos com requisitos pesados de E/S, a menos que você precise de um tempo de atividade realmente bom. Erros de controle de configuração e alteração são uma fonte muito maior de tempo de inatividade não programado do que falhas de hardware em operações modernas de data center.
As SANs podem fornecer uma plataforma de armazenamento mais gerenciável se você tiver um grande portfólio de aplicativos, pois oferecem uma variedade de recursos de gerenciamento de armazenamento centralizado. No entanto, isso tem um preço alto e obter alto desempenho da infraestrutura baseada em SAN é difícil e caro.
1 A Microsoft faz uma versão paralela do SQL Server, mas ela só está disponível por meio de canais OEM integrados ao hardware. As versões disponíveis na prateleira não oferecem suporte a esse recurso.