load averages high, caused by mysql

napraviculom · 12-31-2009, 12:19 AM

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.

janoszen · 12-31-2009, 01:03 AM

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.

napraviculom · 12-31-2009, 01:34 AM

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 ?

janoszen · 12-31-2009, 01:51 AM

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.

EricTRA · 12-31-2009, 01:58 AM

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

napraviculom · 12-31-2009, 04:30 AM

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;

janoszen · 01-01-2010, 02:06 AM

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.

napraviculom · 01-01-2010, 07:29 AM

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