db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Monroe <Greg.Mon...@dukece.com>
Subject RE: Excessive database queries on postgresql: village metadata queries
Date Wed, 07 Jul 2010 14:43:34 GMT
Torque has been using Village pretty much since day 1.. and AFAIK the 
Village has been using metadata as long.  If you didn't have performance
problems before, it's probably a JDBC version problem.

FWIW, getting rid of Village (or highly modifying it) is a 4.0 goal.  But
AFAIK, the Village use of metadata is a sticky problem to tackle.  It's used
to determine what type of object to use in retrieving columns.  Probably 
the correct fix is to use the *Map classes to do this and not the DB Meta
data.

It looks like the actual call to the ResultSet.getMetatData() is only done
once per query. Plus, there is a todo item in on the PostGres JDBC website
about caching the metadata rather than doing selects for each column.

IMHO, it's a PostGres JDBC driver issue, since other drivers seem to cache
this info.

> -----Original Message-----
> From: Graham Leggett [mailto:minfrin@sharp.fm]
> Sent: Tuesday, July 06, 2010 9:19 PM
> To: torque-user@db.apache.org
> Subject: Excessive database queries on postgresql: village metadata
> queries
> 
> Hi all,
> 
> I have been trying to hunt down a performance issue with an existing
> torque based system against a postgresql database. Interspersed
> between each query are thousands and thousands of metadata queries,
> which have slowed our database down by a few orders of magnitude.
> 
> I have managed to trace the key problems down to
> com.workingdogs.village.Column.populate(ResultSetMetaData rsmd, int
> colNum, String tableName), where the following lines cause SQL queries
> to be generated to the database as below:
> 
>          this.columnTypeName = rsmd.getColumnTypeName(columnNumber);
>          this.columnType = rsmd.getColumnType(columnNumber);
>          this.nullAllowed = rsmd.isNullable(columnNumber) == 1;
>          this.autoIncrement = rsmd.isAutoIncrement(columnNumber);
> 
> Has anyone encountered this before and managed to work out how to stop
> the metadata queries being translated into database queries? I would
> expect either the JDBC driver to cache these, or if not for Village to
> cache these.
> 
> I am not 100% of the exact change that introduced this problem, it
> could have been a move from torque v3.2 to torque v3.3, alternatively
> it could have been an update in JDBC driver to
> postgresql-8.4-701.jdbc4.jar. The problem was picked up after a large
> refactoring job, so I am not 100% sure of the cause unfortunately.
> 
> Has anyone seen this problem before?
> 
> A sample of the queries include the following:
> 
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '4'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '4'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '5'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '5'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '8'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '8'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '9'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '9'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '7'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '7'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '10'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '10'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
> FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '6'
> 2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
> FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
> (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
> (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
> a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
> 2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '6'
> 
> Regards,
> Graham
> --
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org

DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message