Optimizacion MYSQL

[wayward]

Hola.

Tengo cntratado un VPS com procesador AMD Opteron y 8 GB de RAM.

Se utilizan principalemte 3 BD con unas 115 tablas. Las tablas que mas peso tienen ocupan 200 MB cada una.

El resultado del mysqlturner es:

Código PHP:

[root@s15434104 ~]# ./mysqltuner.pl
 
 >>  MySQLTuner 1.2.0 - Major Hayden <[e-mail]>
 >>  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.5.30-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 187M (Tables: 274)
[--] Data in InnoDB tables: 55M (Tables: 277)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 9
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 35m 47s (17M q [291.937 qps], 29K conn, TX: 16B, RX: 1B)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 1.3G global + 35.2M per thread (500 max threads)
[!!] Maximum possible memory usage: 18.5G (231% of installed RAM)
[OK] Slow queries: 0% (371/17M)
[OK] Highest usage of available connections: 2% (11/500)
[OK] Key buffer size / total MyISAM indexes: 32.0M/28.2M
[OK] Key buffer hit rate: 100.0% (114M cached / 45K reads)
[OK] Query cache efficiency: 84.7% (14M cached / 17M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 131K sorts)
[!!] Temporary tables created on disk: 46% (114K on disk / 248K total)
[OK] Thread cache hit rate: 99% (11 created / 29K connections)
[OK] Table cache hit rate: 30% (617 open / 2K opened)[HIGHLIGHT="MySQL"]
 

[OK] Open file limit used: 7% (604/8K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 55.4M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

[root@s15434104 ~]#[/HIGHLIGHT]

El fichero MY.CNF es:

Código MySQL:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
collation_server=utf8_unicode_ci
character_set_server=utf8
max_connections=500
query_cache_size=512M
query_cache_limit=512M
query_cache_type=1
delayed_insert_timeout=40
interactive_timeout=10
wait_timeout=3000
connect_timeout=300
thread_cache_size=16k
key_buffer_size=32M
join_buffer_size=25M
max_connect_errors=20
max_allowed_packet=8M
table_cache=512M
table_definition_cache=24K
open_files_limit = 24K
sort_buffer_size=2M
read_buffer_size=4M
read_rnd_buffer_size=4M
thread_concurrency=2
myisam_sort_buffer_size=512M
myisam_max_sort_file_size = 1024M
innodb_file_per_table=1
innodb_buffer_pool_size=256M
slow-query-log = 1
slow-query-log-file = /var/log/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
table_open_cache=256M
low_priority_updates=1
concurrent_insert=ALWAYS
tmp_table_size=256M
max_heap_table_size=256M
server-id=3613594
log-bin=mysql-bin
log-error=mysql-bin.err
log-queries-not-using-indexes= /var/log/mysql-slow2.log
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]
open_files_limit = 8192
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 

Que puedo hacer para optimizar el servidor?

Muchas gracias.

[mortiprogramador]

<saludos>
Con optimizar el servidor, a que se hace referencia?
Mejora en tiempos de consultas, o reportes,
o almacenamiento como tal?
</saludos>