Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033441Openbravo ERPA. Platformpublic2016-07-06 12:162016-12-02 12:30
Reporteralostale 
Assigned ToNaroaIriarte 
PriorityurgentSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR16Q4Fixed in Version3.0PR16Q4 
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
Summary0033441: 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.
Additional Information
TagsPerformance
Relationships
related to defect 0033438 closed alostale DalConnectionProvider.getTransactionConnection does not obtain connection from common pool 
related to design defect 0035007 closed alostale audit trail causes excessive overhead 
related to defect 0037677 closed AugustoMauch No need to check if ad_context_info table exists before creating it 
causes defect 0039601 closed alostale can't execute DB updates after killing a connection 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-07-06 12:16alostaleNew Issue
2016-07-06 12:16alostaleAssigned To => platform
2016-07-06 12:16alostaleOBNetwork customer => No
2016-07-06 12:16alostaleModules => Core
2016-07-06 12:16alostaleTriggers an Emergency Pack => No
2016-07-06 12:16alostaleTag Attached: Performance
2016-07-06 12:16alostaleStatusnew => acknowledged
2016-07-06 12:18alostaleNote Added: 0088234
2016-07-06 12:18alostaleRelationship addedrelated to 0033438
2016-07-06 12:50shuehnerIssue Monitored: shuehner
2016-07-11 11:12alostaleStatusacknowledged => scheduled
2016-07-11 11:12alostaleAssigned Toplatform => NaroaIriarte
2016-08-10 15:54hgbotCheckin
2016-08-10 15:54hgbotNote Added: 0088959
2016-08-10 15:54hgbotStatusscheduled => resolved
2016-08-10 15:54hgbotResolutionopen => fixed
2016-08-10 15:54hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/88ea14de1a84e176f457750118a6d4823588d7d2 [^]
2016-08-10 15:55NaroaIriarteReview Assigned To => alostale
2016-08-11 18:40hudsonbotCheckin
2016-08-11 18:40hudsonbotNote Added: 0089056
2016-08-12 10:02hgbotCheckin
2016-08-12 10:02hgbotNote Added: 0089079
2016-08-16 17:35hudsonbotCheckin
2016-08-16 17:35hudsonbotNote Added: 0089147
2016-08-30 12:06alostaleNote Added: 0089545
2016-08-30 12:06alostaleStatusresolved => closed
2016-08-30 12:06alostaleFixed in Version => 3.0PR16Q4
2016-12-02 12:30alostaleNote Added: 0092025
2017-01-20 13:19alostaleRelationship addedrelated to 0035007
2018-01-19 10:16AugustoMauchRelationship addedrelated to 0037677
2018-11-07 15:05alostaleRelationship addedcauses 0039601

Notes
(0088234)
alostale   
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   
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   
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   
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   
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   
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   
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 [^]