Performance JDBC clients when working with OpenEdge database

Posted by Valeriy Bashkatov on 12-Mar-2015 04:03

Hello,

I need information about the possibilities (performance, configuring, programming etc.) of JDBC connections in large OpenEdge systems. Our client trying to make an assessment of their future project.

Unfortunately, I did not find any information with statistical data of JDBC for OpenEdge.

Could you help me answer to the following questions:

  1. Are there any known problems in the OpenEdge database when connecting a large number of JDBC clients (100, 2000, 5000 concurrent connections)? On what it is necessary to pay special attention?
  2. Is there any recommendations (Best practices) for configuring JDBC for OpenEdge  and programming for OpenEdge/JAVA using JDBC connection? (except OpenEdge documentation)

Thank you in advance for your help.

Regards,
Valeriy

DataDirect Connect - Forum
Posted by steve pittman on 12-Mar-2015 09:59

Hi Valeriy,

There are OE customers with large numbers of JDBC clients. They are doing fine. Scalability seems to be OK, with the understanding that Java can use a lot of memory.

A lot depends on how the JDBC clients are setup. Are they each on different  Windows machines distributed over a network?
Or do they all originate from a Java app server like Tomcat or Weblogic?

If separate machines, provisioning all the machines with the latest JDBC driver and required Java version can be the most work.

If client are on a Java app server, then configuring Java (the JVM really) for good memory support is important.
A lot depends on the JDBC application. Simple reporting uses not a lot of resources. Doing cursor updates, or doing scrollable cursors, uses more client resources.

A lot of JDBC app server setups will use a connection pool, often provided by the app server.
Connection pools can give better latency for JDBC clients. The tradeoff is that the individual connections in the pool, since they stay alive for a long time (maybe forever), can accumulate a fair amount of server resources tied to the connection.

If the app server has a "keep alive" query, used to see whether server is alive, then the query used is signficant.
There is a JDBC api for this purpose also, but it seems like many app servers do not use it?
 The "keepalive query" , if used, fires frequently and  can use a lot of server resources. It is best to make it a sql statement that does not touch data or use transaction resources.
We (OE sql) suggest using the statement:
                  SET   TEST SERVER CONNECTIVITY
This has almost no overhead.

This assumes your OE server is at least OE 11.0.

In all circumstances, you are better off having the latest OE version and service pack that your installations can support.
Just as an example, 11.4 is better than 10.2B08. Both work fine, but 11.4 has better performance and scalability (in various ways). As usual, your mileage may vary.

I will put some more specific comments for your questions below.


[collapse]On 3/12/2015 5:03 AM, Valeriy Bashkatov wrote:
Thread created by Valeriy Bashkatov

Hello,

I need information about the possibilities (performance, configuring, programming etc.) of JDBC connections in large OpenEdge systems. Our client trying to make an assessment of their future project.

Unfortunately, I did not find any information with statistical data of JDBC for OpenEdge.

Could you help me answer to the following questions:

  1. Are there any known problems in the OpenEdge database when connecting a large number of JDBC clients (100, 2000, 5000 concurrent connections)? On what it is necessary to pay special attention?

There are no known problems with this sort of client load.  As that number of truly concurrent connections gets very large, you may be in territory that many OE sql users don't see. Ramping up gradually will be important, as I'm sure you know!

The most important aspects of a large number of clients is to get the OE db server configured well.
All the standard server configuration suggestions, described in various OE documentation and all sorts of websites and Exchange presentations, do apply.
Don't forget the PUG Challenge conferences as a source of info and  great advice.  There are excellent presentations from past years on the PUG Challenge web site.

For OE sql on the server side, there are a couple of general principles to bear in mind. You probably already know some/all of these!

Many installations with a lot of sql clients like to use a Secondary Broker for sql, so they can configure some resources for sql and some of the sql network traffic is offloaded from the broker serving ABL clients.

To the OE sql server, JDBC clients are same as ODBC clients - they are all clients using the same communications and server capabilities. The server does not do anything different for JDBC as compared to ODBC.

Sql has a multi-threaded server, so you can configure more users per server process, using -Mi and -Ma, that you might for an server with all ABL clients.
Where the DB Admin guide talks about number of users per server process, for sql that implies number of connection threads on the server process.

Sql connections uses more memory than ABL connections, for a variety of reasons. But do be aware that your server will use a lot of memory for a lot of sql users.
Depending on the application needs and patterns, you might want to try running sql with a smaller statement cache (-SQLStmtCache startup parameter) or smaller sort buffer (-SQLTempBuff). They default sort buffer is 1M per connection (not shared between connections).   Sort buffer is important for reporting and sorting and grouping, so it all depends.



  1. Is there any recommendations (Best practices) for configuring JDBC for OpenEdge  and programming for OpenEdge/JAVA using JDBC connection? (except OpenEdge documentation)

We have tried to make our JDBC support  as standard as possible, so that all the good JDBC practices recommended by JDBC experts apply to OE.
Our JDBC drivers are from DataDirect of course, so  many thanks to them for making "as standard as possible"  a reality.

Having the latest driver is usually a good practice. Drivers are compatible with servers across several versions, so newer drivers are OK with older servers.
There are some subtle differences between 10.* drivers and 11.* drivers for rowid support, which can affect cursors and updates.

Hope this helps,               ..... steve pittman  {OE sql software architect]



Thank you in advance for your help.

Regards,
Valeriy

Stop receiving emails on this subject.

Flag this post as spam/abuse.


[/collapse]

All Replies

Posted by steve pittman on 12-Mar-2015 09:59

Hi Valeriy,

There are OE customers with large numbers of JDBC clients. They are doing fine. Scalability seems to be OK, with the understanding that Java can use a lot of memory.

A lot depends on how the JDBC clients are setup. Are they each on different  Windows machines distributed over a network?
Or do they all originate from a Java app server like Tomcat or Weblogic?

If separate machines, provisioning all the machines with the latest JDBC driver and required Java version can be the most work.

If client are on a Java app server, then configuring Java (the JVM really) for good memory support is important.
A lot depends on the JDBC application. Simple reporting uses not a lot of resources. Doing cursor updates, or doing scrollable cursors, uses more client resources.

A lot of JDBC app server setups will use a connection pool, often provided by the app server.
Connection pools can give better latency for JDBC clients. The tradeoff is that the individual connections in the pool, since they stay alive for a long time (maybe forever), can accumulate a fair amount of server resources tied to the connection.

If the app server has a "keep alive" query, used to see whether server is alive, then the query used is signficant.
There is a JDBC api for this purpose also, but it seems like many app servers do not use it?
 The "keepalive query" , if used, fires frequently and  can use a lot of server resources. It is best to make it a sql statement that does not touch data or use transaction resources.
We (OE sql) suggest using the statement:
                  SET   TEST SERVER CONNECTIVITY
This has almost no overhead.

This assumes your OE server is at least OE 11.0.

In all circumstances, you are better off having the latest OE version and service pack that your installations can support.
Just as an example, 11.4 is better than 10.2B08. Both work fine, but 11.4 has better performance and scalability (in various ways). As usual, your mileage may vary.

I will put some more specific comments for your questions below.


[collapse]On 3/12/2015 5:03 AM, Valeriy Bashkatov wrote:
Thread created by Valeriy Bashkatov

Hello,

I need information about the possibilities (performance, configuring, programming etc.) of JDBC connections in large OpenEdge systems. Our client trying to make an assessment of their future project.

Unfortunately, I did not find any information with statistical data of JDBC for OpenEdge.

Could you help me answer to the following questions:

  1. Are there any known problems in the OpenEdge database when connecting a large number of JDBC clients (100, 2000, 5000 concurrent connections)? On what it is necessary to pay special attention?

There are no known problems with this sort of client load.  As that number of truly concurrent connections gets very large, you may be in territory that many OE sql users don't see. Ramping up gradually will be important, as I'm sure you know!

The most important aspects of a large number of clients is to get the OE db server configured well.
All the standard server configuration suggestions, described in various OE documentation and all sorts of websites and Exchange presentations, do apply.
Don't forget the PUG Challenge conferences as a source of info and  great advice.  There are excellent presentations from past years on the PUG Challenge web site.

For OE sql on the server side, there are a couple of general principles to bear in mind. You probably already know some/all of these!

Many installations with a lot of sql clients like to use a Secondary Broker for sql, so they can configure some resources for sql and some of the sql network traffic is offloaded from the broker serving ABL clients.

To the OE sql server, JDBC clients are same as ODBC clients - they are all clients using the same communications and server capabilities. The server does not do anything different for JDBC as compared to ODBC.

Sql has a multi-threaded server, so you can configure more users per server process, using -Mi and -Ma, that you might for an server with all ABL clients.
Where the DB Admin guide talks about number of users per server process, for sql that implies number of connection threads on the server process.

Sql connections uses more memory than ABL connections, for a variety of reasons. But do be aware that your server will use a lot of memory for a lot of sql users.
Depending on the application needs and patterns, you might want to try running sql with a smaller statement cache (-SQLStmtCache startup parameter) or smaller sort buffer (-SQLTempBuff). They default sort buffer is 1M per connection (not shared between connections).   Sort buffer is important for reporting and sorting and grouping, so it all depends.



  1. Is there any recommendations (Best practices) for configuring JDBC for OpenEdge  and programming for OpenEdge/JAVA using JDBC connection? (except OpenEdge documentation)

We have tried to make our JDBC support  as standard as possible, so that all the good JDBC practices recommended by JDBC experts apply to OE.
Our JDBC drivers are from DataDirect of course, so  many thanks to them for making "as standard as possible"  a reality.

Having the latest driver is usually a good practice. Drivers are compatible with servers across several versions, so newer drivers are OK with older servers.
There are some subtle differences between 10.* drivers and 11.* drivers for rowid support, which can affect cursors and updates.

Hope this helps,               ..... steve pittman  {OE sql software architect]



Thank you in advance for your help.

Regards,
Valeriy

Stop receiving emails on this subject.

Flag this post as spam/abuse.


[/collapse]

Posted by Valeriy Bashkatov on 13-Mar-2015 06:24

Hi Steve,

Thank you very much for your answer! This is useful information.

I will discuss this with the client and if they have any more questions I will come back )

Regards,

Valeriy

This thread is closed