OPtimizar Mysql

[wayward]

Hola a todos.

Tengo un VPS (Centos 5.8) con 1&1 (Opteron Quad con 8GB de Ram)donde alojo Webs creadas con Wordpress.
Tengo problemas de rendimiento, y decidido analizar la DB con Mysqltuner.

El fichero my.cnf es:

Código MySQL:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
query_cache_size=128M
query_cache_limit=148M
query_cache_type=1
max_connections=200
collation_server=utf8_unicode_ci
character_set_server=utf8
delayed_insert_timeout=40
interactive_timeout=10
wait_timeout=3000
connect_timeout=300
thread_cache_size=16k
key_buffer=256M
join_buffer=2M
max_connect_errors=20
max_allowed_packet=8M
table_cache=96K
table_definition_cache=24K
open_files_limit = 24K
sort_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
thread_concurrency=2
myisam_sort_buffer_size=512M
myisam_max_sort_file_size = 2048M
innodb_file_per_table=1
innodb_buffer_pool_size=512M
log-slow-queries=/var/log/mysql-slow-queries.log
table_open_cache=96
low_priority_updates=1
concurrent_insert=ALWAYS
tmp_table_size=128M
max_heap_table_size=24M
server-id=3613594
log-bin=mysql-bin
log-error=mysql-bin.err
binlog_ignore_db=loquosex2013
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so
 
# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1
 
# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 

Y el resultado del test es:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8M (Tables: 272)
[--] Data in InnoDB tables: 493M (Tables: 231)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 232

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5m 30s (115K q [350.488 qps], 177 conn, TX: 76M, RX: 11M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 936.0M global + 26.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 6.0G (75% of installed RAM)
[OK] Slow queries: 0% (0/115K)
[OK] Highest usage of available connections: 2% (5/200)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.1M
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[OK] Query cache efficiency: 95.7% (110K cached / 115K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 767 sorts)
[!!] Temporary tables created on disk: 49% (645 on disk / 1K total)
[OK] Thread cache hit rate: 97% (5 created / 177 connections)
[!!] Table cache hit rate: 1% (96 open / 4K opened)
[OK] Open file limit used: 0% (82/24K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 493.5M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
tmp_table_size (> 128M)
max_heap_table_size (> 24M)
table_cache (> 96)

Que debería hacer para corregir los errores?

Muchas gracias de antemano.

[AJDM]

Saludos amigo;

Sabes que yo ando en las mismas; buscando un script que NO solamente me diga que está mal sino que sugiera que nuevo valor se deben colocar.

He paso dos días en la nube buscando opciones y encontré algo que llaman [URL="http://www.mysqlcalculator.com/"]mysql calculator[/URL] el cual te dá los valores base del mysql y tu vas jugando con los valores y al final te dice cuanto estás consimiendo en memoria con estos valores; pero a mi criterio eso tampoco sirve, lo ideal sería que partiendo de los datos del servidor (memoria, disco duro, cpu, etc) mas los datos del mysql (con mas de 48 horas de funcionamiento) sea posible generar un archivo my.cnf

Pero aun estoy en la busqueda de eso, si encuentro algo juralo que te aviso.

Juntos es mas facil...

[Gedeon]

¿cuales son los errores?

Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
tmp_table_size (> 128M)
max_heap_table_size (> 24M)
table_cache (> 96)

Esto no son errores sino recomendaciones generales. El propio mysql tiene un log para consultas que tardan demasiado que podrías usar si tuvieras ese problema pero veo esto

OK] Slow queries: 0% (0/115K)

osea que tu no tienes ese problema. Podría ir linea a linea pero veo que usas el 75% de la memoria disponible osea que tampoco tienes problemas de memoria ni tampoco de disco ni de conexiones concurrentes, etc.

Vamos que yo no veo el problema.