Linux - ServerThis forum is for the discussion of Linux Software used in a server related context.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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.
Distribution: Mostly Gentoo, sometimes Debian/(K)Ubuntu
Posts: 143
Rep:
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 ?
Distribution: Mostly Gentoo, sometimes Debian/(K)Ubuntu
Posts: 143
Rep:
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.
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.