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:
Ver original[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
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.
logtable_open_cache=96
low_priority_updates=1
concurrent_insert=ALWAYS
tmp_table_size=128M
max_heap_table_size=24M
server-id=3613594
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]
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.