none
Manutenção em Tabela com milhões de registros RRS feed

  • Pergunta

  • Olá pessoal,

    Temos um processo de auditoria em nossas aplicações que através de triggers, toda e qualquer alterações, inserções e/ou exclusões feitas pelos usuários nas aplicações são gravadas em uma tabela XXLOG. Até aqui o processo funciona em 99% dos casos.

    Contudo, apareceu uma situação de um cliente com uma base realmente gigante. Para termos noção os numeros em apenas um dia de trabalho, a tabela XXLOG ficou com + ou - 30 milhões de registros, e isso não é rodando rotinas principais.

    Ela tem um campo int identity(1,1) que a PK e outros campos que guardam quem fez a manutenção do registro, qual informação foi modificada, quando foi alterada, etc.

    A consulta a esta tabela é quase zero, ou seja, para dar um SELECT não é muito comum. É uma tabela que fica no BD para que algum dia, caso necessário, possa ser consultada para rastrear quem fez uma alteração indevida.

    O ponto é que o campo da PK (int) estourou, devido ao volume de informações deste cliente os INSERTs nesta tabela são constantes (24 horas por dia).

    Como solução paliativa, jogamos os dados para uma outra tabela e depois truncamos a informação. Contudo a tabela continua crescendo nesse nivel.

    Estou estudando formas de solução. Seguem alguns pontos que gostaria da analise de vcs se estou no caminho certo:

    1) Alterar o campo para BIGINT

    2) Criar um banco só de auditoria, onde de tempo em tempo (Talvez diariamente) migrar os dados para este novo BD e truncar as informações do BD principal. Um job automatizaria o processo.

    3) Talvez criar partições nestes novo BD. Mas como não sei qual o critério de consultas, talvez não seja o ideal

    4) Referente ao item 2, o novo BD ser configurado como BULK-LOGGED

     

    Bom, estas são algumas ideias que gostaria de sugestão se usar todas ou algumas em conjunto eu não afete a performance da aplicação.

    Obs.: O banco é SQL 2008

     

    Obrigado.


    Marco Antônio Pinheiro / MCTS - MCC http://marcoantoniopinheiro.blogspot.com
    • Movido Gustavo Maia Aguiar terça-feira, 23 de agosto de 2011 16:32 (De:Programação avançada com o SQL Server)
    terça-feira, 23 de agosto de 2011 15:30

Respostas

  • Boa Tarde,

    O primeiro ponto que me chama a atenção é que se em um único dia foram gerados 30 milhões de registros de log e esses logs são criados por triggers, é provável que a trigger tenha sido disparada perto de 30 milhões de vezes o que equivale a mais de um milhão de vezes por hora. Sabendo que a trigger é um passo a mais, o cliente não reclamou do desempenho ? A trigger ainda permitiu a vazão necessária ?

    Referente ao Bulk Logged, informo que ele não irá ajudar em absolutamente nada nessa situação. Bulk Logged ajuda para cargas, mas somente se forem cargas minimal logged como o BCP ou o BULK INSERT. Um INSERT simples ou com SELECT não irá se beneficiar em nada dessa configuração de log.

    A alteração para BIGINT é natural, pois, se o INT não dá conta é preciso utilizar um tipo de dados que dê conta. Eu não optaria pelo UniqueIdentifier, pois, ele ocupará o dobro do BIGINT e é um tipo de dados de utilização mais complexa. Vale ressaltar que um aumento na PK, incorrerá em um aumento nas FKs e nos índices também e nesse ponto o Unique Identifier pode ser ainda mais desvantajoso.

    Particionar por particionar eu não vejo razão. Se você quiser particionar para conseguir efetuar uma reindexação ou algo do tipo, é bem vindo, mas considerando que a tabela sempre sofrerá INSERTs, sua taxa de fragmentação deve ser manter bem baixa e não creio que haverá benefícios visíveis do particionamento nesse caso.

    Colocar em um banco a parte é uma idéia bem interessante. Se realmente a taxa de consulta é baixa e as criticidades dos dados são diferentes, separar pode ser uma boa idéia para otimizar os recursos de storage e ainda facilitar os backups.

    Acho que uma empresa que é capaz de produzir 30 milhões de registros em um único dia de trabalho não deve ser uma empresa pequena. Eu avaliaria a possibilidade de outros mecanismos de auditoria, pois, as triggers podem limitá-lo (se já não o estão fazendo). Além de ferramentas nativas como CT, CDC e Audit, uma ferramenta própria ou até um Database Firewall poderiam ser avaliados.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de agosto de 2011 16:32

Todas as Respostas

  • Marco,

     

    Não sei qual impacto isso iria trazer para sua aplicação, mas voce ja pensou em um campo com datatype unique identifier e com default value um NEWID()?


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    terça-feira, 23 de agosto de 2011 15:39
    Moderador
  • Boa Tarde,

    O primeiro ponto que me chama a atenção é que se em um único dia foram gerados 30 milhões de registros de log e esses logs são criados por triggers, é provável que a trigger tenha sido disparada perto de 30 milhões de vezes o que equivale a mais de um milhão de vezes por hora. Sabendo que a trigger é um passo a mais, o cliente não reclamou do desempenho ? A trigger ainda permitiu a vazão necessária ?

    Referente ao Bulk Logged, informo que ele não irá ajudar em absolutamente nada nessa situação. Bulk Logged ajuda para cargas, mas somente se forem cargas minimal logged como o BCP ou o BULK INSERT. Um INSERT simples ou com SELECT não irá se beneficiar em nada dessa configuração de log.

    A alteração para BIGINT é natural, pois, se o INT não dá conta é preciso utilizar um tipo de dados que dê conta. Eu não optaria pelo UniqueIdentifier, pois, ele ocupará o dobro do BIGINT e é um tipo de dados de utilização mais complexa. Vale ressaltar que um aumento na PK, incorrerá em um aumento nas FKs e nos índices também e nesse ponto o Unique Identifier pode ser ainda mais desvantajoso.

    Particionar por particionar eu não vejo razão. Se você quiser particionar para conseguir efetuar uma reindexação ou algo do tipo, é bem vindo, mas considerando que a tabela sempre sofrerá INSERTs, sua taxa de fragmentação deve ser manter bem baixa e não creio que haverá benefícios visíveis do particionamento nesse caso.

    Colocar em um banco a parte é uma idéia bem interessante. Se realmente a taxa de consulta é baixa e as criticidades dos dados são diferentes, separar pode ser uma boa idéia para otimizar os recursos de storage e ainda facilitar os backups.

    Acho que uma empresa que é capaz de produzir 30 milhões de registros em um único dia de trabalho não deve ser uma empresa pequena. Eu avaliaria a possibilidade de outros mecanismos de auditoria, pois, as triggers podem limitá-lo (se já não o estão fazendo). Além de ferramentas nativas como CT, CDC e Audit, uma ferramenta própria ou até um Database Firewall poderiam ser avaliados.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 23 de agosto de 2011 16:32
  • Gustavo,

    Como vc disse, a alteração para BIGINT será natural. Esse tinha sido o primeiro ponto de avaliação.

    Realmente foi uma situação, diria que bem peculiar. Dentro dos pontos que pensei, a criação de um outro banco me pareceu bem mais coerente pelo fato de backup. Ficar fazendo backups dos dados e levando a auditoria junto, não me pareceu nem um pouco viavel.

    O único ponto que posso ter gargalo é referente na transmissão dos dados do BD produção para o BD auditoria. Como o volume será grande, tenho que avaliar a melhor hora de fazer isso, pois se deixar o banco lento, consequentemente a aplicação será afetada. Até porque tenho que levar em consideração que a carga de trabalho deles é de 24 horas.

    Sobre as ferramentas citadas vou fazer uma avaliação a respeito de cada uma e verificar a aderencia. Se tiver algum link com detalhes a respeito irá ajudar.

    No mais obrigado pelos esclarecimentos.

    Se aparecerem mais sugestões, sempre serão bem vindas.

     

    Att.,


    Marco Antônio Pinheiro / MCTS - MCC http://marcoantoniopinheiro.blogspot.com
    terça-feira, 23 de agosto de 2011 17:11