Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


sexta-feira, 17 de agosto de 2018

MySQL - Conversão de tabelas MYISAM para INNODB

Por Eduardo Legatti

Olá,

Diferente da engine INNODB que armazena em buffer de memória, tanto blocos de dados de tabelas, quanto blocos de dados de índices, a engine MYISAM armazena em buffer de memória apenas dados de blocos de índices. Com a evolução da engine INNODB ao longo do tempo, a mesma vem oferecendo muitos recursos das quais a engine MYISAM não é capaz de oferecer. Em relação aos níveis de locks, a engine MYISAM possui tratamento de lock apenas no nível de tabela:

  • SELECTs travam UPDATEs, DELETEs mas não INSERTs
  • INSERTs, UPDATEs, DELETEs travam todas as instruções SQL

A engine INNODB oferece tratamento de locks no nível de linhas de tabelas, além de prover recursos como Foreign Keys que ajudam o otimizador do banco de dados a encontrar melhores planos de execução para os SQLs que são executados. A engine INNODB é compatível com as propriedades ACID oferecendo recursos transacionais, além de ser mais seguro que a engine MYISAM no que se refere a corrupção de dados em caso de falhas. Em relação ao parâmetros de configuração, somente a engine INNODB oferece parâmetros de ajustes finos. Segue abaixo uma tabela comparativa.


 
No mais, irei selecionar todas as tabelas MYISAM do banco de dados bd01.

mysql> SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;')
    ->   FROM information_schema.tables
    ->  WHERE table_schema='bd01'
    ->    AND engine='MyISAM';
+--------------------------------------------------------------------------+
| CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;') |
+--------------------------------------------------------------------------+
| ALTER TABLE `bd01`.`tbl01` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl02` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl03` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl04` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl05` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl06` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl07` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl08` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl09` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl10` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl11` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl12` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl13` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl14` ENGINE=InnoDB;                                |
| ALTER TABLE `bd01`.`tbl15` ENGINE=InnoDB;                                |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Como exemplo irei converter a tabela tbl01 para a engine INNODB conforme o comando a seguir.

mysql>  ALTER TABLE `bd01`.`tbl01` ENGINE=InnoDB;
Query OK, 61 rows affected (0.23 sec)
Records: 61  Duplicates: 0  Warnings: 0


Dependendo do tamanho da tabela a ser convertida, o processo pode demorar um pouco. Neste caso, é possível verificar a sessão que está realizando a conversão conforme demonstrado abaixo.
 
mysql> show full processlist;
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+
| Id | User | Host                | db   | Command | Time | State             | Info                             |
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+
|  5 | root | localhost           | bd01 | Sleep   |  804 |                   | NULL                             |
|  6 | root | localhost           | NULL | Query   |    0 | starting          | show full processlist            |
|  7 | root | 192.168.56.51:49970 | bd01 | Sleep   |  103 |                   | NULL                             |
|  8 | root | 192.168.56.51:49971 | NULL | Sleep   | 9838 |                   | NULL                             |
| 14 | root | localhost           | bd01 | Query   |   15 | copy to tmp table | alter table tbl01 engine innodb  |
+----+------+---------------------+------+---------+------+-------------------+----------------------------------+

Apenas como informação adicional e em relação à comparação entre as engines MYISAM e INNODB, segue abaixo um estudo feito e publicado pela Oracle que demonstra algumas diferenças de performance entre as duas engines.

 


O gráfico acima demonstra que a engine INNODB oferece maior escalabilidade ao se utilizar de múltiplos núcleos de CPU . Este teste demonstra que o nível de lock de tabela implementada na engine MYISAM reduz o throughput em ambientes que possuem workloads de leitura/escrita.

 


O gráfico acima demonstra que a performance de leituras utilizando a engine INNODB oferece um throughput e escalabilidades superiores se comparados com a engine MYISAM.



Nenhum comentário:

Postagens populares