topic Slow database responses on requests for /alfresco/service/api/solr/metadata in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/slow-database-responses-on-requests-for-alfresco-service-api/m-p/281072#M234202 <HTML><HEAD></HEAD><BODY><SPAN>Hello,</SPAN><BR /><SPAN>We're experiencing some performance issues with Alfresco 4.0.e and a mysql 5.5 backend.</SPAN><BR /><SPAN>Through a performance monitor I can see a lot of requests (not sure what is triggering these) getting hung up on DB with a response time of nearly 100-200s. In the extreme case its 3000secs.</SPAN><BR /><SPAN>All these requests originate locally from the alfresco application (not share gui which we have on a separate server). The client ip shows 127.0.0.1.</SPAN><BR /><SPAN>URL = /alfresco/service/api/solr/metadata</SPAN><BR /><SPAN>On drilling down further there seem to be 2 queries that take more than 100s each that look like follows-</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />&nbsp;&nbsp;&nbsp;<BR />select<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as id,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentNode.id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as parentNodeId,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentNode.version&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as parentNodeVersion,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentStore.protocol&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as parentNodeProtocol,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentStore.identifier&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as parentNodeIdentifier,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentNode.uuid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as parentNodeUuid,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childNode.id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as childNodeId,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childNode.version&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as childNodeVersion,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childStore.protocol&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as childNodeProtocol,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childStore.identifier&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as childNodeIdentifier,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childNode.uuid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as childNodeUuid,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.type_qname_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as type_qname_id,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.child_node_name_crc&nbsp;&nbsp; as child_node_name_crc,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.child_node_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as child_node_name,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.qname_ns_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as qname_ns_id,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.qname_localname&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as qname_localname,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.is_primary&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as is_primary,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc.assoc_index&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; as assoc_index<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; alf_child_assoc assoc<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join alf_node parentNode on (parentNode.id = assoc.parent_node_id)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join alf_store parentStore on (parentStore.id = parentNode.store_id)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join alf_node childNode on (childNode.id = assoc.child_node_id)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; join alf_store childStore on (childStore.id = childNode.store_id)<BR />&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentNode.id = ?<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>I ran an explain plan on this query with a parameter value that seemed to be causing the most havoc (~3000secs) here's what I saw-</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />select_type&nbsp;&nbsp;&nbsp;table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;type&nbsp;&nbsp;&nbsp;possible_keys&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;key&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;key_len&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rows&nbsp;&nbsp;&nbsp;Extra<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;parentNode&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;PRIMARY,store_id,fk_alf_node_store&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;parentStore&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;childStore&nbsp;&nbsp;&nbsp;index&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;protocol&nbsp;&nbsp;&nbsp;454&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;Using index<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;childNode&nbsp;&nbsp;&nbsp;ref&nbsp;&nbsp;&nbsp;PRIMARY,store_id,fk_alf_node_store&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; store_id&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;alfrescomgr.childStore.id&nbsp;&nbsp;&nbsp;162579&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;assoc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ref&nbsp;&nbsp;&nbsp;parent_node_id,fk_alf_cass_pnode,fk_alf_cass_cnode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fk_alf_cass_cnode8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; alfrescomgr.childNode.id&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;Using where<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,idx_alf_cass_pri&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>Note the rows = 162k.</SPAN><BR /><SPAN>For a couple of such queries, the parent node seemed to point to a folder that stores thousands of small sized quote documents.</SPAN><BR /><SPAN>Our application pushes documents and queries them by some metadata attributes like customer id. We use the apache chemistry cmis api for the interaction.</SPAN><BR /><BR /><SPAN>Here's an example of another mysql query plan which is a bit similar to the one above taking ~50-100 sec.</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />select_type&nbsp;&nbsp;&nbsp;table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type&nbsp;&nbsp;&nbsp;possible_keys&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; key&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; key_len&nbsp;&nbsp;&nbsp;ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rows&nbsp;&nbsp;&nbsp;Extra<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentNode&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;PRIMARY,store_id,fk_alf_node_store&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; parentStore&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRIMARY&nbsp; &nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; assoc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ref&nbsp;&nbsp;&nbsp;parent_node_id,fk_alf_cass_pnode,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fk_alf_cass_cnode,idx_alf_cass_pri&nbsp;&nbsp;&nbsp;parent_node_id&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;const&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 167996&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childNode&nbsp;&nbsp;&nbsp;eq_ref&nbsp;&nbsp;&nbsp;PRIMARY,store_id,fk_alf_node_store&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;alfrescomgr.assoc.child_node_id&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;<BR />SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; childStore&nbsp;&nbsp;&nbsp;eq_ref&nbsp;&nbsp;&nbsp;PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRIMARY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;alfrescomgr.childNode.store_id&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>1. What do you think is triggering the solr queries. Its trying to load information on all children nodes.</SPAN><BR /><SPAN>2. How can we optimize it if we can't control the solr piece.</SPAN><BR /><BR /><SPAN>Would really appreciate your help.</SPAN></BODY></HTML> Wed, 20 Jan 2016 23:08:22 GMT amit_kapps 2016-01-20T23:08:22Z Slow database responses on requests for /alfresco/service/api/solr/metadata https://connect.hyland.com/t5/alfresco-archive/slow-database-responses-on-requests-for-alfresco-service-api/m-p/281072#M234202 Hello,We're experiencing some performance issues with Alfresco 4.0.e and a mysql 5.5 backend.Through a performance monitor I can see a lot of requests (not sure what is triggering these) getting hung up on DB with a response time of nearly 100-200s. In the extreme case its 3000secs.All these request Wed, 20 Jan 2016 23:08:22 GMT https://connect.hyland.com/t5/alfresco-archive/slow-database-responses-on-requests-for-alfresco-service-api/m-p/281072#M234202 amit_kapps 2016-01-20T23:08:22Z Re: Slow database responses on requests for /alfresco/service/api/solr/metadata https://connect.hyland.com/t5/alfresco-archive/slow-database-responses-on-requests-for-alfresco-service-api/m-p/281073#M234203 <HTML><HEAD></HEAD><BODY><SPAN>We found out 2 reasons behind the slowness</SPAN><BR /><SPAN>1. The disk i/o on the data volume of the mysql database was much slower compared to other systems. We used sysbench random i/o test to validate that.</SPAN><BR /><SPAN>2. The folder in question above has a few million documents which seem to cause this query to get slower. Nor can we load the folder contents page from share to display anything (results in a timeout related error). We're planning to restructure this folder to have further sub folders so as to limit docs in each folder. There are indications that this would help, but does not sound right to be doing something like this. We may not really have this control for other similar cases that might come up in the future. I'm wondering if this would still be a limitation in newer versions of Alfresco say 5.x.</SPAN></BODY></HTML> Tue, 16 Feb 2016 06:51:21 GMT https://connect.hyland.com/t5/alfresco-archive/slow-database-responses-on-requests-for-alfresco-service-api/m-p/281073#M234203 amit_kapps 2016-02-16T06:51:21Z