Tema: Optimizacion MYSQL
Ver Mensaje Individual
  #1 (permalink)  
Antiguo 17/07/2013, 14:00
wayward
(Desactivado)
  
Fecha de Ingreso: enero-2004
Mensajes: 136
Antigüedad: 21 años, 1 mes
Puntos: 0
Optimizacion MYSQL
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: 
Ver original
  1. [root@s15434104 ~]# ./mysqltuner.pl
  2.  
  3.  >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
  4.  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
  5.  >>  Run with '--help' for additional options and output filtering
  6.  
  7. -------- General Statistics --------------------------------------------------
  8. [--] Skipped version check for MySQLTuner script
  9. [OK] Currently running supported MySQL version 5.5.30-log
  10. [OK] Operating on 64-bit architecture
  11.  
  12. -------- Storage Engine Statistics -------------------------------------------
  13. [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
  14. [--] Data in MyISAM tables: 187M (Tables: 274)
  15. [--] Data in InnoDB tables: 55M (Tables: 277)
  16. [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
  17. [!!] Total fragmented tables: 9
  18.  
  19. -------- Security Recommendations  -------------------------------------------
  20. [OK] All database users have passwords assigned
  21.  
  22. -------- Performance Metrics -------------------------------------------------
  23. [--] Up for: 16h 35m 47s (17M q [291.937 qps], 29K conn, TX: 16B, RX: 1B)
  24. [--] Reads / Writes: 96% / 4%
  25. [--] Total buffers: 1.3G global + 35.2M per thread (500 max threads)
  26. [!!] Maximum possible memory usage: 18.5G (231% of installed RAM)
  27. [OK] Slow queries: 0% (371/17M)
  28. [OK] Highest usage of available connections: 2% (11/500)
  29. [OK] Key buffer size / total MyISAM indexes: 32.0M/28.2M
  30. [OK] Key buffer hit rate: 100.0% (114M cached / 45K reads)
  31. [OK] Query cache efficiency: 84.7% (14M cached / 17M selects)
  32. [OK] Query cache prunes per day: 0
  33. [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 131K sorts)
  34. [!!] Temporary tables created on disk: 46% (114K on disk / 248K total)
  35. [OK] Thread cache hit rate: 99% (11 created / 29K connections)
  36. [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: 
Ver original
  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. user=mysql
  5. collation_server=utf8_unicode_ci
  6. character_set_server=utf8
  7. max_connections=500
  8. query_cache_size=512M
  9. query_cache_limit=512M
  10. query_cache_type=1
  11. delayed_insert_timeout=40
  12. interactive_timeout=10
  13. wait_timeout=3000
  14. connect_timeout=300
  15. thread_cache_size=16k
  16. key_buffer_size=32M
  17. join_buffer_size=25M
  18. max_connect_errors=20
  19. max_allowed_packet=8M
  20. table_cache=512M
  21. table_definition_cache=24K
  22. open_files_limit = 24K
  23. sort_buffer_size=2M
  24. read_buffer_size=4M
  25. read_rnd_buffer_size=4M
  26. thread_concurrency=2
  27. myisam_sort_buffer_size=512M
  28. myisam_max_sort_file_size = 1024M
  29. innodb_file_per_table=1
  30. innodb_buffer_pool_size=256M
  31. slow-query-log = 1
  32. slow-query-log-file = /var/log/mysql-slow.log
  33. long_query_time = 1
  34. log-queries-not-using-indexes
  35. table_open_cache=256M
  36. low_priority_updates=1
  37. concurrent_insert=ALWAYS
  38. tmp_table_size=256M
  39. max_heap_table_size=256M
  40. server-id=3613594
  41. log-bin=mysql-bin
  42. log-error=mysql-bin.err
  43. log-queries-not-using-indexes= /var/log/mysql-slow2.log
  44. binlog_ignore_db=loquosex2013
  45.  
  46. # Disabling symbolic-links is recommended to prevent assorted security risks
  47. symbolic-links=0
  48.  
  49. # Semisynchronous Replication
  50. # http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
  51. # uncomment next line on MASTER
  52. ;plugin-load=rpl_semi_sync_master=semisync_master.so
  53. # uncomment next line on SLAVE
  54. ;plugin-load=rpl_semi_sync_slave=semisync_slave.so
  55.  
  56. # Others options for Semisynchronous Replication
  57. ;rpl_semi_sync_master_enabled=1
  58. ;rpl_semi_sync_master_timeout=10
  59. ;rpl_semi_sync_slave_enabled=1
  60.  
  61. # http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
  62. ;performance_schema
  63.  
  64. [mysqld_safe]
  65. open_files_limit = 8192
  66. log-error=/var/log/mysqld.log
  67. pid-file=/var/run/mysqld/mysqld.pid

Que puedo hacer para optimizar el servidor?

Muchas gracias.