Hi,
I thought I would share this info. We have upgraded to 1.9.4+ (which is almost identical to 1.9.5) with over 3000 courses and 60000 user accounts (active for summer: around 4000).
Right after the upgrade we started to experience performance issues.
Note from our programmer: "The slow query log indicates some very slow database operations involving the mdl_user_info_data table.
This is the table that holds custom profile attribute values. It is hit during logins and other profile operations.
We added an index to the table. Others apparently agree because MDL-17201 calls for it, but it is not done yet."
1.9.4+ (almost 1.9.5) and performance - table mdl_user_info_data
by Elena Ivanova -
Number of replies: 1
In reply to Elena Ivanova
Re: 1.9.4+ (almost 1.9.5) and performance - table mdl_user_info_data
by Taylor Judd -
Hi Elena,
I found the same result just this week actually:
mysql> explain select data from mdl_user_info_data where userid = '121989' and fieldid = '2' limit 1;
+----+-------------+----------
I found the same result just this week actually:
mysql> explain select data from mdl_user_info_data where userid = '121989' and fieldid = '2' limit 1;
+----+-------------+----------
----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | mdl_user_info_data | ALL | NULL | NULL | NULL | NULL | 20674 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Added an index on the two columns used for the query:
ALTER TABLE `mdl_user_info_data` ADD INDEX `userid-fildid` (`userid`, `fieldid`) ;
Did another explain:
"id";"select_type";"table";"
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | mdl_user_info_data | ALL | NULL | NULL | NULL | NULL | 20674 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Added an index on the two columns used for the query:
ALTER TABLE `mdl_user_info_data` ADD INDEX `userid-fildid` (`userid`, `fieldid`) ;
Did another explain:
"id";"select_type";"table";"
type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"SIMPLE";"mdl_user_info_data";"ref";"userid-fildid";"userid-fildid";"16";"const,const";"1";""
Though my formatting leaves a lo to be desired you can see the difference in the query using the index and returning only one result rathter than having to search through 20,674 rows.
"1";"SIMPLE";"mdl_user_info_data";"ref";"userid-fildid";"userid-fildid";"16";"const,const";"1";""
Though my formatting leaves a lo to be desired you can see the difference in the query using the index and returning only one result rathter than having to search through 20,674 rows.