[GENERAL] Out of control query
I have an inventory system based on PostgreSQL 8.3.5, JBoss, Hibernate.. I have a query builder that lets users filter data in a fairly complex way. For some reason the search gets out of control and consumes all CPU. I set my statement timeout to 2 minutes and this keeps the system from going down totally, but this is happening much more frequently than it used to. I can find the problem with the individual query easily enough, but I would like a more general solution. What seems strange to me is that one one core of my Quad core x5472 is 100%. The others show minimal usage. I would think that other users would get a different core and one query could not deadlock the whole system for everyone. *Do you think this is due to my Postgres or JBoss AS configuration for database connections?* Any advice will be greatly appreciated.
Re: [GENERAL] Out of control query
On Fri, 2008-12-19 at 16:20 -0600, Jason Long wrote: I have an inventory system based on PostgreSQL 8.3.5, JBoss, Hibernate.. I have a query builder that lets users filter data in a fairly complex way. For some reason the search gets out of control and consumes all CPU. I set my statement timeout to 2 minutes and this keeps the system from going down totally, but this is happening much more frequently than it used to. I can find the problem with the individual query easily enough, but I would like a more general solution. What seems strange to me is that one one core of my Quad core x5472 is 100%. The others show minimal usage. I would think that other users would get a different core and one query could not deadlock the whole system for everyone. Do you think this is due to my Postgres or JBoss AS configuration for database connections? Any advice will be greatly appreciated. An explain analyze of the query would be useful. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of control query
Jason Long wrote: For some reason the search gets out of control and consumes all CPU. [snip] What seems strange to me is that one one core of my Quad core x5472 is 100%. The others show minimal usage. Consuming all of one CPU is quite normal if a query requires a lot of CPU-intensive work such as some types of text processing, calculations, etc. I have quite a few CPU-bound queries in the system I'm working with, and they cause no problems. I would think that other users would get a different core and one query could not deadlock the whole system for everyone. That should indeed be the case. Are your other users completely unable to run queries, or are they just too slow? Have you tested by connecting with `psql' and running queries by hand? Have you checked the pg_locks table to see if there's any possible locking issue preventing the other users from proceeding with their queries? Is your disk subsystem also working hard during these difficult queries? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
