Re: Postgresql Performance Issue (deadlock) with Document Fetch

[Chalitha Perera <cp...@zaizi.com>]

Hi Karl,

We have ran the load tests on Postgres as you suggested and it was
completed successfully. But with Alfresco connector the job gets stuck
every-time.
I have opened a Jira[1] and attached jstack jump there. It was taken at the
job stuck stage before giving the deadlock message. Please have a look when
you have time.

Thanks,
Chalitha

[1] https://issues.apache.org/jira/browse/CONNECTORS-1367

On Thu, Dec 1, 2016 at 4:40 PM, Karl Wright <da...@gmail.com> wrote:

> Hi Chalitha,
>
> I have honestly never heard of anything so bizarre before.
>
> We have performance tests for some connectors that run 100,000 documents or
> more.  If what you are encountering is postgresql related, you could try to
> run them against your postgresql instance and see what happens.  That would
> at least point in the right direction.  But this involves a bit of work.
>
> To do this:
>
> - start with the source distribution
> - add the postgresql driver in the right spot
> - Modify the superuser name/password in the class
> framework\core\src\test\java\org\apache\manifoldcf\core\
> tests\BasePostgresql.java
> - Add whatever is needed to point to the right postgresql instance in the
> same package, by overriding writeProperties() in the same class
> - Run "ant load-pg" to kick it off
>
> You can also try the diagnostic route: when there's a deadlock that is
> unexpected, there's often a stack trace too in the manifoldcf.log.
>
> Finally, it's possible that what's happening is that you're hitting the
> maximum transaction count for the postgresql database.  This used to be way
> larger than 34000 -- it used to be something like 2000000000.  But perhaps
> there is a postgresql bug where that's no longer the case.  You might be
> able to test this theory by running until the database halts and then
> shutting down manifoldcf and run "VACUUM FULL" on the database.  That will
> (I think) reset everything.  If all is good after that, you know the
> problem has to be in the postgresql world.  Probably though it is something
> wrong in the configuration.  I'm very interested to learn what is happening
> here.  Please let me know.  FWIW, I run the windows version of 9.3 here and
> see no issues like this on my crawls.
>
> There's one last thing you could try, if everything seems to work with your
> postgresql instance and it seems like it's only the alfresco connector that
> messes stuff up.  When it freezes up, before it dies, get a thread dump of
> the manifoldcf agents process.  Send that to me and I can tell what's
> happening.
>
> Thanks,
> Karl
>
>
> On Thu, Dec 1, 2016 at 4:25 AM, Chalitha Perera <cp...@zaizi.com> wrote:
>
> > Hi All,
> >
> > We are trying Alfresco enterprise 5.0.1 with ManifoldCF 2.5 configured
> with
> > postgresql 9.3. For postgres we use the configs given in [1]. From
> manifold
> > side
> > maxhandles = 105
> > crawler threads = 50
> >
> > In alfresco connector max connections = 10
> >
> > The problem is that when the documents get fetched it gets stuck when a
> > certain number of documents are fetched. The job always get stuck when
> > 34227 documents are fetched and after some long wait it gives the
> database
> > error.
> >
> >
> >
> >
> > *Exception tossed: ERROR: deadlock detected  Detail: Process 5214 waits
> for
> > ShareLock on transaction 155292; blocked by process 18781.Process 18781
> > waits for ShareLock on transaction 155295; blocked by process 5214.*
> >
> > When I restarted the manifold and postgres again only very few documents
> > gets fetched.
> >
> > Any tips on resolving this deadlock issue is greatly appreciated.
> >
> > [1]
> > https://manifoldcf.apache.org/release/release-2.5/en_US/
> > performance-tuning.html
> >
> >
> > Thanks,
> > Chalitha
> >
> > --
> >
> > ------------------------------
> > This message should be regarded as confidential. If you have received
> this
> > email in error please notify the sender and destroy it immediately.
> > Statements of intent shall only become binding when confirmed in hard
> copy
> > by an authorised signatory.
> >
> > Zaizi Ltd is registered in England and Wales with the registration number
> > 6440931. The Registered Office is Brook House, 229 Shepherds Bush Road,
> > London W6 7AN.
> >
>

-- 

------------------------------
This message should be regarded as confidential. If you have received this 
email in error please notify the sender and destroy it immediately. 
Statements of intent shall only become binding when confirmed in hard copy 
by an authorised signatory.

Zaizi Ltd is registered in England and Wales with the registration number 
6440931. The Registered Office is Brook House, 229 Shepherds Bush Road, 
London W6 7AN.