[teiid-issues] [JBoss JIRA] (TEIID-6014) Connection Pooling in SQL server XA data source

[Manoj Majumdar (Jira)]

     [ https://issues.redhat.com/browse/TEIID-6014?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Manoj Majumdar updated TEIID-6014:
----------------------------------
    Description: 
We are implementing connection pooling in the case of XA data source Sql Server.

 

As per our understanding of the connection pooling mechanism in the teiid, we are referring below parameter in the Teiid Admin console.

Below are the parameters mentioned in the Data Source Metrics

i) Available count

ii) Active count

iii) Max used

 

While deploying the XA data source for SQL server, we have configured the min pool size = 4 and max pool size = 8 and below are our understanding and how ideally it should work if we have implemented the connection pooling. (Please correct our observation)
 # Initially 4 connection threads will be active.
 # Whenever we are going to use these connections one by one Available count will decrement.
 # When all 8 connections will have used up, we will wait till connection available back to pool.

 

Below are the actual observations.
 # No changes identified on Admin console.
 # On DB level threads are increasing irrespective of max pool size.
 # connections are increasing till 12 connections even though max size is 8

 

Could you please shed more light on below points:
 # How SQL server XA datasource connection pooling is working.
 # In case of My sql XA datasource its working as expected.

Attaching screenshot for Admin console statistics and DB level thread status.

Please let us know are we missing any configuration.

  was:
We are implementing connection pooling in the case of XA data source Sql Server.

 

As per our understanding of the connection pooling mechanism in the teiid, we are referring below parameter in the Teiid Admin console.

Below are the parameters mentioned in the Data Source Metrics

i) Available count

ii) Active count

iii) Max used

 

While deploying the XA data source for SQL server, we have configured the min pool size = 4 and max pool size = 8 and below are our understanding and how ideally it should work if we have implemented the connection pooling. (Please correct our observation)
 # Initially 4 connection threads will be active.
 # Whenever we are going to use these connections one by one Available count will decrement.
 # When all 8 connections will have used up, we will wait till connection available back to pool.

 

Below are the actual observations.
 # No changes identified on Admin console.
 # On DB level threads are increasing irrespective of max pool size.
 # connections are increasing till 12 connections even though max size is 8

 

Could you please shed more light on below points:
 # How SQL server XA datasource connection pooling is working.
 # In case of My sql XA datasource its working as expected.

Attaching screenshot for Admin console statistics and DB level thread status.

Below is sql server data source configuration in standalone-teiid.xml

				<xa-datasource jndi-name="java:/XXXX" pool-name="XXXX">
                    <xa-datasource-property name="DatabaseName">
                        XXXX
                    </xa-datasource-property>
                    <xa-datasource-property name="PortNumber">
                        XXXX
                    </xa-datasource-property>
                    <xa-datasource-property name="ServerName">
                        XXXX
                    </xa-datasource-property>
                    <xa-datasource-property name="URL">
                        jdbc:sqlserver://127.0.0.1:1433;databaseName=XXXX;sendTimeAsDateTime=false
                    </xa-datasource-property>
                    <driver>sqlserver</driver>
                    <xa-pool>
                        <min-pool-size>4</min-pool-size>
                        <max-pool-size>8</max-pool-size>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </xa-pool>
                    <security>
                        <user-name>XXXX</user-name>
                        <password>XXXX</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                        <validate-on-match>true</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                </xa-datasource>


Please let us know are we missing any configuration.



> Connection Pooling in SQL server XA data source
> -----------------------------------------------
>
>                 Key: TEIID-6014
>                 URL: https://issues.redhat.com/browse/TEIID-6014
>             Project: Teiid
>          Issue Type: Bug
>            Reporter: Manoj Majumdar
>            Assignee: Steven Hawkins
>            Priority: Major
>
> We are implementing connection pooling in the case of XA data source Sql Server.
>  
> As per our understanding of the connection pooling mechanism in the teiid, we are referring below parameter in the Teiid Admin console.
> Below are the parameters mentioned in the Data Source Metrics
> i) Available count
> ii) Active count
> iii) Max used
>  
> While deploying the XA data source for SQL server, we have configured the min pool size = 4 and max pool size = 8 and below are our understanding and how ideally it should work if we have implemented the connection pooling. (Please correct our observation)
>  # Initially 4 connection threads will be active.
>  # Whenever we are going to use these connections one by one Available count will decrement.
>  # When all 8 connections will have used up, we will wait till connection available back to pool.
>  
> Below are the actual observations.
>  # No changes identified on Admin console.
>  # On DB level threads are increasing irrespective of max pool size.
>  # connections are increasing till 12 connections even though max size is 8
>  
> Could you please shed more light on below points:
>  # How SQL server XA datasource connection pooling is working.
>  # In case of My sql XA datasource its working as expected.
> Attaching screenshot for Admin console statistics and DB level thread status.
> Please let us know are we missing any configuration.



--
This message was sent by Atlassian Jira
(v7.13.8#713008)