LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)
-   -   load averages high, caused by mysql (http://www.linuxquestions.org/questions/linux-server-73/load-averages-high-caused-by-mysql-779011/)

napraviculom 12-31-2009 12:19 AM

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.

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


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