Roles performance issues in moodle, whats wrong?

Roles performance issues in moodle, whats wrong?

by Markus Hagman -
Number of replies: 28
Hi,
Ive setup first test installation to moodle 1.53+ -> 1.7.

Now we have problems with performance example at profile page. loading times are extensive.

Could someone check out our mysql configuration, if there is some bottleneck slowing the mysql queries down? I found out that using temp tables in mysql slowns things down dramatically

All help is welcome:

Variable Session value Global value
back log 50 50
basedir /usr/ /usr/
bdb cache size 8388600 8388600
bdb home /var/lib/mysql/ /var/lib/mysql/
bdb log buffer size 98304 98304
bdb logdir

bdb max lock 10000 10000
bdb shared data OFF OFF
bdb tmpdir /tmp/ /tmp/
binlog cache size 32768 32768
bulk insert buffer size 8388608 8388608
character set client utf8 latin1
character set connection latin1 latin1
character set database latin1 latin1
character set results utf8 latin1
character set server latin1 latin1
character set system utf8 utf8
character sets dir /usr/share/mysql/charsets/ /usr/share/mysql/charsets/
collation connection latin1_swedish_ci latin1_swedish_ci
collation database latin1_swedish_ci latin1_swedish_ci
collation server latin1_swedish_ci latin1_swedish_ci
concurrent insert ON ON
connect timeout 5 5
datadir /var/lib/mysql/ /var/lib/mysql/
date format %Y-%m-%d %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s
default week format 0 0
delay key write ON ON
delayed insert limit 100 100
delayed insert timeout 300 300
delayed queue size 1000 1000
expire logs days 0 0
flush OFF OFF
flush time 0 0
ft boolean syntax + -><()~*:""&| + -><()~*:""&|
ft max word len 84 84
ft min word len 4 4
ft query expansion limit 20 20
ft stopword file (built-in) (built-in)
group concat max len 1024 1024
have archive NO NO
have bdb YES YES
have blackhole engine NO NO
have compress YES YES
have crypt YES YES
have csv NO NO
have example engine NO NO
have geometry YES YES
have innodb YES YES
have isam YES YES
have ndbcluster NO NO
have openssl YES YES
have query cache YES YES
have raid NO NO
have rtree keys YES YES
have symlink YES YES
init connect

init file

init slave

innodb additional mem pool size 1048576 1048576
innodb autoextend increment 8 8
innodb buffer pool awe mem mb 0 0
innodb buffer pool size 8388608 8388608
innodb data file path ibdata1:10M:autoextend ibdata1:10M:autoextend
innodb data home dir

innodb fast shutdown ON ON
innodb file io threads 4 4
innodb file per table OFF OFF
innodb flush log at trx commit 1 1
innodb flush method

innodb force recovery 0 0
innodb lock wait timeout 50 50
innodb locks unsafe for binlog OFF OFF
innodb log arch dir

innodb log archive OFF OFF
innodb log buffer size 1048576 1048576
innodb log file size 5242880 5242880
innodb log files in group 2 2
innodb log group home dir ./ ./
innodb max dirty pages pct 90 90
innodb max purge lag 0 0
innodb mirrored log groups 1 1
innodb open files 300 300
innodb table locks ON ON
innodb thread concurrency 8 8
interactive timeout 28800 28800
join buffer size 131072 131072
key buffer size 33554432 33554432
key cache age threshold 300 300
key cache block size 1024 1024
key cache division limit 100 100
language /usr/share/mysql/english/ /usr/share/mysql/english/
large files support ON ON
license GPL GPL
local infile ON ON
locked in memory OFF OFF
log OFF OFF
log bin OFF OFF
log error

log slave updates OFF OFF
log slow queries OFF OFF
log update OFF OFF
log warnings 1 1
long query time 10 10
low priority updates OFF OFF
lower case file system OFF OFF
lower case table names 0 0
max allowed packet 1047552 1047552
max binlog cache size 4294967295 4294967295
max binlog size 1073741824 1073741824
max connect errors 1000 1000
max connections 100 100
max delayed threads 20 20
max error count 64 64
max heap table size 16777216 16777216
max insert delayed threads 20 20
max join size 4294967295 4294967295
max length for sort data 1024 1024
max prepared stmt count 16382 16382
max relay log size 0 0
max seeks for key 4294967295 4294967295
max sort length 1024 1024
max tmp tables 32 32
max user connections 0 0
max write lock count 4294967295 4294967295
myisam data pointer size 4 4
myisam max extra sort file size 2147483648 2147483648
myisam max sort file size 2147483647 2147483647
myisam recover options OFF OFF
myisam repair threads 1 1
myisam sort buffer size 8388608 8388608
myisam stats method nulls_unequal nulls_unequal
net buffer length 8192 8192
net read timeout 30 30
net retry count 10 10
net write timeout 60 60
new OFF OFF
old passwords ON ON
open files limit 1024 1024
pid file /var/run/mysqld/mysqld.pid /var/run/mysqld/mysqld.pid
port 3306 3306
preload buffer size 32768 32768
prepared stmt count 0 0
protocol version 10 10
query alloc block size 8192 8192
query cache limit 1048576 1048576
query cache min res unit 4096 4096
query cache size 33554432 33554432
query cache type ON ON
query cache wlock invalidate OFF OFF
query prealloc size 8192 8192
range alloc block size 2048 2048
read buffer size 520192 520192
read only OFF OFF
read rnd buffer size 520192 520192
relay log purge ON ON
relay log space limit 0 0
rpl recovery rank 0 0
secure auth OFF OFF
server id 0 0
skip external locking ON ON
skip networking OFF OFF
skip show database OFF OFF
slave net timeout 3600 3600
slave transaction retries 0 0
slow launch time 2 2
socket /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock
sort buffer size 2097144 2097144
sql mode

sql notes ON ON
sql warnings ON ON
storage engine MyISAM MyISAM
sync binlog 0 0
sync frm ON ON
sync replication 0 0
sync replication slave id 0 0
sync replication timeout 0 0
system time zone EET EET
table cache 192 192
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by Markus Hagman -
Here's more stuff about this matter. Our server is dual-core and has 2gb memory. This is one sql-query made by Moodle when Im accesing someone's profile page.

SQL-query:
This is done for every course on our moodle (400+) and it takes time 0.3436 sec. 0.3*400 = 120 seconds.. huh? Could someone check is this normal?

Query is made by function has_capability, and here's example

EXPLAIN SELECT rc.capability, c1.id AS id1, c2.id AS id2, (
c1.contextlevel *100 + c2.contextlevel
) AS aggrlevel, rc.permission AS sum
FROM mdl_role_assignments ra, mdl_role_capabilities rc, mdl_context c1, mdl_context c2
WHERE ra.contextid = c1.id
AND ra.roleid = rc.roleid
AND ra.userid =39
AND rc.contextid = c2.id
AND c1.id
IN ( 81, 26, 1, 511 )
AND rc.contextid !=1
AND rc.capability = 'moodle/course:view'
AND (
(
ra.timestart =0
OR ra.timestart <1163102985
)
AND (
ra.timeend =0
OR ra.timeend >1163102985
)
)
GROUP BY rc.capability, (
c1.contextlevel *100 + c2.contextlevel
), c1.id, c2.id, rc.permission
ORDER BY aggrlevel ASC

When I use EXPLAIN on mysql it says:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ra ref contextid-roleid-userid,roleid,contextid,userid userid 4 const 19 Using where; Using temporary; Using filesort
1 SIMPLE rc ref roleid-contextid-capability,roleid,contextid roleid 4 moodle_test.ra.roleid 1783 Using where
1 SIMPLE c1 eq_ref PRIMARY PRIMARY 4 moodle_test.ra.contextid 1 Using where
1 SIMPLE c2 eq_ref PRIMARY PRIMARY 4 moodle_test.rc.contextid 1

Is this normal?

I think what I'm dealing here is a mysql issue, but I have no clue which variable to change to make it go away.
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by Markus Hagman -
! Found it !

All the indexes werent added when updating Moodle. I made a clean Install from moodle 1.7 and found that the most important index was missing in mdl_role_capabilities table.

my upgraded moodle 1.7 had no no index on mdl_role_capabilities.capability, and the clean moodle 1.7 had it.

Is this just my upgrades are there othes alike? Im not sure why this hasnt been problem with anyone else.

Now Im wondering if there's other indexes missing too.. and starting to check those out now.

We have MySQL - 4.1.20.
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by Markus Hagman -
I found that My problem is issued in Moodle Tracker also (http://tracker.moodle.org/browse/MDL-6855) , first sight I looked my update moodle I found that from first 20 tables these are missing



No Index at all:
adodb_logsql INDEX id

mdl_backup_log INDEX mdl_backlog_cou_ix

mdl_block_instance INDEX mdl_blocinst_blo_ix
mdl_block_pinned INDEX mdl_blocpinn_blo_ix
mdl_choice_answers INDEX mdl_choiansw_opt_ix
mdl_course_categories INDEX mdl_courcate_par_ix
mdl_data_comments INDEX mdl_datacomm_rec_ix
mdl_data_content INDEX mdl_datacont_rec_ix recordid
mdl_data_content INDEX mdl_datacont_fie_ix fieldid
mdl_course_display INDEX mdl_courdisp_couuse_ix course,userid
...

double index:
mdl_choice UNIQUE id
mdl_choice_answers UNIQUE id
mdl_choice_options UNIQUE id
mdl_course_categories UNIQUE id
mdl_course_display UNIQUE id
mdl_course_modules UNIQUE id
...

Different:
mdl_course category_sortorder category,sortorder (clean install without sortorder)
...


Before this is fixed there are serious performance issues.
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by M Gardner -
Really interested to read your post we had similar problems
see http://moodle.org/mod/forum/discuss.php?d=58856

We upgraded from 1.6.2

Have you created the indexes? If so has the system started to work ok?

Regards


Average of ratings: -
In reply to M Gardner

Re: Roles performance issues in moodle, whats wrong?

by M Gardner -
As I mentioned in earlier thread I've had similar problem
Too much lag on 1.7 upgrade system

I also made a clean installation of 1.7 and then compared the schema with my upgrade from 1.6.2.

There are lots of indexes missing on the upgrade schema to compare with the clean installation I made.

Is this a known issue? Should a fault report be issued?
Average of ratings: -
In reply to M Gardner

Re: Roles performance issues in moodle, whats wrong?

by Martín Langhoff -
We definitely need help tracking down what conditions trigger this problem. If you can help us, look in the tracker and see if there's a bug already for this. If not, file a new one.

What we need is as much info on your environment, and whether you can repeat the problem. For example, do you have a backup of your pre-upgrade database? If you restore it to a temp database and attempt an upgrade on it, does it get the correct indexes or not?
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by Ethan Sommer -
More important than that, at least on our site, was that in some cases there were indexes but the cardinality of the index was 0 in phpMyAdmin. If I ran "REPAIR TABLE bla QUICK" the cardinality would become something more reasonable. I'm not sure how I would cause that problem to happen if I wanted to.

I ran
"for x in `echo "show tables" | mysql -pMYPASSWORD moodle`; do echo "REPAIR TABLE $x QUICK;" | mysql -pMYPASSWORD moodle; done"

Which appears to have fixed all the tables, and everything seems much quicker now. I guess I'll go back now and check to see if the indexes in the bug are there/important.


Average of ratings: -
In reply to Ethan Sommer

Re: Roles performance issues in moodle, whats wrong?

by Martín Langhoff -
The stats on the indexes are bogus... you need to analyse your database tables. The repair table is doing it for you, but you'll be better served by http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Average of ratings: -
In reply to Martín Langhoff

Re: Roles performance issues in moodle, whats wrong?

by Rick Boyce -
Does anyone have a script or suggestions for fixing these missing indexes? I read the tracker comments but couldn't find any description of a fix for this.

Since the upgrade to 1.7 anywhere where roles are displayed (for example a profile page) is very slow to load, opening a couple of student profiles bogs down the entire server. Its starting to become a big problem for us.

Cheers
Rick
Average of ratings: -
In reply to Rick Boyce

Re: Roles performance issues in moodle, whats wrong?

by Ken Wilson -

Rick

If the shell command suggested by Ethan does not work for you, then try this php script by copying into the root of your moodle directory where your moodle config.php is located. Run by browsing to http://my.moodle.com/moodle/mdl-db-opt.php. Move it out of that directory when you're done with it.

There's no guarantee it will make any difference wink but it will run the mysql CHECK, OPTIMIZE and ANALYZE (REPAIR also if needed) statements on all your tables - this MySQL document suggests that these statements should be run on a weekly basis as a cron job as well as starting mysql with --myiasm-recover=BACKUP,FORCE options. So if doing these checks improves matters, it may be a good idea to run them periodically.

As usual, switch moodle to maintenance mode, wait for all the users to be logged off and backup your database (mysqldump) before attempting to run the script.

If there are any error messages reported then please let everyone here know.

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Roles performance issues in moodle, whats wrong?

by Will Taylor -
Thanks,
running the mdle-db-opt.php script did the job for me -
I was having very slow rendering of User Profiles as the only (discovered) performance issue (on 1.7+ upgraded from 1.6.3+), after optimizing the db with this script this is nicely resolved.
(p.s. - the stats_daily table took quite a while - nearly 2hrs - so folks doing this might wish to allocate adequate time for the process)
- will

Average of ratings: -
In reply to Will Taylor

Re: Roles performance issues in moodle, whats wrong?

by Will Taylor -
Initially, running the mdle-db-opt.php script seemed to speed things up for me, but this did not last or may have only seemed to help on a couple of trials of access to the user profiles. I finally went thru the database table-by-table. There were MANY duplicate indices. After cleaning these up (I did them all in one go, so am not clear on which ones had the most punch), delivery of the User Profile pages - (my biggest problem, these would hang forever) - became normal, and overall performance seems to have improved for the site.
Average of ratings: -
In reply to Ken Wilson

Re: Roles performance issues in moodle, whats wrong?

by Kevin Audleman -
Is it possible to run this script without putting my site in maintenance mode? The timing gets much trickier if I have to take the site down.

Kevin
Average of ratings: -
In reply to Kevin Audleman

Re: Roles performance issues in moodle, whats wrong?

by Ken Wilson -

Kevin

The commands used in the script rewrite indexes so, to play safe and avoid the risk of data loss, it's best practice to do this type of operation with the site inactive. Your users will complain more if they lose their data than if the site is down for a short period smile!

To get an idea of the time implications, try backing-up the moodle database and restoring to a test moodle database. Copy the script to a new moodle directory (called say moodletest), changing the config.php file for the new database. You should then be able to see the time taken and so inform users more accurately of downtime expected.

HTH!

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Roles performance issues in moodle, whats wrong?

by Kevin Audleman -
Thank you for the information Ken!

Kevin
Average of ratings: -
In reply to Kevin Audleman

Re: Roles performance issues in moodle, whats wrong?

by Kevin Audleman -
Ok, I ran mdl-db-opt.php and it didn't help. My site is still dead slow -- we're talking 30 seconds a page load.

I tried to figure out if my server was too slow, but at 2.4 GHz and 2 GB memory it seemed unlikely. In addition, the site takes 30 seconds to render a page even when there's only one user on the entire server.

I figure the problem is in MySQL, probably something to do with the new roles. I used SHOW PROCESSLIST to query the processes running on MySQL. The following query keeps showing up and taking a long time:

State Info
Copying to tmp table SELECT DISTINCT u.id, u.firstname, u.lastname, l.time
FROM mdl_user u,

It looks like copying to a temp table is the cause of the slowdown? Any insight from the developers?

Kevin Audleman


Average of ratings: -
In reply to Kevin Audleman

Re: Roles performance issues in moodle, whats wrong?

by Kevin Audleman -
Ok, I appear to have solved my problem. I added an index to the time field in the log table and things have sped up significantly.

Kevin
Average of ratings: -
In reply to Kevin Audleman

Re: Roles performance issues in moodle, whats wrong?

by John Rodkey -
We are experiencing the same problem - user profile is very slow.

We have run the mdl-db-opt.php and created index on mdl_log.time to no avail.

When it is slow in responding, SHOW PROCESSLIST results in show processlist
-> ;
+----+--------+------------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+------------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 18 | moodle | 10.50.10.250:9166 | moodle | Query | 0 | Copying to tmp table | SELECT rc.capability, c1.id as id1, c2.id as id2, (c1.contextlevel * 100 + c2.contextlevel) AS aggrl |
| 75 | root | localhost | moodle | Query | 0 | NULL | show processlist |
+----+--------+------------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

linux/mysql servers. mysql 4.0.18, not using UTF-8
Average of ratings: -
In reply to John Rodkey

Re: Roles performance issues in moodle, whats wrong?

by Martín Langhoff -
Make sure you tell mysql to optimise the indexes and collect stats on them. On Pg we use VACUUM ANALYSE, I'm sure there's a similar one for MySQL wink
Average of ratings: -
In reply to Martín Langhoff

Re: Roles performance issues in moodle, whats wrong?

by Genner Cerna -
i experience some problem with roles, what i did is to restore my backup...
Average of ratings: -
In reply to John Rodkey

Re: Roles performance issues in moodle, whats wrong?

by Ken Wilson -

John

That script runs the optimise command on your moodle *tables*. If it does not help (and it may not) then the problem is with an expensive query. The particular query in the SHOW PROCESSLIST is being looked at in MDL-8120, so it may be worthwhile keeping an eye on the Issue Tracker.

"Copying to tmp table" may be slowing things down. It's caused by the GROUP BY clause in that query. So, look at your temp table size parameters. If you need help, email me your outputs from SHOW STATUS and SHOW VARIABLES commands (best not to clog-up this thread).

FYI: The mysql OPTIMIZE TABLE command does not have any options, so you may also want to look at optimizing your query cache. See the performance docs for more info.

Ken

Average of ratings: -
In reply to Ken Wilson

Re: Roles performance issues in moodle, whats wrong?

by Damon Blanchette -
Kevin, or anyone else, I'm wondering how long it took you to run the script? We've got about 5,000 users in 1,700 courses. We're using 1.7+. I'd just like to get a ballpark figure, because it's only the second day of classes today, and taking down moodle for too long would not be a good thing.

Or, it would be great if someone could give me some directions on how to back up the database and restore it to a test server, in order to run the script for the fix.

Also, what's the command to index a mysql table?
Thanks!
Damon
Average of ratings: -
In reply to Ken Wilson

Re: Roles performance issues in moodle, whats wrong?

by Mark Dennehy -
Ran the mdl-db-opt script, and it did work - to an extent.
I'm using a 1.7.1+ build, with a little over 4000 courses. The admin add/edit courses page was taking 656 seconds to load, now it takes 493 seconds (about a 25% increase).

Thing is, I don't think a 4000-course courseedit page is ever going to run fast. So, is there a config.php setting that does for the admin's courseedit page what max_category_depth does for the course list page?
Average of ratings: -
In reply to Mark Dennehy

Re: Roles performance issues in moodle, whats wrong?

by Mark Dennehy -
Of course, this just goes to show that the best optimisation is to start from scratch looking at the basics.

Like the size of the page being 84Mb...

Turns out the 800-line page was putting a drop-down box with 800-course categories on each line, and between the size of the resulting html and the database time to call it...

Well, anyway. Commenting out the drop-down box leads from a 600 second load time to a 15 second load time. Problem solved.
Average of ratings: -
In reply to Markus Hagman

Re: Roles performance issues in moodle, whats wrong?

by Ryan Thomas -
In the hope that it will help others in getting these missing indexes created, I've attached the SQL I generated from the diff found in the tracker page referenced in previous post.
Average of ratings: -
In reply to Ryan Thomas

Re: Roles performance issues in moodle, whats wrong?

by Red Morris -

I too am having issues with MYD files. They appear on a, seemingly, random basis, but also happen when roles are assigned. They generate SQL errors all over the place and stop database updates.

At the moment I also have a scheduled task to move them to another folder running every minute, but it's not the best solution.

I wonder if comments I've seen elsewhere about utf-8 are related as there is reference to it if I open the files in Wordpad. I'm new to MySQL so I'm not sure if this is relevant or not.

Average of ratings: -
In reply to Ryan Thomas

Re: Roles performance issues in moodle, whats wrong?

by denis cahalane -
Thanks Ryan, that's really helpful. For others passing this way, there's at least one missing: a primary key on the id field in adodb_logsql. Prior to 1.7 this table doesn't seem to have had an id field, and it didn't get added by my upgrade.
Average of ratings: -