Re: JDBC HighLoad - Mailing list pgsql-jdbc

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

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