AnsweredAssumed Answered

Advanced search too  slow on specific queries

Question asked by enr79 on Jul 16, 2010
Dear all,

I am trying from today to solve a tricky question and I need some help to understand why when I performed a search in the advance search on a custom field, sometimes the response time is too low (I have to wait about 1 minute and a half).
We finished to upload about 40G of documents in Alfresco. We have a custom advanced search on PDFs metadata which were modified with the bulk import extension.

The first problem I met was related to the java heap space, setting different JAVA_OPTS solved my problems.

The second problem was the timeout connection with mysql but I solved this issue too.

I am now trying to understand why sometimes in some search I performed the system appears to be too slow in the response. I activated the debug mode of mysql and in the case of slow responses the mysql service generates a log file of 6 mega but, in the case of fast responses, it generates a log file of 25-30K.
What I am asking for is if I am right on thinking that the bottleneck is the mysql server; I really don’t know if with Alfresco there are other issues involved (like indexes in the alf_data directory) but looking at the queries performed in the log files, in the case of slow responses, I see a lot of big select statements.
So I am quite sure that the problem is on the search and in the queries done by the system but I should need to know if there are others issues I am missing.
If I am right, and there are these problems with mysql, what kind of solutions can be tried? I mean indexes on database, check of the query generated starting from the advanced search mask, mysql tuning and so on.

Thank you all,