LinuxQuestions.org - load averages high, caused by mysql

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

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

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.

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.

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 ?

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.

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

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;

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.

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