pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC driver GREATLY speeded up by trivial fix
5+ messages / 3 participants
[nested] [flat]
* JDBC driver GREATLY speeded up by trivial fix
@ 2000-07-30 16:21 William Chesters <williamc@paneris.org>
0 siblings, 0 replies; 5+ messages in thread
From: William Chesters @ 2000-07-30 16:21 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: melati_development@messageboards.paneris.org
First, sorry if this has been discussed before---I'm working with
the JDBC driver from the Postgresql 7.0 distribution.
I recently had occasion to run a profiler over an app, and
discovered that it was spending a lot of its time at the following
line in org.postgresql.PG_Stream.ReceiveString:
byte[] rst = new byte[maxsiz];
The point is that maxsiz is typically 8192, while the number of bytes
actually read into the buffer is the length of a field name
(i.e. never more than about 20). The resulting performance hit is
absolutely horrendous---the fact that the poor JVM has to
zero-initialise the array is bad enough, but think of the effect on
the CPU cache and the garbage collector!
My replacement for this routine is given below. It's not obviously
the fastest possible implementation, but the effect on the performance
of database-intensive applications is just _unreal_: I mean integer
factors, rather than a few percent.
I don't want to diss the efforts of the drivers' authors, but I
couldn't help noticing a few other horrors lurking around, like in
ResultSet.getDate and .getTimestamp both doing new SimpleDateFormat
every time they are called, which is pretty expensive if you look at
Sun's sources. [Incidentally .getTimestamp is broken by the 7.0
backend ...] Is it being actively maintained? Can I help?
Best wishes,
William
private static final class FinalByteArrayOutputStream
extends ByteArrayOutputStream {
public FinalByteArrayOutputStream(int size) {
super(size);
}
}
/**
* Receives a null-terminated string from the backend. Maximum of
* maxsiz bytes - if we don't see a null, then we assume something
* has gone wrong.
*
* @param maxsiz maximum length of string
* @return string from back end
* @exception SQLException if an I/O error occurs
*/
public String ReceiveString(int maxsiz) throws SQLException
{
FinalByteArrayOutputStream buf = new FinalByteArrayOutputStream(20);
try {
for (;;) {
if (buf.size() >= maxsiz)
throw new PSQLException("postgresql.stream.toomuch");
int c = pg_input.read();
if (c < 0)
throw new PSQLException("postgresql.stream.eof");
else if (c == 0)
break;
else
buf.write(c);
}
}
catch (IOException e) {
throw new PSQLException("postgresql.stream.ioerror",e);
}
return buf.toString(0);
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: JDBC driver GREATLY speeded up by trivial fix
@ 2000-07-31 07:36 Peter Mount <petermount@it.maidstone.gov.uk>
2000-07-31 08:29 ` RE: JDBC driver GREATLY speeded up by trivial fix William Chesters <williamc@paneris.org>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Mount @ 2000-07-31 07:36 UTC (permalink / raw)
To: 'William Chesters' <williamc@paneris.org>; pgsql-interfaces; +Cc: melati_development@messageboards.paneris.org
The getTimestamp() problem has been discussed a lot on this list recently.
I'm currently having horrible problems with my home system (hasn't recovered
well after the move) so I've not been able to sit down and apply the fixes.
As for receiveString(), it predates the driver merger (some 2 years ago?) so
as it's never broke it's been left. I suspect there's a lot of little
performace gems in there if we had time to sit down and do them.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council
-----Original Message-----
From: William Chesters [mailto:williamc@paneris.org]
Sent: Sunday, July 30, 2000 5:21 PM
To: pgsql-interfaces@postgresql.org
Cc: melati_development@messageboards.paneris.org
Subject: [INTERFACES] JDBC driver GREATLY speeded up by trivial fix
First, sorry if this has been discussed before---I'm working with
the JDBC driver from the Postgresql 7.0 distribution.
I recently had occasion to run a profiler over an app, and
discovered that it was spending a lot of its time at the following
line in org.postgresql.PG_Stream.ReceiveString:
byte[] rst = new byte[maxsiz];
The point is that maxsiz is typically 8192, while the number of bytes
actually read into the buffer is the length of a field name
(i.e. never more than about 20). The resulting performance hit is
absolutely horrendous---the fact that the poor JVM has to
zero-initialise the array is bad enough, but think of the effect on
the CPU cache and the garbage collector!
My replacement for this routine is given below. It's not obviously
the fastest possible implementation, but the effect on the performance
of database-intensive applications is just _unreal_: I mean integer
factors, rather than a few percent.
I don't want to diss the efforts of the drivers' authors, but I
couldn't help noticing a few other horrors lurking around, like in
ResultSet.getDate and .getTimestamp both doing new SimpleDateFormat
every time they are called, which is pretty expensive if you look at
Sun's sources. [Incidentally .getTimestamp is broken by the 7.0
backend ...] Is it being actively maintained? Can I help?
Best wishes,
William
private static final class FinalByteArrayOutputStream
extends ByteArrayOutputStream {
public FinalByteArrayOutputStream(int size) {
super(size);
}
}
/**
* Receives a null-terminated string from the backend. Maximum of
* maxsiz bytes - if we don't see a null, then we assume something
* has gone wrong.
*
* @param maxsiz maximum length of string
* @return string from back end
* @exception SQLException if an I/O error occurs
*/
public String ReceiveString(int maxsiz) throws SQLException
{
FinalByteArrayOutputStream buf = new FinalByteArrayOutputStream(20);
try {
for (;;) {
if (buf.size() >= maxsiz)
throw new PSQLException("postgresql.stream.toomuch");
int c = pg_input.read();
if (c < 0)
throw new PSQLException("postgresql.stream.eof");
else if (c == 0)
break;
else
buf.write(c);
}
}
catch (IOException e) {
throw new PSQLException("postgresql.stream.ioerror",e);
}
return buf.toString(0);
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: JDBC driver GREATLY speeded up by trivial fix
2000-07-31 07:36 RE: JDBC driver GREATLY speeded up by trivial fix Peter Mount <petermount@it.maidstone.gov.uk>
@ 2000-07-31 08:29 ` William Chesters <williamc@paneris.org>
2000-07-31 15:02 ` Re: JDBC driver GREATLY speeded up by trivial fix Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 5+ messages in thread
From: William Chesters @ 2000-07-31 08:29 UTC (permalink / raw)
To: pgsql-interfaces; melati_development@messageboards.paneris.org
Peter Mount writes:
> As for receiveString(), it predates the driver merger (some 2 years ago?) so
> as it's never broke it's been left. I suspect there's a lot of little
> performace gems in there if we had time to sit down and do them.
Well, nothing else really leaps out the profiling data. It looks like
the effect of further improvements will be relatively marginal
compared with the unavoidable time spent on socket reads/writes, and
of course waiting for the backend to do its stuff.
> I'm currently having horrible problems with my home system (hasn't recovered
> well after the move)
sympathy ...
If you gave me a CVS p/w I could apply the ReceiveString fix and my
private fix for getTimestamp??
By the way, I've also got a rough implementation of
DatabaseMetaData.getIndexInfo---appended, with test harness---which
certainly does something, whether the JDBC-ly correct thing I don't
know!
Thanks,
William
public java.sql.ResultSet getIndexInfo(String catalog, String schema,
String table, boolean unique,
boolean approximate)
throws java.sql.SQLException {
return ((Connection)getConnection()).ExecSQL(
"SELECT " +
"null AS TABLE_CAT, null AS TABLE_SCHEMA, t.relname AS TABLE_NAME, " +
"NOT i.indisunique AS NON_UNIQUE, null AS INDEX_QUALIFIER, " +
"ti.relname AS INDEX_NAME, " + tableIndexOther + " AS TYPE, " +
"i.indkey[0] AS ORDINAL_POSITION, a.attname AS COLUMN_NAME, " +
"NULL AS ASC_OR_DESC, 0 AS CARDINALITY, 0 AS PAGES, " +
"NULL AS FILTER_CONDITION " +
"FROM " +
"pg_index i, pg_class ti, pg_class t, pg_attribute a " +
"WHERE " +
"ti.oid = i.indexrelid AND t.oid = i.indrelid AND " +
(table == null ?
"" :
"t.relname = '" + StringUtils.escaped(table, '\'') + "' AND ") +
(unique ? "i.indisunique AND " : "") +
"a.attrelid = i.indrelid AND " +
// this strange little construct is needed because
// `a.attnum = i.indkey[0]' causes 6.4.2 (at least) to fail, losing
// connection to backend:
"a.attnum - i.indkey[0] = 0");
}
public static void main(String[] args) throws Exception {
java.sql.DriverManager.registerDriver((Driver)Class.forName("postgresql.Driver").newInstance());
java.sql.Connection c = java.sql.DriverManager.getConnection("jdbc:postgresql:" + args[0], "postgres", "*");
java.sql.DatabaseMetaData m = c.getMetaData();
java.sql.ResultSet inds = m.getIndexInfo(null, "", args[1], false, true);
while (inds.next())
System.out.println("TABLE_CAT " + inds.getString("TABLE_CAT") + "\n" +
"TABLE_SCHEMA " + inds.getString("TABLE_SCHEMA") + "\n" +
"TABLE_NAME " + inds.getString("TABLE_NAME") + "\n" +
"NON_UNIQUE " + inds.getBoolean("NON_UNIQUE") + "\n" +
"INDEX_QUALIFIER " + inds.getString("INDEX_QUALIFIER") + "\n" +
"INDEX_NAME " + inds.getString("INDEX_NAME") + "\n" +
"TYPE " + inds.getShort("TYPE") + "\n" +
"ORDINAL_POSITION " + inds.getShort("ORDINAL_POSITION") + "\n" +
"COLUMN_NAME " + inds.getString("COLUMN_NAME") + "\n" +
"ASC_OR_DESC " + inds.getString("ASC_OR_DESC") + "\n" +
"CARDINALITY " + inds.getInt("CARDINALITY") + "\n" +
"PAGES " + inds.getInt("PAGES") + "\n" +
"FILTER_CONDITION " + inds.getString("FILTER_CONDITION") + "\n");
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: JDBC driver GREATLY speeded up by trivial fix
2000-07-31 07:36 RE: JDBC driver GREATLY speeded up by trivial fix Peter Mount <petermount@it.maidstone.gov.uk>
2000-07-31 08:29 ` RE: JDBC driver GREATLY speeded up by trivial fix William Chesters <williamc@paneris.org>
@ 2000-07-31 15:02 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Lockhart @ 2000-07-31 15:02 UTC (permalink / raw)
To: William Chesters <williamc@paneris.org>; +Cc: pgsql-interfaces; melati_development@messageboards.paneris.org
> If you gave me a CVS p/w I could apply the ReceiveString fix and my
> private fix for getTimestamp??
The usual procedure is to post the patches to the -patches mailing list
and someone will pick them up and apply them. Usually for JDBC stuff we
would wait for Peter to evaluate and apply them, but if he sez the
patches are appropriate then someone else will put them into the tree.
- Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: JDBC driver GREATLY speeded up by trivial fix
@ 2000-07-31 08:36 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Mount @ 2000-07-31 08:36 UTC (permalink / raw)
To: 'William Chesters' <williamc@paneris.org>; pgsql-interfaces; +Cc: Hermit Hacker (E-mail) <scrappy@hub.org>
You'll need to ask the hermit for CVS access ;-)
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council
-----Original Message-----
From: William Chesters [mailto:williamc@paneris.org]
Sent: Monday, July 31, 2000 9:30 AM
To: pgsql-interfaces@postgresql.org;
melati_development@messageboards.paneris.org
Subject: RE: [INTERFACES] JDBC driver GREATLY speeded up by trivial fix
Peter Mount writes:
> As for receiveString(), it predates the driver merger (some 2 years ago?)
so
> as it's never broke it's been left. I suspect there's a lot of little
> performace gems in there if we had time to sit down and do them.
Well, nothing else really leaps out the profiling data. It looks like
the effect of further improvements will be relatively marginal
compared with the unavoidable time spent on socket reads/writes, and
of course waiting for the backend to do its stuff.
> I'm currently having horrible problems with my home system (hasn't
recovered
> well after the move)
sympathy ...
If you gave me a CVS p/w I could apply the ReceiveString fix and my
private fix for getTimestamp??
By the way, I've also got a rough implementation of
DatabaseMetaData.getIndexInfo---appended, with test harness---which
certainly does something, whether the JDBC-ly correct thing I don't
know!
Thanks,
William
public java.sql.ResultSet getIndexInfo(String catalog, String schema,
String table, boolean unique,
boolean approximate)
throws java.sql.SQLException {
return ((Connection)getConnection()).ExecSQL(
"SELECT " +
"null AS TABLE_CAT, null AS TABLE_SCHEMA, t.relname AS TABLE_NAME,
" +
"NOT i.indisunique AS NON_UNIQUE, null AS INDEX_QUALIFIER, " +
"ti.relname AS INDEX_NAME, " + tableIndexOther + " AS TYPE, " +
"i.indkey[0] AS ORDINAL_POSITION, a.attname AS COLUMN_NAME, " +
"NULL AS ASC_OR_DESC, 0 AS CARDINALITY, 0 AS PAGES, " +
"NULL AS FILTER_CONDITION " +
"FROM " +
"pg_index i, pg_class ti, pg_class t, pg_attribute a " +
"WHERE " +
"ti.oid = i.indexrelid AND t.oid = i.indrelid AND " +
(table == null ?
"" :
"t.relname = '" + StringUtils.escaped(table, '\'') + "' AND ")
+
(unique ? "i.indisunique AND " : "") +
"a.attrelid = i.indrelid AND " +
// this strange little construct is needed because
// `a.attnum = i.indkey[0]' causes 6.4.2 (at least) to fail,
losing
// connection to backend:
"a.attnum - i.indkey[0] = 0");
}
public static void main(String[] args) throws Exception {
java.sql.DriverManager.registerDriver((Driver)Class.forName("postgresql.Driv
er").newInstance());
java.sql.Connection c =
java.sql.DriverManager.getConnection("jdbc:postgresql:" + args[0],
"postgres", "*");
java.sql.DatabaseMetaData m = c.getMetaData();
java.sql.ResultSet inds = m.getIndexInfo(null, "", args[1], false,
true);
while (inds.next())
System.out.println("TABLE_CAT " + inds.getString("TABLE_CAT") + "\n" +
"TABLE_SCHEMA " + inds.getString("TABLE_SCHEMA") +
"\n" +
"TABLE_NAME " + inds.getString("TABLE_NAME") + "\n"
+
"NON_UNIQUE " + inds.getBoolean("NON_UNIQUE") +
"\n" +
"INDEX_QUALIFIER " +
inds.getString("INDEX_QUALIFIER") + "\n" +
"INDEX_NAME " + inds.getString("INDEX_NAME") + "\n"
+
"TYPE " + inds.getShort("TYPE") + "\n" +
"ORDINAL_POSITION " +
inds.getShort("ORDINAL_POSITION") + "\n" +
"COLUMN_NAME " + inds.getString("COLUMN_NAME") +
"\n" +
"ASC_OR_DESC " + inds.getString("ASC_OR_DESC") +
"\n" +
"CARDINALITY " + inds.getInt("CARDINALITY") + "\n"
+
"PAGES " + inds.getInt("PAGES") + "\n" +
"FILTER_CONDITION " +
inds.getString("FILTER_CONDITION") + "\n");
}
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-07-31 15:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-30 16:21 JDBC driver GREATLY speeded up by trivial fix William Chesters <williamc@paneris.org>
2000-07-31 07:36 RE: JDBC driver GREATLY speeded up by trivial fix Peter Mount <petermount@it.maidstone.gov.uk>
2000-07-31 08:29 ` William Chesters <williamc@paneris.org>
2000-07-31 15:02 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2000-07-31 08:36 RE: JDBC driver GREATLY speeded up by trivial fix Peter Mount <petermount@it.maidstone.gov.uk>
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