Re: [SPAM] - Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - - Mailing list pgsql-jdbc

Well, just make the data source a static variable and set it up in a
static block.

ie  private static Jdbc3PoolingDataSource datasource = setupDataSource();

as I said look at
http://jakarta.apache.org/commons/dbcp/

there are examples here
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/

Dave

Stéphane RIFF wrote:

> i don't understand you because it's this class that create the pool if
> i instanciate it a hundred time i'll get a hundred pools, no ?
> Do you mean i have to separate the pool and SQLoader class ?
> Can you point me to a good tutorials on pooling connection or show me
> a little example ?
> Thank you
>
> Dave Cramer wrote:
>
>> 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