Dicas para Otimizar a Performance do MySQL

porLuis Augusto Moretto

Dicas para Otimizar a Performance do MySQL

Como otimizar a perfomance do Mysql para a minha cloud de forma customizada?

Performance TIPS Apache Mysql
Dicas para melhorar a Perfomance do Mysql

Introdução

Você sabe porque rodar o servidor de banco de dados MySQL com as configurações otimizadas para uma determinada VM (cloud)?

A razão é que previne a lentidão e maximiza o tempo de resposta do serviço. Ou seja a otimiza a Performance e a robustez.

Consequentemente o SGBD consegue lidar com um número maior de requisições ao serviço. 

A questão que fica é: como identificar os parâmetros a serem customizados em meu banco de dados de forma a otimizar a performance? 

identificando problemas de performance

Para identificar os problemas de performance vamos utilizar um projeto do Open Source do GitHub. O MySQLTuner é um script escrito em Perl que permite que você verifique uma instalação MySQL.

O Script recupera as variáveis de configuração e os dados de sua Cloud. Após a coleta apresenta em um formato breve, juntamente com algumas sugestões básicas de desempenho.

O MySQLTuner na última versão apresenta ~ 300 indicadores.

Para rodar o MySQLTurner em sua Cloud utilize o seguinte comando:

curl -L http://mysqltuner.pl/ | perl

relatório de performance

Após a execução do Script será apresentado um relatório como no Exemplo:

 >>  MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

Arquivos de log

Recomendações para os arquivos de log:

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(26K)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

Storage

Recomendações para sistema de Storage:

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 63M (Tables: 341)
[--] Data in MyISAM tables: 3K (Tables: 1)
[OK] Total fragmented tables: 0

Segurança

Recomendações de segurança:

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

Performance

Recomendações performance:

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 23h 19m 40s (95K q [1.133 qps], 3K conn, TX: 91M, RX: 22M)
[--] Reads / Writes: 55% / 45%
[--] Binary logging is disabled
[--] Physical Memory     : 488.5M
[--] Max MySQL memory    : 336.4M
[--] Other process memory: 66.6M
[--] Total buffers: 176.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 184.5M (37.77% of installed RAM)
[OK] Maximum possible memory usage: 336.4M (68.88% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/95K)
[OK] Highest usage of available connections: 5% (8/151)
[!!] Aborted connections: 17.28%  (528/3055)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 38% (742 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 3K connections)
[!!] Table cache hit rate: 9% (416 open / 4K opened)
[OK] Open file limit used: 1% (16/1K)
[OK] Table locks acquired immediately: 100% (275 immediate / 275 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/46.0K
[!!] Read Key buffer hit rate: 84.0% (106 cached / 17 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/63.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.80% (1253571 hits/ 1256089 total)
[!!] InnoDB Write Log efficiency: 30.55% (6808 hits/ 22284 total)
[OK] InnoDB log waits: 0.00% (0 waits / 15476 writes)
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

Recomendações

Esta sessão tem informações essenciais. É o ponto de partida para começar a personalização dos parâmetros do MySQL 😀

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (431)
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 431)
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Dicas para otimização

Com base nas informações do MysqlTurner abra o arquivo de configuração de seu Mysql.

No Ubuntu ele fica no seguinte caminho (instalação default):

/etc/mysql/mysql.conf.d/mysqld.cnf

Após alterar uma configuração reinicie seu mysql SGBD com o seguinte comando no Bash:

systemctl restart mysqld
  • Altere uma configuração de cada vez!
  • A cada mudança realize testes e rode novamente o MySQLTurner para validar!
  • Se você roda sua VM em uma Cloud compartilhada adicione a propriedade skip-name-resolve: (Assim o SGBD utiliza apenas endereços IP e não nomes de host para combinar hosts conectados a linhas nas tabelas banco MySQL.  Assim apenas as contas especificadas nessas tabelas usando endereços IP podem ser usadas para autenticação.)
  • Faça uma cópia de segurança do seu arquivo de configuração mysqld.cnf. Caso algo saia errado você tem um ponto de controle para restaurar o serviço;

Happy Coding! 😀