Análisis de MySQLTuner script

[Chismopoly]

Bueno aquí le dejo el resultado de el script MySQLTuner. bien lo que esta pasando es que no entiendo la mayoría de cosas para poder optimizar my.cnf si alguien me puede ayudar gracias

Código:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-log
[!!] 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: 72M (Tables: 70)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 2m 11s (2M q [35.312 qps], 120K conn, TX: 2B, RX: 324M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 274.0M global + 80.4M per thread (2000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 157.4G (3987% of installed RAM)
[OK] Slow queries: 2% (50K/2M)
[OK] Highest usage of available connections: 1% (21/2000)
[OK] Key buffer size / total MyISAM indexes: 184.0M/34.7M
[OK] Key buffer hit rate: 99.8% (893M cached / 1M reads)
[OK] Query cache efficiency: 64.2% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 290K sorts)
[!!] Temporary tables created on disk: 45% (129K on disk / 284K total)
[OK] Thread cache hit rate: 99% (21 created / 120K connections)
[!!] Table cache hit rate: 0% (39 open / 14K opened)
[OK] Open file limit used: 0% (69/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 2024)

[Adsl5mb]

Bueno, te ha arrojado muchisimas cosas por optimizar:

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
Esa quiere decir que tienes un sistema operativo de 32 Bits y que el sistema no puede aprovechar al completo la memoria RAM que tiene.

[!!] Maximum possible memory usage: 157.4G (3987% of installed RAM)
Ese otro problema es gravisimo ya que se le esta dejando a MySQL que consuma casi 160 GB de RAM cuando el servidor quizás tenga 4 GB, esto se soluciona reduciendo los buffer en el my.cnf

Estos otros cambios te dice que subas los siguientes valores a un número mayor al que dice:
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 2024)

y estas otras son recomendaciones que te hace el script:
Add skip-bdb to MySQL configuration to disable BDB
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


Si tienes un proveedor administrado lo mejor es que les digas a ellos que miren esos valores y vean la posibilidad de optimizarlos, el MySQLTuner es muy útil pero hay muchos otros valores que mirar, postea el contenido de tu my.cnf si lo deseas para ver que otros cambios se pueden hacer.

[WebTech]

Adsl5mb está en lo correcto, pero no te aventures a hacer cambios si no tienes conocimiento sobre sistemas o servicios, contrata a algún administrador de sistemas o empresa que lo haga por ti, o bien servicios manejados de tu proveedor actual.

Saludos,

[Chismopoly]

Aquí esta my.cnf y la verdad es que el servidor es administrado por mi y no tengo los fondos suficiente para pagar una empresa que me administre el servidor... por eso voy a tratar de yo hacerlo lo mejor posible y así aprender mas... gracia por su ayuda

Código:

# The following options will be passed to all MySQL clients
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock


# Here follows entries for some specific programs

[mysql.server]
user=mysql
basedir=/var/lib

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#
skip-innodb
skip-locking

# CONNECTION LIMITS
max_connection = 2000
max_user_connections = 5000

# QUERY-CACHE
query_cache_limit=16M
query_cache_size=64M
query_cache_type=1
log-slow-queries=/var/log/mysql-slow-queries.log
long_query_time = 5
log-queries-not-using-indexes

# TIMEOUTS
interactive_timeout=20
wait_timeout=20
connect_timeout=6
max_connect_errors=10

# TUNING
max_allowed_packet=16M

thread_cache_size=192

table_cache=2024
key_buffer=184M
join_buffer=32M
record_buffer=32M
sort_buffer_size=32M
read_buffer_size=16M
myisam_sort_buffer_size=128M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

# Don't listen on a TCP/IP port at all. This can be a security enhancement.
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

Aquí esta de nuevo el Análisis de MySQLTuner script lo vi de nuevo y me dios otros dato y ya mysql tiene 1 dia y 18h arriba en el otro tenia 19h

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-log
[!!] 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: 73M (Tables: 70)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 18h 47m 32s (5M q [38.899 qps], 291K conn, TX: 4B, RX: 845M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 274.0M global + 80.4M per thread (2000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 157.4G (3987% of installed RAM)
[OK] Slow queries: 2% (135K/5M)
[OK] Highest usage of available connections: 6% (136/2000)
[OK] Key buffer size / total MyISAM indexes: 184.0M/34.8M
[OK] Key buffer hit rate: 99.6% (1B cached / 6M reads)
[OK] Query cache efficiency: 63.3% (3M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 732K sorts)
[!!] Temporary tables created on disk: 45% (333K on disk / 732K total)
[OK] Thread cache hit rate: 99% (136 created / 291K connections)
[!!] Table cache hit rate: 0% (37 open / 35K opened)
[OK] Open file limit used: 0% (66/10K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
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:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 2024)

[Adsl5mb]

Intenta dar los datos cuando este al menos 2 o 3 días para ver.

Has intentado poner los consejos que he dado en mi post anterior?.