On 13/04/2010, at 5:38 AM, Daniel Roy wrote: > Thanks for the quick replies... > > This is a select from a single table with no relationships. We have done > vacuum analyze against the affected databases but the problem remains. The > problem happens against single tables as well as tables with joins...I'll > spend some time dropping and re-creating the index for the each of the > affected tables to see if that resolves the problem...
Postgres has a REINDEX maintenance command to rebuild indexes, this will save you the trouble of dropping and recreating. Run an 'explain analyze select ....' for your query. This will tell you where the time is being spent in your query plan. If you don't know how to interpret the results, send it to me and I will try to explain. > Daniel Roy > [email protected] > Software Developer > > > On 2010-04-12, at 3:23 PM, Chuck Hill wrote: > >> >> On Apr 12, 2010, at 12:19 PM, Daniel Roy wrote: >> >>> Hi, >>> >>> We are experiencing some strange behaviour from PostgreSQL and our >>> applications. Recently, various queries have started to exceed the warn >>> trace timeout set by >>> er.extensions.ERXAdaptorChannelDelegate.trace.milliSeconds.warn. Some of >>> the most basic select statements against a table with 3 rows are taking >>> upwards of 6 seconds to execute each, >> >> >> Is that a simple select against one table, or are there joins to other >> tables? If just a single table, it could be index corruption. Dropping and >> re-creating the index should fix that. If there are joins to other tables, >> you could be missing an index needed for optimization. >> >> >> Chuck >> >> >>> where they should easily be well under that. We set the warn flag to 2.5 >>> seconds. >>> >>> A partial stacktrace is shown below: >>> >>> java.lang.RuntimeException: Statement running too long >>> at >>> er.extensions.eof.ERXEOAccessUtilities.logExpression(ERXEOAccessUtilities.java:1203) >>> at >>> er.extensions.eof.ERXAdaptorChannelDelegate.adaptorChannelDidEvaluateExpression(ERXAdaptorChannelDelegate.java:88) >>> at sun.reflect.GeneratedMethodAccessor301.invoke(Unknown Source) >>> at >>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) >>> at java.lang.reflect.Method.invoke(Method.java:597) >>> at >>> com.webobjects.foundation.NSSelector._safeInvokeMethod(NSSelector.java:122) >>> at com.webobjects.foundation._NSDelegate._perform(_NSDelegate.java:223) >>> at com.webobjects.foundation._NSDelegate.perform(_NSDelegate.java:163) >>> at >>> com.webobjects.jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:377) >>> at >>> com.webobjects.jdbcadaptor.JDBCChannel.evaluateExpression(JDBCChannel.java:296) >>> at >>> com.webobjects.jdbcadaptor.JDBCChannel.selectAttributes(JDBCChannel.java:220) >>> at >>> er.extensions.jdbc.ERXJDBCAdaptor$Channel.selectAttributes(ERXJDBCAdaptor.java:150) >>> at >>> com.webobjects.eoaccess.EODatabaseChannel._selectWithFetchSpecificationEditingContext(EODatabaseChannel.java:897) >>> at >>> com.webobjects.eoaccess.EODatabaseChannel.selectObjectsWithFetchSpecification(EODatabaseChannel.java:234) >>> at >>> com.webobjects.eoaccess.EODatabaseContext._objectsWithFetchSpecificationEditingContext(EODatabaseContext.java:3055) >>> at >>> er.extensions.eof.ERXDatabaseContext._objectsWithFetchSpecificationEditingContext(ERXDatabaseContext.java:57) >>> at >>> com.webobjects.eoaccess.EODatabaseContext.objectsWithFetchSpecification(EODatabaseContext.java:3195) >>> at >>> com.webobjects.eocontrol.EOObjectStoreCoordinator.objectsWithFetchSpecification(EOObjectStoreCoordinator.java:488) >>> at >>> com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4069) >>> at >>> er.extensions.eof.ERXEC.objectsWithFetchSpecification(ERXEC.java:1114) >>> at >>> com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4444) >>> at >>> com.webobjects.eoaccess.EOUtilities.objectWithPrimaryKey(EOUtilities.java:461) >>> at >>> com.webobjects.eoaccess.EOUtilities.objectWithPrimaryKeyValue(EOUtilities.java:432) >>> >>> Has anyone experienced problems with PostgreSQL and basic statements timing >>> out? What might be the best way to troubleshoot this problem? We have >>> tried various JDBC driver versions, tuned the PostgreSQL configuration, >>> enabled ERXJDBCAdaptor.useConnectionBroker and enabled 5 connections.... >>> >>> We have not changed the production PostgreSQL version (8.4.1) recently, and >>> we run the latest 5.4 Wonder frameworks from the Hudson builds. >>> >>> >>> Daniel Roy >>> [email protected] >>> Software Developer >>> >>> >>> _______________________________________________ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-deploy mailing list ([email protected]) >>> Help/Unsubscribe/Update your Subscription: >>> http://lists.apple.com/mailman/options/webobjects-deploy/chill%40global-village.net >>> >>> This email sent to [email protected] >> >> -- >> Chuck Hill Senior Consultant / VP Development >> >> Practical WebObjects - for developers who want to increase their overall >> knowledge of WebObjects or who are trying to solve specific problems. >> http://www.global-village.net/products/practical_webobjects >> >> >> >> >> >> >> > > _______________________________________________ > Do not post admin requests to the list. They will be ignored. > Webobjects-deploy mailing list ([email protected]) > Help/Unsubscribe/Update your Subscription: > http://lists.apple.com/mailman/options/webobjects-deploy/qdolan%40gmail.com > > This email sent to [email protected] -- Seeya...Q Quinton Dolan - [email protected] Gold Coast, QLD, Australia (GMT+10)
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-deploy mailing list ([email protected]) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-deploy/archive%40mail-archive.com This email sent to [email protected]
