Re: [SPAM] - Re: [SPAM] - Re: JDBC HighLoad - Found word(s)

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.



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