From: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: How to determine cause of performance problem?
Date: Fri, 23 Sep 2005 08:49:27 +0200
Message-ID: <1127458167.2475.105.camel@Panoramix> (raw)
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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: J.Kraaijeveld@Askesis.nl
Subject: Re: How to determine cause of performance problem?
In-Reply-To: <1127458167.2475.105.camel@Panoramix>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox