Hello Community

I have a big problem with my MYSQL server eating up lifting the overload CPU of my server to about 332%.

In fact, I am hosted about 10 websites mainly for classifieds ads where the read/write are quite intense.

Please do see below my actual my.cnf file.

#
# 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]

key_buffer = 292M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 16M
query_cache_size = 56M

#
# * 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
lc-messages-dir = /usr/share/mysql
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            = 192K
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.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# 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
#
# * 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!
#
# * 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

#
# * 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/

And this is the actual free -m



And this is the actual ps -eo pmem,pcpu,rss,vsize,args | sort -k 1 -r | less

%MEM %CPU   RSS    VSZ COMMAND
 4.8  271 395536 4895732 /usr/sbin/mysqld
 2.8  0.1 233952 511892 /usr/sbin/clamd
 1.1  0.0 93764 216596 amavisd (ch6-avail)
 1.1  0.0 93756 216596 amavisd (ch6-avail)
 1.1  0.0 90816 212572 amavisd (master)
 0.8  4.4 69040 389180 /usr/bin/memcached -m 64 -p 11211 -u memcache -l 127.0.0.1
 0.6  0.0 56588 140576 /usr/sbin/spamd --create-prefs --max-children 5 --helper-home-dir -d --pidfile=/var/run/spamd.pid
 0.6  0.0 54156 140576 spamd child
 0.6  0.0 54156 140576 spamd child
 0.2  7.3 17188 308924 /usr/sbin/apache2 -k start
 0.2  6.5 20764 310104 /usr/sbin/apache2 -k start
 0.2  5.6 18572 310136 /usr/sbin/apache2 -k start
 0.2  5.5 19476 310648 /usr/sbin/apache2 -k start
 0.2  5.3 18388 309848 /usr/sbin/apache2 -k start
 0.2  5.1 21820 312256 /usr/sbin/apache2 -k start
 0.2  4.9 19372 308428 /usr/sbin/apache2 -k start
 0.2  4.8 17964 308848 /usr/sbin/apache2 -k start
 0.2  4.8 17032 308056 /usr/sbin/apache2 -k start
 0.2  4.7 17856 309272 /usr/sbin/apache2 -k start
 0.2  4.6 16988 308848 /usr/sbin/apache2 -k start
 0.2  4.4 23636 314872 /usr/sbin/apache2 -k start
 0.2  4.4 19924 310908 /usr/sbin/apache2 -k start
 0.2  4.3 19500 310760 /usr/sbin/apache2 -k start
 0.2  4.1 20328 311036 /usr/sbin/apache2 -k start
 0.2  4.0 19412 308416 /usr/sbin/apache2 -k start
 0.2  3.9 19208 308316 /usr/sbin/apache2 -k start
 0.2  3.6 19404 310480 /usr/sbin/apache2 -k start
 0.2  2.6 20772 314016 /usr/sbin/apache2 -k start
 0.2 16.7 17524 309592 /usr/sbin/apache2 -k start
 0.2 14.7 17748 308824 /usr/sbin/apache2 -k start
 0.2 10.4 16976 309336 /usr/sbin/apache2 -k start
 0.2  0.2 21584  86940 linode-longview
 0.2  0.0 22140  85688 /usr/bin/perl /usr/share/webmin/miniserv.pl /etc/webmin/miniserv.conf
 0.1  9.5 16268 308420 /usr/sbin/apache2 -k start
 0.1  0.2 11664 306444 /usr/sbin/apache2 -k start
 0.0  5.5     0      0 [rcu_sched]
 0.0  2.8     0      0 [apache2] <defunct>
 0.0  2.1  5544  19448 find /var/lib/php5/ -depth -mindepth 1 -maxdepth 1 -type f -cmin +24 ! -execdir fuser -s {} ; -delete
 0.0  1.5  1524 249476 rsyslogd -c5
 0.0  0.6  4132  22896 -bash
 0.0  0.2  6004 482044 /usr/sbin/opendkim -x /etc/opendkim.conf -u opendkim -P /var/run/opendkim/opendkim.pid -p inet:8891@localhost
 0.0  0.2  5456 133672 smtpd -n smtp -t inet -u -c -o stress= -s 2 -v
 0.0  0.2  5404 133580 smtpd -n smtp -t inet -u -c -o stress= -s 2 -v
 0.0  0.2  5388 133580 smtpd -n smtp -t inet -u -c -o stress= -s 2 -v
 0.0  0.2  1956  39840 /usr/bin/freshclam -d --quiet
 0.0  0.2  1676  25112 /usr/lib/postfix/master
 0.0  0.1  5424 133580 smtpd -n smtp -t inet -u -c -o stress= -s 2 -v
 0.0  0.1  2588 115848 trivial-rewrite -n rewrite -t unix -u -c
 0.0  0.1     0      0 [ksoftirqd/7]
 0.0  0.1     0      0 [ksoftirqd/5]
 0.0  0.1     0      0 [ksoftirqd/4]
 0.0  0.1     0      0 [ksoftirqd/3]
 0.0  0.1     0      0 [ksoftirqd/2]
 0.0  0.1     0      0 [ksoftirqd/1]
 0.0  0.1     0      0 [kjournald]
 0.0  0.0   916   8956 dovecot/anvil
 0.0  0.0   868  12756 /sbin/getty -8 38400 hvc0
 0.0  0.0   812   7176 fuser -s ./sess_edndcjrslr1bh4qje8g4aoi004
 0.0  0.0   792  21468 /sbin/udevd --daemon
:

Can someone please help on the best way to make ny server running properly? (By teh way, I am using 8Gb linode one)

Regards
Cloud Dreamer

cloud,
Is this a consistent problem or is it just an occasional occurrence?

What does LongView or your Linode Manager graph say about your historical CPU usage (24 hours & last 30 days)?

Based on the historical data, does this spike at any particular time of the day? Are there times of the day that the CPU usage is significantly lower?


MSJ

Thanks MSJ for looking at my problematic case.

In fact, I moved my 10 EU sites to Linode EU 2 days ago and when a switch from the former physical dedicated server to Linode cloud, I directly start seeing the peak CPU as I switch servers and start sending visitors.

Regards
Cloud dreamer

Is it possible that these site have enough traffic that they need to be split between multiple Linodes?

Do you have any CPU usage data from your former dedicated server as a point of reference?

Keep in mind that you have 8 cores, which means you should see your CPU usage as 332% out of a possible 800% (or as 41.5% overall).

Right, because you're not maxing out your CPU resources (as James pointed out), are you experiencing any slowdowns or issues in actual delivery? If everything is working as expected, you may simply have a sufficiently high load to cause that sort of CPU usage. You don't necessarily have that much room to grow (if your traffic doubles, you'll be rather close to maxing things out), which means it may be a good time to start work on parallelizing your infrastructure. Spin off the DB to a separate linode and start looking into splitting the database load amongst multiple boxes.

You may also want to look into mysqltuner to optimize your config, as you seem to have a very large amount of free RAM that might be better spent on MySQL caching than disk caching.

You may also want to verify that your indexes are sensible, as improperly indexed tables can cause orders of magnitude difference in CPU load. Did your indexes make it through your migration to the new server intact?

Waooo, did not know that the server can go till 800% and actually, I do not get so much traffic that we can say.

Regarding the CPU usage from the former hosting, I cannot really compare the exact same ones, because the physical one and this actual Linode one did not have the exact same sites.

Regarding the speed, I am not really so happy, because the idea of moving the EU sites with more visitors in Europe from US to London servers was to get more speed etc... but it turns out not to be the case at the moment.

mysqltuner is already installed and I'll wait for about 24 hours to get the first suggestions.

What can the best option for me because I would really like my EU sites to be super quick for the EU users:

- Upgrade to 16GB?
- Sign up another 8GB and split the sites and databases?
- Sign up for another 8GB and host just the MySQL in that server?

Regards
Jacques

Hello Guspaz and thank you for your contribution
In fact, the idea of moving the EU sites with more visitors in Europe from US to London Servers was to get more speed etc... but it turns out not to be the case at the moment. The sites hosted at linode LON seem to be slower than when they were hosted in US with teh exact same code and databases.

Regards
Cloud Dreamer