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