Re: [SPAM] - Re: JDBC HighLoad - Found word(s) XXX in the - Mailing list pgsql-jdbc

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


--
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