-   Linux - Server (
-   -   MySQL server started to increase CPU load to high value, how to fix? (

Vita 08-18-2014 05:42 AM

MySQL server started to increase CPU load to high value, how to fix?

I've noticed that from yesterday, my OpenVZ VPS, started to lag, I host a few sites on it that have total of 300-400 active users all time.

VPS Specs:
CPU: 4 Cores / 69% CPU usage
HDD: 40GB / 30 Used
RAM: 4GB / 1.5GB Free

MySQL process shows 180-250% cpu usage almost every second, and my VPS provided does not like that. The problem started yesterday when my views on the sites increased.

I think that both databases use InoDB although I'm not 100% sure.

I tried disabling some intensive MySQL queries from the website, but that didn't help a lot, there is about milion rows of data and maybe more in the database of the seconds site. When I disable the second site from the apache and restart apache2 and mysql service load decreases drastically.

I don't have a lot of knowledge about tweaking the database, i'm more a programmer than sysadmin, but currently I need to do both.


port            = 3306
socket          = /var/run/mysqld/mysqld.sock
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

# * Basic Settings
user            = mysql
pid-file        = /var/run/mysqld/
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
bind-address            = XXXXX
# * Fine Tuning
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size      = 8
myisam-recover        = BACKUP
query_cache_limit      = 1M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size        = 100M

max_allowed_packet      = 16M

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

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/

I've tried increasing buffer sizes but, nothing has changed.

What do you recommend me to do in order to decrease that load?

Best Regards!

Vita 08-18-2014 07:22 AM

So I didn't have luck configuring the server so I migrated the database of the busy site to another server, so load is now spread out.

All times are GMT -5. The time now is 11:22 PM.