[Ajuda] Otimizar Mysql

[Jefferson]

Salve, salve!

 

Estou querendo otimizar o MySQL de um de nossos servidores, onde a CPU do MySQL está 14.2% e a Memória está 29.7%.

 

Segue configuração utilizada no my.cnf:

 

[mysqld]

local-infile = 0

set-variable = max_connections=500

log-slow-queries

safe-show-database

query_cache_size = 500M

join_buffer_size = 2G

tmp_table_size = 950M

max_heap_table_size = 820M

thread_cache_size = 4

table_cache = 13000

innodb_buffer_pool_size = 1G

group_concat_max_len = 1048576

max_allowed_packet = 16777216

open_files_limit=50000

[safe_mysqld]

open-files-limit = 32000

 

 

 

Alguma solução/dica para melhorar a otimização?

 

O problema é que se eu aumento o valor do table_cache, alguns bancos de dados deixam de funcionar corretamente, apresentando erros nas consultas.

 

Obrigado a todos.

[Jefferson]

 

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

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

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

 

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

[[0;34m--[0m] Skipped version check for MySQLTuner script

[[0;32mOK[0m] Currently running supported MySQL version 5.1.68-cll

[[0;31m!![0m] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

 

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

[[0;34m--[0m] Status: [0;32m+Archive [0m[0;31m-BDB [0m[0;31m-Federated [0m[0;32m+InnoDB [0m[0;31m-ISAM [0m[0;31m-NDBCluster [0m

[[0;34m--[0m] Data in MyISAM tables: 14G (Tables: 64511)

[[0;34m--[0m] Data in InnoDB tables: 1G (Tables: 19576)

[[0;34m--[0m] Data in MEMORY tables: 126K (Tables: 362)

[[0;31m!![0m] Total fragmented tables: 22137

 

-------- Security Recommendations  -------------------------------------------

[[0;32mOK[0m] All database users have passwords assigned

 

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

[[0;34m--[0m] Up for: 2m 24s (26K q [183.417 qps], 1K conn, TX: 51M, RX: 3M)

[[0;34m--[0m] Reads / Writes: 58% / 42%

[[0;34m--[0m] Total buffers: 2.3G global + 2.0G per thread (500 max threads)

[[0;31m!![0m] Allocating > 2GB RAM on 32-bit systems can cause system instability

[[0;31m!![0m] Maximum possible memory usage: 1003.6G (12700% of installed RAM)

[[0;32mOK[0m] Slow queries: 0% (0/26K)

[[0;32mOK[0m] Highest usage of available connections: 7% (39/500)

[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 8.0M/1.3G

[[0;32mOK[0m] Key buffer hit rate: 98.8% (189K cached / 2K reads)

[[0;32mOK[0m] Query cache efficiency: 60.4% (10K cached / 18K selects)

[[0;32mOK[0m] Query cache prunes per day: 0

[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)

[[0;31m!![0m] Joins performed without indexes: 55

[[0;31m!![0m] Temporary tables created on disk: 30% (601 on disk / 1K total)

[[0;32mOK[0m] Thread cache hit rate: 90% (108 created / 1K connections)

[[0;32mOK[0m] Table cache hit rate: 99% (1K open / 1K opened)

[[0;32mOK[0m] Open file limit used: 4% (1K/32K)

[[0;32mOK[0m] Table locks acquired immediately: 99% (13K immediate / 13K locks)

[[0;31m!![0m] InnoDB data size / buffer pool: 1.3G/1.0G

 

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

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    MySQL started within last 24 hours - recommendations may be inaccurate

    Adjust your join queries to always utilize indexes

    Temporary table size is already large - reduce result set size

    Reduce your SELECT DISTINCT queries without LIMIT clauses

Variables to adjust:

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

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

    join_buffer_size (> 2.0G, or always use indexes with joins)

    innodb_buffer_pool_size (>= 1G)

 

 

 

Este é o resultado do MySQL tuner.

[Jesmarcelo]

Você precisa diminuir os valores ou os serviços poderão cair:

 

[[0;31m!![0m] Maximum possible memory usage: 1003.6G (12700% of installed RAM)

 

 

12700% da RAM disponivel está configurada para o MySQL.

[joaopaulo]

cara, seu my.cnf esta TOTALMENTE ERRADO.... 

 

Poste aqui as config de sua máquina e diga o uso.

[Jefferson]

cara, seu my.cnf esta TOTALMENTE ERRADO.... 

 

Poste aqui as config de sua máquina e diga o uso.

 

Bom dia, segue configuração:

 

[mysqld]

local-infile = 0

set-variable = max_connections=500

log-slow-queries

safe-show-database

query_cache_size = 500M

join_buffer_size = 2G

tmp_table_size = 950M

max_heap_table_size = 820M

thread_cache_size = 4

table_cache = 13000

innodb_buffer_pool_size = 1G

group_concat_max_len = 1048576

max_allowed_packet = 16777216

open_files_limit=50000

[safe_mysqld]

open-files-limit = 32000

 

 

USO ATUAL:

 

CPU: 16.5

MEMÓRIA: 28.0

 

Poderia me ajudar?

[joaopaulo]

Config da máquina: quad core, dual core, 2gb, 4gb ram....

[Jefferson]

Intel® Xeon® CPU E3-1270 V2 @ 3.50GHz

8 GB DE MEMÓRIA

[joaopaulo]

Okay, esse servidor tem muito banco de dados para pouca ram. 16GB seria o mínimo necessário para funcionar decentemente.

 

Pense seriamente em colocar 32GB de RAM. 85 mil tabelas de banco de dados é muitissíma coisa.

[mysqld]
#skip-locking
#skip-innodb
#skip-name-resolve
#skip-networking
#skip-bdb
local-infile=0

key_buffer_size=1400M 
sort_buffer=1M
join_buffer=1M
max_join_size=2M

thread_concurrency=16
thread_cache=8


table_cache=2000
interactive_timeout=30
wait_timeout=30

max_heap_table_size=64M
tmp_table_size=2500M

query_cache_limit=1M
query_cache_size=512M

max_connections=500
max_user_connections=30

open-files-limit=6000

innodb_buffer_pool_size=1400M
innodb_file_per_table=1
innodb_log_file_size=5M
innodb_thread_concurrency=6

Outra coisa é,  você precisa analisar, reparar e otimizar os bds todo dia... coloque isso no seu cron:

10 3 * * * mysqlcheck -Aa; mysqlcheck -Ao; mysqlcheck -Ar;

Antes de por no cron, execute isso no SSH (apenas: mysqlcheck -Aa), se houver algum erro, use o cron abaixo:

 

 

10 3 * * * mysqlcheck -Aa -u root -p(SUA SENHA, NAO POR ESPAÇ;O ENTRE O p e a senha); mysqlcheck -Ao -u root -p(SUA SENHA, NAO POR ESPAÇO ENTRE O p e a senha); mysqlcheck -Ar -u root -p(SUA SENHA, NAO POR ESPAÇO ENTRE O p e a senha);

E  para finalizar: não saia de maneira alguma incrementando variáveis indicadas pelo Mysqltuner. Elas não representam a necessidade real de sua aplicaçãoo... vi que seu max_join_size estava em 2GB.. isso é um exagero tremendo, sem noção total do impacto disso no load  em servidor compartilhado, 2 MBs  são mais do que suficientes, se fosse um servidor de uma aplicação específica como sei lá, um portal gigantesco (g1 vamos assim dizer) era outros 500s.
 

Abraço

 

[Jesmarcelo]

João,

 

Que tipo de analise/calculo você faz para chegar ao valor ideal de configuração para o servidor/aplicação ?

[joaopaulo]

João,

 

Que tipo de analise/calculo você faz para chegar ao valor ideal de configuração para o servidor/aplicação ?

 

 

Experiência... depois de muito quebrar a cara com Mysql, comecei a estudar o efeito de cada variável daquelas ali e hoje é meio automático configurar o Mysql, é bater o olho e saber qual o melhor valor. Claro que tem algumas específicas como:

sort_buffer=1M
join_buffer=1M
max_join_size=2M
query_cache_limit=1M
query_cache_size=512M
max_user_connections=30

Que são as mais importantes para um desempenho satisfatório em servidores compartilhados. 

 

Por exemplo (se você souber SQL vai ficar mais fácil entender): imagine essa query: 

SELECT * from pedidos as p 
LEFT JOIN estados as e ON p.estado = e.idEstado
LEFT JOIN clientes as c ON c.cpf = p.cpf
WHERE c.cpf = '0101010101'

Não é nada otimizada e só vai carregar o servidor a toa. Se seguirmos o que o mysqltuner disse para o amigo, de setar o max_join_size para 2GB, ele iria juntar essas tabelas até estourar o buffer de 2GB. Imagine se cada uma dessas tabelas tivesse 1gb de dados... O load iria pro espaço devido a uma query bem, mas bem mal escrita. Entenda que raramente você encontrará soluções em PHP com o SQL bem feito, iniciando pelo próprio WHMCS. E isso acontece em 99,9% dos servidores compartilhados. 

 

Assim sendo, porque juntar milhões de linhas de 3 tabelas se o gajo só precisa de uns poucos resultados? Então... Limite ele a 2MB, que vai dar para juntar umas 2000 linhas de dados entre as três tabelas. Se ele não conseguir encontrar o que precisa em 2000 linhas, é hora de otimizar suas querys.

 

Se você ter um servidor de email marketing com interspire,é interessante deixar esse valor alto, visto que o Interspire ao montar o envio de uma campanha, utilza muito join e são sempre milhares e milhares de linhas, eu uso entre 5 a 40MB com o Interspire.

 

Outra variável legal é a sort_buffer.

 

Imagine que você tenha a seguinte query:

SELECT * FROM pedidos

Mais uma vez, uma query sem otimização. Provavelmente quem faz esse tipo de query só quer listar um x de pedidos... ou sei lá, só quer pegar alguma coisa.

 

Se você configurar o sort_buffer para um valor muito alto, ele vai ir listando a tabela... até explodir a memória ou load.. É melhor dar um limite também (1MB), se o gajo não encontrar que precisa em 1000 linhas, é hora de otimizar sua query.

 

Enfim, depois de estudar pacas o que cada variável faz, acaba ficando "automático" configurar o my.cnf. O mysqltuner é importante para te dar o tamanho de Keys do Innodb e MyIsam, bem como quantidade de tabelas (util para calcular o table_cache) e outros dados, mas não siga as dicas dele de otimização.

 

Att,

 

João Paulo