Re: [SPAM] - Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad -

Well, just make the data source a static variable and set it up in a
static block.

ie private static Jdbc3PoolingDataSource datasource = setupDataSource();

as I said look at
http://jakarta.apache.org/commons/dbcp/

there are examples here
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/

Dave

Stéphane RIFF wrote:

> i don't understand you because it's this class that create the pool if
> i instanciate it a hundred time i'll get a hundred pools, no ?
> Do you mean i have to separate the pool and SQLoader class ?
> Can you point me to a good tutorials on pooling connection or show me
> a little example ?
> Thank you
>
> Dave Cramer wrote:
>
>> The pooling mechanism will take care of this; which brings up another
>> point. The internal pooling implementation is not production class.
>> Have a look at apache's dbcp, it is much better.
>>
>> I think this works fine as long as it isn't a singleton. Instantiate
>> the class, get a connection from the pool, do your inserts/updates,
>> return the connection and you're done. I'd probably create the sql
>> strings statically, there's no need for more than one instance of them.
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> Dave Cramer wrote:
>>>
>>>> Because every time you do pstmt_xxx = c.prepareStatement.... you
>>>> are over writing the previous one.
>>>>
>>>> Dave
>>>>
>>>> Stéphane RIFF wrote:
>>>>
>>>>> Dave Cramer wrote:
>>>>>
>>>>>> Stephane,
>>>>>>
>>>>>> You are using this class as a singleton in a multithreaded
>>>>>> environment ???
>>>>>>
>>>>>>
>>>>>> Dave
>>>>>>
>>>>>> Stéphane RIFF wrote:
>>>>>>
>>>>>>> Stéphane RIFF wrote:
>>>>>>>
>>>>>>>> Hi i have a java aplication which connect to postgresql via jdbc.
>>>>>>>> This app make a lot of request per second.
>>>>>>>> The problem is that i get delayed records between the moment
>>>>>>>> the informations arrived via socket to java and
>>>>>>>> the moment it's recorded in the database. It seems that jdbc
>>>>>>>> wait before inserting/updating the db because
>>>>>>>> they are to much requests.
>>>>>>>> For testing i made a little app in c using libpq to see if
>>>>>>>> problem persist and there no delay.
>>>>>>>>
>>>>>>>> So i'd like to know a way to speed up jdbc if possible ???
>>>>>>>>
>>>>>>>>
>>>>>>> Here my connection class, is there something wrong ?
>>>>>>>
>>>>>>> import java.io.*;
>>>>>>> import java.util.*;
>>>>>>> import java.sql.*;
>>>>>>> import java.text.*;
>>>>>>> import javax.sql.DataSource;
>>>>>>> import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
>>>>>>>
>>>>>>> import org.apache.log4j.Logger;
>>>>>>> import org.apache.log4j.PropertyConfigurator;
>>>>>>>
>>>>>>> public class SQLoader {
>>>>>>> private Jdbc3PoolingDataSource datasource ;
>>>>>>> //query object gps
>>>>>>> private PreparedStatement pstmt_gps ;
>>>>>>> //query object io
>>>>>>> private PreparedStatement pstmt_io ;
>>>>>>> //query object gps
>>>>>>> private PreparedStatement pstmt_ugps ;
>>>>>>> //query object io
>>>>>>> private PreparedStatement pstmt_uio ;
>>>>>>> //query object gps
>>>>>>> private PreparedStatement pstmt_hgps ;
>>>>>>> //query object io
>>>>>>> private PreparedStatement pstmt_hio ;
>>>>>>> //singleton class
>>>>>>> private final static SQLoader myRef = new SQLoader();
>>>>>>> //Log4j logger object
>>>>>>> private static Logger logger =
>>>>>>> Logger.getLogger(SQLoader.class.getName());
>>>>>>> //jdbc driver
>>>>>>> private String driver ;
>>>>>>> //jdbc connection string
>>>>>>> private String jdbcConnectionString ;
>>>>>>> //database user
>>>>>>> private String dbUser ;
>>>>>>> //database password
>>>>>>> private String dbPwd ;
>>>>>>> /**
>>>>>>> * Private constructor
>>>>>>> *
>>>>>>> * @since 1.0
>>>>>>> */
>>>>>>> private SQLoader() {}
>>>>>>> /**
>>>>>>> * Give access to the unique instance of this class
>>>>>>> *
>>>>>>> * @return The instance
>>>>>>> * @since 1.0
>>>>>>> */
>>>>>>> public static SQLoader getRef()
>>>>>>> {
>>>>>>> return myRef ;
>>>>>>> }
>>>>>>> public void connect(String driver,String dbc, String dbu,
>>>>>>> String dbp)
>>>>>>> {
>>>>>>> Connection m_conn = null;
>>>>>>> try {
>>>>>>> datasource = setupDataSource(dbc);
>>>>>>> }catch(Exception e){
>>>>>>> logger.fatal(e.toString());
>>>>>>> System.exit(-1);
>>>>>>> }
>>>>>>> }
>>>>>>> private void prepareQuery(Connection c)
>>>>>>> {
>>>>>>> try
>>>>>>> {
>>>>>>> //Construct predefined query
>>>>>>> String qry = "INSERT INTO gps_frame (" +
>>>>>>> "\"sbox_id\"," +
>>>>>>> "\"gps_date\"," +
>>>>>>> "\"badge_id\"," +
>>>>>>> "\"gmt_creation_date\"," +
>>>>>>>
>>>>>>> "\"wgs84_position\","+
>>>>>>> "\"speed\"," +
>>>>>>> "\"altitude\","+
>>>>>>> "\"heading\","+
>>>>>>> "\"validity\"," +
>>>>>>> "\"geom\")"
>>>>>>> + " VALUES(
>>>>>>> ?,?,?,?,?,?,?,?,?,?)";
>>>>>>> pstmt_gps = c.prepareStatement(qry);
>>>>>>>
>>>>>>> String qry1 = "INSERT INTO io_frame ("+
>>>>>>> "\"sbox_id\","+
>>>>>>> "\"gps_date\","+
>>>>>>> "\"io_type\","+
>>>>>>> "\"io_rank\","+
>>>>>>> "\"io_value\")"+
>>>>>>> " VALUES( ?,?,?,?,?)";
>>>>>>> pstmt_io = c.prepareStatement(qry1);
>>>>>>>
>>>>>>> String uqry = "UPDATE gps_frame SET "+
>>>>>>> "\"gps_date\"=?,"+
>>>>>>> "\"badge_id\"=?,"+
>>>>>>> "\"gmt_creation_date\"=?,"+
>>>>>>> "\"wgs84_position\"=?,"+
>>>>>>> "\"speed\"=?,"+
>>>>>>> "\"altitude\"=?,"+
>>>>>>> "\"heading\"=?,"+
>>>>>>> "\"validity\"=?,"+
>>>>>>> "\"geom\"=?"+
>>>>>>> " WHERE \"sbox_id\"=?";
>>>>>>> pstmt_ugps = c.prepareStatement(uqry);
>>>>>>>
>>>>>>> String uqry1 = "UPDATE io_frame SET "+
>>>>>>> "\"gps_date\"=?,"+
>>>>>>> "\"io_value\"=?"+
>>>>>>> " WHERE \"sbox_id\"=? AND
>>>>>>> \"io_rank\"=? AND io_type=?";
>>>>>>> pstmt_uio = c.prepareStatement(uqry1);
>>>>>>>
>>>>>>> qry = "INSERT INTO gps_frame_history (" +
>>>>>>> "\"sbox_id\"," +
>>>>>>> "\"gps_date\"," +
>>>>>>> "\"badge_id\"," +
>>>>>>> "\"gmt_creation_date\"," +
>>>>>>>
>>>>>>> "\"wgs84_position\","+
>>>>>>> "\"speed\"," +
>>>>>>> "\"altitude\","+
>>>>>>> "\"heading\","+
>>>>>>> "\"validity\"," +
>>>>>>> "\"geom\")"
>>>>>>> + " VALUES(
>>>>>>> ?,?,?,?,?,?,?,?,?,?)";
>>>>>>> pstmt_hgps = c.prepareStatement(qry);
>>>>>>>
>>>>>>> qry1 = "INSERT INTO io_frame_history ("+
>>>>>>> "\"sbox_id\","+
>>>>>>> "\"gps_date\","+
>>>>>>> "\"io_type\","+
>>>>>>> "\"io_rank\","+
>>>>>>> "\"io_value\")"+
>>>>>>> " VALUES( ?,?,?,?,?)";
>>>>>>> pstmt_hio = c.prepareStatement(qry1);
>>>>>>> }catch( java.sql.SQLException e)
>>>>>>> {
>>>>>>> logger.fatal(e.toString());
>>>>>>> System.exit(-1);
>>>>>>> }
>>>>>>> }
>>>>>>> /**
>>>>>>> * Disconnect from DB
>>>>>>> *
>>>>>>> * @since 1.0
>>>>>>> */
>>>>>>> public void disconnect()
>>>>>>> {
>>>>>>> try {
>>>>>>> shutdownDataSource(datasource);
>>>>>>> } catch(Exception e){
>>>>>>> logger.fatal(e.toString());
>>>>>>> System.exit(-1);
>>>>>>> }
>>>>>>> }
>>>>>>> public void saveTrame(String boxID, String badgeID,
>>>>>>> String gpsDate, double speed,
>>>>>>> String wgs84, double
>>>>>>> altitude, double azimuth,
>>>>>>> String validity, String
>>>>>>> geom, String sysDate, int[] input, int[] output)
>>>>>>> { Connection m_conn = null;
>>>>>>> try
>>>>>>> {
>>>>>>> m_conn = datasource.getConnection();
>>>>>>> m_conn.setAutoCommit(false);
>>>>>>> prepareQuery(m_conn);
>>>>>>> //set query values for update gps_frame
>>>>>>> pstmt_ugps.setString(1, gpsDate);
>>>>>>> pstmt_ugps.setString(2, badgeID);
>>>>>>> pstmt_ugps.setString(3, sysDate);
>>>>>>> pstmt_ugps.setString(4, wgs84);
>>>>>>> pstmt_ugps.setDouble(5, speed);
>>>>>>> pstmt_ugps.setDouble(6, altitude);
>>>>>>> pstmt_ugps.setDouble(7, azimuth);
>>>>>>> pstmt_ugps.setString(8, validity);
>>>>>>> pstmt_ugps.setString(9, geom);
>>>>>>> pstmt_ugps.setString(10, boxID);
>>>>>>> if(pstmt_ugps.executeUpdate()==0)
>>>>>>> { //if no frame already exists insert it
>>>>>>> pstmt_gps.setString(1, boxID);
>>>>>>> pstmt_gps.setString(2, gpsDate);
>>>>>>> pstmt_gps.setString(3, badgeID);
>>>>>>> pstmt_gps.setString(4, sysDate);
>>>>>>> pstmt_gps.setString(5, wgs84);
>>>>>>> pstmt_gps.setDouble(6, speed);
>>>>>>> pstmt_gps.setDouble(7, altitude);
>>>>>>> pstmt_gps.setDouble(8, azimuth);
>>>>>>> pstmt_gps.setString(9, validity);
>>>>>>> pstmt_gps.setString(10, geom);
>>>>>>> pstmt_gps.executeUpdate();
>>>>>>> for(int i = 0; i < input.length; i++)
>>>>>>> {
>>>>>>> pstmt_io.setString(1, boxID);
>>>>>>> pstmt_io.setString(2, gpsDate);
>>>>>>> pstmt_io.setString(3, "i");
>>>>>>> pstmt_io.setInt(4, (i+1));
>>>>>>> pstmt_io.setInt(5, input[i]);
>>>>>>> pstmt_io.executeUpdate();
>>>>>>> }
>>>>>>> for(int o = 0; o < output.length; o++)
>>>>>>> {
>>>>>>> pstmt_io.setString(1, boxID);
>>>>>>> pstmt_io.setString(2, gpsDate);
>>>>>>> pstmt_io.setString(3, "o");
>>>>>>> pstmt_io.setInt(4, (o+1));
>>>>>>> pstmt_io.setInt(5, output[o]);
>>>>>>> pstmt_io.executeUpdate();
>>>>>>> }
>>>>>>> }else
>>>>>>> { //if frame already exists in gps_frame update his io
>>>>>>> for(int i = 0; i < input.length; i++)
>>>>>>> {
>>>>>>> pstmt_uio.setString(1, gpsDate);
>>>>>>> pstmt_uio.setInt(2, input[i]);
>>>>>>> pstmt_uio.setString(3, boxID);
>>>>>>> pstmt_uio.setInt(4, (i+1));
>>>>>>> pstmt_uio.setString(5,"i");
>>>>>>> pstmt_uio.executeUpdate();
>>>>>>> }
>>>>>>> for(int o = 0; o < output.length; o++)
>>>>>>> {
>>>>>>> pstmt_uio.setString(1, gpsDate);
>>>>>>> pstmt_uio.setInt(2, output[o]);
>>>>>>> pstmt_uio.setString(3, boxID);
>>>>>>> pstmt_uio.setInt(4, (o+1));
>>>>>>> pstmt_uio.setString(5,"o");
>>>>>>> pstmt_uio.executeUpdate();
>>>>>>> }
>>>>>>> }
>>>>>>> //insert in hisory
>>>>>>> pstmt_hgps.setString(1, boxID);
>>>>>>> pstmt_hgps.setString(2, gpsDate);
>>>>>>> pstmt_hgps.setString(3, badgeID);
>>>>>>> pstmt_hgps.setString(4, sysDate);
>>>>>>> pstmt_hgps.setString(5, wgs84);
>>>>>>> pstmt_hgps.setDouble(6, speed);
>>>>>>> pstmt_hgps.setDouble(7, altitude);
>>>>>>> pstmt_hgps.setDouble(8, azimuth);
>>>>>>> pstmt_hgps.setString(9, validity);
>>>>>>> pstmt_hgps.setString(10, geom);
>>>>>>> pstmt_hgps.executeUpdate();
>>>>>>>
>>>>>>> for(int i = 0; i < input.length; i++)
>>>>>>> {
>>>>>>> pstmt_hio.setString(1, boxID);
>>>>>>> pstmt_hio.setString(2, gpsDate);
>>>>>>> pstmt_hio.setString(3, "i");
>>>>>>> pstmt_hio.setInt(4, (i+1));
>>>>>>> pstmt_hio.setInt(5, input[i]);
>>>>>>> pstmt_hio.executeUpdate();
>>>>>>> }
>>>>>>> for(int o = 0; o < output.length; o++)
>>>>>>> {
>>>>>>> pstmt_hio.setString(1, boxID);
>>>>>>> pstmt_hio.setString(2, gpsDate);
>>>>>>> pstmt_hio.setString(3, "o");
>>>>>>> pstmt_hio.setInt(4, (o+1));
>>>>>>> pstmt_hio.setInt(5, output[o]);
>>>>>>> pstmt_hio.executeUpdate();
>>>>>>> }
>>>>>>> m_conn.commit();
>>>>>>> }
>>>>>>> catch(java.sql.SQLException e){
>>>>>>> String msg = e ;
>>>>>>> logger.warn(msg);
>>>>>>> }
>>>>>>> finally {
>>>>>>> try { m_conn.close(); } catch(Exception e) { }
>>>>>>> }
>>>>>>> }
>>>>>>> public Jdbc3PoolingDataSource setupDataSource(String
>>>>>>> connectURI) {
>>>>>>> Jdbc3PoolingDataSource ds = new Jdbc3PoolingDataSource();
>>>>>>> ds.setDataSourceName("Xxx");
>>>>>>> ds.setServerName("xxx.xxx.xxx.xxx");
>>>>>>> ds.setDatabaseName("xxxxxxxxxxxxx");
>>>>>>> ds.setUser("xxxxx");
>>>>>>> ds.setPassword("xxxxx");
>>>>>>> ds.setMaxConnections(10);
>>>>>>> return ds;
>>>>>>> }
>>>>>>> public static void shutdownDataSource(DataSource ds) throws
>>>>>>> SQLException {
>>>>>>> }
>>>>>>> }
>>>>>>>
>>>>>>> When i highload postgresql with a multi-threaded serverSocket
>>>>>>> using this class,
>>>>>>> my app freezes very quickly and all my connection are in idle
>>>>>>> state.
>>>>>>> It seems that the connection aren't released.
>>>>>>>
>>>>>>> It don't know what i can do if someone could help ???
>>>>>>> Thanks
>>>>>>>
>>>>>>>
>>>>>>
>>>>> Yes i use it as a singleon what's the problem with that ???
>>>>> I instanciate One object => 1 pool for all threads and each thread
>>>>> use the saveTrame
>>>>> isn't this right ???
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>
>>> You're right but how can i do if my class is not singleton, i don't
>>> want each thread with a pool of connections
>>> i want a pool for all threads.
>>>
>>>
>>>
>>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561