LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   MySQL server started to increase CPU load to high value, how to fix? (https://www.linuxquestions.org/questions/linux-server-73/mysql-server-started-to-increase-cpu-load-to-high-value-how-to-fix-4175515234/)

Vita 08-18-2014 05:42 AM

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

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.

my.cnf:
Code:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[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
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

[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/

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.