Server load problem....is mysql?

[adivor]

Hello to all,
i have a problem with one server, server load, but not in all cases.
after server restart for 24-48H server load are up tu 1 with ~1000 users online in one forum and ~300 in other blog, forum, ecc... hosted in the same server.
After 24-48H i see any mysql error (send in my mail from vbulletin script) and server load some times ~16-20. In this case my sites works slow and not in good situation .

How is possible for example restart mysql after xxx hours or another best solution?

thanks in advanced!

[kurgans]

You can mount Monit to monitor your server and set conditions for the service is restarted if the workload is very high


http://www.howtoforge.com/server_monitor...an_etch_p2

It is a small example.

if cpu is greater than 10% for 2 cycles then alert
if cpu > 15% for 5 cycles then restart
if totalmem > 500 MB for 5 cycles then restart
if children > 250 then restart

http://www.howtoforge.com/server_monitor...ebian_etch

You can make a cron to restart the services

[adivor]

Thanks...i try this but is normal this problem in my server?
All 2 script installed...i testing now

[kilburn]

Enable the mysql slow query log, because this is probably being caused by some slow querys or high impact procedure (like the ispcp virtual traffic script or something like this). Obviously, it is NOT normal unless you have 100% ram usage and the machine is heavily swapping to disk.

[joximu]

Hi adivor

also check if mysql does a bin-log (log all insters/updates for a slave-server) - debian does this by default.

/J

[adivor]

(07-10-2009 03:06 PM)kilburn Wrote:  Enable the mysql slow query log

Can you help me for this? i have server programmer in holiday.

[joximu]

Open /etc/mysql/my.cnf

if you have a line
log_bin = /var/log/mysql/mysql-bin.log

write a # in front of it.
but also comment the next lines:
expire_logs_days and max_binlog_size

then restart the mysql service.

/J

[adivor]

Olready with # this line:
expire_logs_days and max_binlog_size

ADDING # for lines expire_logs_days and max_binlog_size

under you can see original my.conf

Code:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
language    = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer        = 16M
max_allowed_packet    = 16M
thread_stack        = 128K
thread_cache_size    = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover        = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log        = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer        = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[joximu]

ok, so log-bin was already off (I had a message to also comment the other lines - but maybe it's not neccesarry).
Hm - so, the other approach is: log the "slow" queries.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

and maybe:
log-queries-not-using-indexes

This can help find the scripts that should be optimized...

You also can have a look in phpMyAdmin, Status... there are also some interesting numbers...

/J

[adivor]

(07-10-2009 07:32 PM)joximu Wrote:  Hm - so, the other approach is: log the "slow" queries.

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2

and maybe:
log-queries-not-using-indexes

This can help find the scripts that should be optimized...

You also can have a look in phpMyAdmin, Status... there are also some interesting numbers...

/J

I set this, how many time is necessary?

update:
i see this log but i see my mysql password and i can't publish it. what line are you interested?

update2:
i'm very confused....i use only vbulletin and wordpress, not "crazy" script.... vb and wp are fully optimized with cache system.....confused...