LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 09-06-2013, 11:15 AM   #1
landysaccount
Member
 
Registered: Sep 2008
Location: Dominican Republic
Distribution: Debian
Posts: 188

Rep: Reputation: 18
mysql server not responding with high cpu usage and high load avgs


Hello.

I'm really frustrated trying to get my server up and running correctly. I have a 4 core with 8GB of RAM server. Every time I start and run my server it only runs for about 10 to 15 minutes. After that the cpu % climbs up to 400 and server load shows the following: 120.0 113.0 99.2 .....

I'm hosting 6 joomla websites, one of these have high traffic.

I don't know what to do I've tried different configurations and still having the same problem.

This is what my my.cnf looks like:

Code:
[client] 
port	 = 3306 
socket	 = /var/run/mysqld/mysqld.sock 

[mysqld_safe] 
socket	 = /var/run/mysqld/mysqld.sock 
nice	 = 0 
log_warnings = 1 

[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 
log_warnings = 1 
# 
# 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_size = 384M 

# prf 20110718 changed from default 16M to meet Fogbugz requirements 
max_allowed_packet	= 50M 

thread_stack	 = 192K 
thread_cache_size	= 16 

# This replaces the startup script and checks MyISAM tables if needed 
# the first time they are touched 
myisam-recover	 = BACKUP 

# 
# * Query Cache Configuration 
# 
query_cache_limit	= 16M 
query_cache_size	= 64M 

tmp_table_size = 1024M 
max_heap_table_size = 1024M 

# 
# * 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 logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. 
# 
# Here you can see queries with especially long duration 
log_slow_queries	= /var/log/mysql/mysql-slow.log 
long_query_time = 5 
#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 

#Following 4 lines added by Fog Creek Software 
sort_buffer_size	 = 2M 
read_buffer_size	 = 2M 
read_rnd_buffer_size	= 8M 
myisam_sort_buffer_size	= 64M 
table_open_cache	 = 512 
optimizer_search_depth	= 0 

[mysqldump] 
quick 
quote-names 
max_allowed_packet	= 2GB 

[myisamchk] 
key_buffer_size = 256M 
sort_buffer_size = 256M 
read_buffer_size = 2M 
write_buffer_size = 2M


I don't know what to do, please guide me on what I can do to get this server ready.

Thanks in advanced for your time and help.
 
Old 09-07-2013, 08:16 PM   #2
unSpawn
Moderator
 
Registered: May 2001
Posts: 29,409
Blog Entries: 55

Rep: Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582Reputation: 3582
You could have stopped serving those 6 sites and enabled them one by one to find if one of those is the culprit, you could have run mytop, mtop or plain 'watch -n 5 --differences "mysql -u username -ppassword -e 'show processlist'";' and checked the slow query log to find out what's holding up things. Is there anything at all you could have learned from your previous server performance problem thread? Maybe with respect to posting enough detailed information perhaps?
 
Old 09-15-2013, 04:46 AM   #3
nico34
Member
 
Registered: Jan 2010
Posts: 54

Rep: Reputation: 0
Hi

Could you check if youre mysql supporting innodb or myisam? correct me but seems on your my.cnf file youre not using innodb, innodb will help you enhance your mysql server, you can check it via logging in on your mysql server and type command show engines or show plguins

sample output:

InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE

InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES


Also under the files /usr/share/doc/mysql-server-version/examples, you can find different .cnf files which you could start using and start enhancing on the long run every config file there has a different uses suited base on your server
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Server unresponsive--high load ave, minimal CPU usage Enteleki Linux - Server 3 03-20-2013 08:43 PM
[SOLVED] High CPU load, but low CPU usage (high idle CPU) baffy Linux - Newbie 5 03-13-2013 10:24 AM
High load and high cpu kernel usage enid Linux - Server 8 09-30-2010 04:33 AM
Load is very high but CPU usage is almost zero in top! mam2 Linux - Server 3 12-18-2009 04:53 PM
why high load, but no cpu usage? JustinHoMi Linux - General 6 01-11-2006 11:43 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 12:12 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration