Handler_read_rnd_next increasing rapidly

January 9, 2009 on 3:23 pm | By | In bluefreesky.com | Handler_read_rnd_next increasing rapidly

Upon checking my MySQL status information, I noticed that I have a very rapidly increasing number of Handler_read_rnd_next. It's not as if this number is due to a large duration since statistics begun, it is growing rapidly every minute. With a Server Load Averages 1.71 1.48 1.24 96 Users Online (27 members and 69 guests) the Handler_read_rnd_next went from 3,816.42M to 3,816.53M in one minute, 3,817.37M in 5 minutes.

Now, I read the explanation of this parameter in MySQL site, and it says that 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.

Is there any way to find out which of my tables are causing this behaviour? I checked all the tables and I rebuild them, so I can't for the name of God figure out why this is happening.

Any help will be appreciated.

Well, I just did. Still no answer, but I'll wait.

MYISAM tables do not have row-level locking, only table-level locking, so every SELECT query blocks a whole table for the updates.
InnoDB engine supports row-level locks, so InnoDB tables do not suffer from the table locking that much.

Opened_tables 17 k (yesterday 3675)What's your table_cache value? You may want to increase it to avoid re-opening of the tables.
Table_locks_waited 3,170 (yesterday 383)

One of the changes I've done in my site, is to use the mailqueue table locking in order to avoid having more than one notifications send out for a single thread. I do not know if those 3000 additional locks_waited added since yesterday are due to that, I am not aware of any other case where a table is locked by vB."LOCK TABLE" statements are only a minor part of this number - table_locks_waited is the number of times a table update process waited for a table locked by another process, e.g. when one thread executes a big select query and another tries to update the same table.

Whoa whoa! it's simply not true. Surely you can cherry-pick the workloads in which MyISAM will be faster, and vice versa, but in general, the MyISAM and InnoDB performance are very comparable, and InnoDB scales much better under the heavy workloads. Have a look at this for example:
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
or this:
http://www.mysqlperformanceblog.com/2006/06/13/quick-look-at-ubuntu-606/

I don't want to get in an argument here, but it's funny that you mentioned that you can cherry-pick workloads that will showcase each engine, and then posted links to a page where they only tested SELECT statements.

That said, I have yet to come across a properly-written application that performed better with InnoDB tables than MyISAM. Yes, you can optimize your server to get InnoDB tables to perform pretty well (at the expense of a TON of RAM) but the fact remains that out of the box, InnoDB performance is horrible.

The only app that I've found that worked better with InnoDB was an advert program that was writing impressions back to the same table that it was using to store the data used to generate the ads. In this case, row-locking instead of table-locking caused an increase in response time.

Thanks for your answer.

Indeed my table-cache is (I think) small, 64. The database was set up using all default values. Today I updated the join_buffer_size (as it was suggested above) with no effect on the problem. Perhaps a minor increase in the page loading times, but nothing to write home about.

As for the locking, I understand what you are saying. I was under the impression (being an ex-Oracle person) that reads do not block writes, but that might be an Oracle-only thing.

It can also rapidly increase if there are large joins and the join_buffer_size is set too low. Personally, I keep my servers at 4MB and Handler_read_rnd_next is at 348 after about a week of uptime.

Only vB is installed. Admitedly, my site is heavily hacked, but the hacks I am using are mostly administrative things, not end-user related. And this parameter is growing rapidly even when there are very few users in the site.

I spend the entire morning yesterday verifying that every query run in vB is using an index in its table. I have run out of ideas on what to check.

Scott, I'll definitely go through forumdisplay again and see if there is something I missed. As for the slow-queries-log, I'll check it out. I do not remember what was the value of that Status parameter yesterday, but today it shows only 2 (I know it shouldn't show anything, but maybe this was old info). I'll check again later today.

In the mean time, here are all the Status parameters that are in red in the stats.

Slow_queries 2
Handler_read_rnd 7,777 k (yesterday about the same time it was 1506 k)
Handler_read_rnd_next 827 M (yesterday about the same time it was 145 M)
Created_tmp_disk_tables 477 (yesterday 133)
Select_full_join 200 (yesterday 94)
Opened_tables 17 k (yesterday 3675)
Table_locks_waited 3,170 (yesterday 383)

One of the changes I've done in my site, is to use the mailqueue table locking in order to avoid having more than one notifications send out for a single thread. I do not know if those 3000 additional locks_waited added since yesterday are due to that, I am not aware of any other case where a table is locked by vB.

You could potentially use the slow query log to try and track this down if the queries are taking a while to run.

Though can you check if its maybe forumdisplay, pick some weird sorting method as there are some that aren't indexed.

Thanks for this, I tried this (mine was set at a low 131000) and even though it made my forums site quicker, it didn't affect the Handler_read_rnd_next.

Is there a way to find out which tables are causing this rapid increas in Handler_read_rnd_next?

Can we please stay on the thread's issue?

Many thanks

do you have mysql using scripts other than vB installed on the server as well, as it could be other scripts on server contributing to this as well

... but InnoDB tables are VERY slow, so don't use them if you don't have to.Whoa whoa! it's simply not true. Surely you can cherry-pick the workloads in which MyISAM will be faster, and vice versa, but in general, the MyISAM and InnoDB performance are very comparable, and InnoDB scales much better under the heavy workloads. Have a look at this for example:
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
or this:
http://www.mysqlperformanceblog.com/2006/06/13/quick-look-at-ubuntu-606/

sv1cec
The optimal values for your my.cnf depend on your db size, your server hardware specs and the forum activity. I'd suggest creating a new thread with the required info (http://www.vbulletin.com/forum/showthread.php?t=70117) and have eva2000 look at it.

Can we please stay on the thread's issue?

Many thanks

Sorry...

In any case, I just checked out your other thread (http://www.vbulletin.com/forum/showthread.php?t=223763). You should really take George's suggestion and move away from MySQL 3. MySQL 4 (or 5) will allow you to use a whole host of new performance-enhancing features (such as the query cache). There may be some bug in your version that is causing the issue you've reported.

You may also want to consider moving away from kernel 2.4.x to 2.6.x (yes, I know that will probably require a reinstall or a new server) as there are some significant performance improvements there as well.

Finally, are those really 1.4GHz Xeon chips and not faster chips that happen to be running with some kind of SpeedStep throttling?

Are there any suggested values for the MySQL system variables for vB?

#If you have any other info about this subject , Please add it free.#

edit

bluefreesky

welcome to my space