Thread: random delays
Hi, I noticed that sometimes we seem to have a problem with simple queries that take a long time to execute. For example "select * from config where key='abc'" which normally only takes a few milliseconds, but in some rare cases suddenly takes 10-30 seconds. It is a production system, so there are always other queries going on. But the server is not neccessarly loaded when this happens. I can't think of any reasons that there should be a lock on the tables in question. Is there a way to find out what the query is waiting for? We are using postgresql 8.0.3. Thanks, Baldur
On Fri, Dec 09, 2005 at 02:29:02PM +0100, Baldur Norddahl wrote: > I noticed that sometimes we seem to have a problem with simple queries > that take a long time to execute. For example "select * from config > where key='abc'" which normally only takes a few milliseconds, but in > some rare cases suddenly takes 10-30 seconds. > > It is a production system, so there are always other queries going on. > But the server is not neccessarly loaded when this happens. I can't > think of any reasons that there should be a lock on the tables in question. Do you know there's a lock or is that just a guess? > Is there a way to find out what the query is waiting for? Query pg_locks in another session and look for rows where "granted" is false; if you find any then look for who holds the conflicting lock. If you have stats_command_string enabled then you might be able to query pg_stat_activity and identify the query that holds the lock. Do you see any pattern to the slow queries? Do they always happen around a certain time of day, or during or shortly after a certain kind of activity (e.g., large inserts or routine database maintenace)? -- Michael Fuhr
On Fri, 2005-12-09 at 14:29 +0100, Baldur Norddahl wrote: > I noticed that sometimes we seem to have a problem with simple queries > that take a long time to execute. For example "select * from config > where key='abc'" which normally only takes a few milliseconds, but in > some rare cases suddenly takes 10-30 seconds. > > It is a production system, so there are always other queries going on. > But the server is not neccessarly loaded when this happens. I can't > think of any reasons that there should be a lock on the tables in question. You'll find response times spike somewhat during Checkpoints. We've been working to reduce this effect for some time and there is still more work to be done. Tune your wal settings and bgwriter to improve things, plus upgrade to 8.1 which has made leaps forward with this issue. Best Regards, Simon Riggs
Simon Riggs <simon@2ndquadrant.com> writes: > You'll find response times spike somewhat during Checkpoints. We've been > working to reduce this effect for some time and there is still more work > to be done. > > Tune your wal settings and bgwriter to improve things, plus upgrade to > 8.1 which has made leaps forward with this issue. If that's the problem presumably moving the WAL segments to a dedicated set of spindles would help? It seems to me it would take a pretty severe case of this to cause 10-20s delays though. You aren't perchance running VACUUM FULL or any DDL like REINDEX or CLUSTER periodically are you? -- greg
Welcome Simon Riggs wrote: > > Tune your wal settings and bgwriter to improve things, plus upgrade to > 8.1 which has made leaps forward with this issue. > What is best settings for that case? I have the same on 7.4 but for me it's not critical issue or big problem. I have database (size 2-3 GB) with image from few kb to 20MB. > Best Regards, Simon Riggs > Best Regards GP > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >