Jump to content
Limestone de cara nova! ×
Portal do Host

[Ajuda] Otimizar Mysql


Jefferson
 Share

Recommended Posts

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.
Link to comment
Share on other sites



 

 >>  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.
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
 Share

  • Recently Browsing   0 members

    No registered users viewing this page.



×
×
  • Create New...

Important Information