Optimizar MySQL

frankxd · 20-oct-2007, 22:09

Bueno haber si me pueden ayudar a optimizar mi Base de datos.

Como lo dije antes, tengo un VPS con 1GB RAM, Intel(R) Xeon(R) CPU, CentOS 4

Lo uso para un Foro en vB, Maximo de conectados al mismo tiempo: 450

Estadisticas:

top

Código:

top - 16:05:16 up 1 day,  1:22,  1 user,  load average: 0.70, 1.04, 1.49
Tasks: 125 total,   1 running, 123 sleeping,   0 stopped,   1 zombie
Cpu(s): 20.6% us,  1.6% sy,  0.0% ni, 77.5% id,  0.3% wa,  0.0% hi,  0.0% si
Mem:   1048576k total,   901704k used,   146872k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
23739 nobody    16   0  114m  16m 3672 S   10  1.6   0:00.89 httpd
24288 nobody    15   0  120m  22m 3584 S   10  2.2   0:00.30 httpd
24262 nobody    15   0  113m  15m 3632 S    9  1.5   0:00.27 httpd
22518 nobody    15   0  113m  15m 3644 S    8  1.5   0:00.89 httpd
16325 nobody    16   0  120m  21m 3688 S    8  2.1   0:01.84 httpd
23612 nobody    15   0  119m  20m 3628 S    8  2.0   0:00.81 httpd
24265 nobody    17   0  120m  21m 3580 S    8  2.1   0:00.24 httpd
20106 nobody    16   0  115m  17m 3676 S    7  1.7   0:02.30 httpd
23979 nobody    15   0  113m  15m 3648 S    4  1.5   0:00.26 httpd
17565 nobody    16   0  115m  16m 3516 S    3  1.7   0:00.10 httpd
19639 mysql     15   0  209m  41m 3628 S    3  4.0   1:30.57 mysqld
22264 nobody    15   0  115m  17m 3636 S    2  1.7   0:00.31 httpd
23957 nobody    15   0  115m  17m 3656 S    2  1.7   0:00.73 httpd
24282 nobody    15   0  118m  20m 3652 S    2  2.0   0:00.39 httpd
22102 nobody    15   0     0    0    0 Z    0  0.0   0:00.57 httpd <defunct>
23626 nobody    15   0  116m  17m 3656 S    0  1.7   0:00.76 httpd
23978 nobody    15   0  115m  17m 3612 S    0  1.7   0:01.01 httpd

cat /etc/my.cnf

Código:

[mysqld]
set-variable = max_connections=500
safe-show-database

mysqladmin var ext stat ver

Código:

| read_rnd_buffer_size            | 262144                                   |
| relay_log_purge                 | ON                                       |
| relay_log_space_limit           | 0                                        |
| rpl_recovery_rank               | 0                                        |
| secure_auth                     | OFF                                      |
| server_id                       | 0                                        |
| skip_external_locking           | ON                                       |
| skip_networking                 | OFF                                      |
| skip_show_database              | OFF                                      |
| slave_net_timeout               | 3600                                     |
| slave_transaction_retries       | 0                                        |
| slow_launch_time                | 2                                        |
| socket                          | /var/lib/mysql/mysql.sock                |
| sort_buffer_size                | 2097144                                  |
| sql_mode                        |                                          |
| sql_notes                       | ON                                       |
| sql_warnings                    | ON                                       |
| storage_engine                  | MyISAM                                   |
| sync_binlog                     | 0                                        |
| sync_frm                        | ON                                       |
| sync_replication                | 0                                        |
| sync_replication_slave_id       | 0                                        |
| sync_replication_timeout        | 0                                        |
| system_time_zone                | EDT                                      |
| table_cache                     | 64                                       |
| table_type                      | MyISAM                                   |
| thread_cache_size               | 0                                        |
| thread_stack                    | 196608                                   |
| time_format                     | %H:%i:%s                                 |
| time_zone                       | SYSTEM                                   |
| tmp_table_size                  | 33554432                                 |
| tmpdir                          |                                          |
| transaction_alloc_block_size    | 8192                                     |
| transaction_prealloc_size       | 4096                                     |
| tx_isolation                    | REPEATABLE-READ                          |
| version                         | 4.1.22-standard                          |
| version_comment                 | MySQL Community Edition - Standard (GPL) |
| version_compile_machine         | x86_64                                   |
| version_compile_os              | unknown-linux-gnu                        |
| wait_timeout                    | 28800                                    |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Aborted_clients            | 216        |
| Aborted_connects           | 1          |
| Binlog_cache_disk_use      | 0          |
| Binlog_cache_use           | 0          |
| Bytes_received             | 37504900   |
| Bytes_sent                 | 4171386150 |
| Com_admin_commands         | 0          |
| Com_alter_db               | 0          |
| Com_alter_table            | 0          |
| Com_analyze                | 0          |
| Com_backup_table           | 0          |
| Com_begin                  | 0          |
| Com_change_db              | 11984      |
| Com_change_master          | 0          |
| Com_check                  | 0          |
| Com_checksum               | 0          |
| Com_commit                 | 0          |
| Com_create_db              | 0          |
| Com_create_function        | 0          |
| Com_create_index           | 0          |
| Com_create_table           | 2          |
| Com_dealloc_sql            | 0          |
| Com_delete                 | 1277       |
| Com_delete_multi           | 0          |
| Com_do                     | 0          |
| Com_drop_db                | 0          |
| Com_drop_function          | 0          |
| Com_drop_index             | 0          |
| Com_drop_table             | 2          |
| Com_drop_user              | 0          |
| Com_execute_sql            | 0          |
| Com_flush                  | 4          |
| Com_grant                  | 2          |
| Com_ha_close               | 0          |
| Com_ha_open                | 0          |
| Com_ha_read                | 0          |
| Com_help                   | 0          |
| Com_insert                 | 3395       |
| Com_insert_select          | 2          |
| Com_kill                   | 0          |
| Com_load                   | 0          |
| Com_load_master_data       | 0          |
| Com_load_master_table      | 0          |
| Com_lock_tables            | 3          |
| Com_optimize               | 1          |
| Com_preload_keys           | 0          |
| Com_prepare_sql            | 0          |
| Com_purge                  | 0          |
| Com_purge_before_date      | 0          |
| Com_rename_table           | 0          |
| Com_repair                 | 1          |
| Com_replace                | 247        |
| Com_replace_select         | 0          |
| Com_reset                  | 0          |
| Com_restore_table          | 0          |
| Com_revoke                 | 0          |
| Com_revoke_all             | 0          |
| Com_rollback               | 0          |
| Com_savepoint              | 0          |
| Com_select                 | 94970      |
| Com_set_option             | 54         |
| Com_show_binlog_events     | 0          |
| Com_show_binlogs           | 2          |
| Com_show_charsets          | 12         |
| Com_show_collations        | 12         |
| Com_show_column_types      | 0          |
| Com_show_create_db         | 0          |
| Com_show_create_table      | 0          |
| Com_show_databases         | 14         |
| Com_show_errors            | 0          |
| Com_show_fields            | 39         |
| Com_show_grants            | 6          |
| Com_show_innodb_status     | 0          |
| Com_show_keys              | 0          |
| Com_show_logs              | 0          |
| Com_show_master_status     | 0          |
| Com_show_ndb_status        | 0          |
| Com_show_new_master        | 0          |
| Com_show_open_tables       | 0          |
| Com_show_privileges        | 0          |
| Com_show_processlist       | 9          |
| Com_show_slave_hosts       | 0          |

PD: lo he puesto por separado, me habia sobrepasado el numero de carcateres por tema

Eso, es todo
Que me aconsejan? como podria optimizar al maximo mi base de datos.

Salu2

frankxd · 20-oct-2007, 22:10

Continuacion del: mysqladmin var ext stat ver

Código:

| Com_show_slave_status      | 0          |
| Com_show_status            | 2          |
| Com_show_storage_engines   | 0          |
| Com_show_tables            | 20         |
| Com_show_variables         | 44         |
| Com_show_warnings          | 0          |
| Com_slave_start            | 0          |
| Com_slave_stop             | 0          |
| Com_stmt_close             | 0          |
| Com_stmt_execute           | 0          |
| Com_stmt_prepare           | 0          |
| Com_stmt_reset             | 0          |
| Com_stmt_send_long_data    | 0          |
| Com_truncate               | 2          |
| Com_unlock_tables          | 3          |
| Com_update                 | 8948       |
| Com_update_multi           | 2          |
| Connections                | 11894      |
| Created_tmp_disk_tables    | 2          |
| Created_tmp_files          | 23         |
| Created_tmp_tables         | 5412       |
| Delayed_errors             | 0          |
| Delayed_insert_threads     | 0          |
| Delayed_writes             | 0          |
| Flush_commands             | 1          |
| Handler_commit             | 0          |
| Handler_delete             | 1200       |
| Handler_discover           | 0          |
| Handler_read_first         | 14210      |
| Handler_read_key           | 1508258    |
| Handler_read_next          | 1673494    |
| Handler_read_prev          | 49757      |
| Handler_read_rnd           | 80782      |
| Handler_read_rnd_next      | 7752477    |
| Handler_rollback           | 0          |
| Handler_update             | 139270     |
| Handler_write              | 22969      |
| Key_blocks_not_flushed     | 0          |
| Key_blocks_unused          | 2148       |
| Key_blocks_used            | 6698       |
| Key_read_requests          | 3653490    |
| Key_reads                  | 59004      |
| Key_write_requests         | 10077      |
| Key_writes                 | 6585       |
| Max_used_connections       | 33         |
| Not_flushed_delayed_rows   | 0          |
| Open_files                 | 115        |
| Open_streams               | 0          |
| Open_tables                | 64         |
| Opened_tables              | 3764       |
| Qcache_free_blocks         | 0          |
| Qcache_free_memory         | 0          |
| Qcache_hits                | 0          |
| Qcache_inserts             | 0          |
| Qcache_lowmem_prunes       | 0          |
| Qcache_not_cached          | 0          |
| Qcache_queries_in_cache    | 0          |
| Qcache_total_blocks        | 0          |
| Questions                  | 132843     |
| Rpl_status                 | NULL       |
| Select_full_join           | 2          |
| Select_full_range_join     | 0          |
| Select_range               | 31971      |
| Select_range_check         | 0          |
| Select_scan                | 16059      |
| Slave_open_temp_tables     | 0          |
| Slave_retried_transactions | 0          |
| Slave_running              | OFF        |
| Slow_launch_threads        | 0          |
| Slow_queries               | 0          |
| Sort_merge_passes          | 8          |
| Sort_range                 | 13346      |
| Sort_rows                  | 1226634    |
| Sort_scan                  | 10464      |
| Table_locks_immediate      | 169941     |
| Table_locks_waited         | 378        |
| Threads_cached             | 0          |
| Threads_connected          | 2          |
| Threads_created            | 11893      |
| Threads_running            | 1          |
| Uptime                     | 2757       |
+----------------------------+------------+
Uptime: 2757  Threads: 1  Questions: 132846  Slow queries: 0  Opens: 3764  Flush tables: 1  Open tables: 64  Queries per second avg: 48.185
mysqladmin  Ver 8.41 Distrib 4.1.22, for unknown-linux-gnu on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          4.1.22-standard
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 45 min 57 sec

Threads: 1  Questions: 132846  Slow queries: 0  Opens: 3764  Flush tables: 1  Open tables: 64  Queries per second avg: 48.185

Datacenter1 · 20-oct-2007, 23:38

Saludos,

Los números del servidor a simple vista, no se ven para nada mal, es normal que una instalación de Linux consuma bastante memoria, sin embargo si se puede optimizar un poco MySQL y creo que deberás optimizar también apache/php

Deja que revise con más calma y te enviaré un nuevo archivo my.cnf

Datacenter1 · 20-oct-2007, 23:54

Prueba, con este, no te asustes con max_connections=200 porque lo que he hecho es hacer que acepte hasta 200 simultáneas, pero evitaremos que una conexión se quede abierta por mucho tiempo si no está activa, así que funcionará así tengas 500 usuarios online.

Prueba con esta y te mandaré dos programas que nos ayudarán a afinar mejor aún estos valores iniciales, quizas WebTech pueda ayudarnos con alguna otra opinión o tip

Código:

[mysqld]
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
local-infile
old-passwords
safe-show-database
low-priority-updates
symbolic-links
skip-locking
skip-innodb
key_buffer_size=16M
query_cache_size=256M
query_cache_limit=8M
query_cache_type=1
table_cache= 2048
join_buffer_size=2M
open_files_limit=6144
thread_cache_size=64
concurrent_insert=2
read_buffer_size=4M
tmp_table_size=128M
max_heap_table_size=64M
max_connections=200
max_user_connections=30
interactive_timeout=20
wait_timeout=20
connect_timeout=6

Editado: he añadido query_cache_type=1 sugerido por WebTech

Datacenter1 · 21-oct-2007, 00:00

Despues de modificar el /etc/my.cnf y cambiar los valores por los que te he dado, deberás reiniciar Mysql.

Te aconsejo usarla por un rato y luego ejecuta estos comandos:

Código:

wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 700 tuning-primer.sh
./tuning-primer.sh

Pega la salida del comando aquí, para darte unos valores de acuerdo al uso específico de tu servidor, tuning-primer.sh dará valores más exactos luego de 48 horas de estar usando el servidor MySQL

WebTech · 21-oct-2007, 00:03

Hola,

Bueno, como dice Guillermo, todo parece normal, prueba con esa configuración que te ha dado él, seguro que mejora el rendimiento. Como alternativa, si no ves gran cambio luego de eso, prueba con estos valores en esa misma configuración:

Código:

query_cache_limit=1M
query_cache_size=32M
query_cache_type=1

Adicionalmente, te recuerdo lo que dijo Guillermo antes, apache y php juegan un papel importante a nivel de servidor, optimízalos también.

Otra sugerencia era instalar MyTop, pero realmente no creo que sepas interpretar los datos que te brindará

Saludos!

Datacenter1 · 21-oct-2007, 00:10

Tienes razón WebTech con query_cache_limit=1M, se me pasó por alto, incluye en la configuración que te dí (editaré mi post)
[Editado]

Quise decir con query_cache_type=1M

[/Editado]

frankxd · 21-oct-2007, 00:28

Bueno me acabo de conectar, disculpen por no responder antes, tube que salir a almorzar .

Estare atentos a su ayuda

Leere con calma, haber que tal va todo

frankxd · 21-oct-2007, 00:32

Cita:

Iniciado por Datacenter1.com

Prueba, con este, no te asustes con max_connections=200 porque lo que he hecho es hacer que acepte hasta 200 simultáneas, pero evitaremos que una conexión se quede abierta por mucho tiempo si no está activa, así que funcionará así tengas 500 usuarios online.

Prueba con esta y te mandaré dos programas que nos ayudarán a afinar mejor aún estos valores iniciales, quizas WebTech pueda ayudarnos con alguna otra opinión o tip

Código:

[mysqld]
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
local-infile
old-passwords
safe-show-database
low-priority-updates
symbolic-links
skip-locking
skip-innodb
key_buffer_size=16M
query_cache_size=256M
query_cache_limit=8M
query_cache_type=1
table_cache= 2048
join_buffer_size=2M
open_files_limit=6144
thread_cache_size=64
concurrent_insert=2
read_buffer_size=4M
tmp_table_size=128M
max_heap_table_size=64M
max_connections=200
max_user_connections=30
interactive_timeout=20
wait_timeout=20
connect_timeout=6

Editado: he añadido query_cache_type=1 sugerido por WebTech

Dime, en my my.cnf añado esto cierto?
ó ya me debe aparecer para editarlo, como te puse antes solo me sale esto en my.cnf

[mysqld]
set-variable = max_connections=500
safe-show-database

Salu2

Datacenter1 · 21-oct-2007, 00:35

Debes sustituir el contenido de tu actual /etc/my.cnf por el contenido que te he dado

WebTech · 21-oct-2007, 00:51

frankxd,

Me voy a ir un poco del tema pero te quiero dar 3 consejos, en base a lo que he visto de tus posts creo que sería bueno implementes alguna de estas opciones a corto plazo:

1.- Estudiar administración de sistemas, y tratar de aprender sobre los sistemas basados en unix y Linux particularmente, el shell, servicios, etc. Para tratar de administrar el servidor tu mismo o tratar de llevarlo lo mejor posible, teniendo nociones sobre el sistema, manejo de archivos, ediciones, etc.

2.- Contratar un server que tenga administración incluída, suelen salir un poco más caros que los no manejados, pero te darán más garantías cuando algo vaya mal, pues ellos se encargarán de todo.

3.- Contratar a un SysAdmin o empresa que se encargue de administrar tu sistema.

Te doy estos consejos porque hoy es esto (que es algo bastante fácil y rápido de hacer) y tienes el sistema funcionando, pero el día de mañana puede ser algo más grave y no siempre habrá gente en los foros con tiempo para darte las guías paso a paso (que cuando hay tiempo y se puede, se hace con gusto), es solo un consejo para que tengas en cuenta, sobre todo estudiar sobre Linux y el manejo de consola, te ayudará en cualquier caso, sea administrado o no administrado el server.

Saludos,

frankxd · 21-oct-2007, 00:55

Hola,

Despues de editar el my.cnf

y reiniciar el MySQL,
Me tira este error:

Código:

Shutting down MySQL.. SUCCESS!
Starting MySQL................................... ERROR!

Intentar reiniciar de nuevo:

Código:

 ERROR! MySQL PID file could not be found!
Starting MySQL................................... ERROR!

a que se debio el problema?

WebTech · 21-oct-2007, 00:56

Tal vez editaste algo mal en el archivo de configuración.
Por favor, corre el siguiente comando:

Código:

tail -50 /var/log/mysqld.log

si ese no funciona, prueba con

Código:

tail -50 /var/log/mysql.log

Y postea la salida.

Saludos,

frankxd · 21-oct-2007, 01:01

con los 2 comandos me sale esto:

[root@dominio ~]# tail -50 /var/log/mysql.log
tail: cannot open `/var/log/mysql.log' for reading: No such file or directory
[root@dominio ~]# tail -50 /var/log/mysqld.log
tail: cannot open `/var/log/mysqld.log' for reading: No such file or directory

Datacenter1 · 21-oct-2007, 01:06

Cual es la salida del comando mysql ?

Intenta reniciando el vps completamente

WebTech · 21-oct-2007, 01:10

Cita:

Iniciado por frankxd

con los 2 comandos me sale esto:

[root@dominio ~]# tail -50 /var/log/mysql.log
tail: cannot open `/var/log/mysql.log' for reading: No such file or directory
[root@dominio ~]# tail -50 /var/log/mysqld.log
tail: cannot open `/var/log/mysqld.log' for reading: No such file or directory

Imposible que hayan logs, ya que no se configuró una ruta para los mismos en la configuración del archivo my.cnf sugerido por Guillermo, ya era raro que no existieran

Te sugiero hacer esto, restaurar el archivo /etc/my.conf.bak como hiciste hoy temprano, luego reiniciar el mysql y dejarlo activo. Avisa una vez este listo y te diré como añadir una configuración de logs.

Saludos.

frankxd · 21-oct-2007, 01:14

Listo ya lo restaure.

Yo lo que hise fue en SSH

Escribir "vi /etc/my.cnf"

luego de eso borrar las dos lineas y añadir todo lo que puso Datacenter1,
quisas lhaci no se añade??

Datacenter1 · 21-oct-2007, 01:17

Voy probar la configuración que escribí en un vps a ver si hay algún error que se me escape a simple vista

WebTech · 21-oct-2007, 01:20

Añádele los logs por si acaso Guillermo, siempre son buena fuente para saber por qué falla el servicio en caso de que pase otra vez.

Saludos,

Datacenter1 · 21-oct-2007, 01:34

He probado la configuración y no ha dado ningun tipo de error, para estar seguros que lo hagas bien, copia TODO el bloque que estoy posteando y lo pegas en la consola como usuario root, he añadido logs de error, tal como lo ha sugerido WebTech

Código:

mv /etc/my.cnf /etc/my.cnf.backup2
touch /etc/my.cnf
touch /var/log/mysql.log
chown mysql:mysql /var/log/mysql.log
cat<<EOF>> /etc/my.cnf
[mysqld]
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
local-infile
old-passwords
safe-show-database
low-priority-updates
symbolic-links
skip-locking
skip-innodb
key_buffer_size=16M
query_cache_size=256M
query_cache_limit=8M
query_cache_type=1
table_cache=2048
join_buffer_size=2M
open_files_limit=6144
thread_cache_size=64
concurrent_insert=2
read_buffer_size=4M
tmp_table_size=128M
max_heap_table_size=64M
max_connections=200
max_user_connections=30
interactive_timeout=20
wait_timeout=20
connect_timeout=6
err-log=/var/log/mysql.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[safe_mysqld]
err-log=/var/log/mysql.log
pid-file=/var/run/mysqld/mysqld.pid
EOF
service mysql restart

Solo debes copiar el bloque completo y lo pegas en la consola, se encargara de hacer un respaldo, crear el archivo para logs, escribir la configuración y reiniciar el servicio, no debería fallar