MyISAM versus InnoDB

Estou trabalhando em projetos que envolvem muitas gravações de database, eu diria ( 70% de inserções e 30% de leituras ). Essa proporção também inclui atualizações que considero serem uma só leitura e uma gravação. As leituras podem estar sujas (por exemplo, não preciso de 100% de informações precisas no momento da leitura).
A tarefa em questão fará mais de 1 milhão de transactions de database por hora.

Eu li um monte de coisas na web sobre as diferenças entre MyISAM e InnoDB, e MyISAM parece ser a escolha óbvia para o database / tabelas em particular que eu usarei para esta tarefa. Pelo que pareço estar lendo, o InnoDB é bom se as transactions forem necessárias, já que o bloqueio de nível de linha é suportado.

Alguém tem alguma experiência com este tipo de carga (ou superior)? MyISAM é o caminho a percorrer?

Eu discuti brevemente essa questão em uma tabela para que você possa concluir se deseja usar o InnoDB ou o MyISAM .

Aqui está uma pequena visão geral de qual mecanismo de armazenamento db você deve usar em qual situação:

                                                  MyISAM InnoDB
 -------------------------------------------------- --------------
 Pesquisa obrigatória de texto completo Sim 5.6.4
 -------------------------------------------------- --------------
 Exigir transactions Sim
 -------------------------------------------------- --------------
 Consultas de seleção freqüentes Sim      
 -------------------------------------------------- --------------
 Inserção frequente, atualização, excluir sim
 -------------------------------------------------- --------------
 Bloqueio de linha (multi processamento na tabela única) Sim
 -------------------------------------------------- --------------
 Design de base relacional Sim

Para resumir:

 Leitura freqüente, quase sem escrita => MyISAM
 Pesquisa de texto completo no MySQL < = 5.5 => MyISAM

Em todas as outras circunstâncias, o InnoDB é geralmente o melhor caminho a percorrer.

Não sou especialista em database e não falo por experiência. Contudo:

Tabelas MyISAM usam o bloqueio em nível de tabela . Com base nas suas estimativas de tráfego, você tem cerca de 200 gravações por segundo. Com o MyISAM, apenas um deles pode estar em progresso a qualquer momento . Você precisa ter certeza de que seu hardware pode acompanhar essas transactions para evitar sobrecargas, ou seja, uma única consulta pode demorar mais de 5 ms.

Isso sugere que você precisaria de um mecanismo de armazenamento que suporte bloqueio em nível de linha, ou seja, InnoDB.

Por outro lado, deve ser bastante trivial escrever alguns scripts simples para simular a carga com cada mecanismo de armazenamento e comparar os resultados.

As pessoas costumam falar sobre desempenho, leituras x gravações, foreign keys, etc., mas, na minha opinião, há outro recurso imprescindível para um mecanismo de armazenamento: atualizações atômicas.

Tente isto:

  1. Emita uma ATUALIZAÇÃO contra sua tabela MyISAM que leva 5 segundos.
  2. Enquanto o UPDATE estiver em andamento, digamos 2,5 segundos, pressione Ctrl-C para interrompê-lo.
  3. Observe os efeitos na mesa. Quantas linhas foram atualizadas? Quantos não foram atualizados? A tabela é legível ou foi corrompida quando você pressionou Ctrl-C?
  4. Tente o mesmo experimento com UPDATE em uma tabela InnoDB, interrompendo a consulta em andamento.
  5. Observe a tabela InnoDB. Zero linhas foram atualizadas. O InnoDB garantiu que você tem atualizações atômicas e, se a atualização completa não puder ser confirmada, ele reverterá a alteração inteira. Além disso, a tabela não está corrompida. Isso funciona mesmo se você usar o killall -9 mysqld para simular uma falha.

O desempenho é desejável, é claro, mas não perder dados deve prevalecer sobre isso.

Eu trabalhei em um sistema de alto volume usando o MySQL e eu tentei tanto o MyISAM quanto o InnoDB.

Descobri que o bloqueio em nível de tabela no MyISAM causou sérios problemas de desempenho para nossa carga de trabalho que soa similar à sua. Infelizmente, também descobri que o desempenho sob o InnoDB também era pior do que eu esperava.

No final, resolvi o problema de contenção fragmentando os dados de modo que as inserções entrassem em uma tabela “hot” e selecionasse nunca consultou a tabela dinâmica.

Isso também permitia exclusões (os dados eram sensíveis ao tempo e nós só retíamos X dias) para ocorrer em tabelas “obsoletas” que novamente não foram tocadas por consultas selecionadas. O InnoDB parece ter um desempenho ruim em exclusões em massa, portanto, se você estiver planejando eliminar dados, poderá estruturá-lo de forma que os dados antigos fiquem em uma tabela obsoleta, que pode ser simplesmente descartada em vez de executar exclusões nela.

É claro que não tenho idéia de qual é o seu aplicativo, mas espero que isso lhe dê algumas dicas sobre alguns dos problemas com MyISAM e InnoDB.

Um pouco atrasado para o jogo … mas aqui está um post bastante abrangente que escrevi há alguns meses , detalhando as principais diferenças entre o MYISAM e o InnoDB. Pegue uma xícara de chá (e talvez um biscoito) e aproveite.


A principal diferença entre MyISAM e InnoDB está na integridade e transactions referenciais. Há também outras diferenças, como bloqueios, reversões e pesquisas de texto completo.

Integridade referencial

A integridade referencial garante que os relacionamentos entre as tabelas permaneçam consistentes. Mais especificamente, isso significa que quando uma tabela (por exemplo, Listings) tem uma chave estrangeira (por exemplo, Product ID) apontando para uma tabela diferente (por exemplo Products), quando atualizações ou exclusões ocorrem na tabela apontada, essas alterações são em cascata para o link mesa. Em nosso exemplo, se um produto for renomeado, as chaves externas da tabela de vinculação também serão atualizadas; se um produto for excluído da tabela “Produtos”, todas as listagens que apontarem para a input excluída também serão excluídas. Além disso, qualquer nova listview deve ter essa chave estrangeira apontando para uma input existente válida.

O InnoDB é um SGBD relacional (RDBMS) e, portanto, tem integridade referencial, enquanto o MyISAM não.

Transações e Atomicidade

Os dados em uma tabela são gerenciados usando instruções DML (Data Manipulation Language), como SELECT, INSERT, UPDATE e DELETE. Um grupo de transactions duas ou mais instruções DML juntas em uma única unidade de trabalho, portanto, a unidade inteira é aplicada ou nenhuma delas é.

MyISAM não suporta transactions enquanto o InnoDB faz.

Se uma operação for interrompida durante o uso de uma tabela MyISAM, a operação será abortada imediatamente e as linhas (ou até mesmo os dados dentro de cada linha) afetadas permanecerão afetadas, mesmo que a operação não tenha sido concluída.

Se uma operação é interrompida durante o uso de uma tabela InnoDB, porque ela usa transactions, que tem atomicidade, qualquer transação que não foi concluída não terá efeito, já que nenhuma confirmação é feita.

Bloqueio de tabela vs Bloqueio de linha

Quando uma consulta é executada em uma tabela MyISAM, toda a tabela em que está consultando será bloqueada. Isso significa que as consultas subseqüentes serão executadas somente após o término da atual. Se você estiver lendo uma tabela grande e / ou houver operações freqüentes de leitura e gravação, isso pode significar um enorme acúmulo de consultas.

Quando uma consulta é executada em uma tabela InnoDB, apenas as linhas envolvidas estão bloqueadas, o resto da tabela permanece disponível para as operações CRUD. Isso significa que as consultas podem ser executadas simultaneamente na mesma tabela, desde que não usem a mesma linha.

Esse recurso no InnoDB é conhecido como simultaneidade. Por maior que seja a simultaneidade, há uma grande desvantagem que se aplica a um intervalo selecionado de tabelas, em que há uma sobrecarga na alternância entre encadeamentos do kernel e você deve definir um limite nos encadeamentos do kernel para evitar que o servidor pare. .

Transações e Reversões

Quando você executa uma operação no MyISAM, as mudanças são definidas; no InnoDB, essas mudanças podem ser revertidas. Os comandos mais comuns usados ​​para controlar transactions são COMMIT, ROLLBACK e SAVEPOINT. 1. COMMIT – você pode escrever múltiplas operações DML, mas as mudanças só serão salvas quando um COMMIT for feito 2. ROLLBACK – você pode descartar qualquer operação que ainda não tenha sido confirmada ainda 3. SAVEPOINT – define um ponto na lista de operações para as quais uma operação ROLLBACK pode reverter para

Confiabilidade

O MyISAM não oferece integridade de dados – Falhas de hardware, desligamentos não limpos e operações canceladas podem corromper os dados. Isso exigiria reparo completo ou reconstruções dos índices e tabelas.

O InnoDB, por outro lado, usa um log transacional, um buffer de gravação dupla e sum de verificação e validação automáticas para evitar corrupção. Antes do InnoDB fazer quaisquer alterações, ele registra os dados antes das transactions em um arquivo de espaço de tabela do sistema chamado ibdata1. Se houver uma falha, o InnoDB poderá recuperar automaticamente a reprodução desses logs.

Indexação FULLTEXT

O InnoDB não suporta indexação FULLTEXT até a versão 5.6.4 do MySQL. A partir da escrita deste post, a versão MySQL de muitos provedores de hospedagem compartilhada ainda está abaixo de 5.6.4, o que significa que a indexação FULLTEXT não é suportada para tabelas InnoDB.

No entanto, este não é um motivo válido para usar o MyISAM. É melhor mudar para um provedor de hospedagem que ofereça suporte a versões atualizadas do MySQL. Não que uma tabela MyISAM que use indexação FULLTEXT não possa ser convertida em uma tabela InnoDB.

Conclusão

Em conclusão, o InnoDB deve ser seu mecanismo de armazenamento padrão escolhido. Escolha MyISAM ou outros tipos de dados quando eles atendem a uma necessidade específica.

Para uma carga com mais gravações e leituras, você se beneficiará do InnoDB. Como o InnoDB fornece bloqueio de linha em vez de bloqueio de tabela, seus SELECT podem ser simultâneos, não apenas uns com os outros, mas também com muitos INSERT s. No entanto, a menos que você pretenda usar transactions SQL, configure o flush de confirmação do InnoDB para 2 ( innodb_flush_log_at_trx_commit ). Isso lhe dá de volta muito desempenho bruto que você perderia ao mover tabelas do MyISAM para o InnoDB.

Além disso, considere adicionar replicação. Isto dá-lhe alguma escala de leitura e desde que você declarou que suas leituras não precisam estar atualizadas, você pode deixar a replicação ficar um pouco para trás. Apenas certifique-se de que ele pode alcançar tudo menos o tráfego mais pesado ou sempre estará atrasado e nunca o alcançará. Se você seguir esse caminho, no entanto, eu recomendo fortemente que você isole a leitura dos escravos e o gerenciamento de retardo de replicação para seu manipulador de database. É muito mais simples se o código da aplicação não souber sobre isso.

Finalmente, esteja ciente das diferentes cargas de tabela. Você não terá a mesma proporção de leitura / gravação em todas as tabelas. Algumas tabelas menores, com quase 100% de leituras, poderiam ficar no MyISAM. Da mesma forma, se você tiver algumas tabelas próximas a 100%, poderá se beneficiar de INSERT DELAYED , mas isso é suportado apenas no MyISAM (a cláusula DELAYED é ignorada para uma tabela InnoDB).

Mas benchmark para ter certeza.

Para adicionar à ampla seleção de respostas aqui cobrindo as diferenças mecânicas entre os dois motores, eu apresento um estudo de comparação de velocidade empírica.

Em termos de velocidade pura, nem sempre é o caso do MyISAM ser mais rápido do que o InnoDB, mas na minha experiência, ele tende a ser mais rápido em ambientes de trabalho PURE READ por um fator de cerca de 2,0-2,5 vezes. Claramente isso não é apropriado para todos os ambientes – como outros escreveram, o MyISAM não possui transactions como transactions e foreign keys.

Eu fiz um pouco de benchmarking abaixo – eu usei python for loop e a biblioteca timeit para comparações de tempo. Por interesse eu também incluí o mecanismo de memory, isso dá o melhor desempenho em toda a placa, embora seja adequado apenas para tabelas menores (você encontra continuamente The table 'tbl' is full quando você exceder o limite de memory do MySQL). Os quatro tipos de seleção que eu vejo são:

  1. baunilha SELECTs
  2. conta
  3. SELECTs condicionais
  4. sub-seleções indexadas e não indexadas

Em primeiro lugar, criei três tabelas usando o seguinte SQL

 CREATE TABLE data_interrogation.test_table_myisam ( index_col BIGINT NOT NULL AUTO_INCREMENT, value1 DOUBLE, value2 DOUBLE, value3 DOUBLE, value4 DOUBLE, PRIMARY KEY (index_col) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 

com ‘MyISAM’ substituído por ‘InnoDB’ e ‘memory’ na segunda e terceira tabelas.

1) baunilha seleciona

Consulta: SELECT * FROM tbl WHERE index_col = xx

Resultado: empate

Comparação de seleções de vanilla por diferentes mecanismos de banco de dados

A velocidade destes é amplamente a mesma, e como esperado é linear no número de colunas a serem selecionadas. O InnoDB parece um pouco mais rápido que o MyISAM, mas isso é realmente marginal.

Código:

 import timeit import MySQLdb import MySQLdb.cursors import random from random import randint db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor) cur = db.cursor() lengthOfTable = 100000 # Fill up the tables with random data for x in xrange(lengthOfTable): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Define a function to pull a certain number of records from these tables def selectRandomRecords(testTable,numberOfRecords): for x in xrange(numberOfRecords): rand1 = randint(0,lengthOfTable) selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1) cur.execute(selectString) setupString = "from __main__ import selectRandomRecords" # Test time taken using timeit myisam_times = [] innodb_times = [] memory_times = [] for theLength in [3,10,30,100,300,1000,3000,10000]: innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) ) memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) ) 

2) contagens

Consulta: SELECT count(*) FROM tbl

Resultado: MyISAM vence

Comparação de contagens por diferentes mecanismos de banco de dados

Este demonstra uma grande diferença entre MyISAM e InnoDB – MyISAM (e memory) mantém o controle do número de registros na tabela, então esta transação é rápida e O (1). A quantidade de tempo necessária para o InnoDB contar aumenta de forma super-linear com o tamanho da tabela no intervalo que investiguei. Eu suspeito que muitos dos speed-ups das consultas do MyISAM que são observados na prática são devidos a efeitos similares.

Código:

 myisam_times = [] innodb_times = [] memory_times = [] # Define a function to count the records def countRecords(testTable): selectString = "SELECT count(*) FROM " + testTable cur.execute(selectString) setupString = "from __main__ import countRecords" # Truncate the tables and re-fill with a set amount of data for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" truncateString3 = "TRUNCATE test_table_memory" cur.execute(truncateString) cur.execute(truncateString2) cur.execute(truncateString3) for x in xrange(theLength): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Count and time the query innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) ) memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) ) 

3) Seleções condicionais

Consulta: SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

Resultado: MyISAM vence

Comparação de seleções condicionais por diferentes mecanismos de banco de dados

Aqui, MyISAM e memory executam aproximadamente o mesmo e vencem o InnoDB em cerca de 50% para tabelas maiores. Este é o tipo de consulta para a qual os benefícios do MyISAM parecem ser maximizados.

Código:

 myisam_times = [] innodb_times = [] memory_times = [] # Define a function to perform conditional selects def conditionalSelect(testTable): selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5" cur.execute(selectString) setupString = "from __main__ import conditionalSelect" # Truncate the tables and re-fill with a set amount of data for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" truncateString3 = "TRUNCATE test_table_memory" cur.execute(truncateString) cur.execute(truncateString2) cur.execute(truncateString3) for x in xrange(theLength): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) cur.execute(insertString3) db.commit() # Count and time the query innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) ) memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) ) 

4) Sub-seleciona

Resultado: InnoDB ganha

Para esta consulta, criei um conjunto adicional de tabelas para a sub-seleção. Cada um é simplesmente duas colunas de BIGINTs, uma com um índice de chave primária e outra sem qualquer índice. Devido ao grande tamanho da tabela, não testei o mecanismo de memory. O comando de criação da tabela SQL foi

 CREATE TABLE subselect_myisam ( index_col bigint NOT NULL, non_index_col bigint, PRIMARY KEY (index_col) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

onde mais uma vez, 'MyISAM' é substituído por 'InnoDB' na segunda tabela.

Nessa consulta, deixo o tamanho da tabela de seleção em 1000000 e, em vez disso, vario o tamanho das colunas subelecionadas.

Comparação de sub-seleções por diferentes mecanismos de banco de dados

Aqui o InnoDB ganha facilmente. Depois que chegamos a uma tabela de tamanho razoável, ambos os motores escalam linearmente com o tamanho da sub-seleção. O índice acelera o comando MyISAM, mas, curiosamente, tem pouco efeito na velocidade do InnoDB. subSelect.png

Código:

 myisam_times = [] innodb_times = [] myisam_times_2 = [] innodb_times_2 = [] def subSelectRecordsIndexed(testTable,testSubSelect): selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )" cur.execute(selectString) setupString = "from __main__ import subSelectRecordsIndexed" def subSelectRecordsNotIndexed(testTable,testSubSelect): selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )" cur.execute(selectString) setupString2 = "from __main__ import subSelectRecordsNotIndexed" # Truncate the old tables, and re-fill with 1000000 records truncateString = "TRUNCATE test_table_innodb" truncateString2 = "TRUNCATE test_table_myisam" cur.execute(truncateString) cur.execute(truncateString2) lengthOfTable = 1000000 # Fill up the tables with random data for x in xrange(lengthOfTable): rand1 = random.random() rand2 = random.random() rand3 = random.random() rand4 = random.random() insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")" cur.execute(insertString) cur.execute(insertString2) for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]: truncateString = "TRUNCATE subselect_innodb" truncateString2 = "TRUNCATE subselect_myisam" cur.execute(truncateString) cur.execute(truncateString2) # For each length, empty the table and re-fill it with random data rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength)) rand_sample_2 = random.sample(xrange(lengthOfTable), theLength) for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2): insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")" insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")" cur.execute(insertString) cur.execute(insertString2) db.commit() # Finally, time the queries innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) ) myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) ) innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) ) myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) ) 

Acho que a mensagem de tudo isso é que, se você está realmente preocupado com a velocidade, precisa fazer um benchmark das consultas que está fazendo, em vez de fazer suposições sobre qual mecanismo será mais adequado.

Um pouco off-topic, mas para fins de documentação e integridade, gostaria de acrescentar o seguinte.

Em geral, usar o InnoDB resultará em uma aplicação muito menos complexa, provavelmente também mais livre de erros. Como você pode colocar toda a integridade referencial (restrições de chave estrangeira) no modelo de dados, você não precisa de nenhum código de aplicativo que precise do MyISAM.

Toda vez que você inserir, excluir ou replace um registro, você terá que verificar e manter os relacionamentos. Por exemplo, se você excluir um pai, todos os filhos também devem ser excluídos. Por exemplo, mesmo em um sistema de blog simples, se você excluir um registro de postagem de blog, você terá que excluir os registros de comentários, os gostos, etc. No InnoDB isso é feito automaticamente pelo mecanismo de database (se você especificou as restrições no modelo ) e não requer código de aplicação. No MyISAM isso terá que ser codificado no aplicativo, o que é muito difícil em servidores web. Os servidores Web são, por natureza, muito simultâneos / paralelos e, como essas ações devem ser atômicas e o MyISAM não suporta transactions reais, o uso do MyISAM for web-servers é arriscado / propenso a erros.

Também na maioria dos casos gerais, o InnoDB terá um desempenho muito melhor, por um múltiplo de razões, um deles sendo capaz de usar o bloqueio de nível de registro em oposição ao bloqueio de nível de tabela. Não apenas em situações em que as gravações são mais frequentes do que as leituras, também em situações com junções complexas em grandes conjuntos de dados. Percebemos um aumento de desempenho de 3 vezes apenas usando tabelas InnoDB sobre tabelas MyISAM para junções muito grandes (levando vários minutos).

Eu diria que, em geral, o InnoDB (usando um modelo de dados 3NF completo com integridade referencial) deve ser a escolha padrão ao usar o MySQL. MyISAM só deve ser usado em casos muito específicos. Ele provavelmente terá menos desempenho, resultando em um aplicativo maior e com mais bugs.

Tendo dito isto. Datamodelling é uma arte raramente encontrada entre webdesigners / -programmers. Sem ofensa, mas isso explica o uso do MyISAM.

O InnoDB oferece:

 ACID transactions row-level locking foreign key constraints automatic crash recovery table compression (read/write) spatial data types (no spatial indexes) 

No InnoDB, todos os dados em uma linha, exceto TEXT e BLOB, podem ocupar no máximo 8.000 bytes. Nenhuma indexação de texto completo está disponível para o InnoDB. No InnoDB, o COUNT (*) s (quando WHERE, GROUP BY ou JOIN não é usado) é executado mais devagar que no MyISAM porque a contagem de linhas não é armazenada internamente. O InnoDB armazena dados e índices em um arquivo. O InnoDB usa um buffer pool para armazenar dados e índices em cache.

MyISAM oferece:

 fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) full text indexing smaller disk footprint very high table compression (read only) spatial data types and indexes (R-tree) 

MyISAM possui bloqueio no nível da tabela, mas não possui bloqueio no nível da linha. Nenhuma transação Não há recuperação automática de falhas, mas oferece funcionalidade de tabela de reparo. Nenhuma restrição de chave estrangeira. Tabelas MyISAM são geralmente mais compactas em tamanho no disco quando comparadas a tabelas InnoDB. As tabelas MyISAM podem ser ainda mais reduzidas em tamanho, comprimindo com myisampack, se necessário, mas tornam-se somente leitura. O MyISAM armazena índices em um arquivo e dados em outro. O MyISAM usa buffers de chaves para caching de índices e deixa o gerenciamento de cache de dados para o sistema operacional.

No geral, eu recomendaria o InnoDB para a maioria dos propósitos e o MyISAM apenas para usos especializados. O InnoDB agora é o mecanismo padrão nas novas versões do MySQL.

Se você usar o MyISAM, não fará transactions por hora, a menos que considere que cada declaração DML seja uma transação (que, em qualquer caso, não será durável ou atômica no caso de uma falha).

Portanto, eu acho que você tem que usar o InnoDB.

300 transactions por segundo soam bastante. Se você realmente precisar que essas transactions sejam duráveis ​​em caso de falta de energia, certifique-se de que seu subsistema de E / S possa manipular facilmente essas muitas gravações por segundo. Você precisará de pelo menos um controlador RAID com cache suportado por bateria.

Se você aguentar um pouco de durabilidade, você pode usar o InnoDB com o innodb_flush_log_at_trx_commit definido como 0 ou 2 (consulte os documentos para detalhes), você pode melhorar o desempenho.

There are a number of patches which can increase concurrency from Google and others – these may be of interest if you still can’t get enough performance without them.

The Question and most of the Answers are out of date .

Yes, it is an old wives’ tale that MyISAM is faster than InnoDB. notice the Question’s date: 2008; it is now almost a decade later. InnoDB has made significant performance strides since then.

The dramatic graph was for the one case where MyISAM wins: COUNT(*) without a WHERE clause. But is that really what you spend your time doing?

If you run concurrency test, InnoDB is very likely to win, even against MEMORY .

If you do any writes while benchmarking SELECTs , MyISAM and MEMORY are likely to lose because of table-level locking.

In fact, Oracle is so sure that InnoDB is better that they have all but removed MyISAM from 8.0.

The Question was written early in the days of 5.1. Since then, these major versions were marked “General Availability”:

  • 2010: 5.5 (.8 in Dec.)
  • 2013: 5.6 (.10 in Feb.)
  • 2015: 5.7 (.9 in Oct.)
  • 2018: 8.0 (.11 in Apr.)

Bottom line: Don’t use MyISAM

Please note that my formal education and experience is with Oracle, while my work with MySQL has been entirely personal and on my own time, so if I say things that are true for Oracle but are not true for MySQL, I apologize. While the two systems share a lot, the relational theory/algebra is the same, and relational databases are still relational databases, there are still plenty of differences!!

I particularly like (as well as row-level locking) that InnoDB is transaction-based, meaning that you may be updating/inserting/creating/altering/dropping/etc several times for one “operation” of your web application. The problem that arises is that if only some of those changes/operations end up being committed, but others do not, you will most times (depending on the specific design of the database) end up with a database with conflicting data/structure.

Note: With Oracle, create/alter/drop statements are called “DDL” (Data Definition) statements, and implicitly trigger a commit. Insert/update/delete statements, called “DML” (Data Manipulation), are not committed automatically, but only when a DDL, commit, or exit/quit is performed (or if you set your session to “auto-commit”, or if your client auto-commits). It’s imperative to be aware of that when working with Oracle, but I am not sure how MySQL handles the two types of statements. Because of this, I want to make it clear that I’m not sure of this when it comes to MySQL; only with Oracle.

An example of when transaction-based engines excel:

Let’s say that I or you are on a web-page to sign up to attend a free event, and one of the main purposes of the system is to only allow up to 100 people to sign up, since that is the limit of the seating for the event. Once 100 sign-ups are reached, the system would disable further signups, at least until others cancel.

In this case, there may be a table for guests (name, phone, email, etc.), and a second table which tracks the number of guests that have signed up. We thus have two operations for one “transaction”. Now suppose that after the guest info is added to the GUESTS table, there is a connection loss, or an error with the same impact. The GUESTS table was updated (inserted into), but the connection was lost before the “available seats” could be updated.

Now we have a guest added to the guest table, but the number of available seats is now incorrect (for example, value is 85 when it’s actually 84).

Of course there are many ways to handle this, such as tracking available seats with “100 minus number of rows in guests table,” or some code that checks that the info is consistent, etc…. But with a transaction-based database engine such as InnoDB, either ALL of the operations are committed, or NONE of them are. This can be helpful in many cases, but like I said, it’s not the ONLY way to be safe, no (a nice way, however, handled by the database, not the programmer/script-writer).

That’s all “transaction-based” essentially means in this context, unless I’m missing something — that either the whole transaction succeeds as it should, or nothing is changed, since making only partial changes could make a minor to SEVERE mess of the database, perhaps even corrupting it…

But I’ll say it one more time, it’s not the only way to avoid making a mess. But it is one of the methods that the engine itself handles, leaving you to code/script with only needing to worry about “was the transaction successful or not, and what do I do if not (such as retry),” instead of manually writing code to check it “manually” from outside of the database, and doing a lot more work for such events.

Lastly, a note about table-locking vs row-locking:

DISCLAIMER: I may be wrong in all that follows in regard to MySQL, and the hypothetical/example situations are things to look into, but I may be wrong in what exactly is possible to cause corruption with MySQL. The examples are however very real in general programming, even if MySQL has more mechanisms to avoid such things…

Anyway, I am fairly confident in agreeing with those who have argued that how many connections are allowed at a time does not work around a locked table. In fact, multiple connections are the entire point of locking a table!! So that other processes/users/apps are not able to corrupt the database by making changes at the same time.

How would two or more connections working on the same row make a REALLY BAD DAY for you?? Suppose there are two processes both want/need to update the same value in the same row, let’s say because the row is a record of a bus tour, and each of the two processes simultaneously want to update the “riders” or “available_seats” field as “the current value plus 1.”

Let’s do this hypothetically, step by step:

  1. Process one reads the current value, let’s say it’s empty, thus ‘0’ so far.
  2. Process two reads the current value as well, which is still 0.
  3. Process one writes (current + 1) which is 1.
  4. Process two should be writing 2, but since it read the current value before process one write the new value, it too writes 1 to the table.

I’m not certain that two connections could intermingle like that, both reading before the first one writes… But if not, then I would still see a problem with:

  1. Process one reads the current value, which is 0.
  2. Process one writes (current + 1), which is 1.
  3. Process two reads the current value now. But while process one DID write (update), it has not committed the data, thus only that same process can read the new value that it updated, while all others see the older value, until there is a commit.

Also, at least with Oracle databases, there are isolation levels, which I will not waste our time trying to paraphrase. Here is a good article on that subject, and each isolation level having it’s pros and cons, which would go along with how important transaction-based engines may be in a database…

Lastly, there may likely be different safeguards in place within MyISAM, instead of foreign-keys and transaction-based interaction. Well, for one, there is the fact that an entire table is locked, which makes it less likely that transactions/FKs are needed .

And alas, if you are aware of these concurrency issues, yes you can play it less safe and just write your applications, set up your systems so that such errors are not possible (your code is then responsible, rather than the database itself). However, in my opinion, I would say that it is always best to use as many safeguards as possible, programming defensively, and always being aware that human error is impossible to completely avoid. It happens to everyone, and anyone who says they are immune to it must be lying, or hasn’t done more than write a “Hello World” application/script. 😉

I hope that SOME of that is helpful to some one, and even more-so, I hope that I have not just now been a culprit of assumptions and being a human in error!! My apologies if so, but the examples are good to think about, research the risk of, and so on, even if they are not potential in this specific context.

Feel free to correct me, edit this “answer,” even vote it down. Just please try to improve, rather than correcting a bad assumption of mine with another. 😉

This is my first response, so please forgive the length due to all the disclaimers, etc… I just don’t want to sound arrogant when I am not absolutely certain!

MYISAM:

  1. MYISAM supports Table-level Locking

  2. MyISAM designed for need of speed

  3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
  4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
  5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

INNODB:

  1. InnoDB supports Row-level Locking
  2. InnoDB designed for maximum performance when processing high volume of data
  3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
  4. InnoDB stores its tables and indexes in a tablespace
  5. InnoDB supports transaction. You can commit and rollback with InnoDB

I think this is an excellent article on explaining the differences and when you should use one over the other: http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Also check out some drop-in replacements for MySQL itself:

MariaDB

http://mariadb.org/

MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements including alternate storage engines, server optimizations, and patches.

Percona Server

https://launchpad.net/percona-server

An enhanced drop-in replacement for MySQL, with better performance, improved diagnostics, and added features.

MyISAM

The MyISAM engine is the default engine in most MySQL installations and is a derivative of the original ISAM engine type supported in the early versions of the MySQL system. The engine provides the best combination of performance and functionality, although it lacks transaction capabilities (use the InnoDB or BDB engines) and uses table-level locking .

FlashMAX and FlashMAX Connect: Leading the Flash Platform Transformation Download Now Unless you need transactions, there are few databases and applications that cannot effectively be stored using the MyISAM engine. However, very high-performance applications where there are large numbers of data inserts/updates compared to the number of reads can cause performance proboelsm for the MyISAM engine. It was originally designed with the idea that more than 90% of the database access to a MyISAM table would be reads, rather than writes.

With table-level locking, a database with a high number of row inserts or updates becomes a performance bottleneck as the table is locked while data is added. Luckily this limitation also works well within the restrictions of a non-transaction database.

MyISAM Summary

Name -MyISAM

Introduced -v3.23

Default install -Yes

Data limitations -None

Index limitations -64 indexes per table (32 pre 4.1.2); Max 16 columns per index

Transaction support -No

Locking level -Table


InnoDB

The InnoDB Engine is provided by Innobase Oy and supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.

The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk. This enables very fast recovery, and works even on very large data sets. By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.

As with MyISAM , there are few data types that cannot effectively be stored in an InnoDB database. In fact, there are no significant reasons why you shouldn’t always use an InnoDB database. The management overhead for InnoDB is slightly more onerous, and getting the optimization right for the sizes of in-memory and on disk caches and database files can be complex at first. However, it also means that you get more flexibility over these values and once set, the performance benefits can easily outweigh the initial time spent. Alternatively, you can let MySQL manage this automatically for you.

If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.

InnoDB Summary

Name -InnoDB

Introduced -v3.23 (source only), v4.0 (source and binary)

Default install -No

Data limitations -None

Index limitations -None

Transaction support -Yes (ACID compliant)

Locking level -Row

In my experience, MyISAM was a better choice as long as you don’t do DELETEs, UPDATEs, a whole lot of single INSERT, transactions, and full-text indexing. BTW, CHECK TABLE is horrible. As the table gets older in terms of the number of rows, you don’t know when it will end.

I’ve figure out that even though Myisam has locking contention, it’s still faster than InnoDb in most scenarios because of the rapid lock acquisition scheme it uses. I’ve tried several times Innodb and always fall back to MyIsam for one reason or the other. Also InnoDB can be very CPU intensive in huge write loads.

Every application has it’s own performance profile for using a database, and chances are it will change over time.

The best thing you can do is to test your options. Switching between MyISAM and InnoDB is trivial, so load some test data and fire jmeter against your site and see what happens.

I tried to run insertion of random data into MyISAM and InnoDB tables. The result was quite shocking. MyISAM needed a few seconds less for inserting 1 million rows than InnoDB for just 10 thousand!

myisam is a NOGO for that type of workload (high concurrency writes), i dont have that much experience with innodb (tested it 3 times and found in each case that the performance sucked, but it’s been a while since the last test) if you’re not forced to run mysql, consider giving postgres a try as it handles concurrent writes MUCH better

I know this won’t be popular but here goes:

myISAM lacks support for database essentials like transactions and referential integrity which often results in glitchy / buggy applications. You cannot not learn proper database design fundamentals if they are not even supported by your db engine.

Not using referential integrity or transactions in the database world is like not using object oriented programming in the software world.

InnoDB exists now, use that instead! Even MySQL developers have finally conceded to change this to the default engine in newer versions, despite myISAM being the original engine that was the default in all legacy systems.

No it does not matter if you are reading or writing or what performance considerations you have, using myISAM can result in a variety of problems, such as this one I just ran into: I was performing a database sync and at the same time someone else accessed an application that accessed a table set to myISAM. Due to the lack of transaction support and the generally poor reliability of this engine, this crashed the entire database and I had to manually restart mysql!

Over the past 15 years of development I have used many databases and engines. myISAM crashed on me about a dozen times during this period, other databases, only once! And that was a microsoft SQL database where some developer wrote faulty CLR code (common language runtime – basically C# code that executes inside the database) by the way, it was not the database engine’s fault exactly.

I agree with the other answers here that say that quality high-availability, high-performance applications should not use myISAM as it will not work, it is not robust or stable enough to result in a frustration-free experience. See Bill Karwin’s answer for more details.

PS Gotta love it when myISAM fanboys downvote but can’t tell you which part of this answer is incorrect.

For that ratio of read/writes I would guess InnoDB will perform better. Since you are fine with dirty reads, you might (if you afford) replicate to a slave and let all your reads go to the slave. Also, consider inserting in bulk, rather than one record at a time.

Almost every time I start a new project I Google this same question to see if I come up with any new answers.

It eventually boils down to – I take the latest version of MySQL and run tests.

I have tables where I want to do key/value lookups… and that’s all. I need to get the value (0-512 bytes) for a hash key. There is not a lot of transactions on this DB. The table gets updates occasionally (in it’s entirety), but 0 transactions.

So we’re not talking about a complex system here, we are talking about a simple lookup,.. and how (other than making the table RAM resident) we can optimize performance.

I also do tests on other databases (ie NoSQL) to see if there is anywhere I can get an advantage. The biggest advantage I have found is in key mapping but as far as the lookup goes, MyISAM is currently topping them all.

Albeit, I wouldn’t perform financial transactions with MyISAM tables but for simple lookups, you should test it out.. typically 2x to 5x the queries/sec.

Test it, I welcome debate.

If it is 70% inserts and 30% reads then it is more like on the InnoDB side.

In short, InnoDB is good if you are working on something that needs a reliable database that can handles a lot of INSERT and UPDATE instructions.

and, MyISAM is good if you needs a database that will mostly be taking a lot of read (SELECT) instructions rather than write (INSERT and UPDATES), considering its drawback on the table-lock thing.

you may want to check out;
Pros and Cons of InnoDB
Pros and Cons of MyISAM

bottomline: if you are working offline with selects on large chunks of data, MyISAM will probably give you better (much better) speeds.

there are some situations when MyISAM is infinitely more efficient than InnoDB: when manipulating large data dumps offline (because of table lock).

example: I was converting a csv file (15M records) from NOAA which uses VARCHAR fields as keys. InnoDB was taking forever, even with large chunks of memory available.

this an example of the csv (first and third fields are keys).

 USC00178998,20130101,TMAX,-22,,,7,0700 USC00178998,20130101,TMIN,-117,,,7,0700 USC00178998,20130101,TOBS,-28,,,7,0700 USC00178998,20130101,PRCP,0,T,,7,0700 USC00178998,20130101,SNOW,0,T,,7, 

since what i need to do is run a batch offline update of observed weather phenomena, i use MyISAM table for receiving data and run JOINS on the keys so that i can clean the incoming file and replace VARCHAR fields with INT keys (which are related to external tables where the original VARCHAR values are stored).