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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0033441
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformminoralways2016-07-06 12:162016-12-02 12:30
ReporteralostaleView Statuspublic 
Assigned ToNaroaIriarte 
PriorityurgentResolutionfixedFixed in Version3.0PR16Q4
StatusclosedFix in branchFixed in SCM revision88ea14de1a84
ProjectionnoneETAnoneTarget Version3.0PR16Q4
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Toalostale
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0033441: query to check if ad_context_info exists executed once per request

Descriptionad_context_info is a local temporary table used for auditing purposes. In PostgreSQL these kind of tables are not persistent so they need to be created per connection.

The code in charge of creating this table is in SessionInfo.initDB. It first checks whether the table is created and if not it creates it. This code is invoked each time a connection is borrowed from the pool, though actual table creation occurs only when a new connection is created.
Steps To Reproduce1. In postgresql.conf set min_duration_statements = 0 to keep track of all queries and reload PostgreSQL
2. Check pg logs: tail -f -n0 /var/log/postgresql/postgresql-9.4-main.log | grep -i ad_context_info
3. Start Tomcat
4. Check for each request select count(*) from information_schema.tables where table_name='ad_context_info' and table_type = 'LOCAL TEMPORARY' statement is executed though create statement is executed only once per connection
Proposed SolutionMove this code from connection borrow to connection creation.
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0033438 closedalostale DalConnectionProvider.getTransactionConnection does not obtain connection from common pool 
related to design defect 0035007 closedalostale audit trail causes excessive overhead 
related to defect 0037677 closedAugustoMauch No need to check if ad_context_info table exists before creating it 
causes defect 0039601 closedalostale can't execute DB updates after killing a connection 

-  Notes
(0088234)
alostale (viewer)
2016-07-06 12:18

 To check after this fix is applied: audit trail continues working in all cases:

* DAL processes
* xsql processes

Both with old and new pools.
(0088959)
hgbot (developer)
2016-08-10 15:54

 Repository: erp/devel/pi
Changeset: 88ea14de1a84e176f457750118a6d4823588d7d2
Author: Naroa Iriarte <naroa.iriarte <at> openbravo.com>
Date: Wed Aug 10 15:51:33 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/88ea14de1a84e176f457750118a6d4823588d7d2 [^]

Fixed issue 33441: Queries to check ad_context_info have been reduced

Before, the SessionInfo.initDB was invoqued when a connection was borrowed instead when a connection was created.
The correct behavior should be to execute the intDB method when creating a connection.
A new call to intDB has been added in ConnectionInitializerInterceptor.reset, if the connection is not initialized, it invoques the initDB method. This improves the number of times the initDB method is invoqued when the openbravo pool is used.

---
M modules/org.openbravo.apachejdbcconnectionpool/src/org/openbravo/apachejdbcconnectionpool/ConnectionInitializerInterceptor.java
M modules/org.openbravo.service.datasource/src/org/openbravo/service/datasource/DataSourceServlet.java
M src-core/src/org/openbravo/database/SessionInfo.java
M src/org/openbravo/dal/core/DalSessionFactory.java
M src/org/openbravo/dal/core/SessionHandler.java
---
(0089056)
hudsonbot (viewer)
2016-08-11 18:40

 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/edaa05b1fb18 [^]
Maturity status: Test
(0089079)
hgbot (developer)
2016-08-12 10:02

 Repository: erp/devel/pi
Changeset: e78010359c1b29dc5fcd39596bf6c0fa00c00c3e
Author: Naroa Iriarte <naroa.iriarte <at> openbravo.com>
Date: Fri Aug 12 09:57:15 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/e78010359c1b29dc5fcd39596bf6c0fa00c00c3e [^]

Related to issue 33441: Copyright years updated

Some copyright years have been updated.

---
M modules/org.openbravo.apachejdbcconnectionpool/src/org/openbravo/apachejdbcconnectionpool/ConnectionInitializerInterceptor.java
M src-core/src/org/openbravo/database/SessionInfo.java
M src/org/openbravo/dal/core/DalSessionFactory.java
---
(0089147)
hudsonbot (viewer)
2016-08-16 17:35

 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/e5ecc0ed32a1 [^]
Maturity status: Test
(0089545)
alostale (viewer)
2016-08-30 12:06

 code reviewed

Tested with new and old pools for dal and xsql connections, the only case where it is not feasible to prevent the query is with old pool for dal connections because when the connection is obtained from the pool there is no way to determine whether a new physical DB connection was required or if it was possible to get an existing one from the pool.

All other cases are working properly.
(0092025)
alostale (viewer)
2016-12-02 12:30

 Impact measurement

Checking in an actual customer queries to check whether ad_context_info table is created consumed 6.18% of the total time spent in DB, only 0.08% of those checks resulted in the need of creating the table.

So in this case, the total overhead would be reduced from 6.18% to 0.01%.

https://docs.google.com/spreadsheets/d/14wNWr6cZ__YyV0Mpa_FSWQoW8yPYMPlgeN9k_grai1Q/edit#gid=0 [^]

- Issue History
Date Modified Username Field Change
2016-07-06 12:16 alostale New Issue
2016-07-06 12:16 alostale Assigned To => platform
2016-07-06 12:16 alostale OBNetwork customer => No
2016-07-06 12:16 alostale Modules => Core
2016-07-06 12:16 alostale Triggers an Emergency Pack => No
2016-07-06 12:16 alostale Tag Attached: Performance
2016-07-06 12:16 alostale Status new => acknowledged
2016-07-06 12:18 alostale Note Added: 0088234
2016-07-06 12:18 alostale Relationship added related to 0033438
2016-07-06 12:50 shuehner Issue Monitored: shuehner
2016-07-11 11:12 alostale Status acknowledged => scheduled
2016-07-11 11:12 alostale Assigned To platform => NaroaIriarte
2016-08-10 15:54 hgbot Checkin
2016-08-10 15:54 hgbot Note Added: 0088959
2016-08-10 15:54 hgbot Status scheduled => resolved
2016-08-10 15:54 hgbot Resolution open => fixed
2016-08-10 15:54 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/88ea14de1a84e176f457750118a6d4823588d7d2 [^]
2016-08-10 15:55 NaroaIriarte Review Assigned To => alostale
2016-08-11 18:40 hudsonbot Checkin
2016-08-11 18:40 hudsonbot Note Added: 0089056
2016-08-12 10:02 hgbot Checkin
2016-08-12 10:02 hgbot Note Added: 0089079
2016-08-16 17:35 hudsonbot Checkin
2016-08-16 17:35 hudsonbot Note Added: 0089147
2016-08-30 12:06 alostale Note Added: 0089545
2016-08-30 12:06 alostale Status resolved => closed
2016-08-30 12:06 alostale Fixed in Version => 3.0PR16Q4
2016-12-02 12:30 alostale Note Added: 0092025
2017-01-20 13:19 alostale Relationship added related to 0035007
2018-01-19 10:16 AugustoMauch Relationship added related to 0037677
2018-11-07 15:05 alostale Relationship added causes 0039601

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker