Otimizar Mysql

[andredoom]

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

[Angel Junior]

Amigo,

Rode este comando:

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

e poste os resultados.

[andredoom]

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

[Angel Junior]

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.

[Clebson]

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

[JMonteiro]

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.

[Alexandre Duran]

Como o monteiro falou acima, seria bom um sistema com 64bits.