Tive conhecimento de um novo script PERL que ajuda na optimização do MySQL. O script em questão é o MySQLTuner.

Antes de aplicar este script aconselho a leitura do post anterior sobre a otimização do MySQL em Linux.

Instalar e correr o MySQLTuner - High-performance MySQL optimization script

Fazer o download do script

wget mysqltuner.pl

Correr o script (tem que ter o PERL instalado)

perl mysqltuner.pl

Exemplo do relatório para um caso real

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login: root

Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.48-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 7G (Tables: 778)

[!!] Total fragmented tables: 57

-------- Performance Metrics -------------------------------------------------

[--] Up for: 9d 4h 35m 34s (1B q [1K qps], 9M conn, TX: 15889B, RX: 383B)

[--] Reads / Writes: 87% / 13%

[--] Total buffers: 6.4G global + 46.2M per thread (400 max threads)

[!!] Maximum possible memory usage: 24.5G (314% of installed RAM)

[OK] Slow queries: 0% (34K/1B)

[!!] Highest connection usage: 100%  (401/400)

[OK] Key buffer size / total MyISAM indexes: 2.0G/926.3M

[OK] Key buffer hit rate: 100.0% (158B cached / 694K reads)

[OK] Query cache efficiency: 78.8% (964M cached / 1B selects)

[!!] Query cache prunes per day: 5172787

[OK] Sorts requiring temporary tables: 0% (7K temp sorts / 31M sorts)

[!!] Joins performed without indexes: 63643

[OK] Temporary tables created on disk: 16% (5M on disk / 33M total)

[OK] Thread cache hit rate: 99% (7K created / 9M connections)

[OK] Table cache hit rate: 48% (5K open / 11K opened)

[OK] Open file limit used: 7% (6K/80K)

[OK] Table locks acquired immediately: 99% (380M immediate / 382M locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Reduce your overall MySQL memory footprint for system stability

    Reduce or eliminate persistent connections to reduce connection usage

    Increasing the query_cache size over 128M may reduce performance

    Adjust your join queries to always utilize indexes

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    max_connections (> 400)

    wait_timeout (< 300)

    interactive_timeout (< 300)

    query_cache_size (> 512M) [see warning above]

    join_buffer_size (> 16.0M, or always use indexes with joins)

O relatório está dividido em várias secções. No final do diagnóstico são sugeridas as variáveis que devem ser alteradas no ficheiro de configuração do MySQL my.cnf.

Finalmente devemos aplicar as sugestões e analisar o comportamento do sistema. Este script poupa muito tempo de análise e interpretação dos imensos parâmetros passíveis de otimização. Este processo deve ser revisto regularmente, principalmente se acontecerem mudanças na quantidade de tráfego a chegar ao sistema.