Existem muitos artigos exagerando (IMHO, é claro) a necessidade do innodb_file_per_table. Entendo que com innodb_file_per_table, deve haver um melhor controle sobre as tabelas individuais; como backup de cada tabela separadamente. No entanto, a alegação de melhor desempenho é questionável.
No meu teste, não há diferença no desempenho de innodb_file_per_tablee ibdata1para um banco de dados de 60 GB. Claro, foi um teste simples com consultas normais, e a situação pode ser diferente para consultas complicadas na vida real (esta é a razão pela qual fiz essa pergunta). Linux de 64 bits com ext4pode lidar efetivamente com arquivos grandes.
Com innodb_file_per_table, são necessárias mais operações de E/S de disco; e isso é significativo em JOINs e FOREIGN KEYrestrições complicadas.
O tablespace é compartilhado em single ibdata; como tablespaces dedicados para tabelas separadas podem economizar espaço em disco? Claro, é mais fácil liberar espaço de tabela para cada tabela com ALTER, mas ainda é um processo caro (com bloqueio de tabela).
PERGUNTA: Isso innodb_file_per_tableafeta um melhor desempenho do mysql? Se sim, por quê?
Não acho que seja uma questão de desempenho, mas de gestão.
Com arquivo separado por tabela, você pode armazenar diferentes bancos de dados em diferentes dispositivos de armazenamento, por exemplo.
Você pode lidar com o caso de bancos de dados muito grandes em sistemas de arquivos que não podem lidar com arquivos grandes (pelo menos adie o problema até que uma tabela atinja o limite de tamanho de arquivo).
Você não tem crescimento descontrolado do tablespace. Se você descartar algumas tabelas grandes, o
ibdataarquivo permanecerá pequeno.Um aspecto que pode ter algum efeito no desempenho é a fragmentação dos dados e índices da tabela, que serão limitados por tabela. Mas isso precisa de testes para ser confirmado.
Porque é mais fácil de gerenciar individualmente, pois pode ser feito no nível do arquivo. Isso significa que, mesmo que o servidor esteja inativo, você ainda pode copiar dados copiando os arquivos da tabela, enquanto usar um espaço de tabela compartilhado significa copiar tudo o que pode ser desnecessariamente massivo ou encontrar alguma maneira de fazer o servidor funcionar para extrair dados ( você realmente não deseja extrair manualmente os dados com um editor hexadecimal).
Alguém avisou que você não pode simplesmente copiar e colar
.ibdarquivos de um servidor para outro. Isso pode ser verdade, mas não deve se aplicar a backups no mesmo servidor (estou usando o termo backup aqui no sentido tradicional de fazer uma cópia, ou seja, não mudar drasticamente a coisa toda). Além disso,ibdata1é recriado automaticamente na inicialização (como visto na etapa de exclusãoibdata1da maioria dos guias de “conversão para arquivo por tabela”). Como tal, você não precisa copiaribdata1além de seus.ibdarquivos (e seus.frmarquivos , etc. correspondentes).Se estiver tentando recuperar uma tabela perdida, deve ser suficiente copiar seu arquivo
.ibde.frm, bem comoinformation_schema(que é muito menor queibdata1). Dessa forma, você pode colocá-los em um servidor fictício e extrair sua tabela sem ter que copiar a coisa inteira e enorme.Não surpreendentemente, o desempenho dependerá inteiramente do(s) banco(s) de dados específico(s) em uso. Uma pessoa terá (ainda que muito) resultados diferentes de outra.
É verdade que haverá mais operações de E/S de disco com arquivo por tabela, mas apenas um pouco mais. Pense em como o sistema funciona.
Para um banco de dados monolítico:
ibdata1está abertoibdata1Para um banco de dados por tabela:
ibdata1está aberto.ibdarquivo individual é aberto.ibdarquivo.ibdarquivo já abertoVocê notará que quando o servidor está em execução, você não pode mover os arquivos de dados porque o servidor tem alças abertas para eles. Isso ocorre porque quando ele inicializa, ele os abre e os deixa abertos. Ele não os abre e fecha para cada consulta individual.
Como tal, há apenas mais algumas operações de E/S no início, quando o servidor é inicializado; não enquanto estiver em execução. Além disso, embora cada
.ibdarquivo individual tenha sua própria sobrecarga separada (assinaturas de arquivo, estruturas, etc.), eles são armazenados em cache na memória e não são relidos para cada consulta. Além disso, as mesmas estruturas são lidas mesmo com um espaço de tabela compartilhado, portanto, quase não há (se houver) mais memória necessária.Na verdade, se alguma coisa, o desempenho pode ser pior .
Ao usar um espaço de tabela compartilhado, as operações de leitura e gravação podem às vezes/muitas vezes ser combinadas para que o servidor leia uma amostra de dados de várias tabelas de uma só vez do
ibdata.No entanto, se os dados estiverem espalhados entre vários arquivos, será necessário executar uma operação de E/S separada para cada um individualmente.
É claro que isso depende inteiramente do banco de dados em questão; o impacto no desempenho do mundo real dependeria do tamanho, frequência de consulta e fragmentação interna do espaço de tabela compartilhado. Algumas pessoas podem notar uma grande diferença, enquanto outras podem não ver nenhum impacto.
Isso não. Se alguma coisa, aumenta o uso do disco um pouco.
Eu não tenho um banco de dados de 60 GB para testar, mas meu banco de dados pessoal “irrisório” que contém minha instalação do WordPress e algumas pequenas tabelas para uso pessoal e testes de desenvolvimento pesavam ~ 30 MB ao usar um espaço de tabela compartilhado. Depois de convertê-lo em arquivo por tabela, ele inchou para ~ 85 MB. Mesmo largando tudo e reimportando, ainda era >60 MB.
Esse aumento se deve a dois fatores:
O tamanho mínimo absoluto
ibdata1é - por algum motivo - 10 MB, mesmo se você não tiver nada aléminformation_schemade armazenado nele.Com um espaço de tabela compartilhado, só
ibdata1há sobrecarga, como assinaturas de arquivos, metadados, etc., mas com por tabela, cada.ibdarquivo individual tem tudo isso. Isso significa que o total (mesmo com um hipotético <10 MBibdata1) seria um pouco maior em pelo menos:Obviamente, estes não serão grandes aumentos (a menos que você esteja usando um host que limite o tamanho do seu banco de dados ou armazene-os em uma unidade flash, etc.), mas eles são aumentos mesmo assim, e ao alternar ( toda ) tabela para arquivo -por tabela você pode reduzir
ibdata1para 10 MB, o total geral será invariavelmente maior do que era.Esta é a minha razão para SEMPRE usar innodb_file_per_table:
Sem arquivo por tabela, o arquivo ibdata nunca é compactado, reduzido ou reduzido em espaço. Não quando você exclui uma linha, descarta uma tabela ou um banco de dados. 2 GB de dados podem se tornar um arquivo de 20 GB rapidamente se você tiver um sistema de filas ativo.
Digamos que você queira fazer um backup de sua tabela atual de 1 GB antes de uma alteração e depois soltá-la. Você está preso com um GB de espaço agora não utilizado em seu ibdata. Desapontamento.
Provavelmente existem inúmeros exemplos de instâncias em que medidas temporárias inflam o arquivo de dados único, mas basta dizer que, na minha opinião, nunca há uma razão para NÃO usar innodb_file_per_table
Além disso, aqui está um bom post para ler: http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table
Minha razão pela qual não usar innodb_file_per_table é o desempenho.
Fiz alguns testes para nosso banco de dados com 450 tabelas no mysql 5.5.45 Linux CentOS release 6.7
Para testes de unidade que inserem fixtures no banco de dados antes de cada teste (não usando todas as tabelas todas as vezes) e também os próprios testes funcionam muito com banco de dados (inserir, atualizar, excluir, selecionar) o desempenho foi 3-5x melhor quando as tabelas do banco de dados não foram separados em mais arquivos.
Eu recomendo testar seu banco de dados com as consultas que você deseja usar e compará-lo antes de decidir usar innodb_file_per_table
Talvez você possa descobrir que para servidor de produção você pode usar innodb_file_per_table mas para ambiente CI (continua integração) que inicia testes de unidade (usa muito DB) e também desenvolvedores que iniciam muito testes de unidade é melhor não usá-lo por causa do desempenho.
Isso torna os dados mais gerenciáveis porque você pode recuperar o espaço não utilizado, o que é bom.
Acho que se o seu banco de dados for usado principalmente para consultas selecionadas, isso não afetará muito o desempenho. Ele ainda tem que ler sobre a mesma quantidade de dados. Eu não acho que importa muito de quais arquivos ele está lendo os dados.
No entanto, pode piorar o desempenho em um banco de dados que faz muitas inserções e atualizações. Isso ocorre porque o mysql chama fsync() no arquivo de armazenamento após você confirmar uma transação. Se houver um único arquivo, ele fará uma chamada e aguardará a conclusão da chamada. Se houver muitos arquivos, ele terá que fazer a chamada várias vezes e esperar que todas essas chamadas retornem antes que o comando commit possa retornar.
Aqui está uma postagem de alguém que experimentou esse problema: http://umangg.blogspot.com/2010/02/innodbfilepertable.html
De acordo com o artigo abaixo, o desempenho não é sobre o gerenciamento de dados (operações brutas em si), mas sim sobre a criação e descarte de objetos.
innodb_file_per_table torna a criação massiva e a eliminação de objetos mais lenta do que o armazenamento ibdata e para produção não é aplicável, mas para teste contínuo deve ser relevante.
https://www.percona.com/blog/2015/02/24/mysqls-innodb_file_per_table-slowing/
IMHO é melhor usar innodb_file_per_table, é mais seguro. Se você não usá-lo, poderá ter problemas em sistemas FAT32, onde apenas arquivos de 4 GB são permitidos. Eu escrevi um artigo sobre isso em idioma eslovaco ( https://www.itsoft.sk/preco-sa-neuvolni-miesto-na-disku-po-zmazani-mysql-tabulky/ ).