Clicky

Hospedado por Limestone Networks

Jump to content
  • Sign Up

Sign in to follow this  
andredoom

Otimizar Mysql

Recommended Posts

alguem ai sabe otimizar o mysql corretamente?

meu mysql fica gastando 10/20% da cpu sempre e nunca baixa...

o meu arquivo my.cnf ta assim:

[mysqld]

max_connections=1000

key_buffer_size=742M

max_allowed_packet=128M

table_cache=8096

sort_buffer_size=48M

read_buffer_size=48M

join_buffer_size=48M


read_rnd_buffer_size=32M

max_heap_table_size=1024M

tmp_table_size=1024M

myisam_sort_buffer_size=384M

query_cache_type=1

query_cache_limit=4M

query_cache_size=128M


thread_concurrency=16


innodb_buffer_pool_size=75M

thread_cache_size=4


[myisamchk]

key_buffer=256M

sort_buffer_size=256M

read_buffer=16M

write_buffer=16M

e um servidor de 4gb, 1TB de HD alguem sabe como fazer para otimizar melhor?

veja o processo abaixo:

PID--------------------OWNER--------PRIORITY--------CPU-------MEMORY-----------COMMAND

1544-------------------mysql--------------- 0-------------10.8--------14.5-----------------/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/hostname.err --pid-file=/var/lib/mysql/hostname.pid

Share this post


Link to post
Share on other sites

Amigo,

Rode este comando:

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

e poste os resultados.


"Não encontro defeitos. Encontro soluções. Qualquer um sabe queixar-se." H.Ford

Share this post


Link to post
Share on other sites

>> MySQLTuner 1.1.2 - 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 --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.56

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

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

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

[--] Data in MyISAM tables: 518M (Tables: 11692)

[--] Data in InnoDB tables: 76M (Tables: 2320)

[--] Data in MEMORY tables: 0B (Tables: 12)

[!!] Total fragmented tables: 2747

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

[--] Up for: 4h 33m 18s (1M q [121.899 qps], 54K conn, TX: 4B, RX: 173M)

[--] Reads / Writes: 59% / 41%

[--] Total buffers: 1.9G global + 176.2M per thread (1000 max threads)

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

[!!] Maximum possible memory usage: 174.0G (4349% of installed RAM)

[OK] Slow queries: 0% (29/1M)

[OK] Highest usage of available connections: 2% (24/1000)

[OK] Key buffer size / total MyISAM indexes: 742.0M/108.7M

[OK] Key buffer hit rate: 99.6% (2M cached / 7K reads)

[OK] Query cache efficiency: 70.5% (1M cached / 1M selects)

[!!] Query cache prunes per day: 80003

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

[!!] Joins performed without indexes: 256

[!!] Temporary tables created on disk: 45% (32K on disk / 72K total)

[OK] Thread cache hit rate: 94% (3K created / 54K connections)

[OK] Table cache hit rate: 52% (8K open / 15K opened)

[OK] Open file limit used: 77% (13K/17K)

[OK] Table locks acquired immediately: 99% (751K immediate / 752K locks)

[!!] InnoDB data size / buffer pool: 76.5M/75.0M

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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Enable the slow query log to troubleshoot bad queries

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 ***

query_cache_size (> 128M)

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

innodb_buffer_pool_size (>= 76M)

Share this post


Link to post
Share on other sites

Bom, Pelas indicações do sistema modifique os itens.

query_cache_size = 256M

join_buffer_size = 58.0M

innodb_buffer_pool_size = 76M

No entanto já existe o aviso que as otimizações estão em um uso maximo de memoria, seu serviço esta necessitando de mais memoria.


"Não encontro defeitos. Encontro soluções. Qualquer um sabe queixar-se." H.Ford

Share this post


Link to post
Share on other sites

Deixe o seu /etc/my.cnf com a seguinte configuração:

[mysqld]

max_connections = 300

key_buffer = 32M

myisam_sort_buffer_size = 32M

join_buffer_size = 1M

read_buffer_size = 1M

sort_buffer_size = 2M

table_cache = 4000

thread_cache_size = 286

interactive_timeout = 25

wait_timeout = 7000

connect_timeout = 10

max_allowed_packet = 16M

max_connect_errors = 10

query_cache_limit = 2M

query_cache_size = 12M

query_cache_type = 1

tmp_table_size = 16M

skip-innodb

local-infile=0

[mysqld_safe]

open_files_limit = 8192

[mysqldump]

quick

max_allowed_packet = 16M

[myisamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 16M

write_buffer = 16M

[mysqlhotcopy]

interactive-timeout


Clebson | SH

Share this post


Link to post
Share on other sites

Angel, não vi que o uso de memoria já esta alto.

O aviso que vi sobre memoria informa que o sistema é de 32bits e não será possível o MySQL utilizar mais que 2GB de ram.

Adicionar mais ram no sistema do jeito que está não vai trazer melhorias.

O script pede para trocar o sistema operacional por um 64bits, e ai sim pode ser necessário mais memoria ram.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.



×
×
  • Create New...