Make DBCP Connection Pool Using Tomcat Resource Context

Setup JDBC DBCP (data base connection pooling) for use with any Java class.

Reference:

http://code.google.com/p/parsecsv2sql/source/browse/trunk/Csv2Sql/src/org/gonevertical/dts/lib/pooling/SetupInitialContext.java

How I set it up:

public DatabaseConnection(boolean pool) {

File executionlocation = null;

try {

executionlocation = new File(DatabaseConnection.class.getProtectionDomain().getCodeSource().getLocation().toURI());

} catch (URISyntaxException e) {

e.printStackTrace();

}

String execPath = executionlocation.getParent();

execPath = StringUtil.getValue("(.*?)/war", execPath);

String path = execPath + "/war/META-INF/context.xml";

String contextXmlPath = path;

String tmpPath = "/home/branflake2267/tmp";

// make my own context

SetupInitialContext ic = new SetupInitialContext(tmpPath);

ic.setContextXmlFileLocation(contextXmlPath);

ic.run();

context = ic.getInitialContext();

}

Rough Java DBCP Connection Pool Context Example Setup

Setting up initial context for DBCP connection pool.

public static void test() {

System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");

System.setProperty(Context.PROVIDER_URL, "file:///Users/branflake2267/tmp");

InitialContext ic = null;

try {

ic = new InitialContext();

} catch (NamingException e) {

e.printStackTrace();

}

// Construct BasicDataSource reference

Reference ref = new Reference("javax.sql.DataSource", "org.apache.commons.dbcp.BasicDataSourceFactory", null);

ref.add(new StringRefAddr("driverClassName", "com.mysql.jdbc.Driver"));

ref.add(new StringRefAddr("type", "javax.sql.DataSource"));

ref.add(new StringRefAddr("url", "jdbc:mysql://ark/system?autoReconnect=true"));

ref.add(new StringRefAddr("username", "Web"));

ref.add(new StringRefAddr("password", "pass*7"));

ref.add(new StringRefAddr("removeAbandoned", "true"));

ref.add(new StringRefAddr("removeAbandonedTimeout", "90"));

ref.add(new StringRefAddr("logAbandoned", "true"));

ref.add(new StringRefAddr("maxActive", "1000"));

ref.add(new StringRefAddr("maxIdle", "30"));

ref.add(new StringRefAddr("maxWait", "900"));

try {

ic.rebind("jdbc/basic", ref);

} catch (NamingException e) {

e.printStackTrace();

}

InitialContext ic2 = null;

try {

ic2 = new InitialContext();

} catch (NamingException e) {

e.printStackTrace();

}

DataSource ds = null;

try {

ds = (DataSource) ic2.lookup("jdbc/basic");

} catch (NamingException e) {

e.printStackTrace();

}

Connection conn = null;

try {

conn = ds.getConnection();

} catch (SQLException e) {

e.printStackTrace();

}

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

MySql Communication Failure and Aborted Connections

Have you got an error like this using MySql?

1. Communications link failure

2. Aborted connection

3. Last packet sent to the server was 0 ms ago.

4. Too many connections

Things you can do to figure out whats going on with mysql connections.

1. See how many connections are currently connected with this query: SHOW PROCESSLIST;

2. Check /etc/mysql/my.cnf max connection limit: max_connections = 100 #100 by default

3. Are you running out of file descriptors, check by: ulimit -a #by default its a 1000 on Ubuntu

4. Run your mysql queries and check the active sockets: netstat -na | grep 3306

5. set the file descriptor limits in /etc/security/limits.conf

When rapidly connecting and reconnecting to mysql database you may find that you run out of max connections to start with. The next thing you run out of is file descriptors because each time you connect and cleanly close the tcp socket it will wait to close the connection so that stray packets to not wonder in after you closed it. You can see if this is happening by checking ulimit -a and then watching how many are in time_wait with netstat -na while running your queries.

When you run out of file descriptors (sockets) to open up, mysql will crash and abort all the connections which could be caused on the sending and/or receiving server. MySql can handle thousands of connections at one time, although it may not be the most efficient process due to the authorization and setup cost, it gets the job done. I would recommend using connection pooling which saves time with reuse and keeping them ready to go when needed.

If you want to solve the communications failure with out pooling, raise the file descriptors limit to over 24000 or whatever number you determine suite your needs. Remember you will need alot more file descriptors than actual connections because of time_waits after a socket closes.

MySql Open Tables and Max Linux Open Files

Ubuntu linux default max open files (limit) is 1000 and if your hitting mysql with huge amount of queries at one time, you may hit the open files limit. I also run into this problem with development with eclipse and having to open so many java classes.

Reference to whats happening

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

Check to see what the limits are:

ulimit -a

Change the open files limit:

sudo pico /etc/security/limits.conf

#add - the file open integers are huge due to my deployment, choose your own

mysql soft nofile 24000

mysql hard nofile 32000

sudo reboot

Two Gateways on Ubuntu, Split access.

I set my Ubuntu server with to private gateways, which had access too two public ip addresses. I wanted to receive requests to the web tomcat and apache servers from both networks. This is how I configured my split access on my server to have traffic come in through both private subnets and sent out through the ip that it came in on.

#!/bin/sh

# split access

# http://lartc.org/howto/lartc.rpdb.multiple-links.html#AEN268

# http://tldp.org/HOWTO/Adv-Routing-HOWTO/lartc.rpdb.multiple-links.html

# GoneVertical.org

# 1. One creates two additional routing tables, say T1 and T2. These are added in /etc/iproute2/rt_tables. Then you set up routing in these tables as follows:

# echo 1 T1 >> /etc/iproute2/rt_tables

# echo 2 T2 >> /etc/iproute2/rt_tables

# interface

IF0=lo

IF1=eth0

IF2=eth1

# ips

IP1=192.168.12.100

IP2=192.168.10.100

# gateways

P1=192.168.12.1

P2=192.168.10.1

# ip network

P0_NET=0.0.0.0

P1_NET=192.168.12.0

P2_NET=192.168.10.0

#echo $IF0 $IF1 $IF2

#echo $IP1 $IP2

#echo $P0_NET $P1_NET $P2_NET

# create routing tables

ip route add $P1_NET dev $IF1 src $IP1 table T1

ip route add default via $P1 table T1

ip route add $P2_NET dev $IF2 src $IP2 table T2

ip route add default via $P2 table T2

# create routing for local requests

# not sure if i need this

#ip route add $P0_NET dev $IF0 table T1

#ip route add $P2_NET dev $IF2 table T1

#ip route add 127.0.0.0/8 dev lo table T1

#ip route add $P0_NET dev $IF0 table T2

#ip route add $P1_NET dev $IF1 table T2

#ip route add 127.0.0.0/8 dev lo table T2

# main routing table

ip route add $P1_NET dev $IF1 src $IP1

ip route add $P2_NET dev $IF2 src $IP2

# default route preference

ip route add default via $P1

# routing rules

ip rule add from $IP1 table T1

ip rule add from $IP2 table T2