GUOB Tech Day 2017

16 abril 2014

Armazenamento de arquivos no MySQL em colunas BLOB

Os arquivos de imagem são o caso clássico para o uso de colunas BLOBs, mas isso raramente é uma boa idéia. A recomendação geral é NÃO armazenar arquivos no banco de dados. Isto é válido para qualquer banco de dados relacional. Há alguns casos que isso pode fazer sentido, mas são raros.

Os bancos de dados relacionais, inclusive o MySQL, são projetados para otimizar operações em objetos de tamanho relativamente pequeno. Principalmente quando os arquivos são grandes, há vários problemas decorrentes de armazená-los no Banco de Dados, envolvendo: performance, desperdício de espaço, fragmentação exagerada, dificuldade em backups/restores e maior trabalho na manutenção das tabelas.

O MySQL em particular vai sempre criar uma tabela temporária em disco quando JOINs envolverem tabelas com colunas BLOBs/TEXTs. Tabelas temporárias em disco são muito menos performáticas que tabelas temporárias em memória. A otimização Adaptative Hash Index também não pode ser usada em casos com colunas muito grandes.

Colunas muito grandes também tendem a fragmentar mais o disco e consumir espaço desnecessário no InnoDB. Por exemplo, se um valor não couber em uma linha por apenas 1 byte, será usada uma página inteira para armazenar apenas este último byte, desperdiçando o espaço restante da página. Da mesma forma, se você tiver um valor que é um pouco maior que 32 páginas, vai na verdade usar 96 páginas no disco.

Outro problema com o crescimento exagerado da base é que ao longo do tempo, com data sets maiores, backups e restores serão uma dor-de-cabeça. Será praticamente impossível usar o mysqldump e, mesmo usando técnicas como hot backup ou snapshots, os tempos de backup e restore podem passar do limite aceitável.

Uma abordagem comum para evitar o armazenamento direto no Banco de Dados é gravar os arquivos no file system e manter apenas uma referência (meta-dados) no Banco. Para garantir réplicas dos arquivos e alta-disponibilidade pode-se usar um file system compartilhado na rede, ou um file system distribuído como MogileFS, ClusterFS, GridFS etc. Outra solução cada vez mais popular são serviços na nuvem, com destaque para o S3 da Amazon. Ainda há outras soluções como montar um serviço interno para prover imagens usando algum software como Thumbor ou ImageMagick que expandirão a capacidade de manipular a imagem programaticamente.

Se os arquivos forem servidos em um stream HTTP a decisão por armazená-los no Banco de Dados pode ser pior ainda. O web server vai fazer um trabalho muito mais eficiente ao ler o arquivo diretamente no file system. Além disso, os arquivos podem ser cacheados em CDNs, aumentando muito a eficiência da banda de rede.

Há de fato algumas situações que justificam gravar arquivos no Banco de Dados como:
-Se o arquivo tem que necessariamente fazer parte de uma transação complexa, por exemplo envolvendo um workflow de documentos onde é vital manter a coerência entre os meta-dados e as várias revisões ou versões de documentos;
-Se o tamanho do banco de dados e volume das imagens não forem muito grandes e se há um controle no crescimento desses parâmetros (escalabilidade limitada);
-Se deseja-se usar os mecanismos de segurança, compressão, lock, etc nativos do banco de dados para gerir o acesso aos arquivos e performance não é fator limitante;
-Se deseja-se utilizar a replicação do MySQL como método de transferência rápida de arquivos em uma rede com vários Slaves.

Caso sua conclusão seja que para seu caso específico armazenar arquivos no MySQL é uma boa idéia, tente adotar as seguintes práticas:
-nos SELECTs deixe as colunas BLOBs/TEXTs de fora (não faça SELECT * FROM ...);
-segregue BLOBs/TEXTs em tabelas separadas, mantendo uma referência em uma coluna de tamanho fixo (synthetic indexes); isto reduzirá a fragmentação e permitirá que SELECT * nas tabelas sem BLOBs;
-utilize innodb-one-file-per-table e lembre-se do recurso Transportable Table Spaces para facilitar a gestão dos arquivos de dados;
-utilize particionamento, de preferência RANGE e LIST para facilitar adição e remoção de partições;
-desfragmente tabelas e partições que sofrem UPDATEs/DELETEs com maior frequência; utilize OPTIMIZE TABLE e ALTER TABLE...OPTIMIZE PARTITION; use também e ferramentas de desfragmentação do file system (fragmentação externa);
-ajuste a variável innodb_log_buffer_size para um tamanho maior e mais adequado para BLOBs grandes (acima de 8MB);
-ajuste a variável max_allowed_packet para comportar objetos maiores trafegando na rede;
-considere colocar o tmpdir em um filesystem baseado em memória para aumentar a performance quando surgirem tabelas temporárias em disco;
-queries que não usam índices na cláusula WHERE ficarão bastante lentas com colunas grandes; use covering indexes para ajudar no problema;
-avalie o uso da função COMPRESS(), comprimindo os arquivos na aplicação antes de enviar para o Storage Engine; também avalie a compressão nativa do InnoDB;
-o Storage Engine Archive pode ser interessante no caso de arquivos ainda não compactados.

Se você tem algum caso de uso com vantagens em armazenar arquivos no MySQL ou dicas para melhorias e performance e gestão neste cenário, não deixe de comentar aqui no blog!

Nenhum comentário: