how to troubleshoot the pooled connection failure

Database

how to troubleshoot the pooled connection failure

Dears,

My java program has a connection pool (Min 5 Max 30). I found from time to time it will fail to get a connection, and this troubles me. I see the pool resource is not used up. There are 4 exceptions caught:

- Could not get a connection: An SQLException was provoked by the following failure: com.mchange.v2.resourcepool.ResourcePoolException: A ResourcePool cannot acquire a new resource -- the factory or source appears to be down.

- Could not get a connection: An SQLException was provoked by the following failure: java.lang.InterruptedException

- Could not get a connection: An attempt by a client to checkout a Connection has timed out.

- Could not get a connection: Connections could not be acquired from the underlying database!

I want to ask how to troubleshoot such case. want to know the statistics for all connections, all pools; what are the activities that happened. What errors caused the connection to fail..

I tried the best to search the documentation and it looks like there is something called TDP but that is for channel-attached client. I think mine is network-attached client. 

I will appreciate for any replies, thanks in advance. And please don't mind if this is a stupid question.

Regards

Kang

Tags (1)
3 REPLIES
Teradata Employee

Re: how to troubleshoot the pooled connection failure

You appear to be using c3p0 connection pooling. Please provide your configuration, e.g. c3p0.properties file, if that's what you're using.

Re: how to troubleshoot the pooled connection failure

have no c3p0.propertis file, its configuration looks in the project configuration file, 


DATABASE_CONNECTION_URL=jdbc:teradata://

TERADATA_DRIVER_CLASS=com.teradata.jdbc.TeraDriver

#com.ncr.teradata.TeraDriver

#Connection timeout period (seconds).  Minimum is 30

DATABASE_CONNECTION_TIMEOUT=120

# The polling interval (in SECONDS) between checks for

# shutdown requests.

SHUTDOWN_CHECK_PERIOD=30

#The maximum number of fastloads that can be run simultaneously

#for each queue.

# Specify one per queue - syntax is MAX_LOADS_PER_QUEUE_i,

# where i is the queue ID as defined in the FEEDS table.

MAX_LOADS_PER_QUEUE_1=3

MAX_LOADS_PER_QUEUE_2=3

MAX_LOADS_PER_QUEUE_3=3

MAX_LOADS_PER_QUEUE_4=3

MAX_LOADS_PER_QUEUE_5=3

MAX_LOADS_PER_QUEUE_6=3

# The interval (in SECONDS) at which each landing zone

# will be polled for changes.

LZ_CHECK_PERIOD=10

#Log4j Config File

LOG_CONFIG=/var/opt/abi/framework/log4j.properties

# Yes - failed files will be renamed with a .FAILED extension.

# No - failed files will be deleted

KEEP_FAILED_FILES=Yes

# FILE = Concatenate enriched data into a single file

#        and point fastload to this file

# FIFO = Point fastload to a fifo and pipe enriched data files

#        through this pipe.

# Default is FIFO

FASTLOAD_RUNNER=FIFO

#Set compression to OFF if files from enricher are uncompressed

#Default is ON.

COMPRESSION=ON

# Percentage compression ratio = ( 1 - compressedSize/originalSize) * 100

COMPRESSION_RATIO=85

# The smallest timeout value (in seconds) below

# which the calculated timeout value will not fall.

MIN_TIMEOUT=2000

# The factor by which the time to load estimate will be extended (percent).

# Each load will have an estimated time to complete.  The timeout for

# this job will be set to ESTIMATE * (1 + TIMEOUT_MARGIN/100)

TIMEOUT_MARGIN=80

# The default load rate for each load (bytes/second)

# (Used to estimate a timeout value before a history exists).

DEFAULT_LOAD_RATE=100000

# If a fastload job times out then the loader re-starts if this

# parameter is Yes. Default is Yes.

EXIT_ON_TIMEOUT=Yes

# Time interval (in seconds) at which the average load rates

# will be retrieved from the database.

REFRESH_LOAD_RATES_TIME=3600

#Size of the buffers used to access raw data files

#WARNING: DO NOT ALTER THESE UNLESS YOU KNOW WHAT YOU ARE DOING

BUFFERED_READER_SIZE=65536

BUFFERED_WRITER_SIZE=65536

# Minimum number of pooled connections

MIN_POOLED_CONNECTIONS=5

# Maximum number of pooled connections

MAX_POOLED_CONNECTIONS=30

# Connection pool retry attempts (aquireRetryAttempt)

DATABASE_RETRY=1

# Connection pool retry delay (acquireRetryDelay)

DATABASE_RETRY_DELAY=0

# Other c3p0 options (all times in seconds - including checked_timeout)

DATABASE_MAX_CONNECTION_AGE=0

DATABASE_MAX_IDLE_TIME=300

DATABASE_MAX_IDLE_TIME_EXCESS=0

DATABASE_CHECKOUT_TIMEOUT=300

# Set to No to stop overhead of querying staging table for MIN/MAX data times.

RETRIEVE_DATA_TIMES=Yes

In the program we will read out the configuration and set pool's properties

            pool = new ComboPooledDataSource();

            pool.setDriverClass(driverClass);

            pool.setJdbcUrl(url_type4);

            pool.setUser(username);

            pool.setPassword(password);

            pool.setMinPoolSize(MIN_POOLED_CONNECTIONS);

            pool.setMaxPoolSize(MAX_POOLED_CONNECTIONS);

            pool.setAcquireRetryAttempts(DATABASE_RETRY);

            pool.setAcquireRetryDelay(DATABASE_RETRY_DELAY);

            pool.setMaxConnectionAge(DATABASE_MAX_CONNECTION_AGE);

            pool.setMaxIdleTime(DATABASE_MAX_IDLE_TIME);

            pool.setMaxIdleTimeExcessConnections(DATABASE_MAX_IDLE_TIME_EXCESS);

            pool.setCheckoutTimeout(DATABASE_CHECKOUT_TIMEOUT*1000);

Thanks

Teradata Employee

Re: how to troubleshoot the pooled connection failure

Your issues appear to be with c3p0, not the Teradata JDBC Driver. We recommend studying information available for c3p0.

Fo example, the following forum thread describes a symptom similar to yours:

http://stackoverflow.com/questions/5235912/running-out-of-db-connections

Troubleshooting advice from that thread:


"Try enabling logging and setting the c3p0.debugUnreturnedConnectionStackTraces property to true. Also set c3p0.unreturnedConnectionTimeout to something smaller than your average query time (1 sec?). Then any thing that takes longer than the timeout will log a stack trace. This should allow you to narrow down things pretty quickly. If there's no pattern to the stack traces, it could just be that your pool is too small. You said 100 concurrent users, but any idea how many queries per second this is? If it's 100 queries per second and you have 20 connections, then each sql execution needs to take less than 200 ms (20 connections => 20 total seconds of work per sec of wall clock time to do 100 queries)."