Thread: Long-running query blocks all other queries
Hello, I want to run a long running query in background to collect statistics. I.e. how many users played the game last month more than 50 times. SELECT count(*) FROM (SELECT user_id, COUNT(*) AS total_games FROM games, game2user WHERE games.finished > '2009-02-19' AND games.id = game2user.game_id GROUP BY user_id) AS aaa WHERE aaa.total_games > 50; I have 4-cored machine, so I expected the query to be able to run for a long time without performance degrade for application. But the query has completely blocked all other queries. Even those, which are not related to tables, used by the long-running query. Why does this block happen and how to aid it? Thanks.
In response to "Anton V. Belyaev" <anton.belyaev@gmail.com>: > Hello, > > I want to run a long running query in background to collect > statistics. I.e. how many users played the game last month more than > 50 times. > > SELECT count(*) FROM (SELECT user_id, COUNT(*) AS total_games FROM > games, game2user WHERE games.finished > '2009-02-19' AND games.id = > game2user.game_id GROUP BY user_id) AS aaa WHERE aaa.total_games > 50; > > I have 4-cored machine, so I expected the query to be able to run for > a long time without performance degrade for application. > > But the query has completely blocked all other queries. Even those, > which are not related to tables, used by the long-running query. > > Why does this block happen and how to aid it? Off the top of my head, I can't think of any reason why that query would block anything else. Start with SELECT * FROM pg_locks; to see if there are actually any locks occurring that could be causing problems. If there are no explicit locks, you may be looking at IO starvation. All the cores in the world won't help you if your disks are too slow to feed the required information. Have you investigated this possibility yet? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Thu, Mar 19, 2009 at 8:42 AM, Anton V. Belyaev <anton.belyaev@gmail.com> wrote: > I have 4-cored machine, so I expected the query to be able to run for > a long time without performance degrade for application. As Bill mentioned, this won't help a bit if you've got a slow / dodgy / overloaded IO subsystem. What does vmstat 1 60 have to say about the situation when the queries are running?