Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s)

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