How to determine cause of performance problem? - Mailing list pgsql-performance
From | Joost Kraaijeveld |
---|---|
Subject | How to determine cause of performance problem? |
Date | |
Msg-id | 1127458167.2475.105.camel@Panoramix Whole thread Raw |
Responses |
Re: How to determine cause of performance problem?
Re: How to determine cause of performance problem? Re: How to determine cause of performance problem? |
List | pgsql-performance |
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();
pgsql-performance by date: