Wikiadmin: /* Performance considerations */

2022-01-21T09:19:55Z

‎Performance considerations

Wikiadmin: /* Requirements */

2022-01-21T09:18:03Z

‎Requirements

Wikiadmin: /* Introduction */

2022-01-21T09:15:57Z

‎Introduction

Wikiadmin: Created page with "== Introduction == [http://centralrepository.openbravo.com/openbravo/org.openbravo.forge.ui/sso/ForgeModuleDetail/DB-Pool-Logging-Utilities DB Pool Logging Utilities] is a co..."

2022-01-21T09:01:35Z

Created page with "== Introduction == [http://centralrepository.openbravo.com/openbravo/org.openbravo.forge.ui/sso/ForgeModuleDetail/DB-Pool-Logging-Utilities DB Pool Logging Utilities] is a co..."

New page

== Introduction ==

[http://centralrepository.openbravo.com/openbravo/org.openbravo.forge.ui/sso/ForgeModuleDetail/DB-Pool-Logging-Utilities DB Pool Logging Utilities] is a community module that provides the ability of generating log for some events of database Pool.

It is primarily intended to be used by developers to facilitate them to pinpoint the code that is causing performance issues related to database queries/connections.

This information is logged in standard [[How_To_Get_Openbravo_Logs | openbravo log]].

== Requirements ==

* Depends on [[Release_Notes/3.0PR17Q1|Openbravo 3.0PR17Q1]].
* It can only generate log for [[Apache_JDBC_Connection_Pool|Tomcat DB Pool]].

== Performance considerations ==

When installing this module, especially in production environments, take into account that, depending on how it is configured, it can cause an overhead in terms of performance. Read carefully Configuration section to select the settings before using it. You can find [[Projects:DB_Pool_Logging_Utilities/QA#Performance_overhead|here]] some measures taken regarding this overhead.

== What to log ==

=== Slow Queries ===

Logging slow queries is important to fine tune them. DB logs (ie. <code>min_duration_statements</code> in PostgreSQL) allows to log them, but sometimes it is not easy from those logs to reach the code that triggered the query. Adding stack traces to logged slow queries helps to easily know the code behind the queries.

=== Connections ===

Some times, it is useful also to know all the connections that are present in the pool, understanding where they were borrowed for last time.

== Configuration ==

This module is highly configurable in order to exactly determine what to log. Note that by default the module is not enabled, so even it is installed in the Openbravo instance, it will not log anything until it is configured.

There are two ways to configure the settings for this module: Openbravo.properties and MBeans.

=== How to set parameters ===
==== Openbravo.properties ====

{{(!)|Note after any change in Openbravo.properties, you will need to compile and restart Tomcat in order to make it effective.}}

All parameters described in Configuration section can be set through [[Openbravo.properties | <code>Openbravo.properties</code> file]]. The properties are named <code>db.pool.logger.</code> followed the parameter name to configure starting in lowercase. For example to enable logging, use: <code>db.pool.logger.enabled=true</code>.

Settings defined in <code>Openbravo.properties</code> are applied when Tomcat starts.

==== MBeans ====

It is also possible to change logging configuration in a live instance using JMX MBeans. The MBean is registered in <code>Openbravo:''contextName-''name=DBPoolLogger</code> where contextName is the application's context name.

There are different clients to connect to JMX MBeans:
[[Image:Jmxterm-pool-log.png|right|400px]]

* [http://docs.oracle.com/javase/8/docs/technotes/guides/management/jconsole.html JConsole] which is included in JDK.
* [https://github.com/jiaqi/jmxterm jmxterm] command line client

=== Parameters ===

* '''Enabled'''. Default <code>false</code>. Enables or disabled pool logging, when it is not enabled, none of the rest of parameter values is take into account. When logging is disabled, its overhead in performance is negligible.

Settings for logging statements:

* '''MinDurationStatements'''. Default <code>-1</code>. Defines the minimum time in milliseconds to log statements. Whenever a query takes longer than this threshold to be executed in database in will be logged including the time it took to be executed. <code>-1</code> indicates that no statement will be logged. <code>0</code> will log ALL statements. Note that setting a low value for this parameter will produce too much log, adding overall overhead to execution times.
* '''QuerySubstrings'''. Default empty. List of substrings in sensitive case queries are compared to in order to log. If this list is empty, all queries taking more than ''MinDurationStatements'' will be logged; if there are values in this list, only queries that match any of these substrings will be logged in case they take more than ''MinDurationStatements'' to execute.
** '''AllQuerySubstrings'''. This parameter can be set to provide a full list of substring queries separated by #.
** '''addQuerySubstring'''. Only as JMX operation. Allows to add a new query substring to the existing list.
** '''resetQuerySubstrings'''. Only as JMX operation. Removes all query substrings, so from this moment all queries will be electable to logged.
* '''LogParamValues'''. Default <code>false</code>. When enabled, if logged statements contain parameters, their values will be also logged.
* '''LogStatementStackTrace'''. Default <code>true</code>. When enabled, logged statements will include a stack trace making in this manner possible to know which is the code that executed the query.

Stack trace configuration:
*'''MaxStackTraceLength'''. Default <code>8</code>. Maximum number of lines to be logged in stack traces. Stack traces can be very long but to get to the relevant piece, usually it is not required to have it complete in order to get to the code executing the query. Note than having here a big number and a low ''MinDurationStatements'' value can generate a very verbose log.
*'''UseStackTraceBlackList'''. Default <code>true</code>. When it is true, some classes/packages are excluded from stack trace logs. There are some common infrastructure packages that are not usually interesting in logs, that's why are excluded by default. The complete list is:
** <code>org.apache.catalina.core.ApplicationFilterChain</code>
** <code>org.apache.tomcat</code>
** <code>org.openbravo.apachejdbcconnectionpool</code>
** <code>org.openbravo.util.db</code>
** <code>org.hibernate</code>
** <code>com.sun.proxy</code>

Settings for logging connections:

* '''LogMultipleConnectionsPerThread''': Default <code>false</code>. When it is active, a warning message will appear in log if a thread borrows at the same time more than one connection from the pool. In this message will appear for each not returned connection the stack trace from where it was borrowed. Having at the same time several connections in the same thread can be problematic as described in {{IssueReference|issuenum=35855}}. Note that enabling this feature in production environments can cause performance degradation because for every borrowed connection it is needed store thread information and its stack trace and also to check if there are any other connection already borrowed in the same thread.
* '''TrackConnections''': Default <code>false</code>. When enabled, for each connection in the pool it is saved the stack trace for the last time it was borrowed. Its purpose is to be able to log on demand through JMX MBeans. Note if this is enabled having already pooled idle connections, stack traces will not be available for them till they are not borrowed again. Take into account tracking connections can cause performance degradation in production environments, as per each connection that is borrowed, stack traces are saved.

Logging connections, these features are only available through JMX MBeans in case ''TrackConnections'' is set to <code>true</code>:
* '''logActiveConnections''': will log all connections that are currently active. A connection is considered to be active in case it has been borrowed and not returned yet to the pool. Log includes stack traces of the moment each connection was borrowed from pool, as well as current stack traces.
* '''logAllConnections''': logs all connections in the pool, including both active and idle connections. In the log, it is included the stack trace of the connection when it was borrowed from the pool.

@@ Line 14: / Line 14: @@
 == Performance considerations ==
-When installing this module, especially in production environments, take into account that, depending on how it is configured, it can cause an overhead in terms of performance. Read carefully Configuration section to select the settings before using it. You can find [[Projects:DB_Pool_Logging_Utilities/QA#Performance_overhead|here]] some measures taken regarding this overhead.
+When installing this module, especially in production environments, take into account that, depending on how it is configured, it can cause an overhead in terms of performance. Read carefully Configuration section to select the settings before using it. You can find [[Projects:DB Pool Logging Utilities/QA#Performance_overhead|here]] some measures taken regarding this overhead.
 == What to log ==

@@ Line 10: / Line 10: @@
 * Depends on [[Release_Notes/3.0PR17Q1|Openbravo 3.0PR17Q1]].
-* It can only generate log for [[Apache_JDBC_Connection_Pool|Tomcat DB Pool]].
+* It can only generate log for [[Modules:Apache JDBC Connection Pool | Tomcat DB Pool]].
 == Performance considerations ==

@@ Line 5: / Line 5: @@
 It is primarily intended to be used by developers to facilitate them to pinpoint the code that is causing performance issues related to database queries/connections.
-This information is logged in standard [[How_To_Get_Openbravo_Logs | openbravo log]].
+This information is logged in standard [[How To Configure Log | openbravo log]].
 == Requirements ==

Module:DB Pool Logging Utilities - Revision history

Wikiadmin: /* Performance considerations */

Wikiadmin: /* Requirements */

Wikiadmin: /* Introduction */

Wikiadmin: Created page with "== Introduction == [http://centralrepository.openbravo.com/openbravo/org.openbravo.forge.ui/sso/ForgeModuleDetail/DB-Pool-Logging-Utilities DB Pool Logging Utilities] is a co..."