Turbinando o desempenho do
MySQL na Nuvem
Marcus Vinícius Soares
[email protected]
! Agenda
• Queries e Índices
• Arquitetura de Aplicações
• Técnicas de Sharding/Master
Slaves/Federation/Replication
Tuning Ground Rules
•
•
•
•
1 Parâmetro de cada vez
Teste cada alteração
Mantenha um histórico
Evite o transtorno da otimização obsessiva
MySQL – Processo de Otimização
Fácil
• MySQL Config
• Parâmetros de Kernel
• Índices
• Cache
Médio
• Infra
• Sistema Operacional
• Versão do MySQL
• Storage Engine
Difícil
• Modelo de Dados
• Arquitetura da Aplicação
Complexidade atual (e crescendo)
O que mudou?
• Nossos aplicativos foram enjaulados
Modelo Tradicional com
Máquina Virtual
(ESX, HyperV, KVM, XEN)
Lightweight
Virtualization
(Jail, Zones, nPartitions,
Vpars, IVms, PowevVM,
OpenVZ, LXC)
O que mudou?
• Restrição a recursos
– Processador
– Memória
– Rede
– IO
O que mudou?
• Fornecedores mentem (clientes também)
• Concorrência
Áreas para ajustes
•
•
•
•
Sistema Operacional (SO)
Rede (Network)
Armazenamento (Storage)
MySQL Config (Conf)
OS
• Swappiness
echo 0 > /proc/sys/vm/swappiness
sysctl.conf : vm.swappiness = 0
• Dirty Ratio
– vm.dirty_background_ratio % memória que inicia a
escrita para os discos
– vm.dirty_ratio % da memória que, quando atingido,
bloqueia as escritas e escreve as páginas para o disco
– Entre 5 e 60
echo 5 > /proc/sys/vm/dirty_background_ratio
echo 60 > /proc/sys/vm/dirty_ratio
vm.dirty_background_ratio = 5
vm.dirty_ratio=5
OS
IO na VM
Uso de
ciclos de
cpu para
ordernar o
IO na VM
Envia as
requisições
para o Host
HPV reordena
baseado no
seu
algoritmo
• Scheduler: cfq, noop, anticipatory, deadline
Grub.conf
title Red Hat Enterprise Linux Server (2.6.32431.el6.x86_64)
root (hd0,0)
kernel /vmlinuz-2.6.32-431.el6.x86_64 ro
root=/dev/mapper/Volume00-root .... elevator=noop
initrd /initramfs-2.6.32-431.el6.x86_64.img
echo noop
>/sys/block/sda/queue/scheduler
HPV envia
para o HW
OS
• Malloc Lib
– jemalloc OU tcmalloc( caso tenha 8+ cores )
– tcmalloc vem junto com o MySQL
[mysqld_safe]
malloc-lib=tcmalloc
• I/O Queue Size
– iostat -x : avgqu-sz
Select magic_number from hat where tunnable =
‘I/O Queue Size’
Empty set (0.00 sec)
echo 99999 >
/sys/block/[DEVICE]/queue/nr_requests
OS – NUMA
• “swap insanity”
– (http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-thenuma-architecture/)
• numactl –hardware
Network
• Geralmente culpase o banco
• Latencia (distância
e hops)
• 1G obrigatório
• trunk
recomendado
• 10 G mais acessível
echo 8192 >
/proc/sys/net/ipv4/tcp_max_syn_
backlog
net.ipv4.ip_local_port_range="1
024 64000“
net.core.somaxconn = 1024
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380
16777216
net.ipv4.tcp_wmem = 4096 65536
16777216
net.ipv4.max_tw_buckets=360000
net.core.netdev_max_backlog =
2500
ifconfig eth0 txqueuelen 1000
FS/Storage
• SSD / FusionIO
• Partições separadas para o / , tmpfs, /var/lib/mysql
• Ajuste o tamanho do bloco (
– innodb datafiles: 16Kb
– Innodb logs files: 128kb+
• Ajuste os limites no /etc/security/limits.conf
– # /etc/security/limits.conf
mysql
mysql
mysql
mysql
soft
soft
hard
hard
nproc
nofile
nproc
nofile
4096
4096
63536
63536
FS/Storage
• Mount options
– Ext4: ext4
(rw,noatime,nodiratime,nobarrier,data=ordered)
– xfs (rw,
noatime,nodiratime,nobarrier,logbufs=8,logbsize
=32k)
– NFS
(rw,bg,tcp,noatime,nodiratime,actimeo=??,nocto,
hard,rsize=1048576,wsize=1048576,intr,nolock)
FS/Storage
• Tmpfs
mkdir -p /mnt/tmpfs-mysql/
mount -t tmpfs -o size=1024M tmpfs /mnt/tmpfsmysql
entrada no /etc/fstab
tmpfs
/mnt/tmpfs-mysql
tmpfs
size=1024M,nr_inodes=0 0
0
(my.cnf) tmpdir
= /mnt/tmpfs-mysql/
• Dtrace
mysqld
•
•
•
•
•
•
•
•
•
•
Mysql 5.6
max_connections = [DIMINUA]
max_user_connections = [BAIXO]
performance_schema = off
default_storage_engine = InnoDB
back_log = 500
event_scheduler = ON/OFF
skip_name_resolve
slow_query_log = 1
key_buffer_size (MyISAM)
Query Cache
•
•
•
•
Off – mysql 5.6 já é desabilitado
Gargalo - Single thread
Fragmentação da memória
MySQL Query Cache Fragmentation Slows
Down the Server (Doc ID 1308051.1)
• Dever estar na aplicação
Table caching
•
•
•
•
table_open_cache
table_defintion_cache
table_cache_instances = 8 ou 16
thread_cache_size
– Limitado pelo processo do usuário (limite os)
InnoDB
• http://dev.mysql.com/doc/refman/5.6/en/innodbparameters.html
• innodb_buffer_pool_size = 80% RAM
– Mas..não tenha swap
• innodb_buffer_pool_instances >= 8
• innodb_spin_wait_dela = 96 quando muito
concorrência
• innodb_log_file_size >= 2047 no 5.6
• innodb_file_per_table = ON
• innodb_thread_concurrency = 36 in 5.6
– Alinhas ao disponível se < 32 Cores
InnoDB Flushing
• innodb_flush_log_at_trx_commit = 1
// sync no disco em cada commit – melhor durabilidade
• innodb_flush_log_at_trx_commit = 2
// sync / 1sec – escreve sempre - boa performance
• innodb_flush_log_at_trx_commit = 0
// sync e escrita / 1 sec - melhor performance – NOT
ACID
• innodb_flush_method = O_DIRECT
// Linux, skips the FS cache
Pre-Cache
• Pre-cache
– select * from DERP_TABLE
– select count(*) DERP_TABLE
Resumo
Avisos e Perguntas
Marcus Vinícius Soares
[email protected]
http://www.uolhost.com.br/