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

From: Stéphane RIFF <stephane(dot)riff(at)cerene(dot)fr>
To: pg(at)fastcrypt(dot)com
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s)
Date: 2005-01-28 13:46:16
Message-ID: 41FA4228.7050807@cerene.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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.

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.4 - Release Date: 25/01/2005

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2005-01-28 14:59:11 Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s)
Previous Message Dave Cramer 2005-01-28 13:21:18 Re: [SPAM] - Re: JDBC HighLoad - Found word(s) XXX in the