Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0030503
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. Performancemajoralways2015-08-05 11:192015-08-25 15:48
ReporterVictorVillarView Statuspublic 
Assigned Toaferraz 
PriorityurgentResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision00b72bf1ea14
ProjectionnoneETAnoneTarget Version3.0PR15Q4
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product Version3.0PR15Q2.2SCM revision 
Review Assigned Tovmromanos
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0030503: Performance problems in the Acct Server process

DescriptionPerformance problems in the Acct Server process with a client with huge amount of data. For instance: more than 500000 registers in the fin_payment table where 250000 register were posted = 'N, 25000 registers in the c_periodcontrol
Steps To Reproducewe have detected that the following query takes 80 seconds to be executed.

select 1 as id from dual where exists ( select 1 from ( SELECT a.Paymentdate as dateacct from FIN_Payment a, c_periodcontrol, c_period WHERE a.ad_client_id = '9718AC9640AE46A995EA3906D8577B57' AND a.ad_org_id in ('78ECECB031B040D8B92F2A386122EB8A', 'A7C52F17532B421A85510826CC533A56', 'A8428F67B4FB4DE39858ED7961664642', '12D699E251A14BACA4AD467C0C7BF740', 'C4E6CDEDB7E846468E49AD857074E05F', 'CA2C1E01F3F8429DB53A4B38B0359584', '9B37FD5895F6423AA7057CC98BEDF8F2', '6B1E650FE6B14B6D8B1077321ACD2E33', '1E92FDE556754B20A5006347DCD774C8', 'D2281E3729FC46E6823447B86F7A93CD', '2E405F05605F4DFE962B645DAA9B1BED', '789F6A29473C40A89286644E541FE27F', '0FCC1AF0592645C787D440672831DD3F', 'BA947E63D9A14A6BBC5FEC0390211F14', 'AFBE100B46334B5689E0BF3DE184B347', 'E009ABF05825407597475082CC94F730', '8EB0E39BAB82401FA2B588C3C7E72E21', '8718431D9D354531BD8738CC6D68DDB3', 'FE508C08DE544254B2F05FC7209F3822', 'AFEC47C91EBD476AB15E57BF1F6CBC13', 'C84FC775D2314020966726D0D6F67E62', 'C3D8332AAFA541C681BAD55F88578201', 'B8EBDAC4EAA3443687CE90017308A7B0', '7C1EA8EDB5604C75940C7B7BB01924F7', '0', 'FE6382F18D3B43F18FC1AB2715A4D581', 'C34408E3763C40BCAD126A1F68254E90', '3F567EF8CABB4D999331B911585D1778', 'E26A832E0FD940F09633DB7FD061C97E', '23ADA885129149DC972870124B59A72E', '782A82AED7984D5A803C0633B7A5B3D2', '35EE4B7E0DB244CA9795E288C5665DCD', '1CC2678CAE0D4D21B87C9533E0BADD2C', 'FEA5E2E616684B86BD35ED5DDF554052', '841DB3F9C1B84DE99C75534C08B21BDE', '1C5F9E2C8AAF4476A59502F3163968E9', '7657FC7FF6B647689B1034847130ADAB', '967469128B5A4F14A0F8F7DEEA50F6BF', 'A4524702F9A9470E84CD478A7F1FA87D', '768E33F2A4544D1E8F1B79CB737F5D73', '16803346D1B441E6BDCC95C5A6262165', 'E0A04220DF9C4CC7974CAA1AB2B4E33B', 'C6600C3D9DAD4A4086B5E7DEED8F43CC', '9425B0E5552A446BA7FA51CEEF790220', 'FC5438951DB04D4B861C546E33D9873A', '53B00F0067B84C15BF25FBA1C57407FB', '51D2661E032D4BB7B1DB52188A594791', '3086199143774727BCF56CBF0FCA4548', '51F9ECC16D1C46B89A17FABF017EC48E', '9795575573F141078C085C1931938718', '202294ACE8504545BF17A1661E3B10E8', '8A5FB32697D04C66921046E5DA04144F', 'CD57526F95934A0B9DAD8531A3E11781', '34D18289081A453B99D090059264EBEF', '48830FF3B878455498AAD7DCB3DB821D', 'D60C026E4C704BDBA1ED6A47868554C0', '93362394887045C39E0804D13654EBB3', '140461D18F474F05852320F228DF1765', '67BF6D38B523421483E3E68BB61B2199', 'F0487725FAB84F4AB61F4E4750FE481E', '09D0E20020B048EC81E730F641896FC5', 'D5ED79BC46AA4C9494400EAE07A73C25', '049154C13FD044178B3DCA9854DA7097', '4BF951FCB1E347EAA021A5B1E8597EC4', 'C34FC9B26126418693723213FB9F110F') AND a.posted = 'N' AND a.processed = 'Y' AND (a.processing = 'N' OR a.processing IS NULL) and c_periodcontrol.c_period_id = c_period.c_period_id AND c_periodcontrol.docbasetype = 'ARR' AND c_periodcontrol.periodstatus = 'O' AND ad_org_getperiodcontrolallow(a.ad_org_id) = c_periodcontrol.ad_org_id AND a.Paymentdate >= c_period.StartDate AND a.Paymentdate < c_period.EndDate + 1 ) b where 1=1 );


We have created an index for the posted column, and the query takes 3 seconds:

CREATE INDEX fin_payment_posted
  ON fin_payment
  USING btree
  (posted COLLATE pg_catalog."default");
Proposed SolutionIt would be interesting to think to create an index for posted column for every table that is taken into account in the Acct server
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0079210)
VictorVillar (developer)
2015-08-06 08:36

 Besides, in Support we have the client instance where we reproduce the problem. Please contact with Egoitz or Víctor.
(0079637)
vmromanos (manager)
2015-08-24 15:05

 The proposed solution of adding an index on the Posted column might have collateral effects, like increasing the physical size in the hard disk of the affected tables, slower inserts/updates...

However, we think it makes sense to add them, as the performance increase in the posting process is very significant.

The fix will include an index for all the tables with a Posted column. This includes unused tables belonging to the old payment flow or tables where the number of records is usually not too high. The reason to do that is to be consistency in all the application, so all the Posted columns (regardless the table) must have an index over it.
(0079638)
hgbot (developer)
2015-08-24 15:07

 Repository: erp/devel/pi
Changeset: 00b72bf1ea141026307018796429830a8cf67a1c
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Fri Aug 21 17:12:24 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/00b72bf1ea141026307018796429830a8cf67a1c [^]

Fixes issue 30503: Performance problems in Acct Server process

Added indexes in tables with Posted column

---
M src-db/database/model/tables/A_AMORTIZATION.xml
M src-db/database/model/tables/C_BANKSTATEMENT.xml
M src-db/database/model/tables/C_CASH.xml
M src-db/database/model/tables/C_DP_MANAGEMENT.xml
M src-db/database/model/tables/C_INVOICE.xml
M src-db/database/model/tables/C_ORDER.xml
M src-db/database/model/tables/C_PROJECTISSUE.xml
M src-db/database/model/tables/C_SETTLEMENT.xml
M src-db/database/model/tables/FIN_BANKSTATEMENT.xml
M src-db/database/model/tables/FIN_DOUBTFUL_DEBT.xml
M src-db/database/model/tables/FIN_PAYMENT.xml
M src-db/database/model/tables/FIN_RECONCILIATION.xml
M src-db/database/model/tables/GL_JOURNAL.xml
M src-db/database/model/tables/M_COSTADJUSTMENT.xml
M src-db/database/model/tables/M_INOUT.xml
M src-db/database/model/tables/M_INTERNAL_CONSUMPTION.xml
M src-db/database/model/tables/M_INVENTORY.xml
M src-db/database/model/tables/M_LANDEDCOST.xml
M src-db/database/model/tables/M_LC_COST.xml
M src-db/database/model/tables/M_MATCHINV.xml
M src-db/database/model/tables/M_MATCHPO.xml
M src-db/database/model/tables/M_MOVEMENT.xml
M src-db/database/model/tables/M_PRODUCTION.xml
M src-db/database/model/tables/S_TIMEEXPENSE.xml
---
(0079639)
vmromanos (manager)
2015-08-24 15:09

 Test plan:

Verify tables which generate accounting have an index over the Posted column.
Affected tables can be gotten using this SQL (Postgres only):

select table_name
from information_schema.columns
where column_name ilike 'posted'
and is_updatable = 'YES'
order by table_name;
(0079640)
vmromanos (manager)
2015-08-24 15:09

 Code review OK
(0079724)
hudsonbot (developer)
2015-08-25 15:48

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/e847ea2dfcc5 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2015-08-05 11:19 VictorVillar New Issue
2015-08-05 11:19 VictorVillar Assigned To => platform
2015-08-05 11:19 VictorVillar Modules => Core
2015-08-05 11:19 VictorVillar Resolution time => 1441058400
2015-08-05 11:19 VictorVillar Triggers an Emergency Pack => No
2015-08-05 12:01 VictorVillar Issue Monitored: networkb
2015-08-05 12:33 alostale Assigned To platform => Triage Finance
2015-08-05 12:33 alostale Tag Attached: Performance
2015-08-06 08:36 VictorVillar Note Added: 0079210
2015-08-20 11:56 aferraz Assigned To Triage Finance => aferraz
2015-08-20 11:56 aferraz Status new => scheduled
2015-08-24 15:05 vmromanos Note Added: 0079637
2015-08-24 15:07 hgbot Checkin
2015-08-24 15:07 hgbot Note Added: 0079638
2015-08-24 15:07 hgbot Status scheduled => resolved
2015-08-24 15:07 hgbot Resolution open => fixed
2015-08-24 15:07 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/00b72bf1ea141026307018796429830a8cf67a1c [^]
2015-08-24 15:09 vmromanos Note Added: 0079639
2015-08-24 15:09 vmromanos Review Assigned To => vmromanos
2015-08-24 15:09 vmromanos Note Added: 0079640
2015-08-24 15:09 vmromanos Status resolved => closed
2015-08-25 15:48 hudsonbot Checkin
2015-08-25 15:48 hudsonbot Note Added: 0079724

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker