On Apr 8, 2013, at 8:46 PM, Jeff Janes wrote: > On Monday, April 8, 2013, Ramsey Gurley wrote: > Hi all, > > I'm having issues with slow queries using postgres, and I'm finding some of > the issues difficult to reproduce. My application logs slow queries for me, > but often, when I go back to run explain analyze on the query it is very > fast. I assume this is due to some sort of caching. Here is an example: > > sqlLogging _valuesForSQL ran 116509ms with sql string: > > I think that your IO subsystem is getting congested on occasion, and that you > have a "selection bias". You are seeing in your log the occasions on which > it is running slow, but not seeing the occasions it is running fast. When > you run it manually, you see a result similar to the (invisible, but > frequent) times that it did not run slow.
I think you're right about this. It seems to run fast most of the time. It's those times it takes 2 minutes to respond to a customer which I'd like to minimize or eliminate. But I think caching has a bit to do with it too. I can go back to the logs from last week and run a slow query and it will frequently be equally slow again. Not always, but enough that I think something else is wrong with my setup. > > You should monitor with sar, or iostat, or vmstat, or similar. I'll look into that. Thanks Jeff :) > > Cheers, > > Jeff
