Re: How to determine cause of performance problem? - Mailing list pgsql-performance

Joost,

I presume you are using a relatively new jdbc driver. Make sure you
have added prepareThreshold=1 to the url to that it will use a named
server side prepared statement

You could also use your mod 100 code block to implement batch
processing of the inserts.

see addBatch, in  jdbc specs

Dave

On 23-Sep-05, at 2:49 AM, Joost Kraaijeveld wrote:

> Hi,
>
> I must convert an old table into a new table. The conversion goes at ~
> 100 records per second. Given the fact that I must convert 40 million
> records, it takes too much time: more hours than the 48 hour weekend I
> have for the conversion;-).
>
> The tables are rather simple: both tables only have a primary key
> constraint (of type text) and no other indexes. I only copy 3
> columns. I
> use Java for the conversion. For the exact code see below.
>
> During the conversion my processor load is almost non existant. The
> harddisk throughput is ~ 6 megabyte/second max (measured with iostat).
>
> My platform is Debian Sarge AMD64. My hardware is a Tyan Thunder K8W
> 2885 motherboard, 2 Opteron 248 processors, 2 GB RAM, a SATA bootdisk
> with / and swap, and a 3Ware 9500S-8 RAID-5 controller with 5 attached
> SATA disks with /home and /var. /var contains *all* PostgreSQL log and
> database files (default Debian installation).
>
> Output of hdparm -Tt /dev/sdb (sdb is the RAID opartition)
>
> /dev/sdb:
>  Timing cached reads:   1696 MB in  2.00 seconds = 846.86 MB/sec
>  Timing buffered disk reads:  246 MB in  3.01 seconds =  81.79 MB/sec
>
>
> I want to determine the cause of my performance problem (if it is
> one).
>
> 1. Is this a performance I can expect?
> 2. If not, how can I determine the cause?
> 3. Can I anyhow improve the performance without replacing my hardware,
> e.g. by tweaking the software?
> 4. Is there a Linux (Debian) tool that I can use to benchmark write
> performance?
>
>
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
>
> The Java code I use for the conversion  :
>
> //////////////// ....
> ResultSet resultSet = selectStatement.executeQuery(
> "select ordernummer, orderdatum, klantnummer from odbc.orders order by
> ordernummer");
>
> connection.setAutoCommit(false);
>
> PreparedStatement ordersInsertStatement =
> connection.prepareStatement("insert into prototype.orders
> (objectid,ordernumber,orderdate,customernumber) values (?,?,?,?)");
>
> while( resultSet.next() )
> {
>
> if( (++record % 100) == 0){
>     System.err.println( "handling record: " + record);
> }
>
> // the next line can do > 1.000.000 objectId/sec
> String orderObjectId = ObjectIdGenerator.newObjectId();
> ordersInsertStatement.setString(1,orderObjectId);
> ordersInsertStatement.setInt(2,resultSet.getInt("ordernummer"));
> ordersInsertStatement.setDate(3,resultSet.getDate("orderdatum"));
> ordersInsertStatement.setInt(4,resultSet.getInt("klantnummer"));
>
> ordersInsertStatement.execute();
>
> }
>
> connection.commit();
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>