Introdução ao MySQL 5.6

Introdução ao MySQL 5.6Uma abordagem que lhe possibilitará saber de

todas as novidades e o estado da arte do servidor de bancos de dados mais popular do mundo!

Wagner Bianchi é especialista em MySQL e outros servidores de bancos de dados relacionais, como Oracle e SQL Server.

Atualmente é Sales Engineer na empresa norte americana Percona (www.percona.com) para negócios na América Latina.

Formado em Gerenciamento de Bancos de Dados, com MBA em Administração de Empresas pela Fundação Getúlio Vargas e Pós-Graduando em Bancos de Dados pela Universidade Gama Filho do Distrito Federal. Possui várias certificações, entre elas a SCMA, SCMDEV, SCMDBA, SMCDBA e MCDBA.

Autor

Contatos

• E-mail: [email protected]• Skype: wbianchijr• Twitter: @wagnerbianchijr

mailto:[email protected]

MySQL Certification Program

Conteúdo do Workshop• Arquitetura do servidor de bancos de dados MySQL 5.6;• MySQL 5.6 INFORMATION_SCHEMA;• Melhorias no Otimizador de Consultas;• Melhorias no Storage Engine InnoDB;• Interface NoSQL com MEMCACHED API;• Melhorias no particionamento de tabelas (MySQL Partitioning);• Melhorias em pontos críticos da replicação de dados;• Melhorias no monitoramento de performance;

Destaques:• InnoDB Plugin e o suporte ao FullText Search;• Binlog Group Commit (Replicação de Dados);

Arquitetura do servidor de bancos de dados MySQL 5.6

Arquitetura MySQL 5.6


• Primeira camada contendo módulos que compõem o software de gerenciamento dos bancos de dados (parser, query transformation, query cache, etc);

• Segundo camada contendo os “bocais” para “plugar” os Storage Engines;– Possibilidade de lidar com características de armazenamento de

dados;– Possibilidade de lidar com vários Storage Engines em um mesmo

banco de dados;– Habilitar e desabilitar Storage Engines;

Arquitetura MySQL 5.6• Desde a versão anterior, a 5.5, lançada em 2010, o InnoDB Plugin se

tornou o Storage Engine padrão. – quando você cria uma tabela sem a cláusula ENGINE, cria-se

uma nova tabela InnoDB com suporte à transação, integridade referencial, FullText Search, logs de transação, tablespace...

• Para verificar os Storage Engine disponíveis nativos do MySQL, basta utilizar o comando SHOW ENGINES;

mysql> SHOW ENGINES\G[...]*************************** 8. row *************************** Engine: InnoDBSupport: YESComment: Supports transactions, row-level locking, and foreign keys


• Controle de logs parte é feito pelo SGBD, parte é feito pelo Storage Engine – e.g. InnoDB Transaction Logs (ib_logsx);

– Controlados pelo SGBD• --general-log• --log-bin (log binário do MySQL - registra atualizações - replicação)• --slow-query-log (--log-slow-query até versão 5.0);• --log-error• --pid-file (caso especial, log para troca de mensagens entre mysqld e SO);

– Controlados pelo Storage Engine (InnoDB)• ib_logsx, onde x é o número do log, geralmente localizados em /var/lib/mysql

Até a versão 5.5 do MySQL, a soma da capacidade de armazenamento dos arquivos de log do InnoDB não poderia passar de 4G. Com o MySQL 5.6, esse tamanho foi ampliado para 12G – quanto maior a capacidade dos arquivos de logs de transação, mais disponibilidade, maior performance;


• Os Storage Engines mais utilizados do MySQL são o MyISAM e agora, de longe o mais usado é o InnoDB por suas características e este utilizam memória da seguinte forma;

– MyISAM• key_buffer_size para armazenamento de dados de índices (PK);• read_buffer_size para armazenamento de buffer de dados já recuperados;• Cache de dados realizado com a ajuda do sistema operacional (very busy!);

– InnoDB• innodb_buffer_pool_size para armazenamento de índices e dados;• innodb_buffer_pool_size_awe para armazenamento de metadados;

Arquitetura MySQL 5.6• MyISAM key buffer/cache:

Arquitetura MySQL 5.6• MyISAM key buffer/cache status variables:

mysql> show status like 'key%';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| Key_blocks_not_flushed | 0 || Key_blocks_unused | 2 || Key_blocks_used | 167893 || Key_read_requests | 1560 || Key_reads | 178590 || Key_write_requests | 1980 || Key_writes | 102785 |+------------------------+--------+7 rows in set (0.01 sec)

Tuning!

Arquitetura MySQL 5.6• InnoDB buffer pool:


• InnoDB buffer pool:– InnoDB mantém logs em memória (buffer) e em disco;

• innodb_log_group_home_dir [ =/var/lib/mysql ]• Innodb_log_files_in_group = [ 4 ]• Innodb_log_file_size = [ 768M ]• innodb_log_buffer_size = 12G # MySQL 5.6 ++

– A quantidade de memória configurada para o parâmetro acima será alocada logo que o mysqld for iniciado;

– As variáveis acima são parte do conjunto utilizado para configurar o comportamento dos logs do InnoDB;

Tuning!


MySQL 5.6INFORMATION_SCHEMA

MySQL 5.6 INFORMATION_SCHEMA

• O INFORMATION_SCHEMA, no MySQL, assim como um vários outros produtos de bancos de dados, é o dicionário de dados;– Dados sobre dados:

• Lista de bancos de dados, Storage Engines, tabelas, colunas, índices;• Lista de aspectos do “runtime” do servidor de bancos de dados;

– Formado por um conjunto de visões (não podem ser alteradas diretamente):• INFORMATION_SCHEMA.SCHEMATA;• INFORMATION_SCHEMA.TABLES;• INFORMATION_SCHEMA.TABLE_CONSTRAINTS;• INFORMATION_SCHEMA.GLOBAL_VARIABLES;• INFORMATION_SCHEMA.PROFILING;


• Com o MySQL 5.6, outras visões próprias para a leitura de performance do InnoDB foram implementadas;

mysql> show tables like “INNODB_SYS%”;+--------------------------------------------+| Tables_in_information_schema (INNODB_SYS%) |+--------------------------------------------+| INNODB_SYS_FIELDS || INNODB_SYS_INDEXES || INNODB_SYS_TABLESTATS || INNODB_SYS_COLUMNS || INNODB_SYS_FOREIGN_COLS || INNODB_SYS_FOREIGN || INNODB_SYS_TABLES |+--------------------------------------------+7 rows in set (0.00 sec)


• Visões para monitoramento de outras features já no MySQL 5.5:

mysql> show tables like 'INNODB%';+----------------------------------------+| Tables_in_INFORMATION_SCHEMA (INNODB%) |+----------------------------------------+| INNODB_CMP_RESET || INNODB_TRX || INNODB_CMPMEM_RESET || INNODB_LOCK_WAITS || INNODB_CMPMEM || INNODB_CMP || INNODB_LOCKS |+----------------------------------------+7 rows in set (0.05 sec)

MySQL 5.6Otimizador de Consultas

Otimizador de Consultas

• Mudanças consideráveis aconteceram no otimizador de consultas, que tem suas variáveis que influenciam na hora de determinar a melhor estratégia de recuperação de dados;

mysql> SELECT @@optimizer_switch\G

*************************** 1. row *************************** @@optimizer_switch: index_merge=on,

index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on,mrr_cost_based=on

Otimizador de Consultas• Index Condition Pushdown (ICP):– Otimizações de recuperação de dados baseadas em índices;– Operação com suporte à todos os Storage Engines;– As linhas são recuperadas já com a aplicação do filtro (WHERE);– Recurso originado em operações com o MySQL Cluster 7.0 ++;

mysql> EXPLAIN SELECT Name FROM City WHERE ID > '2000' AND ID <= 4000\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 2160 Extra: Using index condition1 row in set (0.00 sec)

Otimizador de Consultas

• Multi-Range Read:– Comportamento padrão do otimizador para o MySQL 5.6;– InnoDB armazena dados de forma randômica, páginas, blocos and extensões;– Acesso randômico é ruim para índices secundários (PK+[UNIQUE|KEY]);– O MRR ou Multi-Range Read possibilita acesso seqüencial aos dados;

• File Sort Optimization:– Comportamento padrão do otimizador para o MySQL 5.6;– Recurso interessante para sistemas que precisam “paginar” dados;– Melhor performance para consultas que utilizam ORDER BY com

coluna não indexada com a cláusula LIMIT;

MySQL 5.6 Storage Engine InnoDB

Storage Engine InnoDB

• Persistent Optimizer Stats:

– O Storage Engine InnoDB, na versão 1.2.2, oferece estatísticas persistentes para que a recuperação de dados não seja afetada após um restart do servidor de bancos de dados;

– Este comportamento é controlado pelas seguintes variáveis de ambiente: • innodb_analyze_is_persistent [= ON];• innodb_stats_persistent_sample_pages; • andinnodb_stats_transient_sample_pages;

– Com o POS ativado, as estatísticas somente serão recomputadas caso seja rodado explicitamente o comando ANALYZE TABLE;

Storage Engine InnoDB• Novas tabelas no INFORMATION_SCHEMA:

Utilidade Tabelas

Métricas de utilização INNODB_METRICS

Visão interna de todos os pontos do InnoDB

INNODB_SYS_TABLES, INNODB_SYS_TABLESTAT,INNODB_SYS_INDEXES, INNODB_SYS_COLUMNS, INNODB_SYS_FIELDS,INNODB_SYS_FOREIGN, e INNODB_SYS_FOREIGN_COLS

Informações sobre o consumo da área de memória configurada para o Buffer Pool

INNODB_BUFFER_PAGE,INNODB_BUFFER_PAGE_LRU e INNODB_BUFFER_POOL_STATS


• Multi-Threaded Purge:– Com o MySQL 5.6 o InnoDB passa a ter threads especializadas para o processo

de expurgo de dados, que na verdade é tido como um tipo de garbage collector, o que evita problemas com performance;

– Tal comportamento é configurado através da variável innodb_purge_threads, que pode ter valores de 0 (off) até 32, que significa o número de threads que serão dedicadas ao processo de expurgo;

• Separate Flush Thread:– Uma novidade no MySQL 5.6, uma thread dedicado à limpeza de páginas

sujas, denominada page_cleaner, trabalhando no mesmo conceito do Adaptive Flushing;


• Pruning the InnoDB Table Cache:– Tableas que não são acessadas mais por algum tempo são retiradas da

memória (table_cache_definition) para liberar espaço;– Um algorítimo de LRU é utilizado para fazer esse controle;

MySQL 5.6 NoSQL com MEMCACHED API

NoSQL com MEMCACHED API

• Com uma crescente utilização de armazenamento NoSQL (Not Only SQL) e com o surgimento de vários players no mercado:

– MySQL dá acesso NoSQL baseado em MEMCACHED API;– Acesso a dados diretamente em tabelas controladas pelo InnoDB nativo;– Persistente, crash-safe, transacional baseado em ACID;– Oferece aos usuários o melhor dos dois mundos, a utilização de linguagem

SQL para recuperação de dados (tabelas InnoDB) e a performance melhorada para sistemas que acessam dados diretamente, $key -> $value;

NoSQL com MEMCACHED API

MySQL 5.6 MySQL 5.6 Partitioning Engine

MySQL 5.6 Partitioning Engine

• O particionamento de tabelas:– Se aplicado com padrões poderá melhorar performance radicalmente;– Organizar melhorar os dados em partições e em discos diferentes;– Performance!

• Com o MySQL 5.6:

– Selecionar dados de uma partição diretamente com a função PARTITION();• SELECT * FROM employees PARTITION (p0, p2); • DELETE FROM employees PARTITION (p0, p1); • UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill';

– Migrar dados entre tabelas com comandos SQL;• ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

MySQL 5.6 MySQL 5.6 Replicação de Dados

Replicação de Dados

• Multi-Threaded Slaves:– Múltiplas threads no servidor SLAVE podem ser utilizadas para

executar as atualizações que são recuperadas do log binário do servidor MASTER, aumentando a disponibilidade dos serviços;

– As atualizações replicadas são realizadas em paralelo, e não mais seqüencial como antes;

• Crash-Safe Slaves:– Aumento da segurança com base na integridade dos dados em

replicação. Através dos arquivos master.info e relaylog.info, uma transação poderá ter seu skip automático, evitando uma intervenção do DBA;

– Maior concentração do DBA e atividades mais estratégicas;


• Replication Checksums:– Ao replicar dados, um checksum é relaizado para que seja conferido

no seu destino, evitando problemas de corrompimento de pacotes de dados;

• Time-Delayed Replication:– A partir do MySQL 5.6, você poderá definir um tempo (em

milissegundos) de quando os dados serão liberados para os servidores SLAVES.

– Configurado em nível de servidor SLAVE e realizado através da SQL_THREAD;

• Informational Log Events:– Melhorias em questões de auditoria através do log binário;


• Remote Binlog Back-up:– Utilizando a opção --raw, juntamente com a opção --read-from-remote-server, é possível ler e fazer backup dos logs binários em outro servidor:

shell> mysqlbinlog --read-from-remote-server --host=host_name --raw / binlog.000130 binlog.000131 binlog.000132

• Server UUIDs– Não é mais necessário atribuir um server_id único para cada

servidor dentro da topologia de replicação, uma vez que ao executar o MySQL 5.6 pela primeira vez, uma arquivo auto.cnf será criado com um valor único para cada servidor – automaticamente.

– Esta informação estará disponível através do SHOW SLAVE STATUS;

Destaques:• InnoDB Plugin e o suporte ao FullText Search;• Binlog Group Commit (Replicação de Dados);

Obrigado!

• E-mail: [email protected]• Skype: wbianchijr• Twitter: @wagnerbianchijr

mailto:[email protected]

Documents

Introdução ao MySQL 5.6