db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Graham Leggett <minf...@sharp.fm>
Subject Excessive database queries on postgresql: village metadata queries
Date Wed, 07 Jul 2010 01:18:44 GMT
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


Mime
View raw message