Hi Nayan,
We are evaluating the mifos-x for performance.
The mysql deadlock logs are here which I got from show engine innodb status;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-03-14 10:59:41 7ffcdf10f700
*** (1) TRANSACTION:
TRANSACTION 27230, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 14 lock struct(s), heap size 30248, 6 row lock(s), undo log
entries 1002
MySQL thread id 15, OS thread handle 0x7ffcdefca700, query id 17014
localhost 127.0.0.1 root updating
update m_product_loan set accounting_type=1, close_date='2015-04-24',
description='car loan', external_id=null, fund_id=1,
include_in_borrower_cycle=0, max_disbursals=null,
allow_multiple_disbursals=0, max_outstanding_loan_balance=null,
max_nominal_interest_rate_per_period=null, max_number_of_repayments=null,
max_principal_amount=null, min_nominal_interest_rate_per_period=null,
min_number_of_repayments=null, min_principal_amount=null,
amortization_method_enum=1, annual_nominal_interest_rate=120.000000,
currency_code='USD', currency_digits=2, currency_multiplesof=0,
grace_interest_free_periods=null, grace_on_interest_periods=null,
grace_on_principal_periods=null, arrearstolerance_amount=null,
interest_calculated_in_period_enum=1, interest_method_enum=0,
interest_period_frequency_enum=2,
nominal_interest_rate_per_period=10.000000, number_of_repayments=10,
principal_amount=100000.000000, repay_every=1, repaymen
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 523 n bits 72 index `PRIMARY` of table
`mifostenant-default`.`m_product_loan` trx id 27230 lock_mode X locks rec
but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 40; compact format; info
bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000686b; asc hk;;
2: len 7; hex 090000074201f1; asc B ;;
3: len 4; hex 6361726c; asc carl;;
4: len 3; hex 555344; asc USD;;
5: len 2; hex 8002; asc ;;
6: len 2; hex 8000; asc ;;
7: len 9; hex 8000000186a0000000; asc ;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: len 8; hex 636172206c6f616e; asc car loan;;
12: len 8; hex 636172206c6f616e; asc car loan;;
13: len 8; hex 8000000000000001; asc ;;
14: len 9; hex 80000000000a000000; asc ;;
15: SQL NULL;
16: SQL NULL;
17: len 2; hex 8002; asc ;;
18: len 9; hex 800000000078000000; asc x ;;
19: len 2; hex 8000; asc ;;
20: len 2; hex 8001; asc ;;
21: len 2; hex 8001; asc ;;
22: len 2; hex 8000; asc ;;
23: len 2; hex 800a; asc ;;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 2; hex 8001; asc ;;
30: len 2; hex 8001; asc ;;
31: len 8; hex 8000000000000007; asc ;;
32: SQL NULL;
33: len 1; hex 80; asc ;;
34: len 1; hex 80; asc ;;
35: len 3; hex 8fbc61; asc a;;
36: len 3; hex 8fbe98; asc ;;
37: len 1; hex 80; asc ;;
38: SQL NULL;
39: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 27217, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
14 lock struct(s), heap size 30248, 6 row lock(s), undo log entries 1002
MySQL thread id 10, OS thread handle 0x7ffcdf10f700, query id 20487
localhost 127.0.0.1 root updating
update m_product_loan set accounting_type=1, close_date='2015-04-24',
description='car loan', external_id=null, fund_id=1,
include_in_borrower_cycle=0, max_disbursals=null,
allow_multiple_disbursals=0, max_outstanding_loan_balance=null,
max_nominal_interest_rate_per_period=null, max_number_of_repayments=null,
max_principal_amount=null, min_nominal_interest_rate_per_period=null,
min_number_of_repayments=null, min_principal_amount=null,
amortization_method_enum=1, annual_nominal_interest_rate=120.000000,
currency_code='USD', currency_digits=2, currency_multiplesof=0,
grace_interest_free_periods=null, grace_on_interest_periods=null,
grace_on_principal_periods=null, arrearstolerance_amount=null,
interest_calculated_in_period_enum=1, interest_method_enum=0,
interest_period_frequency_enum=2,
nominal_interest_rate_per_period=10.000000, number_of_repayments=10,
principal_amount=100000.000000, repay_every=1, repaymen
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 523 n bits 72 index `PRIMARY` of table
`mifostenant-default`.`m_product_loan` trx id 27217 lock mode S locks rec
but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 40; compact format; info
bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000686b; asc hk;;
2: len 7; hex 090000074201f1; asc B ;;
3: len 4; hex 6361726c; asc carl;;
4: len 3; hex 555344; asc USD;;
5: len 2; hex 8002; asc ;;
6: len 2; hex 8000; asc ;;
7: len 9; hex 8000000186a0000000; asc ;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: len 8; hex 636172206c6f616e; asc car loan;;
12: len 8; hex 636172206c6f616e; asc car loan;;
13: len 8; hex 8000000000000001; asc ;;
14: len 9; hex 80000000000a000000; asc ;;
15: SQL NULL;
16: SQL NULL;
17: len 2; hex 8002; asc ;;
18: len 9; hex 800000000078000000; asc x ;;
19: len 2; hex 8000; asc ;;
20: len 2; hex 8001; asc ;;
21: len 2; hex 8001; asc ;;
22: len 2; hex 8000; asc ;;
23: len 2; hex 800a; asc ;;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 2; hex 8001; asc ;;
30: len 2; hex 8001; asc ;;
31: len 8; hex 8000000000000007; asc ;;
32: SQL NULL;
33: len 1; hex 80; asc ;;
34: len 1; hex 80; asc ;;
35: len 3; hex 8fbc61; asc a;;
36: len 3; hex 8fbe98; asc ;;
37: len 1; hex 80; asc ;;
38: SQL NULL;
39: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 523 n bits 72 index `PRIMARY` of table
`mifostenant-default`.`m_product_loan` trx id 27217 lock_mode X locks rec
but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 40; compact format; info
bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000686b; asc hk;;
2: len 7; hex 090000074201f1; asc B ;;
3: len 4; hex 6361726c; asc carl;;
4: len 3; hex 555344; asc USD;;
5: len 2; hex 8002; asc ;;
6: len 2; hex 8000; asc ;;
7: len 9; hex 8000000186a0000000; asc ;;
8: SQL NULL;
9: SQL NULL;
10: SQL NULL;
11: len 8; hex 636172206c6f616e; asc car loan;;
12: len 8; hex 636172206c6f616e; asc car loan;;
13: len 8; hex 8000000000000001; asc ;;
14: len 9; hex 80000000000a000000; asc ;;
15: SQL NULL;
16: SQL NULL;
17: len 2; hex 8002; asc ;;
18: len 9; hex 800000000078000000; asc x ;;
19: len 2; hex 8000; asc ;;
20: len 2; hex 8001; asc ;;
21: len 2; hex 8001; asc ;;
22: len 2; hex 8000; asc ;;
23: len 2; hex 800a; asc ;;
24: SQL NULL;
25: SQL NULL;
26: SQL NULL;
27: SQL NULL;
28: SQL NULL;
29: len 2; hex 8001; asc ;;
30: len 2; hex 8001; asc ;;
31: len 8; hex 8000000000000007; asc ;;
32: SQL NULL;
33: len 1; hex 80; asc ;;
34: len 1; hex 80; asc ;;
35: len 3; hex 8fbc61; asc a;;
36: len 3; hex 8fbe98; asc ;;
37: len 1; hex 80; asc ;;
38: SQL NULL;
39: SQL NULL;
*** WE ROLL BACK TRANSACTION (2)
Pl. have a look and let me know if any settings for mysql can resolve it.
Thanks
Neeraj
On Thursday, March 13, 2014 6:22:22 PM UTC+5:30, Nayan Ambali wrote:
>
> Hi Neeraj,
>
> We have logged similar issue in JIRA
> https://mifosforge.jira.com/browse/MIFOSX-968.
>
> But not at this volume, this was for some where around 1000+ concurrent
> users and for collection sheet and each collection sheet was equivalent to
> 40 repayments so concurrency was 40k repayments.
>
> Try looking at mysql process list during the load testing, may be useful.
>
> And also can you please give more context on your work (objectives), I am
> very much interested to work with you if possible.
>
> Thanks
> Nayan Ambali
>
>
>
>
> Thanks and Regards,
> Nayan Ambali
> +91 9591996042
> skype: nayangambali
>
>
> On Thu, Mar 13, 2014 at 5:45 PM, sparkmydream <[email protected]<javascript:>
> > wrote:
>
>> Hi Nayan,
>>
>> Thanks for the reply.
>>
>> max_connection of mysql is currently 151 set.
>>
>> Event with concurrent 2 users, 1 transaction get failed with mysql
>> deadlock.
>>
>> In the jmeter response I can see the json response of success cases and
>> exception log with error 500 for failure cases.
>> I am posting on mifosng-provider/api/v1/loans with payload like
>>
>> {
>> "dateFormat": "dd MMMM yyyy",
>> "locale": "en_GB",
>> "clientId": 1,
>> "productId": 1,
>> "principal": "1000.00",
>> "loanTermFrequency": 1000,
>> "loanTermFrequencyType": 2,
>> "loanType": "individual",
>> "numberOfRepayments": 10,
>> "repaymentEvery": 1,
>> "repaymentFrequencyType": 2,
>> "interestRatePerPeriod": 9,
>> "amortizationType": 1,
>> "interestType": 0,
>> "interestCalculationPeriodType": 1,
>> "transactionProcessingStrategyId": 1,
>> "expectedDisbursementDate": "07 March 2014",
>> "submittedOnDate": "05 March 2014"
>> }
>>
>> When I create concurrent client with the API's I do not get any dead lock
>> issues. With transactions I am getting deadlock.
>>
>> Thanks
>> Neeraj
>>
>> On Thursday, March 13, 2014 5:04:52 PM UTC+5:30, Nayan Ambali wrote:
>>
>>> Hi Neeraj,
>>>
>>> That is great to know that you trying for load testing.
>>> One quick things look at is max_connection value of mysql.
>>>
>>> Also refer https://mifosforge.jira.com/browse/MIFOSX-949, may help you
>>> understand issue better.
>>>
>>> few more question, when you hit demo server how did you know that there
>>> were no such issues? do you have access to server logs :) or based on
>>> Jmeter response? And which request did you hit (x: login, viewclient etc)
>>>
>>> Thanks
>>> Nayan Ambali
>>>
>>>
>>>
>>>
>>>
>>>
>>> Thanks and Regards,
>>> Nayan Ambali
>>> +91 9591996042
>>> skype: nayangambali
>>>
>>>
>>> On Thu, Mar 13, 2014 at 4:46 PM, Neeraj kushwaha <[email protected]>wrote:
>>>
>>>> Hi,
>>>>
>>>> I have deployed mifos-x on my centos local machine. Using jmeter when i
>>>> fire concurrent users (10 users), I am seeing failure with logs as shown
>>>> below.
>>>>
>>>> If i hit the https://demo.openmf.org server with jmeter I do not get
>>>> that deadlock issue.
>>>>
>>>> Is I am my missing some setting for tomcat or mysql.
>>>>
>>>> Pl. help me resolve it.
>>>>
>>>> Thanks
>>>> Neeraj
>>>>
>>>> org.springframework.dao.CannotAcquireLockException: Deadlock found when
>>>> trying to get lock; try restarting transaction; SQL [n/a]; nested
>>>> exception is org.hibernate.exception.LockAcquisitionException: Deadlock
>>>> found when trying to get lock; try restarting transaction
>>>>
>>>> org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:639)
>>>>
>>>> org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
>>>>
>>>> org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
>>>>
>>>> org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
>>>>
>>>> org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
>>>>
>>>> org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:387)
>>>>
>>>> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
>>>>
>>>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
>>>>
>>>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
>>>> com.sun.proxy.$Proxy66.processAndLogCommand(Unknown Source)
>>>>
>>>> org.mifosplatform.commands.service.PortfolioCommandSourceWritePlatformServiceImpl.logCommandSource(PortfolioCommandSourceWritePlatformServiceImpl.java:72)
>>>> sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
>>>>
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>> java.lang.reflect.Method.invoke(Method.java:606)
>>>>
>>>> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
>>>>
>>>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
>>>> com.sun.proxy.$Proxy67.logCommandSource(Unknown Source)
>>>>
>>>> org.mifosplatform.portfolio.loanaccount.api.LoansApiResource.calculateLoanScheduleOrSubmitLoanApplication(LoansApiResource.java:493)
>>>> sun.reflect.GeneratedMethodAccessor167.invoke(Unknown Source)
>>>>
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>> java.lang.reflect.Method.invoke(Method.java:606)
>>>>
>>>> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
>>>>
>>>> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
>>>>
>>>> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
>>>>
>>>> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
>>>>
>>>> com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
>>>>
>>>> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
>>>>
>>>> com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
>>>>
>>>> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
>>>>
>>>> com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
>>>>
>>>> com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
>>>>
>>>> com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
>>>>
>>>> com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
>>>>
>>>> com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
>>>>
>>>> com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
>>>> javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>>> org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
>>>>
>>>> org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
>>>>
>>>> org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>>>
>>>> org.mifosplatform.infrastructure.security.filter.TenantAwareBasicAuthenticationFilter.doFilter(TenantAwareBasicAuthenticationFilter.java:118)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.access.channel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:144)
>>>>
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
>>>>
>>>> org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
>>>>
>>>> org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
>>>>
>>>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
>>>>
>>>>
>>>> ------------------------------------------------------------
>>>> ------------------
>>>> Learn Graph Databases - Download FREE O'Reilly Book
>>>> "Graph Databases" is the definitive new guide to graph databases and
>>>> their
>>>> applications. Written by three acclaimed leaders in the field,
>>>> this first edition is now available. Download your free book today!
>>>> http://p.sf.net/sfu/13534_NeoTech
>>>> _______________________________________________
>>>> Mifos-users mailing list
>>>> [email protected]
>>>> https://lists.sourceforge.net/lists/listinfo/mifos-users
>>>>
>>>>
>>>
>>
>> ------------------------------------------------------------------------------
>> Learn Graph Databases - Download FREE O'Reilly Book
>> "Graph Databases" is the definitive new guide to graph databases and their
>> applications. Written by three acclaimed leaders in the field,
>> this first edition is now available. Download your free book today!
>> http://p.sf.net/sfu/13534_NeoTech
>> _______________________________________________
>> Mifos-users mailing list
>> [email protected] <javascript:>
>> https://lists.sourceforge.net/lists/listinfo/mifos-users
>>
>>
>------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
_______________________________________________
Mifos-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-users
