LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
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 12-31-2009, 12:19 AM   #1
napraviculom
LQ Newbie
 
Registered: Dec 2009
Posts: 4

Rep: Reputation: 0
load averages high, caused by mysql


Here's some information:

Code:
Web Server with Apache 2.2.
Mysql ver. 5.0.45 via socket on x86_64 centos 5.2, 1 CPU 4core with 4 Gb RAM, 3Tb SATA disk space
Real memory 3.86 GB total, 1.38 GB used
Virtual memory 4 GB total, 288 kB used
Code:
[root@server init.d]# top
top - 14:59:07 up 1 day, 34 min,  1 user,  load average: 9.94, 10.91, 11.07
Mem:   6231956k total,  4825868k used,  1406088k free,   410000k buffers
Swap:  2040212k total,        0k used,  2040212k free,  3164740k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
24804 mysql     16   0 1451m 410m 4476 S 132.5  6.7   1468:33 mysqld
 4874 root      16   0  5400 2800 1308 S  0.0  0.0   0:19.15 hald
These are the red marked rows on Runtime Information in phpMyAdmiin
Code:
Slow_queries  	14 k
Innodb_buffer_pool_reads  	33
Handler_read_rnd  	24 M
Handler_read_rnd_next  	3,316.21 M
Slow_launch_threads  	2
Created_tmp_disk_tables  	3,624 
Select_full_join  	621
Select_range_check  	1 
Opened_tables  	12 k
Table_locks_waited  	17 k
At the moment I'm setting up slow log to detect the slow queries.

phpMyAdmin on high Handler_read_rnd_next:
The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
So I am going to check the indexing of tables.
The biggest table has over 40,000 rows.

Maybe someone with similar problem experirence..
I appreciate any suggestions and help.
 
Old 12-31-2009, 01:03 AM   #2
janoszen
Member
 
Registered: Oct 2009
Location: Budapest
Distribution: Mostly Gentoo, sometimes Debian/(K)Ubuntu
Posts: 143

Rep: Reputation: 22
The one most common reasons for high load on MySQL are queries not using indexes. You're on the right track in fixing this. Also use the EXPLAIN command.
 
Old 12-31-2009, 01:34 AM   #3
napraviculom
LQ Newbie
 
Registered: Dec 2009
Posts: 4

Original Poster
Rep: Reputation: 0
The slow query log was already enabled. After disabling it (it was over 450MB heavy), it seems that the load averages droped a bit (not really sure, though, not many visitors at the moment).
Does that make sense?

If so, are there any other logs I should consider emptying/disabling ?

I tried to direct the slow query log to another file but no success.
Maybe I shold try emptying it ?
 
Old 12-31-2009, 01:51 AM   #4
janoszen
Member
 
Registered: Oct 2009
Location: Budapest
Distribution: Mostly Gentoo, sometimes Debian/(K)Ubuntu
Posts: 143

Rep: Reputation: 22
Well, that means that you have a LOT of slow queries. You should look into your indexing. Normally you should not disable any logs besides query log. After changing log settings, you may need to restart your MySQL server because the file descriptor for the old log is held by the process and settings are not re-read to my knowledge.

Last edited by janoszen; 12-31-2009 at 01:53 AM.
 
Old 12-31-2009, 01:58 AM   #5
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello,

There is a very handy perl script on the internet named mysqltuner.pl that can help you a lot when troubleshooting MySQL.

Kind regards,

Eric
 
Old 12-31-2009, 04:30 AM   #6
napraviculom
LQ Newbie
 
Registered: Dec 2009
Posts: 4

Original Poster
Rep: Reputation: 0
Thank you all for replies!
These are the last 5 slow queries from the slow-log.
Does this suggest that there is indexing problem?
This should be a matter of mysql now, right, so, maybe, i should post it somewhere else. ?

Code:
# Time: 091231 11:10:45
# User@Host: user @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 46373  Rows_examined: 46373
SELECT * FROM adds1 WHERE 
			kateg=8 AND 1;
# Time: 091231 11:10:47
# User@Host: user1 @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 58  Rows_examined: 48899
use db_test2;
SELECT DISTINCTROW O.lotok, O.lregija, O.lregijas, O.lzupanija FROM adds AS O WHERE 1  ORDER BY O.lotok;

# Time: 091231 11:11:40
# User@Host: user @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 46373  Rows_examined: 46373
use db_web;
SELECT * FROM adds1 WHERE 
			kateg=8 AND 1;
# User@Host: user1 @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 15  Rows_examined: 48813
use db_test2;
SELECT DISTINCTROW O.lregija, O.lregijas FROM adds AS O ORDER BY O.lregija;

# Time: 091231 11:11:48
# User@Host: user @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 46373  Rows_examined: 46373
use db_web;
SELECT * FROM adds1 WHERE 
			kateg=8 AND 1;

Last edited by napraviculom; 12-31-2009 at 04:31 AM.
 
Old 01-01-2010, 02:06 AM   #7
janoszen
Member
 
Registered: Oct 2009
Location: Budapest
Distribution: Mostly Gentoo, sometimes Debian/(K)Ubuntu
Posts: 143

Rep: Reputation: 22
Whoever wrote these queries should really learn some programming.

Look at this: Rows_sent: 46373 Rows_examined: 46373

This is horrible performance-wise. Indexing alone won't help you with this. Talk to the guy who wrote this mess.
 
Old 01-01-2010, 07:29 AM   #8
napraviculom
LQ Newbie
 
Registered: Dec 2009
Posts: 4

Original Poster
Rep: Reputation: 0
Thanks for the replies.
There was an indexing issue with some queries.

I have to look into this, what it really does and if it is necessary at all
Code:
Rows_sent: 46373 Rows_examined: 46373
 
  


Reply

Tags
high, load


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
Mysql and httpd memory load is HIGH !!! Pariah Linux - General 3 10-31-2009 12:56 PM
Need help troubleshooting high load averages jdw52 Linux - Server 6 12-31-2008 06:37 PM
Mysql stopped and high load lavinya Linux - Server 0 12-05-2007 03:40 PM
uptime load averages Longinus Linux - Newbie 10 01-27-2005 11:24 PM
high mysql load hardigunawan Linux - General 0 03-04-2003 01:43 AM


All times are GMT -5. The time now is 07:42 PM.

Main Menu
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
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration