Proxy to Oracle Pool Datasource connection

[AnuradhaBose]

Hi Team,

I have been using ToxiProxy for quite sometime to proxy http endpoints and it is working fine.
I have a new requirement of creating a proxy to the Oracle Database. The database connection is being created using the Oracle PoolDataSource during the application start up, and during the API execution, these connection pools are being used to add/update data into the Database.
I have created a proxy to the database host and port, and the connection is being established fine. The problem is that, since our application is using the Pooled Data Source connections which are being created during the application start-up itself, the subsequent calls where the DB transactions are being done, does not have any impact on any toxic (latency or timeout) that is being added to the proxy. In short, I am not being able to make use of the features of ToxiProxy when the Oracle Connection is being used as a Pooled DataSource connection.

Note: If an application creates a new connection every time for a database operation, in such a case the latency added to the proxy is visible, the api takes longer time than usual. But if the connection is made only once during startup and the oracle PoolDataSource connection is being used to do the DB operations, instead of creating the physical connection every time, the latency added to the proxy does not have any impact. The application makes use of a connection already available in the Pool of connections and does the operation.

Have you come across any such scenario and can you help me find a workaround for this. We are trying to setup this scenario into one of our applications for a chaos test and we are stuck at this point.

[miry]

@AnuradhaBose Do you have a docker solution to reproduce the problem.

I don't have any information about Oracle Pool Datasource,
but if it uses TCP connection, then it should be impacted with toxics.

You can try to setup different toxics like Bandwidth, if latency is not working properly.
Sometime an application has Cache mechanism, to avoid query DB.

Using tools like TCPDump and lsof would help understand if you application uses Toxiproxy or doing direct connection DB.

The problem is that, since our application is using the Pooled Data Source connections which are being created during the application start-up itself, the subsequent calls where the DB transactions are being done, does not have any impact on any toxic (latency or timeout) that is being added to the proxy.

I would think you can add Toxics before application start and verify if it is working during the boot?

[AnuradhaBose]

Hi @miry - The toxics maybe working during the boot, but my need is to chaos test the database by adding toxics during the API calls, when there are Database transactions being done. Considering the fact that in Oracle DataSource Pool connections, the database connections are made only once (in this case during the application start up) and in the subsequent API calls, these connection objects are being used to query the database or save data, is there a way in which ToxiProxy could add toxics dynamically to these already created connection objects or in their subsequent database operations? This design pattern of creating connection pools for acquiring database connection objects are very common in today's world, this helps reuse of the same connection objects instead of having to connect to database multiple times. In this scenario, could you please suggest a way where we could carry out chaos tests for database calls. Is there any workaround.
I do not have a docker solution available for this test, but this is the link to the Oracle Pool Datasource connection documentation- https://docs.oracle.com/cd/E11882_01/java.112/e12265/connect.htm#CHDDCICA

[miry]

@AnuradhaBose Toxiproxy add toxic to existen connections. Example application connected to the Proxy by port 1000, then add toxic with client. the changes would apply to current open connections on the fly without reconnection.

[AnuradhaBose]

Hi @miry - How do I add proxy to an existing connection? Currently, I am creating a proxy on the database hostname and port. Say for example the database hostname is phxecs1.phoenixvcn.oraclevcn.com and port is 1521, I am creating a proxy to this, and then replacing the database url with the proxy url instead of the actual db host. Something like this- jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_TIMEOUT=90)(RETRY_COUNT=20)(RETRY_DELAY=4)(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=ToxiProxy.internal.com)(PORT=8083)))(CONNECT_DATA=(SERVICE_NAME=chubd.phxecs1.phoenixvcn.oraclevcn.com)(SERVER=DEDICATED))). Now the application which is creating the connection is using Oracle Datasource Pool, and it is creating the connections during the application start up, which I have explained in the comments above. For doing the database operations, the application is using the same connection object from the pool and executing the database queries. Is there any way to add toxicities to these already created connections? As per my understanding, to create a database proxy we need the database hostname and port. This works only when the code is creating a DB connection every time it is executing a query. But in this case, since the connection objects are already existing (it also has toxicities added via proxy) but this is not reflecting in the subsequent database calls, since the connection is created only once during application start,

[miry]

I would start first to check if your application uses Toxiproxy.
Here is some sample steps to start use Toxiproxy.

1. Run Toxiproxy-server: LOG_LEVEL=trace toxiproxy-server
   (it also supports to read config with proxies, but for now we skip it).
2. Open second terminal.
3. Check that toxiproxy is running: curl -v localhost:8474/version. Should return server version. It should be also visible in logs from first terminal, where is toxiproxy-server is running.
4. Create a proxy: toxiproxy-cli create -l 0.0.0.0:1521 -u phxecs1.phoenixvcn.oraclevcn.com:1521 phxecs_proxy.
5. Validate response from client, also check logs from server.
6. Now you can test that it works with sending some message to the port and it should redirect to Oraclevcn: curl --max-time 1 0.0.0.0:1521/some/endpoint/doesnotmatter.
   The response does not matter, the main point you see something back from the service.
   Check logs from toxiproxy-server. It should see messages when client connected.
7. If you have a oraclevcn database client, try it also to communicate with DB via 0.0.0.0:1521. It should work, like it works directly with phxecs1.phoenixvcn.oraclevcn.com.
8. I would first play with Proxy on this stage: read logs, run traffic through proxy and check that it works.

Toxic

1. Introduce toxics. Create a simple toxic with command with command:

toxiproxy-cli toxic add --downstream \
              --type=latency \
              --toxicName="latency_downstream" \
              --attribute="latency=1000" \
              --attribute="jitter=50" \
              --toxicity=0.99 \
              phxecs_proxy

1. Validate that it was created with: toxiproxy-cli inspect phxecs_proxy
2. Try to use some original client or use curl to check that it still works, but a bit slower. Validate logs from toxiproxy-server.
3. Remove toxic: toxiproxy-cli toxic delete --toxicName="latency_downstream" phxecs_proxy
4. And check that everything is working the same as before.
5. Play a bit with different toxic and attributes to make famillar.

Tests

1. Now point your tests to proxy and execute them without Toxics.
2. Check logs, and validate that it was any communication done via Toxiproxy server.
3. Start write Toxics with toxiproxy clients for single test.
4. Validate that toxic was created with toxiproxy-cli list or toxiproxy-cli inspect <proxy>

I collected some examples how to use toxiproxy-server and toxiproxy-cli in https://github.com/Shopify/toxiproxy/blob/master/scripts/test-e2e and https://github.com/Shopify/toxiproxy/blob/master/scripts/test-e2e-hazelcast

PS: I would first start with something simple, example Redis.

1. Run redis-server
2. Run LOG_LEVL=debug toxiproxy-server
3. Create proxy: toxiproxy-cli create --listen localhost:16379 --upstream localhost:6379 redis
4. Open redis client: redis-cli -p 16379
5. Run in redis: keys *
6. Run toxiproxy-cli toxic add --downstream --type=latency --toxicName="latency_downstream" --attribute="latency=1000" redis
7. Run again in redis: keys *

[AnuradhaBose]

Hi @miry - I have already used ToxiProxy and have done many experiments on http endpoints as well as database. The proxy to the database host works fine, and the toxicities added to the proxy also works as well, there is no doubt.
My question is on a specific scenario where the target application we want to execute chaos testing on, uses Oracle Data Source Pool to create the connections. That's where the problem lies as I have explained in my previous comments. The Database connections are being created during the application start-up and are there are a pool of say 5 such connections. Now the application (during an API call) makes use of these connections from the pool to execute database operations. The toxicities that we add to the database only applies when the connection is created, not when the connections are reused (in case of pools). My question is, is there a way to add toxicities to these connections so that they apply on the Database operations as well?

[miry]

Now the application (during an API call) makes use of these connections from the pool to execute database operations. The toxicities that we add to the database only applies when the connection is created, not when the connections are reused (in case of pools). My question is, is there a way to add toxicities to these connections so that they apply on the Database operations as well?

Toxiproxy does not know about any protocols.
It adds toxic on TCP level to all connections.

If you know that you use Toxiproxy and in logs you see communication,
then it should be applied existing toxics if they presented.

If you can provide an small example with Docker,
that I can run and reproduce the problem, it would help me.

[AnuradhaBose]

Hi @miry - I have one more question, does the ToxiProxy toxicities work only during database connection establishment? Or can we add latency during the step of executing a query from the java code- Statement statement = connection.createStatement();

[miry]

does the ToxiProxy toxicities work only during database connection establishment

I answered this question in #444 (comment)

TLDR: Toxic applied to existent and new connections.

To test this behavior I provide steps in #444 (comment) (check a small redis experiment)

TIPS:

Sometime DB clients has cache mechanism (no db calls and use in memory cache).
And there is example how to bypass cache in Ruby: Shopify/toxiproxy-ruby#39 (comment)

[AnuradhaBose]

Hi @miry - I tried to do a small PoC to test out the effect of ToxiProxy on Database Connection Pools. The toxicities added to the proxy, is only effective when the physical connections are made to the database via TCP. Once the connections are established the latency has no effect on the logical connections. In most applications on production, the Connection Pools are used and in 98% of cases, an existing connection is used to perform a DB operation. Do you have any idea on how we could chaos test such applications during runtime?

Here is some background on Connection Pooling-
What is connection pooling?
A connection pool is a group of connection objects that represent physical database connections. At runtime, an application requests a connection from the pool. When the application is finished with the connection, it releases it back to the pool.

Opening a physical connection to a database involves establishing a TCP/IP connection, negotiating session parameters (from the protocol), and authenticating the user. User authentication can require considerable processing for heavy cryptographic key generation. Worse, each of these steps requires a remote procedure call (RPC), which entails a network round-trip to the database with the implied network latency.

Achieving maximum application uptime without interruptions requires outage detection, transparent planned maintenance, and balancing the workload. All of these factors critically influence application availability and performance. Connection pooling is an effective means of handling these concerns. Just as critically, connection pooling lets us re-use connections rather than starting a new one each time a request is made.

Connection pooling minimizes the use of resources in both the client and the database. The client only needs to access a pool of active connections, which is shared by a larger number of clients. In the database, each active connection allocates a set of resources—both in memory and in the CPU—that can be minimized by using a pool in the client.

When using Oracle Universal Connection Pool (UCP), a Java developer opens and closes connections only at the logical level. Connections are kept active in the pool and borrowed and returned to the pool as needed.