Re: JDBC HighLoad - Mailing list pgsql-jdbc

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


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