Enormous Handler_read_rnd_next in MySQL

[sv1cec]

Folks,

Upon checking my status variables for my database, I see a huge number for Handler_read_rnd (81M) and Handler_read_rnd_next (3,812.55M) and the second number is increasing rapidly (it was 3,810.07 about 2 minutes ago).

At this moment, my board is not very active (about 80 users online), Server Load Averages 1.05 0.97 0.93.

I read the explanation offered by MySQL for this parameter, so I went around checking for tables that are used without indexes (or with wrong indexes) and I can't find anything significantly wrong.

Do you guys have any idea what can cause this? Is there any way I can find out what tables are causing this?

Many tnx

[Daky]

Actually i have a same question..


Handler_read_rnd_next 13 G 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.

This MySQL server has been running for 3 days, 17 hours, 7 minutes and 29 seconds. It started up on Feb 04, 2009 at 10:56 PM.

Query statistics: Since its startup, 7,262,067 queries have been sent to the server.

PHP Code:

[mysqld]
log-slow-querie = /var/log/mysql/mysql-slow.log
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
#skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 1024M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 256
wait_timeout = 28800
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 8
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8

[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 384M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M 

Server is:
Quad Xeon 2.4 Ghz
4GB RAM
2x250GB SATAII 32Mb cache.

Please advise,
D

[Marco van Herwaarden]

Please use vBulletin.com to get suggestions on your servers configuration on the topic of general PHP/MySQL optimisation. On the vBulletin.com Server Configuration you will get suggestions by one of our Support Professionals.