!.7.1+ Slow Login

!.7.1+ Slow Login

by Drew Rogers -
Number of replies: 5

Hello all,

We recently upgraded from moodle 1.5 to 1.7.1+. The installation went fine. Everything seems to be the same, however when user login, it takes from 10 seconds to 50 seconds. I optimized all the tables and made sure all the indexes are still there.

Sometimes when i do a `top` on the moodle server, the load is more then 3.0 and mysqld is using 100% off the CPU, even when there are only 10 people logged on.

When I go under phpMyAdmin runtime information, it tells me this:

Handler_read_rnd 171 M 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 316 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.

Slow_launch_threads 9,817 The number of threads that have taken more than slow_launch_time seconds to create.

Created_tmp_disk_tables 61 k The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

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

Opened_tables 24 k The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Table_locks_waited 301 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Thanks for all your help in advance,

~Drew

Average of ratings: -
In reply to Drew Rogers

Re: !.7.1+ Slow Login

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Search the forums for 'missing indexes' when upgrading from 1.5 or 1.6 versions to 1.7 or 1.8

There are very useful comments related to that issue.

Saludos. Iñaki.
Average of ratings: -
In reply to Drew Rogers

Re: !.7.1+ Slow Login

by Valery Fremaux -

I studied out this issue and experimented same length at first login. The responsible of this huge time overhead is the function that loads back the user profile containing all capailities for all contexts he will be in.

I didn't go further than identifying this cause, localizing the function call in witch major time is spent (user profile retrieve). I do not know if this raise is structural, dued to the capacity and permission system (I fear it is), and the enormous amount of additional data that becomes necessary to run a user session, or dependent of some particular modes of Moodle (I use LDAP authentication, but seems not being involved in such overhead).    

My only solution till now is displaying an explicit message to users in the login interface to prevent them of the unusual time needed to login, and telling them that the application runs OK once logged.incertain

Average of ratings: -
In reply to Valery Fremaux

Re: !.7.1+ Slow Login

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Yes, this is structural, but there is a huge amount of work being done to fix it, thanks to the Catalyst guys (search for Martin Langhoff's posts in the General Developer forum).

Saludos. Iñaki.
Average of ratings: -
In reply to Drew Rogers

Re: !.7.1+ Slow Login

by Drew Rogers -

The Problem was the table mdl_role_capabilities was missing 2 indexes:

capability and contextid.

After installing the indexes, log in takes about 1.5 seconds which is great. I am going to install a fresh verion (not an upgrade) of 1.7.1+ and compaire a database with 1.7.1+ upgrade to see if there are anymore missing.

Average of ratings: -