Re: Excessive database queries on postgresql: village metadata queries
On 7 July 2010 21:32, Graham Leggett minf...@sharp.fm wrote: On 07 Jul 2010, at 4:43 PM, Greg Monroe wrote: 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. I had a similar issue with Sybase https://issues.apache.org/jira/browse/TORQUE-36 We worked around by creating manual prepared statements on the critical queries which was a whole bunch of trouble. https://issues.apache.org/jira/browse/TORQUE-36Joe
RE: Excessive database queries on postgresql: village metadata queries
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:minf...@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'
Re: Excessive database queries on postgresql: village metadata queries
I had a very similar problem (if not the same), using the wrong version of Postgres ODBC drivers (not from Java...). Try with a different version of the driver... Regards, Álvaro From: Greg Monroe greg.mon...@dukece.com To: Apache Torque Users List torque-user@db.apache.org Sent: Wed, July 7, 2010 11:43:34 AM Subject: RE: Excessive database queries on postgresql: village metadata queries 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:minf...@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
RE: Excessive database queries on postgresql: village metadata queries
... 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 remember dimly using a patched version of village which caches the metadata (per connection, if i remember correctly). I do not know whether this was a public version or whether this was a private hack. Anyway, unfortunately I did not find the patched version but if I remember correctly it was only a few lines of code. Thomas - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Excessive database queries on postgresql: village metadata queries
On 07.07.10 19:15, Thomas Fischer wrote: ... 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 remember dimly using a patched version of village which caches the metadata (per connection, if i remember correctly). I do not know whether this was a public version or whether this was a private hack. Anyway, unfortunately I did not find the patched version but if I remember correctly it was only a few lines of code. I was planning to do a patch release of Village anyway to have TORQUE-133 and some old Oracle BLOB-bug fixed. If you cared to dig up the patch, we could include it in a 3.3.1 release. Bye, Thomas. - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
Re: Excessive database queries on postgresql: village metadata queries
On 07 Jul 2010, at 4:43 PM, Greg Monroe wrote: 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. I've just methodically tried the postgresql 8.4 jdbc4 driver, then 8.4 jdbc3, then 8.3 jdbc4, and 8.3 jdbc3, then finally 8.2 jdbc4, and all of them cause a storm of metadata queries (I have a database with many tables, and many relationships between tables). I am beginning to suspect that the problem was introduced when torque was changed from v3.2 to v3.3. Regards, Graham -- - To unsubscribe, e-mail: torque-user-unsubscr...@db.apache.org For additional commands, e-mail: torque-user-h...@db.apache.org
