mysql profiling com enterprise monitor
DESCRIPTION
Uma atividade que toma bastante tempo do administrador ou desenvolvedor é encontrar Queries que consomem muitos recursos do servidor. Há um expressivo ganho de performance otimizando queries lentas, podendo chegar a aumentar várias vezes o tempo de resposta. Vamos ver como a ferramenta Query Analyzer pode nos ajudar nesta tarefa e ainda conhecer um pouco melhor o que está contido na edição MySQL Enterprise, com foco principalmente em desenvolvedores.TRANSCRIPT
<Insert Picture Here>
Marcos [email protected]
Airton [email protected]
dez-2011
Profiling e Monitoramentocom MySQL Enterprise Monitor
agenda
Problemas cotidianos do DBA e Desenvolvedor
Profiling e Monitoramento do MySQL
MySQL Enterprise Edition
… qual versão do MySQL rodar?
… servidores down?
… os sistemas escalarão?
… política de segurança, muda?
… problemas de performance?
… onde, quando, como “tunar”?
… queries lentas, custosas?
… problemas de replicação?
… produtividade de desenv./QA?
… posso recuperar?
Questões difíceis
… qual versão do MySQL rodar?
… servidores down?
… os sistemas escalarão?
… política de segurança, muda?
… problemas de performance?
… onde, quando, como “tunar”?
… queries lentas, custosas?
… problemas de replicação?
… produtividade de desenv./QA?
… posso recuperar?
Questões difíceis
Profiling
In software engineering, profiling ("program profiling", "software profiling") is a form of dynamic program analysis that measures, for example, the usage of memory, the usage of particular instructions, or frequency and duration of function calls. The most common use of profiling information is to aid program optimization.
Responsabilidades comuns para BDs 1/2
Desenvolvedor/Arquiteto• definir mecanismos eficientes de interação entre aplicação e BD• dimensionamento/upgrade de hardware • responsabilidade sobre a performance geral da aplicação,
além de outros requisitos não funcionais • correção de problemas em QA ou Produção (inclusive
reescrita de queries para otimização)
SysAdmin/DBA• otimização do esquema de dados• criação e utilização correta de índices • ajuste de variáveis no MySQL Server (tuning) • monitoramento e ações corretivas• manutenção dos indices e dados
Responsabilidades comuns para BDs 2/2
AMBOS papéis• modelagem do esquema de dados mais eficiente • identificar e melhorar queries que consomem muitos
recursos
Lembre-se: o objetivo do profiling é otimizar!
normalmente a razão #1 de perdas de performance
Otimização de Queries em 2 passos
1. seja um incansável caçador de Queries lentas mais executadas
• MySQL Query Analyzer• maior produtividade, menor overhead
• Recursos nativos• slow query log, mysqldumpslow > SHOW
PROCESSLIST > select_scan, select_full_join
2. aplique melhorias• indexação, EXPLAIN• reescrita de queries• re-modelagem do esquema de dados
Otimização de Queries em 2 passos
1. seja um incansável caçador de Queries lentas mais executadas
• MySQL Query Analyzer• maior produtividade, menor overhead
• Recursos nativos• slow query log, mysqldumpslow > SHOW
PROCESSLIST > select_scan, select_full_join
2. aplique melhorias• indexação, EXPLAIN• reescrita de queries• re-modelagem do esquema de dados
MySQL Enterprise Monitor: Query Analyzer
Navegando na execução da QueryExemplo query exec com substitução de
variável
Stack Trace com informações do
código-fonte
EXPLAIN completo para otimização de
índices
Integração com MyOracleSupport
MySQL Support Diagnostic Reports • Coleta e empacota rapidamente dados do MySQL, SO,
diagnósticos de Queries em um relatório para o Oracle Support• Acompanhamento de tickets do Oracle Support (SRs)• Tempo de resposta acelerado, menor risco
Otimização de Queries em 2 passos
1. seja um incansável caçador de Queries lentas mais executadas
• MySQL Query Analyzer• maior produtividade, menor overhead
• Recursos nativos• slow query log, mysqldumpslow > SHOW
PROCESSLIST > select_scan, select_full_join
2. aplique melhorias• indexação, EXPLAIN• reescrita de queries• re-modelagem do esquema de dados
Índices e EXPLAIN
1. Índices são intensamente utilizados• operações de Filtro• operações Join• operações Sort
2. EXPLAIN mostra como (e se) estão sendo utilizados 3. Boas práticas
• não criar índices desnecessários (degrada performance de escrita)
• não criar indices com inicio idêntico (leading part)• não usar toda string para os índices (apenas uma parte
inicial já basta)• 16 bytes ou menos• com mais de 32 bytes devem ter cuidados especiais (como
cache próprio MyISAM)• crie colunas com hashes e indexe-os ao invés de string
longas
http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
Reescreva Queries lentas
1. Utilize IN clause• SELECT ... WHERE idx IN(1,23,345,456)• muito mais rápido que JOINS• ex. 1k-2k vezes para 80k items
2. Não encapsule seus índices no WHERE• Mantenha índices isolados no lado esquerdo da
condição• Select ... Where func(idx) = 20 [index ignored]• Select .. Where idx = otherfunc(20) [may use index]
3. Evite % no início do LIKE em um índice• Select ... Where idx LIKE(‘ABC%’) pode usar índice• Select ... Where idx LIKE(‘%XYZ’) fará full table scan
4. Use UNION ALL quando apropriado• default é UNION DISTINCT
5. Entenda Left/Right JOINS e use apropriadamente
http://dev.mysql.com/doc/refman/5.5/en/query-speed.html
Re-modelagem do esquema de dados1. Storage Engine (tipo de tabela) adequado
• InnoDB• MyISAM• Outros: Memory, Archive ...
2. Tipos de dados e tamanhos corretos• Tamanhos menores, melhor performance• Não use 255 VARCHAR para tudo• Considere o uso de ENUM e SET• Temporary Tables e Caches estouram tamanho total• PROCEDURE ANALYZE
3. Comprima strings longas• COMPRESS e UNCOMPRESS• No caso de InnoDB, mais importante para versões
anteriros a 5.5
http://www.mysql.com/why-mysql/white-papers/mysql_5.5_perf_myisam_innodb.phphttp://dev.mysql.com/doc/refman/5.5/en/procedure-analyse.html
MySQL Workbench SE análise automática e documentação do modelo de dados
Bônus: boas práticas gerais 1/2
1. atue nos gargalos2. não tenha vergonha de perguntar: Google, Forums, MyOracleSupport
3. monitore, seja pró ativo e aceite alguns conselhos: Advisors4. nunca faça mudanças no ambiente de produção, tenha um
bom conjunto de testes1. benchmark: DBT2, SysBench, mysqlslap, mybench2. testes automatizados com queries reais: general log, MySQL
Proxy, TCP Dump3. simule carga que se aproxima do ambiente de produção
5. defina objetivos e uma base de comparação (antes e depois)1. aumentar Transações Por Segundo (TPS)2. aumentar usuários simultâneos3. diminuir IO time wait
6. mude apenas uma coisa por vez1. identifique um possível conjunto de mudanças2. tente mudá-las separadamente3. tente combinações
Bônus: boas práticas gerais 2/2
7. monitore os resultados• performance das queries
• foco no gargalo - mais executadas e que tomam maior tempo
• Throughput• média dos tempos das queries melhor que tempos únicos
das queries• CPU• IO• Network bandwidth
8. documente e salve os resultados, mantenha seu histórico para seu uso e da equipe
MySQL Enterprise Monitor implementa boas práticas com muita produtividade
MySQL Essentials Webinars (Partes de 1 a 7)mysql.com/news-and-events/web-seminars/mysql-essentials.html
MySQL Training (incluindo MySQL Performance Tuning)education.oracle.com
MySQL Performance Forumforums.mysql.com/list.php?24
MySQL White Papersdev.mysql.com/why-mysql/white-papers
MySQL Enterprise Monitor (trial 30 dias) edelivery.oracle.com
Links
Subscrições MySQLV
alo
r A
gre
gad
o
Cluster Carrier Grade
Enterprise +
• Cluster Manager
Enterprise Edition
Standard +
• Plug-Ins• Enterprise Monitor• Enterprise Backup
StandardEdition
• MySQL Database• Premier Support• Workbench SE
Oracle Suporte para o MySQL
* MySQL Premier Support – 5 anos, começando pelo GA
Suporte Consultivo MySQL
DBA e
Desenvolvedores
My Oracle Support
Extensões Comerciais
• Enterprise Security• Plug-In: MySQL External Authentication
• Enterprise Scalability• Plug-In: MySQL Thread Pool
Windows Authentication
Connector
Connect as
1. MyDomain\joe faz login na aplicação com senha Windows
Connected
Authenticate
CREATE USER win_joe
IDENTIFIED WITH authentication_windows
AS ‘joe';
AppApp
LDAP/ADWindows
Auth
Connect as
2. MyDomain\joe é autenticado via LDAP/AD
Connect as
4. Tokens são verificados, o usuário win_joe é autenticado com Windows login, porém com privilégios do MySQL.
Connect as
3. A aplicação conecta-se ao MySQL com usuário win_joe.
Gestão Padrão do Thread Pool
Clientes Internos
Gestão Padrão doThread Pool
Conexões / Declarações
atribuídas aos Threads durante sua existência
Execução das Conexões das Threads
Clientes Externos
• Conexões são atribuídas a 1 thread durante a existência da Conexão. • A mesma thread é usada para todas as declarações de execução (single
threaded)
Com o Thread Pool Habilitado
Clientes Internos
Clientes Externos
Thread Pool
Grupo Thread 1
Threads 1 - 4096
Grupo Thread 2
Threads 4097 - 8193
Grupo Thread N
Threads 8194 - N
Grupo Thread 1
Grupo Thread 2
Grupo Thread N
• Pool contém um número de Grupo de Threads(default = 16), cada um gerencia até 4096 threads reusáveis
• Cada conexão é atribuída ao grupo de thread via round robin
mantém a performance em altas cargas de usuários/conexões
Execução das Conexões das Threads
MySQL Enterprise EditionComThread Pool
MySQL Community ServerSem Thread Pool
Comparativo com/sem Thread Pool
20x Melhor Escalabilidade comThread Pool
8 16 32 64128
256512
1,024
1,536
2,048
2,560
3,072
3,584
4,096
0
1,000
2,000
3,000
4,000
5,000
6,000
7,000
8,000
MySQL 5.5 Sysbench OLTP Leitura/Escrita
Conexões Simultâneas no Banco de Dados
Tra
ns
aç
õe
s p
or
se
gu
nd
o
MySQL 5.5.16Oracle Linux 6.1, Unbreakable Kernel 2.6.322 sockets, 24 cores, 2 X 12-coreIntel(R) Xeon(R) X5670 2.93GHz CPUs72GB DDR3 RAM2 X LSI SCSI Disk (MR9261-8i) (597GB)
MySQL Enterprise EditionComThread Pool
MySQL Community ServerSemThread Pool
3x Melhor Escalabilidade com Thread Pool
MySQL 5.5.16Oracle Linux 6.1, Unbreakable Kernel 2.6.322 sockets, 24 cores, 2 X 12-coreIntel(R) Xeon(R) X5670 2.93GHz CPUs72GB DDR3 RAM2 X LSI SCSI Disk (MR9261-8i) (597GB)
8 16 32 64128
256512
1,024
1,536
2,048
2,560
3,072
3,584
4,096
0
2,000
4,000
6,000
8,000
10,000
12,000
MySQL 5.5 Sysbench OLTP Apenas Leitura
Conexões Simultâneas no Banco de Dados
T
Comparativo com/sem Thread Pool
• Visão única e consolidada em todo o ambiente MySQL
• Auto-descoberta dos servidores MySQL, topologias de replicação
• Regras personalizáveis de monitorização e alertas
• Identificação de problemas antes que eles ocorram
• Reduz o risco de inatividade
• Facilita “scale out” sem exigir mais DBAs
Assistente virtual do DBA MySQL!
MySQL Enterprise Monitor
http://mysql.com/trials/
MySQL Enterprise Monitor: Query Analyzer
MySQL Enterprise Backup
• Anteriormente “InnoDB Hot Backup”• Online, non-locking backup & recovery
• Tables, Indexes
• Server, database, ou object-level
• Backups Lógico e Físico• Backups Completos ou Incrementais• Point-in-time recovery• Backups Comprimidos• Também oferece backup e recuperação para MyISAM• Multi-plataforma (Windows, Linux, Unix)• Certificado para Oracle Secure Backup (SBT 2.0)
Backups
Backups são até 3.5x mais rápidos do que mysqldump
Restores
Restore é até 16x mais rápido do que mysqldump - mysqldump performance não é linear (mais tables/indexes impacta na performance)- MySQL Enterprise performance é quase linear
Compressão do Backup
O tamanho do Backup é reduzido de 65% até 93%
Mais sobre MySQL
MySQL Treinamento e Certificação
MySQL Boot Camp Accelerated
MySQL Performance Tuning Boot Camp
Accelerated
MySQL for Begginers
MySQL for Database Administrators
MySQL Performance Tuning
MySQL High Availability
MySQL Cluster
MySQL DBA
MySQL Boot Camp Accelerated
MySQL for Developers
MySQL Performance Tuning Boot Camp
Accelerated
MySQL for Begginers
MySQL and PHP Developing Dynamic
Web Applicationg
MySQL Advanced Stored Procedures
MySQLDeveloper
http://education.oracle.com
TreinamentosCertificações
Opcional
Necessário
07-dez-2011
Registre-se:http://bit.ly/usLQoD
… qual versão do MySQL rodar?
… servidores down?
… os sistemas escalarão?
… política de segurança, muda?
… problemas de performance?
… onde, quando, como “tunar”?
… queries lentas, custosas?
… problemas de replicação?
… produtividade de desenv./QA?
… posso recuperar?
Questões Difíceis, Soluções Reais
• MySQL Enterprise Scalability• MySQL Enterprise Monitor, Advisors, Query Analyzer• MySQL Consultative Support
• MySQL Enterprise Monitor, Query Analyzer
• MySQL Enterprise Monitor, Advisors, Query Analyzer• MySQL Consultative Support
• MySQL Enterprise Monitor, Replication Monitor• Oracle Premier 7x24 Support
• MySQL Enterprise Scalability• MySQL Enterprise Monitor, Advisors, Query Analyzer• MySQL Consultative Support
• Oracle Premier 7x24 Support • MySQL Enterprise Monitor, Advisors• MySQL Enterprise High Availability
• MySQL External Authentication• MySQL Enterprise Monitor, Security Advisor
• MySQL Workbench SE• MySQL Enterprise Monitor, Query Analyzer
• Oracle Premier 7x24 Support
• MySQL Enterprise Backup
Obrigado!
Time MySQL Brasil
[email protected] [email protected]@oracle.com [email protected]
@MySQLBR meetup.com/MySQL-BR