VPS, ¿Mal configurado?

[Marchello]

Hola,

He estado intentando mejorar el redimiento de mi VPS con varias ideas pero no he conseguido grandes logros. Os pido ayuda para ver si podéis ayudarme a poner en pie el server y asegurar que deje de dar problemas.

El problema parece ser de RAM. Las alertas QoS de numproc y privvmpages son las más recurrentes en el panel de Virtuozzo, pero necesito saber la solución. Os pego datos del VPS:

Ram (Guaranteed/Max): 512/1024MB
Guaranteed CPU Speed: 2.0 GhZ
Disk Space: 18.2G / Used 11.8G
Monthly Bandwidth: 800G / Used 114G
CENTOS 5.2 i686 virtuozzo with Cpanel and WHM

¿Qué hay en el servidor?

a) Un site pequeño-mediano (12.000 hits diarios) montado con Joomla y Phpbb (sí, sé que no es la mejor opción pero es lo que hay)
b) Servidores de Icecast y Teamspeak que, en cualquier caso, están apagados
c) La base de datos Mysql, que he reducido hasta tener una principal de 30Mb y otra con los foros de 90Mb

Free -m:

total used free shared buffers cached
Mem: 900 321 578 0 0 0
-/+ buffers/cache: 321 578
Swap: 0 0 0

Top

top - 13:12:13 up 22 min, 1 user, load average: 0.75, 1.41, 1.52
Tasks: 68 total, 1 running, 65 sleeping, 0 stopped, 2 zombie
Cpu(s): 27.2% us, 7.9% sy, 0.0% ni, 43.6% id, 21.3% wa, 0.0% hi, 0.0% si
Mem: 921600k total, 334456k used, 587144k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached

[Marchello]

My.cnf

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M

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

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Muchas gracias a todos por la ayuda de antemano

[Apolo]

Yo supongo que para tener solamente 512 MB de RAM, entonces tendrás que bajar, y bastante, todas esas variables que incluyen el término "buffer", pues estás pretendiendo utilizar (reservar) en algunos casos hasta el 50% de la RAM total real disponible y entonces lo demás se queda con casi nada para trabajar, empezando porque supongo que estarás usando algún panel de control y con suerte, ya se te van 256 MB sólo en eso.

De todas formas, una adecuada optimización de MySQL pasa por un período de observación y pruebas, de acuerdo al uso específico que se le da a cada base de datos.

¿Y tu proveedor de hosting no te da soporte?

Saludos,

[Marchello]

Hablé con el proveedor y me ha sugerido esta configuración:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size= 8M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Disable Federated by default
skip-federated

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Parece que va bien, pero se sigue saturando....

-------
-bash-3.2# cat /proc/user_beancounters
Version: 2.5
uid resource held maxheld barrier limit failcnt
8244: kmemsize 7146741 7165042 24606720 27340800 0
lockedpages 0 0 512 512 0
privvmpages 95976 95981 230400 256000 263928
shmpages 660 660 43008 43008 0
dummy 0 0 0 0 0
numproc 85 85 240 240 18869
physpages 22711 22712 0 2147483647 0
vmguarpages 0 0 128000 2147483647 0
oomguarpages 22802 22803 52224 2147483647 0
numtcpsock 48 48 720 720 0
numflock 11 11 338 376 0
numpty 1 1 32 32 0
numsiginfo 0 0 512 512 0
tcpsndbuf 263148 263148 2113536 3522560 63
tcprcvbuf 262916 262916 2113536 3522560 0
othersockbuf 30860 30860 1373184 3522560 0
dgramrcvbuf 0 0 536576 536576 0
numothersock 32 32 360 360 0
dcachesize 672231 675176 4093977 4548864 0
numfile 2671 2680 11640 11640 75600
dummy 0 0 0 0 0
dummy 0 0 0 0 0
dummy 0 0 0 0 0
numiptent 52 52 256 256 0
-bash-3.2#
--------

¿Qué pensáis? ¿Qué más puedo mirar?

[Adsl5mb]

Podrías pegar un comando top en el horario de mayor visitas, ya que el que pusiste en el primer posteo
top - 13:12:13 up 22 min, 1 user, load average: 0.75, 1.41, 1.52

No debería ser ningún problema ya que un load de 1 es perfectamente admisible y no tendría que notarse problemas de lentitud.

Para optimizar MySQL creo que los mejores scripts que existen son el MySQLTuner y el tuning-primer los dos funcionan bajo PERL y te dicen que valores subir, que otros bajar para obtener mejor rendimiento, recuerda que antes de utilizarlos hayas tenido el servicio del MySQL funcionando por más de 2 días para que pueda dar información más precisa y poder darte los valores óptimos.

[Marchello]

MySQL Version 5.0.77-community-log i686

Uptime = 3 days 0 hrs 0 min 52 sec
Avg. qps = 53
Total Questions = 13914412
Threads Connected = 1


SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 19 out of 13914433 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 41
The number of used connections is 41% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 406 M
Configured Max Per-thread Buffers : 634 M
Configured Max Global Buffers : 146 M
Configured Max Memory Limit : 780 M
Physical Memory : 900 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 22 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 6799
Key buffer free ratio = 85 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 4 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 57.88 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 3184 queries where a join could not use an index properly
You have had 2 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 128 tables
You have a total of 1417 tables
You have 128 open tables.
Current table_cache hit rate is 1%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 214338 temp tables, 12% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 355 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 7613
Your table locking seems to be fine

He desactivado algunas secciones de la web para no evitar que se viniera abajo continuamente el server y por eso he conseguido estabilizarlo durante dos dias

[WebTech]

Si sigues optimizándolo (server y/o web) y el problema persiste, considera ir por mayor hardware, un dedicado quizás.

Saludos,

[netandino]

pues el tu mayor problema esta en el tamaño de la basa de datos y las consultas que haga esto pues al tener vistantes en linena siempre hacen uso de bd y ello mas recursos se te hagotan saluda mas solucionable un servidor de mayor potencia.

Saludos