[LF6.2] Slow query while checking permissions

[Diego Cantone, modificado 6 Anos atrás.]

Hi guys,
i recently upgraded one MySQL db from 5.5 to 5.6, all went fine, except that now some page load for minutes because of these MySQL queries that take really long time, the problem doesn't exist if i use an user with Administrator role, probably because there are't permission check in this case.
Anyone have some idea of what can be happened?
These are the queries:

SELECT COUNT(DISTINCT assetCategory.categoryId) AS COUNT_VALUE FROM AssetCategory assetCategory   INNER JOIN ResourcePermission ON (ResourcePermission.companyId = 20155) AND (ResourcePermission.name = 'com.liferay.portlet.asset.model.AssetCategory') AND (ResourcePermission.scope = 4) AND (MOD(ResourcePermission.actionIds, 2) = 1) AND (ResourcePermission.roleId IN (20165,20166,36641,36642,20163) OR (ResourcePermission.ownerId = 36799)) AND (((ResourcePermission.primKey = CAST(assetCategory.categoryId AS CHAR)) AND (((assetCategory.groupId = 20195)))))   WHERE assetCategory.groupId = 20195 AND assetCategory.vocabularyId = 28536;
# Time: 170428 15:27:10
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 20368
# Query_time: 156.226916  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 6575
SET timestamp=1493386030;

And:

SELECT COUNT(DISTINCT assetCategory.categoryId) AS COUNT_VALUE FROM AssetCategory assetCategory   INNER JOIN ResourcePermission ON (ResourcePermission.companyId = 20155) AND (ResourcePermission.name = 'com.liferay.portlet.asset.model.AssetCategory') AND (ResourcePermission.scope = 4) AND (MOD(ResourcePermission.actionIds, 2) = 1) AND (ResourcePermission.roleId IN (20165,20166,36641,36642,20163) OR (ResourcePermission.ownerId = 36799)) AND (((ResourcePermission.primKey = CAST(assetCategory.categoryId AS CHAR)) AND (((assetCategory.groupId = 20195)))))   WHERE assetCategory.groupId = 20195 AND assetCategory.vocabularyId = 21578;
# Time: 170428 15:24:44
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 20352
# Query_time: 81.005573  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 3413
SET timestamp=1493385884;

[Andrew Jardine, modificado 6 Anos atrás.]

Hi Diego,

Do you still have a 5.5 version of the database available? The first thing I would check would be to make sure that the indexes that existed on the 5.5 tables (associated with permissions) are one to one with those in the 5.6 version. I have seen threads (not necessarily Liferay specific, but 5.6 specific) that have talked about slow queries and missing indexes.

Let me know if you find anything.

[Diego Cantone, modificado 6 Anos atrás.]

Andrew Jardine:

Hi Diego,

Do you still have a 5.5 version of the database available? The first thing I would check would be to make sure that the indexes that existed on the 5.5 tables (associated with permissions) are one to one with those in the 5.6 version. I have seen threads (not necessarily Liferay specific, but 5.6 specific) that have talked about slow queries and missing indexes.

Let me know if you find anything.

Hi Andrew, i've just see your answer today, and sadly i haven't already found a solution to the problem, i have the 5.5 version of the database and i've checked the indexes in the ResourcePermission tables and in the AssetCategory tables, but they are the same..
Do you have any other ideas?

[Alfonso Crisci, modificado 6 Anos atrás.]

Ciao Diego,

I could not find any related issues in Jira so far, it clearly looks and issue with MySQL on INNER JOIN/several ResourcePermissions. If, as you said, the Admin has no issues, permissions.inline.sql.check.enabled=false should help, but this is not advised and isn't the best solution, especially in productive environments.

If you are not on latest version, you could make a test after upgrading and starting the portal with database.indexes.update.on.startup=true, many performance fixes have been released in newer GA's

[Diego Cantone, modificado 6 Anos atrás.]

Alfonso Crisci:

Ciao Diego,

I could not find any related issues in Jira so far, it clearly looks and issue with MySQL on INNER JOIN/several ResourcePermissions. If, as you said, the Admin has no issues, permissions.inline.sql.check.enabled=false should help, but this is not advised and isn't the best solution, especially in productive environments.

If you are not on latest version, you could make a test after upgrading and starting the portal with database.indexes.update.on.startup=true, many performance fixes have been released in newer GA's

Hi Alfonso,
seems that permissions.inline.sql.check.enabled=false it's a solution in my case!
I'm on the latest version so can't try the second solution!

[Optimum Informatique, modificado 5 Anos atrás.]

Hello Alfonso,

I didn't find substancial documentation on the utility of the parameter permissions.inline.sql.check.enabled. Some say it is use by the collaboration components and if we don't use them, we should disable this feature.

http://what-when-how.com/Tutorial/topic-2648rlr/Liferay-Portal-Performance-Best-Practices-82.html

http://www.surekhatech.com/blog/liferay-performance-tuning

I see that you would not recommend to disable this parameter:  "...this is not advised and isn't the best solution, especially in productive environments."

Is this parameter really only use by the collaboration components? 

Why we shouldn't disable this parameter?

Regards

[Alfonso Crisci, modificado 5 Anos atrás.]

Optimum Informatique:

Hello Alfonso,

I didn't find substancial documentation on the utility of the parameter permissions.inline.sql.check.enabled. Some say it is use by the collaboration components and if we don't use them, we should disable this feature.

http://what-when-how.com/Tutorial/topic-2648rlr/Liferay-Portal-Performance-Best-Practices-82.html

http://www.surekhatech.com/blog/liferay-performance-tuning

I see that you would not recommend to disable this parameter:  "...this is not advised and isn't the best solution, especially in productive environments."

Is this parameter really only use by the collaboration components? 

Why we shouldn't disable this parameter?

Regards

Hi there,

During search, Liferay runs a permission check on each of the results to see if the user that is searching has rights to view the result in question. If the user has the right, the result will be shown, otherwise it will not be returned.

This functionality can be configured with the permissions.inline.sql.check.enabled property, which is set to true by default.

Setting the property to false, the search will return results even if the user has no view permissions on them. The portal will run a permission check should the user try to open the result, and deny access if the necessary permissions are missing.

Example: Consider executing a search that would return 500 files from the Document Library. With permissions.inline.sql.check.enabled=true, the portal will run a permission check on each of the found documents and only show those to which the user has access. If the property is set to false, all 500 files are returned and shown.

The intent of this property is to make inline permission checks configurable, thereby giving an option to trade performance for added permission checks.