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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0020422
TypeCategorySeverityReproducibilityDate SubmittedLast Update
design defect[Openbravo ERP] A. Platformmajorhave not tried2012-05-04 14:522013-05-02 16:31
ReporteralostaleView Statuspublic 
Assigned ToAugustoMauch 
PriorityhighResolutionfixedFixed in Version3.0MP23
StatusclosedFix in branchFixed in SCM revisionc0c3d4e9feab
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toalostale
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0020422: Slow audit

DescriptionIn an instance with ~4M rows in ad_audit_trail table, inserting new rows in audited tables takes much more time than if they were not audited.

For example inserting a row in c_order takes around 200ms whereas not auditing it ~20ms.

The problem is caused by the primary key and indexes in ad_audit_trail, this makes each insertion on it a little bit slower.
Steps To ReproduceDo any insertion with and without audit enabled and verify it is faster without it.
Proposed Solution-Create a temporary table identical to ad_audit_trail but without primary nor indexes.
-Use this table to do the audit insertions.
-Create a background process to move data to temporary to actual table
-Modify audit queries to get info from both sides
TagsPerformance
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to design defect 0020421 closedalostale Review needed indexes 

-  Notes
(0052363)
AugustoMauch (manager)
2012-09-24 17:43

Effort: 10
Impact: high
Plan: short
(0057105)
egoitz (developer)
2013-03-06 17:01

A possible solution

1. Add AD_Table.AuditInserts as a Boolean
2. When AD_Table.IsFullyAudited is set to True, then AD_Table.AuditInserts should appear on the screen (with label of "Audit Insert Statements"),
defaulted to True (which matches "Fully Audited" expectation).
3. The user may set set AD_Table.AuditInserts to False to avoid adding the unnecessary insert trigger to the table in question
4. When "Update Audit Trail Infrastructure" is run, the added Trigger must NOT include the Insert operation if AD_Table.AuditInserts is False


For example, the normal audit trigger on the m_product table looks like:

CREATE TRIGGER au_m_product_trg
BEFORE INSERT OR UPDATE OR DELETE
ON m_product
FOR EACH ROW
EXECUTE PROCEDURE au_m_product_trg();

With this feature, when AD_Table.AuditInserts is False, the trigger should be as follows (same as before, except the INSERT operation is omitted):

CREATE TRIGGER au_m_product_trg
BEFORE UPDATE OR DELETE
ON m_product
FOR EACH ROW
EXECUTE PROCEDURE au_m_product_trg();

Note that there is no compelling reason to touch the logic which creates the trigger PROCEDURE itself--it doesn't matter that the Insert logic is still in
the procedure, since it will never be executed.
(0057118)
alostale (manager)
2013-03-07 07:54
edited on: 2013-03-07 11:29

The solution explained by egoitz is the one we are now thinking to implement.

The only problem with this solution is that it would not audit where the record was created from (from which window or process). To solve this, when inserting new records in audited tables, we could save a single row in audit table with the id of the new record + where it was created.

(0058120)
hgbot (developer)
2013-04-23 12:23

Repository: erp/devel/pi
Changeset: 0175279b4dbea5abe7e308d10a04fd86d649d625
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 10:54:40 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0175279b4dbea5abe7e308d10a04fd86d649d625 [^]

Related to issue 20422: Adds the new Insert Audits fields

This changeset contains all the changes related to the addition of the new Insert Audits field:
- Creation of the new column in the database
- Changes in the Application Dictionary (AD_COLUMN, AD_FIELD; AD_ELEMENT)
- It is a mandatory column, so it has been created in all AD_TABLES with its default value ('Y')

---
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.application/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.kernel/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.myob/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.datasource/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.google/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.openid/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.userinterface.selector/src-db/database/sourcedata/AD_TABLE.xml
M src-db/database/model/tables/AD_TABLE.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_ELEMENT.xml
M src-db/database/sourcedata/AD_FIELD.xml
M src-db/database/sourcedata/AD_TABLE.xml
---
(0058121)
hgbot (developer)
2013-04-23 12:23

Repository: erp/devel/pi
Changeset: d8c6a9c2b6acff52bc7d314b526d7d0d7bbe785c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:03:37 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d8c6a9c2b6acff52bc7d314b526d7d0d7bbe785c [^]

Related to issue 20422: Updates the ad_create_audit_triggers SQL function

The ad_create_audit_triggers SQL function has been updated to use the new AD_TABLE.ISAUDITINSERTS column,

The behaviour of the triggers when a record is updated or deleted has not been changed. When a record is inserted, if the ISAUDITINSERTS column of the audit column is'Y' (default value), the behaviour will be the same as the previous one. If ISAUDITINSERTS is 'N', then only the key column of the audited table will be inserted in the audit table.

---
M src-db/database/model/postscript-Oracle.sql
M src-db/database/model/postscript-PostgreSql.sql
---
(0058122)
hgbot (developer)
2013-04-23 12:23

Repository: erp/devel/pi
Changeset: c33d0871c1d8c6ccef4075d90f9c99d79950fe6c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:08:42 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/c33d0871c1d8c6ccef4075d90f9c99d79950fe6c [^]

Related to issue 20422: Updates AD_TABLE_MOD_TRG trigger

The AD_TABLE_MOD_TRG has been updated, so that the Updated column of the table is not changed if the only modified column is ISFULLYAUDITED or ISAUDITINSERTS.

---
M src-db/database/model/triggers/AD_TABLE_MOD_TRG.xml
---
(0058123)
hgbot (developer)
2013-04-23 12:23

Repository: erp/devel/pi
Changeset: c0c3d4e9feabb621a302624bdb27209da34a3770
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Apr 23 11:12:29 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/c0c3d4e9feabb621a302624bdb27209da34a3770 [^]

Fixes issue 20422: Updates the AuditTrailPopup to consider show ID column

The AuditTrailPopup has been updated to show the ID column of the audit table. This is needed because now if an audited table is flagged as ISAUDITINSERTS='N', only the ID column of the table will be inserted in the audit table. Originally the popup did not show the records of the audit table belonging to its id column.

---
M src/org/openbravo/erpCommon/businessUtility/AuditTrailPopup.java
---
(0058124)
AugustoMauch (manager)
2013-04-23 12:35

Test plan:
As System Admin with an activated instance:
- Open Tables and Columns
- Select the C_UOM table.
- Open in form view, set the flag Fully Audited to true. Check that the flag Audit Inserts is shown and defaulted to 'Y'
- Run Update Audit Trail Infrastucture
- Open the Unit of Measure window
- Create a new record and save it. Change it and save again. The Audit Trail toolbar button should be shown (it is not shown until the record has been updated).
- Check that there is a record for each column of the C_UOM table in the INSERT section of the Audit Trail.
- Open again Tables and Columns, set the flag Audit Inserts to 'N', and run Update Audit Trail Infrastructure.
- Open Unit of Measure, create a new record and updated. Check that in the Audit Trail now the only column audited in the INSERT is the table key column
(0058125)
AugustoMauch (manager)
2013-04-23 12:35

Test it in Postgres and Oracle.
(0058189)
hudsonbot (developer)
2013-04-25 01:50

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/9fa1ecafd560 [^]

Maturity status: Test
(0058190)
hudsonbot (developer)
2013-04-25 01:50

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/9fa1ecafd560 [^]

Maturity status: Test
(0058191)
hudsonbot (developer)
2013-04-25 01:50

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/9fa1ecafd560 [^]

Maturity status: Test
(0058192)
hudsonbot (developer)
2013-04-25 01:50

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/9fa1ecafd560 [^]

Maturity status: Test
(0058253)
hgbot (developer)
2013-04-25 14:08

Repository: erp/devel/pi
Changeset: 478bd1f3ab24a447ccff0ae2d99329252d485f7c
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:03:29 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/478bd1f3ab24a447ccff0ae2d99329252d485f7c [^]

Related to issue 20422: Audit fields are placed in a new line

---
M src-db/database/sourcedata/AD_FIELD.xml
---
(0058254)
hgbot (developer)
2013-04-25 14:08

Repository: erp/devel/pi
Changeset: 334847ea1bcf6f0470a5678e497cafeaad2121b5
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:06:17 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/334847ea1bcf6f0470a5678e497cafeaad2121b5 [^]

Related to issue 20422: Updated callout to initialize isAuditInserts

The callout SL_TableAudit, which is called whenever the value of isFullyAudited is changed, has been updated to give a proper initial value to the isAuditInserts column:
- If at the moment there are no tables being audited with isAuditInserts='Y', it will be initialized to 'N'
- Otherwise, it will be initialized to 'Y'

---
M src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit.java
A src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit_data.xsql
---
(0058255)
hgbot (developer)
2013-04-25 14:08

Repository: erp/devel/pi
Changeset: 1cfc2ceb27c1be402f90a78e37c1ae6efc078f33
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 14:08:00 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/1cfc2ceb27c1be402f90a78e37c1ae6efc078f33 [^]

Related to issue 20422: isAuditInserts column should not be exported

The isAuditInserts column should not be exported, because it is an instance level configuration column

---
M modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.application/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.kernel/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.myob/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.datasource/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.google/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.service.integration.openid/src-db/database/sourcedata/AD_TABLE.xml
M modules/org.openbravo.userinterface.selector/src-db/database/sourcedata/AD_TABLE.xml
M src-db/database/sourcedata/AD_DATASET_COLUMN.xml
M src-db/database/sourcedata/AD_TABLE.xml
---
(0058260)
hgbot (developer)
2013-04-25 15:30

Repository: erp/devel/pi
Changeset: 0136c51b6c5f0783497d7084805748f256fdce0f
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu Apr 25 15:28:34 2013 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0136c51b6c5f0783497d7084805748f256fdce0f [^]

Related to issue 20422: xsql query replaced with DAL OBCriteria

---
M src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit.java
R src/org/openbravo/erpCommon/ad_callouts/SL_TableAudit_data.xsql
---
(0058271)
alostale (manager)
2013-04-26 09:56

Code reviewed and tested on pi@0136c51b6c5f

Tested on Oracle and PosgreSQL

Improvement in a process doing massive insertions is from 7min to 30s
(0058287)
hudsonbot (developer)
2013-04-26 23:54

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/1db7e66bd5c5 [^]

Maturity status: Test
(0058288)
hudsonbot (developer)
2013-04-26 23:54

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/1db7e66bd5c5 [^]

Maturity status: Test
(0058289)
hudsonbot (developer)
2013-04-26 23:54

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/1db7e66bd5c5 [^]

Maturity status: Test
(0058291)
hudsonbot (developer)
2013-04-26 23:54

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/1db7e66bd5c5 [^]

Maturity status: Test

- Issue History
Date Modified Username Field Change
2012-05-04 14:52 alostale New Issue
2012-05-04 14:52 alostale Assigned To => alostale
2012-05-04 14:52 alostale Modules => Core
2012-05-04 14:53 alostale Relationship added related to 0020421
2012-09-24 17:43 AugustoMauch Note Added: 0052363
2012-09-24 17:43 AugustoMauch Priority normal => high
2012-09-24 17:43 AugustoMauch Status new => scheduled
2013-03-06 13:22 egoitz Issue Monitored: egoitz
2013-03-06 16:56 egoitz Triggers an Emergency Pack => No
2013-03-06 16:56 egoitz Note Added: 0057104
2013-03-06 17:00 egoitz Note Deleted: 0057104
2013-03-06 17:01 egoitz Note Added: 0057105
2013-03-07 07:54 alostale Note Added: 0057118
2013-03-07 07:58 alostale Tag Attached: Performance
2013-03-07 11:29 alostale Note Edited: 0057118 View Revisions
2013-04-23 11:12 AugustoMauch Assigned To alostale => AugustoMauch
2013-04-23 11:12 AugustoMauch Issue Monitored: alostale
2013-04-23 11:12 AugustoMauch Review Assigned To => alostale
2013-04-23 12:23 hgbot Checkin
2013-04-23 12:23 hgbot Note Added: 0058120
2013-04-23 12:23 hgbot Checkin
2013-04-23 12:23 hgbot Note Added: 0058121
2013-04-23 12:23 hgbot Checkin
2013-04-23 12:23 hgbot Note Added: 0058122
2013-04-23 12:23 hgbot Checkin
2013-04-23 12:23 hgbot Note Added: 0058123
2013-04-23 12:23 hgbot Status scheduled => resolved
2013-04-23 12:23 hgbot Resolution open => fixed
2013-04-23 12:23 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c0c3d4e9feabb621a302624bdb27209da34a3770 [^]
2013-04-23 12:35 AugustoMauch Note Added: 0058124
2013-04-23 12:35 AugustoMauch Note Added: 0058125
2013-04-25 01:50 hudsonbot Checkin
2013-04-25 01:50 hudsonbot Note Added: 0058189
2013-04-25 01:50 hudsonbot Checkin
2013-04-25 01:50 hudsonbot Note Added: 0058190
2013-04-25 01:50 hudsonbot Checkin
2013-04-25 01:50 hudsonbot Note Added: 0058191
2013-04-25 01:50 hudsonbot Checkin
2013-04-25 01:50 hudsonbot Note Added: 0058192
2013-04-25 14:08 hgbot Checkin
2013-04-25 14:08 hgbot Note Added: 0058253
2013-04-25 14:08 hgbot Checkin
2013-04-25 14:08 hgbot Note Added: 0058254
2013-04-25 14:08 hgbot Checkin
2013-04-25 14:08 hgbot Note Added: 0058255
2013-04-25 15:30 hgbot Checkin
2013-04-25 15:30 hgbot Note Added: 0058260
2013-04-26 09:56 alostale Note Added: 0058271
2013-04-26 09:56 alostale Status resolved => closed
2013-04-26 09:56 alostale Fixed in Version => 3.0MP23
2013-04-26 23:54 hudsonbot Checkin
2013-04-26 23:54 hudsonbot Note Added: 0058287
2013-04-26 23:54 hudsonbot Checkin
2013-04-26 23:54 hudsonbot Note Added: 0058288
2013-04-26 23:54 hudsonbot Checkin
2013-04-26 23:54 hudsonbot Note Added: 0058289
2013-04-26 23:54 hudsonbot Checkin
2013-04-26 23:54 hudsonbot Note Added: 0058291
2013-05-02 16:31 johnfandl Issue Monitored: johnfandl


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker