Moodle in English: Apparent problem with indexes

Apparent problem with indexes

by Kevin Audleman - Wednesday, March 28, 2007, 9:13 AM

My site has been experiencing performace problems. When it gets to 40 users it gets very slow and starts refusing connections. I've been playing with my MySQL settings and came across the following in the runtime information (the numbers are after the server had been running for about an hour):

Handler_read_rnd	61 k	The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd_next	13 M	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.

Select_full_join	1	The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

These numbers grow quite quickly, and each indicates that there is a problem with indexing. When I performed the upgrade to 1.7, the site slowed to a crawl and I discovered a missing index in the log table. Now it appears like I may be missing a few more indexes.

I'm not sure where to go from here. If I am missing indexes, how can I go about discovering which ones?

Any advice much appreciated!

Kevin Audleman

Re: Apparent problem with indexes

by Ken Wilson - Friday, March 30, 2007, 8:42 PM

Take a look at http://moodle.org/mod/forum/discuss.php?d=58208 - there's a summary file at the end showing some of the missing indexes.

Average of ratings: -

Re: Apparent problem with indexes

by Kevin Audleman - Saturday, March 31, 2007, 6:43 AM

Holy crap, thank you so much for pointing that out to me. I ran the script and it added a missing index to the mdl_role_capabilities table. Wow, important. Immediately my site sped up significantly!!!

I can't thank everyone who was involved in finding that problem, posting the solution, and bringing it to my attention enough. So let me just say thank you and let you know that your contribution has been very significant to my school.

My site has been slow and troublesome for the last quarter and it may just be ok now. I am exuberant

Thank you,

Kevin Audleman

Average of ratings: -

Re: Apparent problem with indexes

by Henry Johnson - Sunday, April 1, 2007, 1:51 AM

I am not having any real performance issues so far, but I do have a fast server to play with. My issue is if this has happened to a number of sites that have upgraded from 1.6 to 1.7, why hasn't it been treated as a bug or update?

Being loathe to start creating and deleting indexes on a functioning Moodle system I am very curious to find out more. When looking at the number of indexes I am supposed to add, are their names important (and it does seem I do not have the indexes listed in createmissingindexes.txt)? And why do I have a number of tables that when I look at their structure using phpMyAdmin I get stuff like:

mdl_choice_answers - PRIMARY and INDEX keys should not both be set for column `id`

Should I go through all the tables and just remove (seeming randomly) one of the entries to make the warnings go away? And just run the createmissingindexes.txt as a sql command file and hope my joins and whatever become happy again?

Any help would be appreciated!

Thanks

Henry Johnson

Average of ratings: -

Hardware and performance

Apparent problem with indexes