Mariadb/mysql hangs when you search for user to add to journal

[mirkh]

We upgraded to version OJS 3.3.0.6 recently. Since then load has increased on our server, and there are some database queries that hangs the database. When that happens

• all cores are at 100%
• the processlist shows queries writing to tmp table
• it is not possible to login to OJS, or perform any searches

A case when this happens is, when logged on as admin, I go to Administration / Hosted Journals, fold out a journal, click Users, then I do a search.

Example query

SELECT DISTINCT u.*, COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given, CASE WHEN ugl.setting_value <> '' THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = 'affiliation')
                                LEFT JOIN user_interests ui ON (u.user_id = ui.user_id)
                                LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id)
                                LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id)
                                LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id)
                                LEFT JOIN user_settings ugl ON (u.user_id = ugl.user_id AND ugl.setting_name = 'givenName' AND ugl.locale = 'sv_SE')
                        LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = 'givenName' AND ugpl.locale = 'sv_SE')
                        LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = 'familyName' AND ufl.locale = 'sv_SE')
                        LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = 'familyName' AND ufpl.locale = 'sv_SE')
                                LEFT JOIN user_settings usgs ON (usgs.user_id = u.user_id AND usgs.setting_name = 'givenName')
                                LEFT JOIN user_settings usfs ON (usfs.user_id = u.user_id AND usfs.setting_name = 'familyName')
                        WHERE   1=1  AND (   ( LOWER(CONCAT(COALESCE(usgs.setting_value,''),COALESCE(usfs.setting_value,''),u.email,COALESCE(us.setting_value,''))) LIKE '%anders%' OR LOWER(cves.setting_value) LIKE '%anders%' )   ) ORDER BY user_family, user_given LIMIT 25 OFFSET 0;

I ran an explain of the query and got

+------+-------------+-------+------+----------------------------------------------------------------------------------+--------------------------+---------+----------------------------------+-------+---------------------------------+
| id   | select_type | table | type | possible_keys                                                                    | key                      | key_len | ref                              | rows  | Extra                           |
+------+-------------+-------+------+----------------------------------------------------------------------------------+--------------------------+---------+----------------------------------+-------+---------------------------------+
|    1 | SIMPLE      | u     | ALL  | NULL                                                                             | NULL                     | NULL    | NULL                             | 10241 | Using temporary; Using filesort |
|    1 | SIMPLE      | us    | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 8       | ojs.u.user_id                    |     6 | Using where                     |
|    1 | SIMPLE      | ui    | ref  | u_e_pkey                                                                         | u_e_pkey                 | 8       | ojs.u.user_id                    |     1 | Using index                     |
|    1 | SIMPLE      | cves  | ref  | c_v_e_s_pkey,c_v_e_s_entry_id                                                    | c_v_e_s_pkey             | 8       | ojs.ui.controlled_vocab_entry_id |     1 | Using where                     |
|    1 | SIMPLE      | uug   | ref  | user_user_groups_user_id                                                         | user_user_groups_user_id | 8       | ojs.u.user_id                    |     1 | Using index                     |
|    1 | SIMPLE      | ugl   | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 819     | ojs.u.user_id,const,const        |     1 | Using where                     |
|    1 | SIMPLE      | ugpl  | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 819     | ojs.u.user_id,const,const        |     1 | Using where                     |
|    1 | SIMPLE      | ufl   | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 819     | ojs.u.user_id,const,const        |     1 | Using where                     |
|    1 | SIMPLE      | ufpl  | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 819     | ojs.u.user_id,const,const        |     1 | Using where                     |
|    1 | SIMPLE      | usgs  | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 8       | ojs.u.user_id                    |     6 | Using where; Distinct           |
|    1 | SIMPLE      | usfs  | ref  | user_settings_pkey,user_settings_user_id,user_settings_locale_setting_name_index | user_settings_pkey       | 8       | ojs.u.user_id                    |     6 | Using where; Distinct           |
+------+-------------+-------+------+----------------------------------------------------------------------------------+--------------------------+---------+----------------------------------+-------+---------------------------------+

we have about 10.000 users registered.

Any tips?

[marc]

Are those 10.000 users real? Sounds like… a lot for a regular journal.

If your OJS was attacked in past by spammers you will like to read this post and apply some of the patterns to clean your user list:

Collaborative list of spam user patterns

Whether you’ve inherited a new magazine or it’s time to put your house in order, in the forum you can find several good threads on how to deal with spam users but I miss an unified and updated list of spam patterns like the one @jmacgreg started in How do I combat spam?, so, here you have a forum-wiki-page here with an initial compilation that could be collectively extended/enriched. I tried to make it compact and with same structure to facilitate the reading and are numbered for an easy refere…

Cheers,
m.

[mirkh]

Hi, and thanks for replying!

We used to have 17.000, and I cleared out the obvious spam users. Our site has 63 journals, of which 50 are enabled at the moment.

We do still have quite a few users with no role, though. (Using “SELECT username FROM users WHERE user_id NOT IN (SELECT user_id FROM roles)”).

Do you think the problem of the query is because of the amount of users?

/ Maria

[marc]

Well… I’m not a DB expert but those nested left joins look quite stressing for any server.

But if you like you can try with a different approach:
You can notice performance issues when you have mixed engines (move all MyISAM to InnoDB) or mixed charsets.

A mysqldump, edit your dump and recreate the DB is usually the best way to do those engine and charset changes… but feel free to try with ALTER table if you like (of course, remember to make a DB backup first ).

Cheers,
m.

[mirkh]

Hi marc, you saved us. When we upgraded we only altered submission_files to InnoDB.

Now we changed all tables involved in the query that was hanging, and now the database was able to work while performing the search.

Thank you very much!

[marc]

Happy it helped.

Post again if you find any trouble.